Различный совокупный счетчик в оконной функции PostgreSQL
У меня есть таблица со многими столбцами, некоторые из них:
product_id, territory_id, quarter_num (например, это номер четверти от 1 до 28).
Есть еще несколько столбцов, но они не нужны в этом запросе.
Мне нужно посчитать количество различных товаров на каждой территории в каждом совокупном квартале: только 1 первый, 1+2 второй, 1+2+3 третий и так далее до от 1 до 28.
Раньше этот запрос был реализован в QlikSence с помощью цикла. Теперь мне нужно переписать его на PostgreSQL в одном запросе (даже в одной CTE-части длинного запроса), используя стандартный SQL без циклов и т.д.
Это было бы просто что-то вроде этого:
select *
,count(distinct product_id)
filter(where some_condition)
over(partition by territory_id order by quarter_num)
as cum_filtered_product_count
from some_table
Если бы у меня не было внятных, не реализованных в оконных функциях. Я сломал голову, прочитал и попробовал использовать здесь множество советов, но правильного решения так и не нашел. Любая помощь будет оценена по достоинству.
PS Решение с двумя подзапросами, где первый учитывает отчетливо за один квартал в группе, а второй суммирует результаты первого в оконной функции, кумулятивно не работает. Потому что последний подзапрос потенциально суммирует одни и те же продукты.
Ответы 1
Вы можете использовать intarray для эмуляции недостающего count(distinct x)over() с помощью наборов: db<>fiddle
select distinct quarter_num
,territory_id
,#uniq(sort(array_agg(product_id)
filter(where product_id<>13)
over(partition by territory_id
order by quarter_num)))
as cum_filtered_product_count
from some_table
order by 1,2;
Превратив агрегированный массив в набор, вы сохраните только отдельные элементы, а # сообщит вам, сколько их у вас получилось. Вы также можете использовать -'{}' как трюк, чтобы незаметно превратить массив в набор, но хотя операция короче, она все равно включает в себя uniq(sort()) плюс пустое вычитание.
Если product_id не является int и вы не хотите его сопоставлять, вы можете полагаться на тот факт, что ключи jsonb также являются наборами, поэтому при агрегации в объект jsonb по своей природе будут сохраняться только уникальные ключи:
select distinct quarter_num
,territory_id
,jsonb_array_length(jsonb_path_query_array(cum_filtered_product,'$.*'))
as cum_filtered_product_count
from (
select *,jsonb_object_agg(product_id,0)
filter(where product_id<>13)
over(partition by territory_id order by quarter_num)
as cum_filtered_product
from some_table)_
order by 1,2;
Две функции jsonb.. просто извлекают и подсчитывают ключи.
Вы можете подробнее посмотреть, что это делает, в демо. Some_condition это product_id<>13. Обратите внимание, как он отбрасывает дубликаты как из одного, так и из более ранних кварталов для данной территории:
Что такое # в #uniq(…)?
Это метод intarray array_length(x,1). Я случайно опубликовал слишком рано, поэтому ссылки и объяснения отсутствовали, но сейчас они добавлены.
О, это две функции, # из uniq(…)! Я думал, что это было написано с ошибкой. Я сам использовал intarray, но никогда не сталкивался с унарным оператором #.
Большое спасибо. Это гениально! Я попробовал intarray, но понял, что он не работает с текстом. Дойти до использования идентификаторов вместо этого я не смог сам. Мне стыдно! еще раз спасибо