Длинные рецепты - расчёт "идеального случая" с итогами

-- тройные кавычки для переменных из Питона

-- фильтр по дате (раньше 2022.11.01 не имеет смысла смотреть, там не было длинных рецептов)
-- на выходе получаем список id пациентов для дальнейшей работы
WITH id_filter AS
     (SELECT DISTINCT "ИД пациента" AS pac_id
      FROM public.recipes
      WHERE 1 = 1
            AND "Дата выписки рецепта" >= '2022.11.01'
      -- LIMIT 10000
      )

-- все записи по найденным id пациентов, пронумерованные окнами по id и ЛП и сгруппированные по ним же
     , detailed AS
       (SELECT r."ИД пациента" AS id_with_1_or_more_long_at_all
               , r."Препарат по МНН"
               , min("Дата выписки рецепта") OVER (PARTITION BY r."ИД пациента"
                                                                , r."Препарат по МНН") AS min_date
               , max("Дата выписки рецепта") OVER (PARTITION BY r."ИД пациента"
                                                                , r."Препарат по МНН") AS max_date
               , r."Дата выписки рецепта"
               , r."Длинный/ короткий рецепт"
               , max("Продолжительность применения в дн") AS max_dur
               , DENSE_RANK() OVER (PARTITION BY r."ИД пациента"
                                                 , r."Препарат по МНН"
                                                 ORDER BY r."ИД пациента"
                                                          , r."Препарат по МНН"
                                                          , r."Дата выписки рецепта"
                                                          ) AS num
        FROM public.recipes AS r
        RIGHT JOIN id_filter ON id_filter.pac_id = r."ИД пациента"
        WHERE 1 = 1
              AND doctor_or_paramedic IN ''' + ls_doctor_or_paramedic + '''
              AND mo_type LIKE 'Взрослая'
              AND "Дата выписки рецепта" >= '2022.11.01'
              ''' + ls_is_commented + ''' AND "Препарат по МНН" in ''' + ls_drugs + '''
        GROUP BY r."ИД пациента"
                 , r."Препарат по МНН"
                 , r."Дата выписки рецепта"
                 , r."Длинный/ короткий рецепт"
        ORDER BY r."ИД пациента"
                 , r."Препарат по МНН"
                 , r."Дата выписки рецепта"
                 , r."Длинный/ короткий рецепт"
        -- LIMIT 10000
        )

-- добавляем максимальные значения по столбцу с нумерацией
-- и "идеальные" даты, когда у пациентов закончатся лекарства
     , detailed_full AS
       (SELECT *
               , max(num) OVER (PARTITION BY d.id_with_1_or_more_long_at_all
                                            , d."Препарат по МНН"
                                            ) AS max_num
               , max_date::date + max_dur::int - 1 - min_date::date AS date_range
        FROM detailed AS d
-- уберём выброс, который, к тому же, приводит к делению на ноль
        WHERE max_dur::int > 1)

-- фильтруем (есть хотя бы 1 длинный)
     , agg AS
       (SELECT *
        FROM detailed_full AS df
        WHERE 1 = 1
              AND df."Длинный/ короткий рецепт" LIKE 'Длинный'
              -- AND NOT df.num = df.max_num
              )

-- рассчитываем "идеальный случай" (все рецепты - длинные, продолжительности - максимально зафиксированные)
     , ideal_by_drugs AS
       (SELECT id_with_1_or_more_long_at_all as id_pac
               , "Препарат по МНН"
               , min_date
               , date_range
               , max_dur
               , (('today'::date - min_date::date)::int / date_range + 1) AS ideal_n_of_visits
        FROM agg
        GROUP BY id_with_1_or_more_long_at_all
                 , "Препарат по МНН"
                 , min_date
                 , date_range
                 , max_dur)

-- находим суммы визитов за периоды приёма ЛП по пациентам, считаем при этом, что
-- определённые рецепты на ЛП в случае совпадения начальных дат и продолжительностей
-- пациент всё время будет получать в одни и те же дни
     , ideal_agg_sum AS
       (SELECT id_pac
               , sum(ideal_n_of_visits) AS ideal_n_of_visits_sum_by_drugs
       FROM ideal_by_drugs
       GROUP BY id_pac
       ORDER BY sum(ideal_n_of_visits) DESC)

-- строка с агрегированными итогами
SELECT count(id_pac)::text AS id_pac
       -- , avg(ideal_n_of_visits_sum_by_drugs) AS ideal_n_of_visits_sum_by_drugs
       , PERCENTILE_CONT(0.5) WITHIN
         GROUP(ORDER BY ideal_n_of_visits_sum_by_drugs) AS ideal_n_of_visits_sum_by_drugs
FROM ideal_agg_sum

UNION ALL

-- вся остальная таблица
SELECT *
FROM ideal_agg_sum

-- далее строим скрипку или ящик с усами по полученному результату
;