Длинные рецепты - оконные выражения (wo_pwd)

WITH rec_mm_3_4_2022 AS
     (SELECT "ИД пациента" AS patient_id_mm_3_4_2022,
             "Препарат по МНН" AS drug_name_mm_3_4_2022,
             "Дата выписки рецепта" AS date_mm_3_4_2022,
             MIN("Дата выписки рецепта") OVER (PARTITION BY "ИД пациента") AS min_date_mm_3_4_2022,
             MAX("Дата выписки рецепта") OVER (PARTITION BY "ИД пациента") AS max_date_mm_3_4_2022,
             COUNT("Дата выписки рецепта") OVER (PARTITION BY "ИД пациента") AS count_recipes_mm_3_4_2022,
             MIN("Дата выписки рецепта") OVER (PARTITION BY "ИД пациента", "Препарат по МНН") AS drug_min_date_mm_3_4_2022,
             MAX("Дата выписки рецепта") OVER (PARTITION BY "ИД пациента", "Препарат по МНН") AS drug_max_date_mm_3_4_2022,
             COUNT("Дата выписки рецепта") OVER (PARTITION BY "ИД пациента", "Препарат по МНН") AS count_drug_recipes_mm_3_4_2022
      FROM public.recipes
      WHERE mo_type LIKE 'Взрослая'
            AND doctor_or_paramedic LIKE 'Врач'
            AND "Дата выписки рецепта" >= ''' + mm_3_4_2022_start + '''
            AND "Дата выписки рецепта" < ''' + mm_3_4_2022_end + '''
      ORDER BY "ИД пациента"),

     t_visits_mm_3_4_2022 AS
     (SELECT "ИД пациента" AS patient_id_mm_3_4_2022_2,
             COUNT(DISTINCT "Дата выписки рецепта") AS visits_mm_3_4_2022
      FROM public.recipes
      WHERE mo_type LIKE 'Взрослая'
            AND doctor_or_paramedic LIKE 'Врач'
            AND "Дата выписки рецепта" >= ''' + mm_3_4_2022_start + '''
            AND "Дата выписки рецепта" < ''' + mm_3_4_2022_end + '''
      GROUP BY "ИД пациента"),

      total_2022 AS
      (SELECT patient_id_mm_3_4_2022,
              drug_name_mm_3_4_2022,
              date_mm_3_4_2022,
              min_date_mm_3_4_2022,
              max_date_mm_3_4_2022,
              count_recipes_mm_3_4_2022,
              drug_min_date_mm_3_4_2022,
              drug_max_date_mm_3_4_2022,
              count_drug_recipes_mm_3_4_2022,
              visits_mm_3_4_2022,
              count_recipes_mm_3_4_2022 / visits_mm_3_4_2022 AS avg_rec_per_visit_mm_3_4_2022 --,
       --     visits_mm_3_4_2022 / (max_date_mm_3_4_2022::date - min_date_mm_3_4_2022::date) AS avg_visits_per_day
       FROM rec_mm_3_4_2022
       JOIN t_visits_mm_3_4_2022 ON rec_mm_3_4_2022.patient_id_mm_3_4_2022 = t_visits_mm_3_4_2022.patient_id_mm_3_4_2022_2
       ORDER BY visits_mm_3_4_2022 DESC,
                patient_id_mm_3_4_2022),

      rec_mm_3_4_2023 AS
      (SELECT "ИД пациента" AS patient_id_mm_3_4_2023,
              "Препарат по МНН" AS drug_name_mm_3_4_2023,
              "Дата выписки рецепта" AS date_mm_3_4_2023,
              MIN("Дата выписки рецепта") OVER (PARTITION BY "ИД пациента") AS min_date_mm_3_4_2023,
              MAX("Дата выписки рецепта") OVER (PARTITION BY "ИД пациента") AS max_date_mm_3_4_2023,
              COUNT("Дата выписки рецепта") OVER (PARTITION BY "ИД пациента") AS count_recipes_mm_3_4_2023,
              MIN("Дата выписки рецепта") OVER (PARTITION BY "ИД пациента", "Препарат по МНН") AS drug_min_date_mm_3_4_2023,
              MAX("Дата выписки рецепта") OVER (PARTITION BY "ИД пациента", "Препарат по МНН") AS drug_max_date_mm_3_4_2023,
              COUNT("Дата выписки рецепта") OVER (PARTITION BY "ИД пациента", "Препарат по МНН") AS count_drug_recipes_mm_3_4_2023
       FROM public.recipes
       WHERE mo_type LIKE 'Взрослая'
             AND doctor_or_paramedic LIKE 'Врач'
             AND "Дата выписки рецепта" >= ''' + mm_3_4_2023_start + '''
             AND "Дата выписки рецепта" < ''' + mm_3_4_2023_end + '''
       ORDER BY "ИД пациента"),

      t_visits_mm_3_4_2023 AS
      (SELECT "ИД пациента" AS patient_id_mm_3_4_2023_2,
              COUNT(DISTINCT "Дата выписки рецепта") AS visits_mm_3_4_2023
       FROM public.recipes
       WHERE mo_type LIKE 'Взрослая'
             AND doctor_or_paramedic LIKE 'Врач'
             AND "Дата выписки рецепта" >= ''' + mm_3_4_2023_start + '''
             AND "Дата выписки рецепта" < ''' + mm_3_4_2023_end + '''
       GROUP BY "ИД пациента"),

      total_2023 AS
      (SELECT patient_id_mm_3_4_2023,
              drug_name_mm_3_4_2023,
              date_mm_3_4_2023,
              min_date_mm_3_4_2023,
              max_date_mm_3_4_2023,
              count_recipes_mm_3_4_2023,
              drug_min_date_mm_3_4_2023,
              drug_max_date_mm_3_4_2023,
              count_drug_recipes_mm_3_4_2023,
              visits_mm_3_4_2023,
              count_recipes_mm_3_4_2023 / visits_mm_3_4_2023 AS avg_rec_per_visit_mm_3_4_2023 --,
       --     visits_mm_3_4_2023 / (max_date_mm_3_4_2023::date - min_date_mm_3_4_2023::date) AS avg_visits_per_day
       FROM rec_mm_3_4_2023
       JOIN t_visits_mm_3_4_2023 ON rec_mm_3_4_2023.patient_id_mm_3_4_2023 = t_visits_mm_3_4_2023.patient_id_mm_3_4_2023_2
       ORDER BY visits_mm_3_4_2023 DESC,
                patient_id_mm_3_4_2023),

      agg AS
      (SELECT patient_id_mm_3_4_2022,
              drug_name_mm_3_4_2022,
              TO_CHAR(min_date_mm_3_4_2022, 'YYYY-MM') AS min_mm_3_4_2022,
              TO_CHAR(max_date_mm_3_4_2022, 'YYYY-MM') AS max_mm_3_4_2022,
              count_recipes_mm_3_4_2022,
              TO_CHAR(drug_min_date_mm_3_4_2022, 'YYYY-MM') AS drug_min_mm_3_4_2022,
              TO_CHAR(drug_max_date_mm_3_4_2022, 'YYYY-MM') AS drug_max_mm_3_4_2022,
              count_drug_recipes_mm_3_4_2022,
              patient_id_mm_3_4_2023,
              drug_name_mm_3_4_2023,
              TO_CHAR(min_date_mm_3_4_2023, 'YYYY-MM') AS min_mm_3_4_2023,
              TO_CHAR(max_date_mm_3_4_2023, 'YYYY-MM') AS max_mm_3_4_2023,
              count_recipes_mm_3_4_2023,
              TO_CHAR(drug_min_date_mm_3_4_2023, 'YYYY-MM') AS drug_min_mm_3_4_2023,
              TO_CHAR(drug_max_date_mm_3_4_2023, 'YYYY-MM') AS drug_max_mm_3_4_2023,
              count_drug_recipes_mm_3_4_2023
       FROM total_2022
       JOIN total_2023 ON total_2022.patient_id_mm_3_4_2022 = total_2023.patient_id_mm_3_4_2023
       ORDER BY count_drug_recipes_mm_3_4_2023 DESC),

      tmp_mm_3_4_2022 AS
      (SELECT patient_id_mm_3_4_2022 AS id,
              drug_name_mm_3_4_2022 AS drug_name,
              min_mm_3_4_2022 AS start_year_mm,
              count_recipes_mm_3_4_2022 AS count_recipes,
              count_drug_recipes_mm_3_4_2022 AS count_drug_recipes,
              (((''' + mm_3_4_2022_end + '''::DATE - ''' + mm_3_4_2022_start + '''::DATE) / 365.25 * 12) / count_recipes_mm_3_4_2022) AS INTERVAL,
              (((''' + mm_3_4_2022_end + '''::DATE - ''' + mm_3_4_2022_start + '''::DATE) / 365.25 * 12) / count_drug_recipes_mm_3_4_2022) AS interval_by_drugs
       FROM agg
       ORDER BY count_drug_recipes_mm_3_4_2022 DESC,
                INTERVAL DESC,
                drug_name_mm_3_4_2022,
                patient_id_mm_3_4_2022
       LIMIT 12),

      tmp_mm_3_4_2023 AS
      (SELECT patient_id_mm_3_4_2023 AS id,
              drug_name_mm_3_4_2023 AS drug_name,
              min_mm_3_4_2023 AS start_year_mm,
              count_recipes_mm_3_4_2023 AS count_recipes,
              count_drug_recipes_mm_3_4_2023 AS count_drug_recipes,
              (((''' + mm_3_4_2023_end + '''::DATE - ''' + mm_3_4_2023_start + '''::DATE) / 365.25 * 12) / count_recipes_mm_3_4_2023) AS INTERVAL,
              (((''' + mm_3_4_2023_end + '''::DATE - ''' + mm_3_4_2023_start + '''::DATE) / 365.25 * 12) / count_drug_recipes_mm_3_4_2023) AS interval_by_drugs
       FROM agg
       ORDER BY count_drug_recipes_mm_3_4_2023 DESC,
                INTERVAL DESC,
                drug_name_mm_3_4_2023,
                patient_id_mm_3_4_2023
       LIMIT 12)

SELECT *
FROM tmp_mm_3_4_2022

UNION ALL

SELECT *
FROM tmp_mm_3_4_2023;