MySQL查询同比，环比，排名等实训练习

大家好，欢迎来到IT知识分享网。

今天要处理的“销售订单表”数据表，含有订单编号、日期、产品、单价、销售数量，销售额共6个字段，6000条，时间跨度为2021-01-01至2024-06-30。如图所示：

销售订单表

Q1 查询商品A、B、C和D，统计区间内每个月的销售量、销售额

查询结果如图所示：

Q2 查询不同商品，每年的年销售量，年销售额

查询结果如图所示：

Q3 按年份统计4种商品的销售量，并且按照每年商品的年销售总量降序排名

RANK() OVER(

PARTITION BY YEAR(日期)

ORDER BY SUM(销售数量) DESC

) AS 销量排名

查询结果如图所示：

Q4 按年份统计4种商品的销售额，并且按照每年商品的年销售额降序排名

RANK() OVER(

PARTITION BY YEAR(日期)

ORDER BY SUM(销售额) DESC

) AS 年销售额排名

查询结果如图所示：

Q5 按每年的每个月，统计不同商品的月销售额，并且按销售额降序排列并且排名

查询结果如图所示：

Q6 查询商品A、B、C和D，统计区间内每年，每个月的销售额，并且按照年度进行销售额的排名。（略有难度）

查询结果如图所示：

Q7 按商品统计每年的年销售额，并且求每年的同比销售额

LAG(SUM(销售额), 1, 0) OVER(PARTITION BY 产品 ORDER BY YEAR(日期)) AS 上一年销售额

查询结果如图所示：

Q8 按商品统计每年的年销售额，并且求每年的环比销售额，环比增长率

LAG(SUM(销售额), 1, 0) OVER(PARTITION BY 产品 ORDER BY YEAR(日期)) AS 上一年销售额

环比是指上一期，例如上一年，上一个月等等。

查询结果如图所示：

Q9 按商品月份，销售额，统计其上月的销售额，进而计算同比增长率

查询结果如图所示：

Q10 按商品，月份计算月销售额，并且计算上年同比销售额，同比增长率

LAG(月销售额, 12, 0) OVER(PARTITION BY 产品 ORDER BY 月份) AS 上年同比销售额

同比一般用在相邻两年，相同时间段内，例如去年的5月跟今年的5月

LAG(月销售额, 12, 0) LAG函数有3个参数，第1参数是字段，第二参数是周期，同比是12个月之前比较，因此用12，第3参数用0，意味着如果没有数据的时候，用0来替代。

CONCAT(FORMAT(((月销售额 – 上年同比销售额) / 上年同比销售额 * 100), 2), ‘%’)

另外注意

CASE

WHEN 上年同比销售额 = 0 THEN NULL

ELSE CONCAT(FORMAT(((月销售额 – 上年同比销售额) / 上年同比销售额 * 100), 2), ‘%’)

END AS 环比增长率

没有同比数据如何处理的问题 WHEN 上年同比销售额 = 0 THEN NULL

查询结果如图所示：

补充知识：

环比与同比的区别

1、同比是本期与同期做对比，环比是本期与上期做对比。

2、环比一般是用在月、日很少用在年上，主要是对比很短时间内涨幅程度，不过由于行业差异，比如旅游，会受到淡旺季影响。

3、同比一般用在相邻两年，相同时间段内，查看涨幅程度，一般用在两年相同月份，很少用在两月相同日期。

环比与同比的计算

1、本期「环比」增长（下降）率计算公式

环比分为日环比、周环比、月环比和年环比。

本期环比增长（下降）率(%)=(本期价格/上期价格—1)×100%

说明：

（1）如果计算值为正值（+），则称增长率；如果计算值为负值（-），则称下降率。

（2）如果本期指本日、本周、本月和本年，则上期相应指上日、上周、上月和上年。

2、本期同比增长（下降）率计算公式

本期同比增长（下降）率(%)=(本期价格/上年同期价格—1)×100%

说明：

（1）如果计算值为正值（+），则称增长率；如果计算值为负值（-），则称下降率。

（2）如果本期指本日、本周和本月，则上年同期相应指上年同日、上年同周和上年同月。