题目三:
打折日期交叉问题:计算每个品牌总的打折销售天数,注意其中的交叉日期
(1)数据准备
-- 创建手机品牌促销表
CREATE TABLE bdms.promotion_table (
brand string,
start_date string,
end_date string
);
-- 插入示例数据
INSERT INTO bdms.promotion_table (brand, start_date, end_date) VALUES
('Apple', '2022-07-09', '2022-07-18'),
('Apple', '2022-07-07', '2022-07-10'),
('Samsung', '2022-07-08', '2022-07-12'),
('Samsung', '2022-07-16', '2022-07-20'),
('Xiaomi', '2022-07-11', '2022-07-15'),
('Xiaomi', '2022-07-14', '2022-07-22'),
('Huawei', '2022-07-13', '2022-07-16'),
('Huawei', '2022-07-19', '2022-07-24');
(2)自己写法:
with t1 as(
select
brand ,
start_date,
end_date,
LAG(end_date) OVER (PARTITION BY brand ORDER BY start_date) lag_end_date,
min(start_date) over(partition by brand order by start_date rows between UNBOUNDED PRECEDING and current row) min_start_date,
max(end_date) over(partition by brand order by start_date rows between UNBOUNDED PRECEDING and current row) max_end_date
from bdms.promotion_table
),
t2 as(
select brand,max(t1.max_end_date) max_end_date
from t1
group by brand
)
select a.brand,
if(a.lag_end_date>a.start_date,datediff(a.max_end_date,a.min_start_date)+1,(datediff(a.max_end_date,a.min_start_date)+1)-(datediff(a.start_date,a.lag_end_date)+1-2)) as promotion_days
from t1 a
join t2 b
on a.brand=b.brand
and a.max_end_date = b.max_end_date
(3)Chatgpt生成:
select brand,sum(if(end_date>coal_start_date,datediff(new_end_date,start_date),datediff(new_end_date,start_date)+1)) days
FROM (
SELECT brand,start_date, end_date,
COALESCE(LEAD(start_date) OVER (PARTITION BY brand ORDER BY start_date),end_date) coal_start_date,
LEAST(end_date, COALESCE(LEAD(start_date) OVER (PARTITION BY brand ORDER BY start_date),end_date)) new_end_date
FROM bdms.promotion_table
) AS subquery
group by brand
(4)分析结果:
a.对比运行时长:
自己写的运行时长:85.1s
Chatgpt运行时长:44.6s
b.结果阐述:
缺点:本题目涉及到有交叉日期在统计的时候不允许重复,利用Chatgpt生成的sql无法同时满足有交叉日期和没有交叉日期的统计,多次生成的sql只能满足一个条件,需要自己修改代码,才能满足需要
优点:Chatgpt生成的sql经过简单的修改,运行时间比我最开始自己写的运行时间短,而且它提供了一种新的思路,用到了我之前一直没有用过的LEAST()函数,让代码更加简洁