我需要帮助从"sales_track"表中查找和计算某些连续时间段内的订单，这些订单来自至少有两个或更多交易的用户(改写：有多少用户在 n 天内有 2 笔或更多笔交易，甚至没有跳过一天)
sales_track
sales_tx_id | u_id | create_date | item_id | price
------------|------|-------------|---------|---------
ffff-0291 | 0001 | 2019-08-01 | 0300 | 5.00
ffff-0292 | 0001 | 2019-08-01 | 0301 | 2.50
ffff-0293 | 0002 | 2019-08-01 | 0209 | 3.50
ffff-0294 | 0003 | 2019-08-01 | 0020 | 1.00
ffff-0295 | 0001 | 2019-08-02 | 0301 | 2.50
ffff-0296 | 0001 | 2019-08-02 | 0300 | 5.00
ffff-0297 | 0001 | 2019-08-02 | 0209 | 3.50
ffff-0298 | 0002 | 2019-08-02 | 0300 | 5.00
为简单起见，样品仅连续两天(时间段在 2019-08-01 和 2019-08-02 之间)，在实际操作中我必须搜索例如连续 10 天交易。
到目前为止，我能够找到最少两笔或更多笔交易。
SELECT user_id, COUNT (user_id) FROM sales_track WHERE created_at BETWEEN
('2019-08-01') AND ('2019-08-02')
GROUP BY u_id HAVING COUNT (sales_tx_id) >= 2;
我正在寻找的输出如下：
u_id | tx_count | tx_amount
------|----------|------------
0001 | 5 | 18.50
提前感谢您的帮助。
逐步演示：db<>fiddle
首先：我的扩展数据集：
sales_tx_id | user_id | created_at | item_id | price
:---------- | :------ | :--------- | :------ | ----:
ffff-0291 | 0001 | 2019-08-01 | 0300 | 5.00
ffff-0292 | 0001 | 2019-08-01 | 0301 | 2.50
ffff-0293 | 0002 | 2019-08-01 | 0209 | 3.50
ffff-0294 | 0003 | 2019-08-01 | 0020 | 1.00
ffff-0295 | 0001 | 2019-08-02 | 0301 | 2.50
ffff-0296 | 0001 | 2019-08-02 | 0300 | 5.00
ffff-0297 | 0001 | 2019-08-02 | 0209 | 3.50
ffff-0298 | 0002 | 2019-08-02 | 0300 | 5.00
ffff-0299 | 0001 | 2019-08-05 | 0209 | 3.50
ffff-0300 | 0001 | 2019-08-05 | 0020 | 1.00
ffff-0301 | 0001 | 2019-08-06 | 0209 | 3.50
ffff-0302 | 0001 | 2019-08-06 | 0020 | 1.00
ffff-0303 | 0001 | 2019-08-07 | 0209 | 3.50
ffff-0304 | 0001 | 2019-08-07 | 0020 | 1.00
ffff-0305 | 0002 | 2019-08-08 | 0300 | 5.00
ffff-0306 | 0002 | 2019-08-08 | 0301 | 2.50
ffff-0307 | 0001 | 2019-08-09 | 0209 | 3.50
ffff-0308 | 0001 | 2019-08-09 | 0020 | 1.00
ffff-0309 | 0002 | 2019-08-09 | 0300 | 5.00
ffff-0310 | 0002 | 2019-08-09 | 0301 | 2.50
ffff-0311 | 0001 | 2019-08-10 | 0209 | 3.50
ffff-0312 | 0001 | 2019-08-10 | 0020 | 1.00
ffff-0313 | 0002 | 2019-08-10 | 0300 | 5.00
用户 1 有 3 条条纹：
- 2019-08-01， 2019-08-02
- 2019-08-05， 2019-08-06， 2019-08-07
- 2019-08-09， 2019-08-10
用户 2：
在 2019-08-01- 、2019-08-02 有交易，但每个日期只有一次，所以不算在内
- 已连胜 2019-08-08， 2019-08-09 (2019-08-10 只有一笔交易，不延续连胜)
所以我们期待 4 行：每个用户 1 连胜 3 行，用户 2 1 行
SELECT -- 4
user_id,
SUM(count),
SUM(price),
MIN(created_at) AS consecutive_start
FROM (
SELECT *, -- 3
SUM(is_in_same_group) OVER (PARTITION BY user_id ORDER BY created_at) AS group_id
FROM (
SELECT -- 2
*,
(lag(created_at, 1, created_at) OVER (PARTITION BY user_id ORDER BY created_at) + 1 <> created_at)::int as is_in_same_group
FROM (
SELECT -- 1
created_at,
user_id,
COUNT(*),
SUM(price) AS price
FROM
sales_track
WHERE created_at BETWEEN '2018-02-01' AND '2019-08-11'
GROUP BY created_at, user_id
HAVING COUNT(*) >= 2
) s
) s
) s
GROUP BY user_id, group_id
- 将所有(
created_at、user_id)分组并删除带有COUNT() < 2的组 lag()窗口函数允许获取一个有序组中前一条记录的值。这里的小组是user_id.这里的检查是：如果当前created_at值紧挨着上一个值(当前 + 1)，则0，否则1。- 现在我们可以使用累积
SUM()窗口函数对这些值求和： 如果差距太大(如果值为1)，则值增加，否则它与前一个日期的值相同。现在，我们得到了所有日期的group_id，这些日期仅相差+1 - 最后，可以将这些组分组为
SUM()和COUNT()