HIVE SQL 最近N日汇总

1：数据

2022-01-01,10
2022-01-02,10
2022-01-03,10
2022-01-04,10
2022-01-05,10
2022-01-06,10
2022-01-07,10
2022-01-08,10
2022-01-09,10
2022-01-10,10
2022-01-11,10
2022-01-12,10
2022-01-13,10
2022-01-14,10
2022-01-15,10
2022-01-16,10
2022-01-17,10
2022-01-18,10
2022-01-19,10
2022-01-20,10
2022-01-21,10
2022-01-22,10
2022-01-23,10
2022-01-24,10
2022-01-25,10
2022-01-26,10
2022-01-27,10
2022-01-28,10
2022-01-29,10
2022-01-30,10
2022-01-31,10

2：SQL

#2022-01-31最近1,7,15日汇总
select
        '2022-01-31' create_time,
        days,
        sum(_c1) pv
        from tb lateral view explode(Array(1,7,15)) as days
        where _c0>=date_sub('2022-01-31',days-1)
        group by days
#结果
+-----------+----+-----+
|create_time|days|   pv|
+-----------+----+-----+
| 2022-01-31|   1| 10.0|
| 2022-01-31|  15|150.0|
| 2022-01-31|   7| 70.0|
+-----------+----+-----+