探索在数据开发中利用chatgpt提高代码质量---第三篇

题目三：

打折日期交叉问题：计算每个品牌总的打折销售天数，注意其中的交叉日期

（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(selectbrand ,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_dateFROM bdms.promotion_table
) AS subquery
group by brand

（4）分析结果：

a.对比运行时长：

自己写的运行时长：85.1s

Chatgpt运行时长：44.6s