探索在数据开发中利用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

 b.结果阐述：

缺点：本题目涉及到有交叉日期在统计的时候不允许重复，利用Chatgpt生成的sql无法同时满足有交叉日期和没有交叉日期的统计，多次生成的sql只能满足一个条件，需要自己修改代码，才能满足需要

优点：Chatgpt生成的sql经过简单的修改，运行时间比我最开始自己写的运行时间短，而且它提供了一种新的思路，用到了我之前一直没有用过的LEAST()函数，让代码更加简洁

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！