南京手机网站设计,陕西西安网站建设公司哪家好,怎么做网址导航网站,一般的学校网站怎么做目录 问题#xff1a;SQL解答#xff1a;第一种方式#xff1a;第二种方式#xff1a; 问题#xff1a;
如下为某平台的商品促销数据#xff0c;字段含义分别为品牌名称、打折开始日期、打折结束日期#xff0c;现在要计算每个品牌的打折销售天数#xff08;注意其中的… 目录 问题SQL解答第一种方式第二种方式 问题
如下为某平台的商品促销数据字段含义分别为品牌名称、打折开始日期、打折结束日期现在要计算每个品牌的打折销售天数注意其中的交叉日期。比如vivo的打折销售天数就为17天。 brand start_date end_date
xiaomi 2021-06-05 2021-06-09
xiaomi 2021-06-11 2021-06-21
vivo 2021-06-05 2021-06-15
vivo 2021-06-09 2021-06-21
honor 2021-06-05 2021-06-21
honor 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-21SQL解答
第一种方式
根据每个品牌的促销开始时间和结束时间可以得到品牌每天促销的明细数据然后按品牌分组日期去重就可以得到每个品牌打折销售天数。但此种方式适合数据量不大的情况因为该方法会让数据膨胀的很厉害。
with temp as (select xiaomi as brand ,2021-06-05 as start_date,2021-06-09 as end_dateunion allselect xiaomi as brand ,2021-06-11 as start_date,2021-06-21 as end_dateunion allselect vivo as brand ,2021-06-05 as start_date,2021-06-15 as end_dateunion allselect vivo as brand ,2021-06-09 as start_date,2021-06-21 as end_dateunion all select honor as brand ,2021-06-05 as start_date,2021-06-21 as end_dateunion all select honor as brand ,2021-06-09 as start_date,2021-06-15 as end_dateunion allselect honor as brand ,2021-06-17 as start_date,2021-06-26 as end_dateunion allselect huawei as brand ,2021-06-05 as start_date,2021-06-26 as end_dateunion allselect huawei as brand ,2021-06-09 as start_date,2021-06-15 as end_dateunion allselect huawei as brand ,2021-06-17 as start_date,2021-06-21 as end_date
)select
brand
,count(distinct dt) as dts
from (
selectbrand,start_date,end_date,date_add(start_date,tmp.col_idx) as dt
from temp
lateral VIEW posexplode(split(repeat(#,,datediff(date(end_date), date(start_date))),#)) tmp AS col_idx,col_val
) tt
group by brand
;备注补充repeat函数
select repeat(#,,datediff(2023-12-18,2023-12-01))
#,#,#,#,#,#,#,#,#,#,#,#,#,#,#,#,#,select split(repeat(#,,datediff(2023-12-18,2023-12-01)),#)
[,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,]第二种方式
第二种方式规避数据膨胀的情况经过适当的处理消除日期交叉的情况
with temp as (select xiaomi as brand ,2021-06-05 as start_date,2021-06-09 as end_dateunion allselect xiaomi as brand ,2021-06-11 as start_date,2021-06-21 as end_dateunion allselect vivo as brand ,2021-06-05 as start_date,2021-06-15 as end_dateunion allselect vivo as brand ,2021-06-09 as start_date,2021-06-21 as end_dateunion all select honor as brand ,2021-06-05 as start_date,2021-06-21 as end_dateunion all select honor as brand ,2021-06-09 as start_date,2021-06-15 as end_dateunion allselect honor as brand ,2021-06-17 as start_date,2021-06-26 as end_dateunion allselect huawei as brand ,2021-06-05 as start_date,2021-06-26 as end_dateunion allselect huawei as brand ,2021-06-09 as start_date,2021-06-15 as end_dateunion allselect huawei as brand ,2021-06-17 as start_date,2021-06-21 as end_date
)select
brand
,sum(datediff(date(end_date),date(start_date))1)
from
(
selectbrand,casewhen start_datemax_date then date_add(date(max_date),1)else start_date endas start_date,end_datefrom(selectbrand,start_date,end_date,max(end_date) over(partition by brand order by start_date rows between UNBOUNDED PRECEDING and 1 PRECEDING ) as max_date --获取同一品牌内按开始日期排序后取第一行到前一行的最大结束时间from temp)t1)t1
where end_datestart_date
group by brand
;补充rows 和range的区别 在 SQL 中rows 和 range 是两种不同的窗口帧window frame类型它们定义了窗口函数的计算范围。 rows 窗口帧是基于行的它使用一组相对于当前行的行号来定义窗口函数的计算范围。rows 窗口帧可以指定 UNBOUNDED PRECEDING、n PRECEDING、CURRENT ROW、n FOLLOWING 和 UNBOUNDED FOLLOWING 五种窗口帧范围。 range 窗口帧是基于值的它使用一组相对于当前行的数值范围来定义窗口函数的计算范围。range 窗口帧可以指定 UNBOUNDED PRECEDING、n PRECEDING、CURRENT ROW、n FOLLOWING 和 UNBOUNDED FOLLOWING 五种窗口帧范围。 注释 PRECEDING往前 FOLLOWING往后 CURRENT ROW当前行 UNBOUNDED起点 UNBOUNDED PRECEDING 表示从前面的起点 UNBOUNDED FOLLOWING表示到后面的终点
一般来说rows 和 range 窗口帧都可以用于定义窗口函数的计算范围但是它们有一些不同的特点rows 窗口帧是基于行的它使用一组相对于当前行的行号来定义窗口函数的计算范围。因此rows 窗口帧适用于基于行号的计算例如计算排名、移动平均等。range 窗口帧是基于值的它使用一组相对于当前行的数值范围来定义窗口函数的计算范围。因此range 窗口帧适用于基于数值范围的计算例如计算累计和、百分比等。
一般情况下rows 窗口帧比 range 窗口帧更常用因为基于行号的计算更加常见。但是在某些特殊情况下range 窗口帧也可以使用。 例如当窗口函数的计算范围基于连续的数值范围时可以使用 range 窗口帧。例如计算累计和、计算百分比等。当窗口函数的计算范围包含重复的值时可以使用 range 窗口帧来避免重复计算。例如计算连续相同值的最大长度、计算某个值在窗口中的出现次数等。 需要注意的是对于一些特殊的窗口函数可能只能使用 rows 窗口帧例如计算排名、计算移动平均等。因此在使用 range 窗口帧时需要根据具体的需求和窗口函数的特性选择合适的窗口帧类型。
