需求
2、蚂蚁森林低碳用户排名分析
问题:查询user_low_carbon表中每日流水记录,条件为:
用户在2017年,连续三天(或以上)的天数里,
每天减少碳排放(low_carbon)都超过100g的用户低碳流水。
需要查询返回满足以上条件的user_low_carbon表中的记录流水。
例如用户u_002符合条件的记录如下,因为2017/1/2~2017/1/5连续四天的碳排放量之和都大于等于100g:
分析:首先根据用户和天数进行分组,过滤掉总能量小于100的用户。
接下来就是比较难理解的一点,就是连续三天,我们怎么做到这个连续三天呢?
我也是在看到第一步的结果之后突然发现的,
假如是连续的天数,那么他们的天数成等差数列,差值为1,
假如我们再按照userid进行开创,排名。用时间减去他们的排名就会得到一个固定的值。
假如他们的日期不是连续的,那么开窗之后相减的差值就不是一个固定的值。
基于以上思路,我们做一个简单的测试。
代码是没有进行优化的,我会把自己的思路一步一步理出来。
1.:和第一问类似,先进行时间格式的优化
SELECT
user_id,
DATE_FORMAT(regexp_replace(data_dt,'/','-'),'yyyy-MM-dd') data_dt,
low_carbon
FROM user_low_carbon t1
+----------+-------------+-------------+
| user_id | data_dt | low_carbon |
+----------+-------------+-------------+
| u_001 | 2017-01-01 | 10 |
| u_001 | 2017-01-02 | 150 |
| u_001 | 2017-01-02 | 110 |
| u_001 | 2017-01-02 | 10 |
| u_001 | 2017-01-04 | 50 |
| u_001 | 2017-01-04 | 10 |
| u_001 | 2017-01-06 | 45 |
| u_001 | 2017-01-06 | 90 |
| u_002 | 2017-01-01 | 10 |
| u_002 | 2017-01-02 | 150 |
| u_002 | 2017-01-02 | 70 |
| u_002 | 2017-01-03 | 30 |
| u_002 | 2017-01-03 | 80 |
| u_002 | 2017-01-04 | 150 |
2. 按照用户和时间进行分组,过滤掉当天总能量小于100的用户
SELECT
t1.user_id,
t1.data_dt,
SUM(t1.low_carbon) day_sum_low_carbon
FROM
t1
GROUP