【面试真题】Mysql实现计算同比、环比

最新推荐文章于 2024-09-29 11:23:02 发布

原创最新推荐文章于 2024-09-29 11:23:02 发布 · 3.3k 阅读

16 ·

CC 4.0 BY-SA版权

文章标签：

#数据分析 #mysql #同比环比 #开窗函数 #日期函数

数据库操作同时被 2 个专栏收录

5 篇文章

订阅专栏

数据分析

4 篇文章

订阅专栏

本文通过一道面试真题介绍了如何使用MySQL计算同比和环比。内容包括知识点梳理，如同比、环比的定义和计算公式，以及如何利用DATE_SUB()函数进行数据处理。文中通过创建数据表、插入数据和编写SQL查询来详细解释了如何实现这一计算过程。

真题回顾

博主的一位朋友今天去面试，题目是这样的。有一张表，里面有三个字段，分别为datetime，city，order，要求计算出2017年10月1日每个城市订单数量与上周同一天的比值。稍加分析可知，这是一道典型的求同比、环比类型的题目。
在这里插入图片描述

知识点梳理

同比一般情况下是今年第n月与去年第n月比。同比发展速度主要是为了消除季节变动的影响，用以说明本期发展水平与去年同期发展水平对比而达到的相对发展速度。如，本期2月比去年2月，本期6月比去年6月等。其计算公式为：同比发展速度的公式应该改成：同比发展速度=本期发展水平/去年同期水平×100%；同比增长速度=（本期发展水平-去年同期水平）/去年同期水平×100%。在实际工作中，经常使用这个指标，如某年、某季、某月与上年同期对比计算的发展速度，就是同比发展速度。

同比增长率=（本期数-同期数）/|同期数|×100%

环比，表示连续2个统计周期（比如连续两月）内的量的变化比。
环比增长率=（本期数-上期数）/上期数×100%。反映本期比上期增长了多少；环比发展速度，一般是指报告期水平与前一时期水平之比，表明现象逐期的发展速度。

环比增长率=（本期数-上期数）/上期数×100%

DATE_SUB() 函数从日期减去指定的时间间隔。

DATE_SUB(date,INTERVAL expr type)
date 参数是合法的日期表达式；expr 参数是您希望添加的时间间隔；type 参数可以是下列值：YEAR、MONTH、WEEK、DAY、HOUR等等。

实例
假设我们有如下的表：
在这里插入图片描述
现在，我们希望从 “OrderDate” 减去 2 天。

我们使用下面的 SELECT 语句：

SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate FROM Orders;

结果
在这里插入图片描述

真题详解

1.创建数据表

create table job_interview (datetime date,city varchar(10),orderId varchar(10));

2.插入模拟数据

insert into job_interview (datetime,city,orderId) values (20190924,'Beijing','123a'),(20190924,'Beijing','123b'),(20190924,'Beijing','123c');
insert into job_interview (datetime,city,orderId) values (20190924,'Shanghai','123a'),(20190924,'Shanghai','123b'),(20190924,'Shanghai','123c'),(20190924,'Shanghai','123d');
insert into job_interview (datetime,city,orderId) values (20190917,'Beijing','123a'),(20190917,'Beijing','123b');
insert into job_interview (datetime,city,orderId) values (20190917,'Shanghai','123a'),(20190917,'Shanghai','123b');
insert into job_interview (datetime,city,orderId) values (20190224,'Beijing','123c');
insert into job_interview (datetime,city,orderId) values (20190224,'Shanghai','123c');

在这里插入图片描述
3.书写sql

<case 1>
select distinct city,count(case when datetime='20190924' then orderId else null end) over (partition by city) / count(case when datetime=date_sub('20190924',interval 1 week) then orderId else null end) over (partition by city) as '比例' from job_interview;

在这里插入图片描述

<case 2>
select city,count(case when datetime='20190924' then orderId else null end) / count(case when datetime=date_sub('20190924',interval 1 week) then orderId else null end)  as '比例' from job_interview group by city;

在这里插入图片描述

结束语

以上内容简单地把环比的情况进行了说明，如果能够看懂以上内容，那么环比增长率就比较简单了。相应地，同比计算也是如此，比较简单。当然，可能博主水平有限，如果大家有什么比较好的方法，也可以留言哦。我很早就建立了一个微信交流群，里面都是数据行业的从业者，有数据开发、数据分析、数据仓库、BI、数据运营等等。讨论交流的内容涉及很广，如Python、Hadoop、Hive、Oracle、Mysql、Spark等等，欢迎大家加我微信，博主带你进群。加微信请备注：微信交流群字样。谢谢~祝大家开心快乐每一天！

在这里插入图片描述