SQL面试题:求时间差之和(有重复不计)

2023-08-08 14:13:06 98

面试某某公司BI岗位的时候，面试题中的一道sql题，咋看一下很简单，写的时候发现自己缺乏总结，没有很快的写出来。

题目如下：

求每个品牌的促销天数

表sale为促销营销表，数据中存在日期重复的情况，例如id为1的end_date为20180905，id为2的start_date为20180903，即id为1和id为2的存在重复的销售日期，求出每个品牌的促销天数(重复不算)

表结果如下：

+------+-------+------------+------------+
|id|brand|start_date|end_date|
+------+-------+------------+------------+
|1|nike|2018-09-01|2018-09-05|
|2|nike|2018-09-03|2018-09-06|
|3|nike|2018-09-09|2018-09-15|
|4|oppo|2018-08-04|2018-08-05|
|5|oppo|2018-08-04|2018-08-15|
|6|vivo|2018-08-15|2018-08-21|
|7|vivo|2018-09-02|2018-09-12|
+------+-------+------------+------------+

最终结果应为

brand	all_days
nike	13
oppo	12
vivo	18

建表语句

------------------------------
--Tablestructureforsale
------------------------------
DROPTABLEIFEXISTS`sale`;
CREATETABLE`sale`(
`id`int(11)DEFAULTNULL,
`brand`varchar(255)DEFAULTNULL,
`start_date`dateDEFAULTNULL,
`end_date`dateDEFAULTNULL
)ENGINE=InnoDBDEFAULTCHARSET=utf8;

------------------------------
--Recordsofsale
------------------------------
INSERTINTO`sale`VALUES(1,'nike','2018-09-01','2018-09-05');
INSERTINTO`sale`VALUES(2,'nike','2018-09-03','2018-09-06');
INSERTINTO`sale`VALUES(3,'nike','2018-09-09','2018-09-15');
INSERTINTO`sale`VALUES(4,'oppo','2018-08-04','2018-08-05');
INSERTINTO`sale`VALUES(5,'oppo','2018-08-04','2018-08-15');
INSERTINTO`sale`VALUES(6,'vivo','2018-08-15','2018-08-21');
INSERTINTO`sale`VALUES(7,'vivo','2018-09-02','2018-09-12');

方式1：

利用自关联下一条记录的方法

selectbrand,sum(end_date-befor_date+1)all_daysfrom
(
selects.id,
s.brand,
s.start_date,
s.end_date,
if(s.start_date>=ifnull(t.end_date,s.start_date),s.start_date,DATE_ADD(t.end_date,interval1day))asbefor_date
fromsalesleftjoin(selectid+1asid,brand,end_datefromsale)tons.id=t.idands.brand=t.brand
orderbys.id
)tmp
groupbybrand

运行结果

+-------+---------+
|brand|all_day|
+-------+---------+
|nike|13|
|oppo|12|
|vivo|18|
+-------+---------+

该方法对本题中的表格有效，但对于有id不连续的品牌的记录时不一定适用。

方式2：

SELECTa.brand,SUM(
CASE
WHENa.start_date=b.start_dateANDa.end_date=b.end_date
ANDNOTEXISTS(
SELECT*
FROMsalecLEFTJOINsaledONc.brand=d.brand
WHEREd.brand=a.brand
ANDc.start_date=a.start_date
ANDc.id<>d.id
AND(d.start_dateBETWEENc.start_dateANDc.end_dateANDd.end_date>c.end_date
OR
c.start_dateBETWEENd.start_dateANDd.end_dateANDc.end_date>d.end_date)
)
THEN(a.end_date-a.start_date+1)
WHEN(a.id<>b.idANDb.start_dateBETWEENa.start_dateANDa.end_dateANDb.end_date>a.end_date)THEN(b.end_date-a.start_date+1)
ELSE0END
)ASall_days
FROMsaleaJOINsalebONa.brand=b.brandGROUPBYa.brand

运行结果

+-------+----------+
|brand|all_days|
+-------+----------+
|nike|13|
|oppo|12|
|vivo|18|
+-------+----------+

其中条件

d.start_dateBETWEENc.start_dateANDc.end_dateANDd.end_date>c.end_date
OR
c.start_dateBETWEENd.start_dateANDd.end_dateANDc.end_date>d.end_date

可以换成

c.start_dated.start_date)

结果同样正确

用分析函数同样可行的，自己电脑暂时没装oracle，用的mysql写的。

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持毛票票。

声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。

SQL面试题:求时间差之和(有重复不计)

热门推荐

随机推荐