stats.py 文件源码

def patients_by_recruitment_group(group):
    """
    Calculate the number of patients each group has recruited to
    the specified group.

    The recruiting group is the first group to add the patient to
    the group we're querying. In practice this is the membership record
    with the earliest from date.
    """

    # Use a window function to find the group that recruited each
    # patient. The recruiting group is the group that created the
    # earliest membership record (determined by from date). The query
    # is filtered by the specified group and the distinct clause ensures
    # we only get one result per patient.
    first_created_group_id_column = func.first_value(GroupPatient.created_group_id)\
        .over(partition_by=GroupPatient.patient_id, order_by=GroupPatient.from_date)\
        .label('created_group_id')
    q1 = db.session.query(first_created_group_id_column)
    q1 = q1.distinct(GroupPatient.patient_id)
    q1 = q1.join(GroupPatient.patient)
    q1 = q1.filter(GroupPatient.group_id == group.id)
    q1 = q1.filter(Patient.test == false())

    if group.type == GROUP_TYPE.SYSTEM:
        q1 = q1.filter(Patient.current(group) == true())
    else:
        q1 = q1.filter(Patient.current() == true())

    q1 = q1.subquery()

    # Aggregate the results by recruiting group to get the number of
    # patients recruited by each group.
    created_group_id_column = q1.c.created_group_id.label('created_group_id')
    patient_count_column = func.count().label('patient_count')
    q2 = db.session.query(created_group_id_column, patient_count_column)\
        .group_by(created_group_id_column)\
        .subquery()

    # Join the results with the groups table.
    q3 = db.session.query(Group, q2.c.patient_count)\
        .join(q2, Group.id == q2.c.created_group_id)\
        .order_by(Group.id)

    return q3.all()