SQL面试题:求时间差之和(有重复不计)
作者:开心学大数据 发布时间:2024-01-22 00:09:14
标签:SQL,时间差
面试某某公司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 |
建表语句
-- ----------------------------
-- Table structure for sale
-- ----------------------------
DROP TABLE IF EXISTS `sale`;
CREATE TABLE `sale` (
`id` int(11) DEFAULT NULL,
`brand` varchar(255) DEFAULT NULL,
`start_date` date DEFAULT NULL,
`end_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of sale
-- ----------------------------
INSERT INTO `sale` VALUES (1, 'nike', '2018-09-01', '2018-09-05');
INSERT INTO `sale` VALUES (2, 'nike', '2018-09-03', '2018-09-06');
INSERT INTO `sale` VALUES (3, 'nike', '2018-09-09', '2018-09-15');
INSERT INTO `sale` VALUES (4, 'oppo', '2018-08-04', '2018-08-05');
INSERT INTO `sale` VALUES (5, 'oppo', '2018-08-04', '2018-08-15');
INSERT INTO `sale` VALUES (6, 'vivo', '2018-08-15', '2018-08-21');
INSERT INTO `sale` VALUES (7, 'vivo', '2018-09-02', '2018-09-12');
方式1:
利用自关联下一条记录的方法
select brand,sum(end_date-befor_date+1) all_days from
(
select s.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,interval 1 day) ) as befor_date
from sale s left join (select id+1 as id ,brand,end_date from sale) t on s.id = t.id and s.brand = t.brand
order by s.id
)tmp
group by brand
运行结果
+-------+---------+
| brand | all_day |
+-------+---------+
| nike | 13 |
| oppo | 12 |
| vivo | 18 |
+-------+---------+
该方法对本题中的表格有效,但对于有id不连续的品牌的记录时不一定适用。
方式2:
SELECT a.brand,SUM(
CASE
WHEN a.start_date=b.start_date AND a.end_date=b.end_date
AND NOT EXISTS(
SELECT *
FROM sale c LEFT JOIN sale d ON c.brand=d.brand
WHERE d.brand=a.brand
AND c.start_date=a.start_date
AND c.id<>d.id
AND (d.start_date BETWEEN c.start_date AND c.end_date AND d.end_date>c.end_date
OR
c.start_date BETWEEN d.start_date AND d.end_date AND c.end_date>d.end_date)
)
THEN (a.end_date-a.start_date+1)
WHEN (a.id<>b.id AND b.start_date BETWEEN a.start_date AND a.end_date AND b.end_date>a.end_date ) THEN (b.end_date-a.start_date+1)
ELSE 0 END
) AS all_days
FROM sale a JOIN sale b ON a.brand=b.brand GROUP BY a.brand
运行结果
+-------+----------+
| brand | all_days |
+-------+----------+
| nike | 13 |
| oppo | 12 |
| vivo | 18 |
+-------+----------+
其中条件
d.start_date BETWEEN c.start_date AND c.end_date AND d.end_date>c.end_date
OR
c.start_date BETWEEN d.start_date AND d.end_date AND c.end_date>d.end_date
可以换成
c.start_date < d.end_date AND (c.end_date > d.start_date)
结果同样正确
用分析函数同样可行的,自己电脑暂时没装oracle,用的mysql写的。
来源:https://blog.csdn.net/u012955829/article/details/102754141


猜你喜欢
- 关于F.normalize计算理解动机最近多次看到该方法出现,于是准备了解一下,搜了后发现原来是所谓的L2 norm计算简介函数定义torc
- 一、SeabornSeaborn 建于 matplotlib 库的之上。它有许多内置函数,使用这些函数,只需简单的代码行就可以创建漂亮的绘图
- 选择最实用来谈一下首先,你要慎重选择你就业的城市。这点是目前多数人都忽略的重要因素。无论你的设计思路和发展方向都要依托你所在城市来作为载体。
- mysql select into给多个字段变量赋值在into之后顺序写上要赋值的变量就行SELECT
- 在学习编程过程中,我们不仅要学习python语法,同时也需要学习如何把自己代码写的更美观,效率更高。一.什么是推导式推导式是从一个或者多个迭
- 在js中调用asp文件的方法很简单,我们可以用在静态页面的点击数统计,虽然静态页面不支持asp程序,但是我们可以使用js调用,来变相的达到这
- 本文实例讲述了wxPython窗口的继承机制,分享给大家供大家参考。具体分析如下:示例代码如下:import wx class
- 有的时候,可能会遇到表格中出现重复的索引,在操作重复索引的时候可能要注意一些问题。一、判断索引是否重复a、Series索引重复判断s = S
- 很多人在使用AJAX调用别人站点内容的时候,JS会提示"没有权限"错误,这是XMLHTTP组件的限制-安全起见禁止访问非
- 本文实例讲述了Python实现判断给定列表是否有重复元素的方法。分享给大家供大家参考,具体如下:题目很简单,只是简单温习一个方法,most_
- 在很多项目启动的时候都需要连接到数据库,因此判断数据库服务器是否启动就十分必要了,如何判断数据库服务器是否启动呢?可以通过判断数据库服务是否
- 需求手百小程序的toast仅支持在页中展示,不能自定义位置、字体大小、在单个文本中增加icon等需求;因此自定义一个全局可通用的Toast。
- 一、简介Pickle模块实现了基本的数据序列化与反序列化操作。通过序列化操作,我们可以将程序中运行的对象信息转化为字节流保存到文件中去,永久
- 国外有很多优秀的文章可以用来学习,我决定花些时间翻译。我并不知道这篇文章有没有人翻译过,原文名 10 Awful IE Bugs and F
- 本文实例讲述了Python列表切片操作。分享给大家供大家参考,具体如下:切片指的是列表的一部分。1 基本用法指定第一个元素和最后一个元素的索
- 给定一个可迭代sequence,对其中的值进行出现次数统计:方法1:def get_counts(sequence): counts = {
- 利用函数:group_concat(),实现一个ID对应多个名称时,原本为多行数据,把名称合并成一行。 其完整语法: GROUP_CONCA
- 下面就是JavaScript实现大文件上传功能的代码bigFileUpload.jsconst path = require('pa
- 之前在网上查找了很多相关资料,有说设置icon高度来支持item的,有说要添加自己写指定高度的view来填充的,但是对于一个只有文字的Qco
- 一段简单的pygame代码,只在pygame界面背景绘制了一个背景图片# -*- coding=utf-8 -*-import sysimp