题目
如下数据为蚂蚁森林中用户领取的减少碳排放量
id dt lowcarbon
1001 2021-12-12 123
1002 2021-12-12 45
1001 2021-12-13 23
1001 2021-12-13 45
1001 2021-12-13 23
1002 2021-12-14 45
1001 2021-12-14 230
1002 2021-12-15 43
1001 2021-12-15 23
....
找出连续3天及以上减少碳排放量在100以上的用户
思路
等差数列法
题解
SELECT
id ,
flag,
COUNT(*) ct
FROM (
SELECT
id
dt,
lowcarborn,
date_sub(dt,rk) flag
FROM (
SELECT
id
dt,
lowcarborn ,
RANK() OVER (PARTITION BY id ORDER BY dt ) rk
FROM (
SELECT
id,
dt,
SUM(lowcarborn) lowcarborn
FROM
test1
GROUP BY id ,dt
HAVING lowcarbon>100
) a
) b
) c
GROUP BY id,flag
HAVING ct >=3