HQL:求出每个商品的打折天数！_计算每个商品总的打折销售天数-优快云博客

本文详细介绍了如何通过HQL（Hive SQL）查询语句来计算每个商品的打折天数。首先，从数据表中提取商品的打折开始和结束日期，然后利用流式思想和窗口函数进行处理，包括标记开始和结束时间、合并窗口、计算日期差等步骤。最后通过一系列的SQL操作，如LAG()和SUM()函数，得出每个商品的打折天数。整个过程涉及到了数据处理和分析的关键技术，对于理解和应用大数据处理有很好的参考价值。

HQL:求出每个商品的打折天数！

1.商品打折数据

id		stt		edt
1	2021-06-01	2021-06-05
1	2021-06-07	2021-06-09
2	2021-06-01	2021-06-10
2	2021-06-05	2021-06-15
3	2021-06-01	2021-06-21
3	2021-06-05	2021-06-17
3	2021-06-15	2021-06-27
4	2021-06-01	2021-06-21
4	2021-06-01	2021-06-05
4	2021-06-07	2021-06-15
5	2021-06-01	2021-06-21
5	2021-06-05	2021-06-10
5	2021-06-08	2021-06-15

-- 建表
create table dz
(
    id  String,
    stt date,
    edt date
) row format delimited fields terminated by '\t';

-- 加载数据
load data local inpath "/opt/module/hive/date/dz.txt" into table dz;

2.需求

求出每个商品的打折天数！(最终结果)
1	8
2	15
3	27
4	21
5	21

3.需求解析

1.每个商品优惠券的开始时间,和优惠券结束时间
2.打折天数分为2大类
	(1)时间有间隔
	(2)时间没有间隔
3.按照流的思想,统计每个窗口的相差时间

4.思路分析

-- 思路:
1.将开始时间定义标志1,结束时间标志-1
2.将开始时间和结束时间的标志位相加,得到真正窗口结束的时间为0(有开始就有结束)
3.找出每个窗口的开始时间和结束时间
4.结束时间:0的标志位
5.开始时间:如果窗口最开始没有数据,那么给0,lag()over()
6.将结束窗口用if将0转换成结束日期,将开始窗口的0转换成开始日期
7.两个日期对齐,合并id相减+1

-- 表说明: 
t1:将开始件事定义标志为1,结束时间标志为-1,两张表进行union all
t2:将标志位相加,得出关闭窗口时间为0
t3:找出窗口的开始时间,利用lag()over(),按照窗口,窗口的上一条没有数据则给0,所以窗口的开始时间标志位0
t4:将结束窗口用if将0转换成结束日期,将开始窗口的0转换成开始日期
t5:两个日期对齐
t6:合并id,日期相减+1
t7:合并id,求出每个窗口的和,即为每个商品的打折天数

-- 用到的函数
1.union all
2.标记(流式思想)
3.sum()over()
4.lag()over()
5.if()
6.lead()over()
7.datediff()
8.sum()

5.编码

select
    id,
    sum(sum00000001)
from (
    select
        id,
        datediff(end1_window, start_window) + 1 as sum00000001
    from
        (
        select
            id,
            start_window,
            lead(end_window) over (partition by id ) end1_window
        from
            (
            select
                id,
                stt,
                if(lagWindow = 0, stt, null) start_window,
                if(sum = 0, stt, null)       end_window
            from
                (
                select
                    id,
                    stt,
                    sum,
                lag(sum, 1, 0) over (partition by id order by stt) lagWindow
                from
                (
                    select
                        id,
                        stt,
                        sum(flag) over (partition by id order by stt) sum
                    from
                        (
                        select
                            id,
                            stt,
                            1 flag
                        from dz
                        union all
                        select
                            id,
                            edt,
                            -1 flag
                        from dz
                        ) t1
                    ) t2
                ) t3
            ) t4
            where (end_window is not null or start_window is not null)
        ) t5
        group by id, end1_window, start_window
        order by id
    ) t6
group by id
order by id;