sql高级用法之窗口函数

窗口函数(Window Functions)

贴上我写的sql

需求

SELECT  DISTINCT patients.* FROM "patients" LEFT OUTER JOIN "hospitals" 
ON "hospitals"."id" = "patients"."hospital_id" WHERE "patients"."pc_confirmed" = true
AND "hospitals"."id" in (4,6,66,32,34,65)
AND (
patients.id in (
    SELECT patient_id FROM
    (
        SELECT *,row_number() over(PARTITION by patient_id order by visit_date asc) as first_order FROM 
        (
            SELECT patient_id,psa,visit_date, 
                lag(psa,1) OVER(partition by patient_id order by visit_date asc) AS prev_psa, 
                lag(visit_date,1) OVER(partition by patient_id order by visit_date asc) AS prev_visit_date 
            FROM follow_up_visits
            where patient_id in (select patient_id from operations where gsum >= 8 AND is_radical_operation = true) 
        ) follow_up_visits
        WHERE psa > prev_psa AND prev_psa > 0.2 AND prev_visit_date > (select operation_date from operations where patient_id = follow_up_visits.patient_id)
    ) follow_up_visits
    where first_order = 1 AND patient_id = (
        SELECT patient_id FROM
        (
            SELECT *,ABS(start_date -first_date),ABS(end_date -first_date) as abs1 FROM
            (
                SELECT * FROM
                (
                    SELECT patient_id,start_date,end_date,first_value(start_date) over(ORDER BY start_date asc) as first_date
                    FROM endocrines WHERE parent_id is null AND soft_deleted_at is null AND patient_id = follow_up_visits.patient_id AND start_date > follow_up_visits.visit_date
                ) endocrines
                ORDER BY start_date desc limit 1
            ) endocrines
        ) endocrines
        WHERE abs >= 547 OR abs1 >= 547
    )
)
 OR patients.id in (
    SELECT patient_id FROM
    (
        SELECT *,row_number() over(PARTITION by patient_id order by visit_date asc) as first_order FROM 
        (
            SELECT patient_id,psa,visit_date, 
                lag(psa,1) OVER(partition by patient_id order by visit_date asc) AS prev_psa, 
                lag(visit_date,1) OVER(partition by patient_id order by visit_date asc) AS prev_visit_date 
            FROM follow_up_visits
            where patient_id in (select patient_id from operations where gsum >= 8 AND is_radical_operation = true) 
        ) follow_up_visits
        WHERE psa > prev_psa AND prev_psa > 0.2 AND prev_visit_date > (select operation_date from operations where patient_id = follow_up_visits.patient_id)
    ) follow_up_visits
    where first_order = 1 AND patient_id = (

        SELECT patient_id FROM
        (
            SELECT * FROM
            (
                SELECT *,ABS(start_date -first_date),ABS(end_date -first_date) as abs1 FROM
                (
                    SELECT * FROM
                    (
                        SELECT patient_id,start_date,end_date,first_value(start_date) over(ORDER BY start_date asc) as first_date
                        FROM endocrines WHERE parent_id is null AND soft_deleted_at is null AND patient_id = follow_up_visits.patient_id AND start_date > follow_up_visits.visit_date
                    ) endocrines
                    ORDER BY start_date desc limit 1
                ) endocrines
            ) endocrines
            WHERE not abs >= 547 OR not abs1 >= 547
        ) endocrines
        WHERE patient_id = (
            SELECT patient_id FROM follow_up_visits WHERE patient_id = endocrines.patient_id AND psa <= 4 AND psa >= 2 AND visit_date >= endocrines.first_date AND (not visit_date > endocrines.start_date or not visit_date > endocrines.end_date) limit 1
            )
        )
)
OR patients.id in (
    SELECT patient_id FROM
    (
        SELECT * FROM
        (
            SELECT *,ABS(start_date -first_date),ABS(end_date -first_date) as abs1 FROM
            (
                SELECT * FROM
                (
                    SELECT patient_id,start_date,end_date,first_value(start_date) over(ORDER BY start_date asc) as first_date
                    FROM endocrines WHERE parent_id is null AND soft_deleted_at is null
                ) endocrines

            ) endocrines
        ) endocrines
        WHERE abs >= 365 OR abs1 >= 365
    ) endocrines
    WHERE patient_id = (
        SELECT patient_id FROM follow_up_visits WHERE patient_id = endocrines.patient_id AND psa <= 4 AND psa >= 2 AND visit_date >= endocrines.first_date AND (not visit_date > endocrines.start_date or not visit_date > endocrines.end_date) limit 1
        ) AND (
            (select true from operations where is_radical_operation = true AND patient_id = endocrines.patient_id) 
            OR 
            (select true from radiotherapies where target = '根治性放疗(前列腺)' AND patient_id = endocrines.patient_id limit 1) 
        )

)
OR patients.id in (
    SELECT patient_id FROM
    (
        SELECT * FROM
        (
            SELECT *,ABS(start_date -first_date),ABS(end_date -first_date) as abs1 FROM
            (
                SELECT * FROM
                (
                    SELECT patient_id,start_date,end_date,first_value(start_date) over(ORDER BY start_date asc) as first_date
                    FROM endocrines WHERE parent_id is null AND soft_deleted_at is null
                ) endocrines

            ) endocrines
        ) endocrines
        WHERE abs >= 365 OR abs1 >= 365
    ) endocrines
    WHERE patient_id = (
        SELECT patient_id FROM follow_up_visits WHERE patient_id = endocrines.patient_id AND psa <= 4 AND psa >= 2 AND visit_date >= endocrines.first_date AND (not visit_date > endocrines.start_date or not visit_date > endocrines.end_date) limit 1
        ) AND (
            (select false from operations where is_radical_operation = true AND patient_id = endocrines.patient_id) 
            OR 
            (select false from radiotherapies where target = '根治性放疗(前列腺)' AND patient_id = endocrines.patient_id limit 1) 
        )

)
)
AND "patients"."deleted_at" IS NULL 
AND (deleted_at is null);