探索在数据开发中利用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(
	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()函数，让代码更加简洁