HiveQL实战蚂蚁森林低碳用户排名分析：解法二

大数据真有意思 2020-05-30

388

蚂蚁森林背景和数据准备：HiveQL实战蚂蚁森林植物申领统计分析

table_name：user_low_carbon

user_id	data_dt	low_carbon
用户	日期	减少碳排放（g）

蚂蚁森林植物换购表，用于记录申领环保植物所需要减少的碳排放量。

table_name: plant_carbon

plant_id	plant_name	low_carbon
植物编号	植物名	换购植物所需要的碳

问题：查询user_low_carbon表中每日流水记录，条件为：

用户在2020年，连续三天（或以上）的天数里，

每天减少碳排放（low_carbon）都超过100g的用户低碳流水。

需要查询返回满足以上条件的user_low_carbon表中的记录流水。

例如，假设用户u_002符合条件的记录如下，因为2020/1/2~2020/1/5连续四天，每天的碳排放量之和都大于等于100g：

2号 150 + 70 = 220

3号 30 + 80 = 110

4号 150

5号 101

seq（key） user_id data_dt low_carbon

xxxxx10 u_002 2020/1/2 150

xxxxx11 u_002 2020/1/2 70

xxxxx12 u_002 2020/1/3 30

xxxxx13 u_002 2020/1/3 80

xxxxx14 u_002 2020/1/4 150

xxxxx14 u_002 2020/1/5 101

第二种解法：

SQL流程【建表只是用于观察中间数据，可以选择使用临时表，session关闭后会自动删除】：

1.根据用户ID和时间两个字段进行聚合，计算每个人每天的低碳量（2020年）并给每一条数据打标签（同一个用户不同时间排序）

create table t1(user_id String, data_dt String, low_carbon_sum int, rn int);
insert overwrite table t1
select
    user_id,
    data_dt,
    sum(low_carbon) low_carbon_sum,
    row_number() over(partition by user_id order by data_dt) as rn
from user_low_carbon
where substring(data_dt, 1, 4) = '2020'
group BY user_id, data_dt
having low_carbon_sum > 100; -- t1

hive> select * from t1;
OK
u_001  2020/1/2  270  1
u_001  2020/1/6  135  2
u_002  2020/1/2  220  1
u_002  2020/1/3  110  2
u_002  2020/1/4  150  3
u_002  2020/1/5  101  4
...

2.获取每一条数据时间跟标签之间的差值

create table t2(user_id String, data_dt String, diff_date String);
insert overwrite table t2
select
    user_id,
    data_dt,
    date_sub(to_date(regexp_replace(data_dt, "/", "-")), rn) diff_date 
from t1; -- t2

hive> select * from t2;
OK
u_001  2020/1/2  2020-01-01
u_001  2020/1/6  2020-01-04
u_002  2020/1/2  2020-01-01
u_002  2020/1/3  2020-01-01
u_002  2020/1/4  2020-01-01
u_002  2020/1/5  2020-01-01
...

3.按照所获得的差值聚合，得到同一个用户下相同差值的个数

create table t3(user_id String, data_dt String, diff_date_count int);
insert overwrite table t3
select
    user_id,
    data_dt,
    count(*) over(partition by user_id, diff_date) diff_date_count
from t2; -- t3

hive> select * from t3;
OK
u_001  2020/1/2  1
u_001  2020/1/6  1
u_002  2020/1/2  4
u_002  2020/1/3  4
u_002  2020/1/4  4
u_002  2020/1/5  4
...

4.过滤出相同差值个数在3及以上的数据

create table t4(user_id String, data_dt String);
insert overwrite table t4
select
  user_id,
  data_dt
from t3 where diff_date_count >= 3; -- t4

hive> select * from t4;
OK
u_002  2020/1/2
u_002  2020/1/3
u_002  2020/1/4
u_002  2020/1/5
u_005  2020/1/3
u_005  2020/1/2
u_005  2020/1/4
...

5.与原表join得到最终需要的结果

select
    t5.user_id,
    t5.data_dt,
    t5.low_carbon
from user_low_carbon t5
join t4
where t4.user_id = t5.user_id and t4.data_dt = t5.data_dt
order by t5.user_id, t5.data_dt;

6.最终sql

SELECT
    t5.user_id,
    t5.data_dt,
    t5.low_carbon
FROM
    user_low_carbon t5
JOIN (
    SELECT
        user_id,
        data_dt
    FROM
        (
            SELECT
                user_id,
                data_dt,
                count(*) over (
                    PARTITION BY user_id,
                    diff_date
                ) diff_date_count
            FROM
                (
                    SELECT
                        user_id,
                        data_dt,
                        date_sub(
                             to_date (
                                regexp_replace (data_dt, "/", "-")
                            ),
                            rn
                        ) diff_date
                    FROM
                        (
                            SELECT
                                user_id,
                                data_dt,
                                sum(low_carbon) low_carbon_sum,
                                row_number () over (
                                    PARTITION BY user_id
                                    ORDER BY
                                        data_dt
                                ) AS rn
                            FROM
                                user_low_carbon
                            WHERE
                                substring(data_dt, 1, 4) = '2020'
                            GROUP BY
                                user_id,
                                data_dt
                            HAVING
                                low_carbon_sum > 100
                        ) t1
                ) t2
        ) t3
    WHERE
        diff_date_count >= 3
) t4
WHERE
    t4.user_id = t5.user_id
AND t4.data_dt = t5.data_dt
ORDER BY
    t5.user_id,
    t5.data_dt;

7.结果展示

MapReduce Jobs Launched: 
Stage-Stage-1:  HDFS Read: 12976 HDFS Write: 974 SUCCESS
Stage-Stage-2:  HDFS Read: 12976 HDFS Write: 974 SUCCESS
Stage-Stage-4:  HDFS Read: 12976 HDFS Write: 974 SUCCESS
Total MapReduce CPU Time Spent: 0 msec
OK
u_002  2020/1/2  150
u_002  2020/1/2  70
u_002  2020/1/3  30
u_002  2020/1/3  80
u_002  2020/1/4  150
u_002  2020/1/5  101
u_005  2020/1/2  50
u_005  2020/1/2  80
u_005  2020/1/3  180
u_005  2020/1/4  180
u_005  2020/1/4  10
u_008  2020/1/4  260
u_008  2020/1/5  360
u_008  2020/1/6  160
u_008  2020/1/7  60
u_008  2020/1/7  60
u_009  2020/1/2  70
u_009  2020/1/2  70
u_009  2020/1/3  170
u_009  2020/1/4  270
u_010  2020/1/4  90
u_010  2020/1/4  80
u_010  2020/1/5  90
u_010  2020/1/5  90
u_010  2020/1/6  190
u_010  2020/1/7  90
u_010  2020/1/7  90
u_011  2020/1/1  110
u_011  2020/1/2  100
u_011  2020/1/2  100
u_011  2020/1/3  120
u_013  2020/1/2  150
u_013  2020/1/2  50
u_013  2020/1/3  150
u_013  2020/1/4  550
u_013  2020/1/5  350
u_014  2020/1/5  250
u_014  2020/1/6  120
u_014  2020/1/7  270
u_014  2020/1/7  20

推荐阅读：

Hive-DML(Data Manipulation Language)数据操作语言

Hive-DDL(Data Definition Language)数据定义

文章转载自大数据真有意思，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

HiveQL实战蚂蚁森林低碳用户排名分析：解法二

评论