图解SQL面试题:如何比较日期数据? |
您所在的位置:网站首页 › sql中日期比较 › 图解SQL面试题:如何比较日期数据? |
![]() 【题目】 下面是某公司每天的营业额,表名为“日销”。“日期”这一列的数据类型是日期类型(date)。 ![]() 请找出所有比前一天(昨天)营业额更高的数据。(前一天的意思,如果“当天”是1月,“昨天”(前一天)就是1号) 例如需要返回一下结果: 【解题思路】 1.交叉联结 首先我们来复习一下之前课程《从零学会sql》里讲过的交叉联结(corss join)的概念。 使用交叉联结会将两个表中所有的数据两两组合。如下图,是对表“text”自身进行交叉联结的结果: ![]() 直接使用交叉联结的业务需求比较少见,往往需要结合具体条件,对数据进行有目的的提取,本题需要结合的条件就是“前一天”。 2.本题的日销表交叉联结的结果(部分)如下。这个交叉联结的结果表,可以看作左边三列是表a,右边三列是表b。 ![]() 红色框中的每一行数据,左边是“当天”数据,右边是“前一天”的数据。比如第一个红色框中左边是“当天”数据(2号),右边是“前一天”的数据(1号)。 题目要求,销售额条件是:“当天” > “昨天”(前一天)。所以,对于上面的表,我们只需要找到表a中销售额(当天)大于b中销售额(昨天)的数据。 3.另一个需要着重去考虑的,就是如何找到 “昨天”(前一天),这里为大家介绍两个时间计算的函数 daffdate(日期1, 日期2): 得到的结果是日期1与日期2相差的天数。 如果日期1比日期2大,结果为正;如果日期1比日期2小,结果为负。例如:日期1(2019-01-02),日期2(2019-01-01),两个日期在函数里互换位置,就是下面的结果 ![]() 另一个关于时间计算的函数是: timestampdiff(时间类型, 日期1, 日期2) 这个函数和上面diffdate的正、负号规则刚好相反。 日期1大于日期2,结果为负,日期1小于日期2,结果为正。在“时间类型”的参数位置,通过添加“day”, “hour”, “second”等关键词,来规定计算天数差、小时数差、还是分钟数差。示例如下图: ![]() 【解题步骤】 1.将日销表进行交叉联结 ![]() 2.选出上图红框中的“a.日期比b.日期大一天” 可以使用“diffdate(a.日期, b.日期) = 1”或者“timestampdiff(day, a.日期, b.日期) = -1”,以此为基准,提取表中的数据,这里先用diffdate进行操作。 代码部分: elect * from 日销 as a cross join 日销 as b on datediff(a.日期, b.日期) = 1;得到结果: ![]() 3.找出a中销售额大于b中销售额的数据 where a.销售额(万元) > b.销售额(万元) 得到结果: ![]() 4.删掉多余数据 题目只需要找销售额大于前一天的ID、日期、销售额,不需要上表那么多数据。所以只需要提取中上表的ID、日期、销售额(万元)列。 结合一开始提到的两个处理时间的方法,最终答案及结果如下: select a.ID, a.日期, a.销售额(万元) from 日销 as a cross join 日销 as b on datediff(a.日期, b.日期) = 1 where a.销售额(万元) > b.销售额(万元);或者 elect a.ID, a.日期, a.销售额(万元) from 日销 as a cross join 日销 as b on timestampdiff(day, a.日期, b.日期) = -1 where a.销售额(万元) > b.销售额(万元);![]() 【本题考点】 1)考察逻辑思维能力,可以使用课程《分析方法》中的逻辑树分析方法将复杂问题拆解成一个一个可以解决的子问题 2)考察多表联结 3)针对时间的处理语句是在业务中经常用到的,需要熟练掌握。 4) 尤其考察对不同sql数据格式处理的掌握程度, 【举一反三】 下面是气温表,名为weather,date列的数据格式为date,请找出比前一天温度更高的ID和日期 ![]() 参考答案: elect a.ID, a.date from weather as a cross join weather as b on datediff(a.date, b.date) = 1 where a.temp > b.temp;或者: elect a.ID, a.date from weather as a cross join weather as b on timestampdiff(day, a.date, b.date) = -1 where a.temp > b.temp;得到结果: 我是猴子,中科院硕士/前IBM高级软件工程师/豆瓣8分《数据分析思维》作者,我和知乎知学堂研职在线联合出品的「数据分析训练营」即将开课,3天带你掌握数据分析实用技巧,包含课程+实战带练,工作提效、升职加薪必备神器! 报名还有独家Excel自学资料领取,点击开启数据分析升职加薪密码: ![]() |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |