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

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

-- фильтр по дате (раньше 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
               , 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
        FROM detailed AS d)

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

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

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