Совокупная сумма отдельных пользователей с течением времени, сгруппированных по категориям

У меня есть следующая таблица:

WITH orders AS
      (SELECT '1234' as user_id, '12340' as order_id, DATE(2021, 01, 05) as date
        UNION ALL SELECT '1234', '1234A', DATE(2022, 01, 07)
        UNION ALL SELECT '1234', '1234B', DATE(2022, 02, 10)
        UNION ALL SELECT '1234', '1234C', DATE(2022, 02, 11)
        UNION ALL SELECT '1234', '1234D', DATE(2022, 03, 21)
        UNION ALL SELECT '1234', '1234E', DATE(2022, 06, 23)
        UNION ALL SELECT '1234', '1234F', DATE(2022, 07, 01)
        UNION ALL SELECT '1234', '1234G', DATE(2022, 08, 04)
        UNION ALL SELECT '1234', '1234H', DATE(2022, 08, 08)
        UNION ALL SELECT '1234', '1234I', DATE(2022, 10, 23)
        UNION ALL SELECT '456', '456A', DATE(2022, 01, 11)
        UNION ALL SELECT '456', '456B', DATE(2022, 02, 23)
        UNION ALL SELECT '456', '456C', DATE(2022, 03, 08)
        UNION ALL SELECT '456', '456D', DATE(2022, 03, 15)
        UNION ALL SELECT '456', '456E', DATE(2022, 07, 19)
        UNION ALL SELECT '456', '456F', DATE(2022, 08, 12)
      )

Я хотел бы иметь возможность:

  1. сгруппировать этих пользователей в сегменты, определяемые их количеством заказов с течением времени
  2. подсчитайте определенное количество пользователей в каждом сегменте с течением времени

Первым шагом будет получение совокупной суммы заказов для каждого пользователя с течением времени с помощью чего-то вроде этого COUNT(order_id) OVER(PARTITION BY user_id ORDER BY date) AS cumul_orders. Затем, основываясь на этом новом столбце cumul_orders, я могу разделить своих пользователей на разные категории в зависимости от их количества заказов на дату x, используя формулу случая:

CASE
  WHEN cumul_purchases = 1 THEN 'bucket_1'
  WHEN cumul_purchases = 2 THEN 'bucket_2'
  WHEN cumul_purchases = 3 THEN 'bucket_3'
  WHEN cumul_purchases >= 4 THEN 'bucket_4_more'

Затем я застрял, потому что мне нужно подсчитать определенное количество пользователей в каждом сегменте для каждой даты...

редактировать #1

Это промежуточный результат, с которым я застрял. Оттуда мне нужно иметь возможность подсчитывать общее количество различных пользователей в любой момент времени для каждого сегмента.

изменить # 2

Это (я думаю!) Результат, который я хочу:

Date            bucket                value     
2022/01/01      'bucket_1'            0
2022/01/01      'bucket_2'            0
2022/01/01      'bucket_3'            0
2022/01/01      'bucket_4_more'       0
2022/01/02      'bucket_1'            0
2022/01/02      'bucket_2'            0
2022/01/02      'bucket_3'            0
2022/01/02      'bucket_4_more'       0
...
2022/01/07      'bucket_1'            1
2022/01/07      'bucket_2'            0
2022/01/07      'bucket_3'            0
2022/01/07      'bucket_4_more'       0
...
2022/01/11      'bucket_1'            2
2022/01/11      'bucket_2'            0
2022/01/11      'bucket_3'            0
2022/01/11      'bucket_4_more'       0
2022/01/12      'bucket_1'            2
2022/01/12      'bucket_2'            0
2022/01/12      'bucket_3'            0
2022/01/12      'bucket_4_more'       0
...
2022/06/01      'bucket_1'            0
2022/06/01      'bucket_2'            0
2022/06/01      'bucket_3'            0
2022/06/01      'bucket_4_more'       2

на каждую дату пользователь классифицируется в корзине на основе совокупного количества сделанных им заказов, и каждый отдельный пользователь учитывается в каждой корзине.

Добавление желаемого вывода очень помогает

Fact 23.11.2022 06:52

Привет @Simon Breton, можешь попробовать добавить строку count(distinct user_id) over(partition by purchase_count_bucket) distinct_user_count в свой запрос? Можете ли вы предоставить образец вывода?

Shipra Sarkar 23.11.2022 11:59

нам нужен надежный пример ожидаемого результата (на основе уже представленных входных данных). тогда я чувствую, что это относительно простой «вызов», поэтому вы получите ответ быстро: о)

Mikhail Berlyant 23.11.2022 12:04

Я обновил свой вопрос, это желаемый результат

Simon Breton 23.11.2022 19:06
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
4
72
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Ниже приведено направление для изучения

select date, bucket, 
  (select count(distinct user_id) from t.users user_id) as value
from (
  select *, array_agg(user_id) over(partition by bucket order by date) users
  from temp
) t

также проверьте stackoverflow.com/a/73546428/5221944 решение потенциальных проблем с производительностью или памятью

Mikhail Berlyant 24.11.2022 02:05

Другие вопросы по теме