假如现在是双十一，同一品牌的很多商品在不同时间段打折，求每个如 A 品牌的 A1 商品打折时间段为：2021-11-01 ~ 2021-11-04，A2 商品打折时间段为：2021-11-03 ~ 2021-11-11，那么 A 品牌的打折时间为 12 天。
原始数据
数据如下：
0: jdbc:hive2://localhost:10000> select * from crossover_problem;
+--------------------------+------------------------+------------------------+
| crossover_problem.brand | crossover_problem.stt | crossover_problem.edt |
+--------------------------+------------------------+------------------------+
| oppo | 2021-06-05 | 2021-06-09 |
| oppo | 2021-06-11 | 2021-06-21 |
| vivo | 2021-06-05 | 2021-06-15 |
| vivo | 2021-06-09 | 2021-06-21 |
| redmi | 2021-06-05 | 2021-06-21 |
| redmi | 2021-06-09 | 2021-06-15 |
| redmi | 2021-06-17 | 2021-06-26 |
| huawei | 2021-06-05 | 2021-06-26 |
| huawei | 2021-06-09 | 2021-06-15 |
| huawei | 2021-06-17 | 2021-06-21 |
+--------------------------+------------------------+------------------------+
10 rows selected (0.17 seconds)
很多同学反应说 SQL 系列的文章看不出是 hive 还是 mysql 还是什么数据库，导致复制过去的 SQL 各种报错，索性这次直接复制控制台的数据。
思路分析
这类问题最容易想到的思路是拿结束时间 - 开始时间 + 1，按照品牌累加即可，但这种方式如果商品打折的时间返回出现重合就会多算一次，因此只要解决重合这种情况那么问题九迎难而解了！单独拿 redmi 的数据来分析一下
redmi,2021-06-05,2021-06-21
redmi,2021-06-09,2021-06-15
redmi,2021-06-17,2021-06-26
如果我能把数据转换成不重合的时间段，那不就可以了吗！我们看上一条的结束时间和下一条的开始时间，如果开始时间小于上一条的结束时间我们手动把开始时间替换成结束时间+1，看下面结果
redmi,2021-06-05,2021-06-21,1970-01-01
redmi,2021-06-09,2021-06-15,2021-06-21
redmi,2021-06-17,2021-06-26,2021-06-15
第一条，开始时间大于上一条的结束时间，表示和上一条没有重叠，开始时间不变
第二条，开始时间小于上一条的结束时间，表示和上一条存在重叠，修改开始时间
第三条，同第一条
redmi,2021-06-05,2021-06-21
redmi,2021-06-22,2021-06-15
redmi,2021-06-17,2021-06-26
拿结束时间 - 开始时间 + 1 即可，这时候可能出现负数，剔除掉即可，负数的业务含义其实是当前的时间段被上一条的时间段完全包含，实际也是不用计算的。思路貌似很完美！无懈可击
实际上只比较上一条是存在 bug 的，看下面的数据
huawei,2021-06-05,2021-06-26,1970-01-01
huawei,2021-06-09,2021-06-15,2021-06-26
huawei,2021-06-17,2021-06-21,2021-06-15
比较替换
huawei,2021-06-05,2021-06-26
huawei,2021-06-27,2021-06-15
huawei,2021-06-17,2021-06-21
发现问题了，第三条数据理论上也需要被替换，因为第三条时间范围被第一条覆盖，同时第三条覆盖了第二条，也就是说我们仅比较上一条数据范围是不对了，应该取这条数据上面所有数据的最大值就没有问题了
SQL 实现
SQL 最难实现的是如何对本条数据之前所有的数据进行开窗，这时候需要给开窗指定一个范围
0: jdbc:hive2://localhost:10000> select brand,
. . . . . . . . . . . . . . . .> stt,
. . . . . . . . . . . . . . . .> edt,
. . . . . . . . . . . . . . . .> max(edt) over (partition by brand order by stt rows between UNBOUNDED PRECEDING and 1 PRECEDING) maxedt
. . . . . . . . . . . . . . . .> from crossover_problem t;
+---------+-------------+-------------+-------------+
| brand | stt | edt | maxedt |
+---------+-------------+-------------+-------------+
| huawei | 2021-06-05 | 2021-06-26 | NULL |
| huawei | 2021-06-09 | 2021-06-15 | 2021-06-26 |
| huawei | 2021-06-17 | 2021-06-21 | 2021-06-26 |
| oppo | 2021-06-05 | 2021-06-09 | NULL |
| oppo | 2021-06-11 | 2021-06-21 | 2021-06-09 |
| redmi | 2021-06-05 | 2021-06-21 | NULL |
| redmi | 2021-06-09 | 2021-06-15 | 2021-06-21 |
| redmi | 2021-06-17 | 2021-06-26 | 2021-06-21 |
| vivo | 2021-06-05 | 2021-06-15 | NULL |
| vivo | 2021-06-09 | 2021-06-21 | 2021-06-15 |
+---------+-------------+-------------+-------------+
10 rows selected (14.461 seconds)
UNBOUNDED PRECEDING：表示窗口第一条数据
1 PRECEDING：表示上一条数据
之后先处理一下 null，后进行比较替换
0: jdbc:hive2://localhost:10000> select brand,
. . . . . . . . . . . . . . . .> if(maxedt = null, stt, if(stt <= maxedt, date_add(maxedt, 1), stt)) new_stt,
. . . . . . . . . . . . . . . .> edt
. . . . . . . . . . . . . . . .> from (
. . . . . . . . . . . . . . . .> select brand,
. . . . . . . . . . . . . . . .> stt,
. . . . . . . . . . . . . . . .> edt,
. . . . . . . . . . . . . . . .> max(edt) over (partition by brand order by stt rows between UNBOUNDED PRECEDING and 1 PRECEDING) maxedt
. . . . . . . . . . . . . . . .> from crossover_problem t
. . . . . . . . . . . . . . . .> ) t1;
+---------+-------------+-------------+
| brand | new_stt | edt |
+---------+-------------+-------------+
| huawei | 2021-06-05 | 2021-06-26 |
| huawei | 2021-06-27 | 2021-06-15 |
| huawei | 2021-06-27 | 2021-06-21 |
| oppo | 2021-06-05 | 2021-06-09 |
| oppo | 2021-06-11 | 2021-06-21 |
| redmi | 2021-06-05 | 2021-06-21 |
| redmi | 2021-06-22 | 2021-06-15 |
| redmi | 2021-06-22 | 2021-06-26 |
| vivo | 2021-06-05 | 2021-06-15 |
| vivo | 2021-06-16 | 2021-06-21 |
+---------+-------------+-------------+
10 rows selected (14.164 seconds)
日期相减，分组，剔除可能出现的负数，+1 求和即可
0: jdbc:hive2://localhost:10000> select brand, sum(if(subdt >= 0, subdt + 1, 0)) days
. . . . . . . . . . . . . . . .> from (
. . . . . . . . . . . . . . . .> select brand, datediff(edt, new_stt) subdt
. . . . . . . . . . . . . . . .> from (
. . . . . . . . . . . . . . . .> select brand,
. . . . . . . . . . . . . . . .> if(maxedt = null, stt, if(stt <= maxedt, date_add(maxedt, 1), stt)) new_stt,
. . . . . . . . . . . . . . . .> edt
. . . . . . . . . . . . . . . .> from (
. . . . . . . . . . . . . . . .> select brand,
. . . . . . . . . . . . . . . .> stt,
. . . . . . . . . . . . . . . .> edt,
. . . . . . . . . . . . . . . .> max(edt)
. . . . . . . . . . . . . . . .> over (partition by brand order by stt rows between UNBOUNDED PRECEDING and 1 PRECEDING) maxedt
. . . . . . . . . . . . . . . .> from crossover_problem t
. . . . . . . . . . . . . . . .> ) t1
. . . . . . . . . . . . . . . .> ) t2
. . . . . . . . . . . . . . . .> ) t3
. . . . . . . . . . . . . . . .> group by brand;
+---------+-------+
| brand | days |
+---------+-------+
| huawei | 22 |
| oppo | 16 |
| redmi | 22 |
| vivo | 17 |
+---------+-------+
4 rows selected (33.273 seconds)
验算一下，完美呀！！！
评论区