У меня есть следующая таблица:
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)
)
Я хотел бы иметь возможность:
Первым шагом будет получение совокупной суммы заказов для каждого пользователя с течением времени с помощью чего-то вроде этого 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
на каждую дату пользователь классифицируется в корзине на основе совокупного количества сделанных им заказов, и каждый отдельный пользователь учитывается в каждой корзине.
Привет @Simon Breton, можешь попробовать добавить строку count(distinct user_id) over(partition by purchase_count_bucket) distinct_user_count
в свой запрос? Можете ли вы предоставить образец вывода?
нам нужен надежный пример ожидаемого результата (на основе уже представленных входных данных). тогда я чувствую, что это относительно простой «вызов», поэтому вы получите ответ быстро: о)
Я обновил свой вопрос, это желаемый результат
Ниже приведено направление для изучения
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 решение потенциальных проблем с производительностью или памятью
Добавление желаемого вывода очень помогает