|
|
51CTO旗下网站
|
|
移动端

详解MySQL数据库累积聚合原理与用法实例分析

今天主要介绍一下mysql累积聚合原理与用法。下面,我们一起来看看吧。

作者:波波说运维来源:今日头条|2020-01-03 08:10

今天主要介绍一下mysql累积聚合原理与用法。下面,我们一起来看看吧~

MySQL/数据库/累积聚合

累积聚会

累积聚合为聚合从序列内第一个元素到当前元素的数据,如为每个员工返回每月开始到现在累积的订单数量和平均订单数量。

行号问题有两个解决方案,分别是为使用子查询和使用连接。子查询的方法通常比较直观,可读性强。但是在要求进行聚合时,子查询需要为每个聚合扫描一次数据,而连接方法通常只需要扫描一次就可以得到结果。

下面的查询使用连接来得到结果

  1. SELECTa.empid,a.ordermonth,a.qty AS thismonth,SUM(b.qty) AS total,CAST(AVG(b.qty) AS DECIMAL(5,2)) AS avgFROM emporders a  INNER JOIN emporders bON a.empid=b.empidAND b.ordermonth <= a.ordermonth  GROUP BY a.empid,a.ordermonth,a.qtyORDER BY a.empid,a.ordermonth 

如果只是查询2015年的累积订单,可以加上以where条件

  1. WHERE DATE_FORMAT(a.ordermonth,'%Y')='2015' AND DATE_FORMAT(b.ordermonth,'%Y')='2015' 

运行结果如下

详解mysql数据库累积聚合原理与用法实例分析

此外可能还需要筛选数据,例如只需要返回每个员工到达某一目标之前每月订单的情况。这里假设统计每个员工的合计订单数量达到1000之前的累积情况。

这里可以使用HAVING过滤器来完成查询

  1. SELECT a.empid, a.ordermonth,a.qty AS thismonth, SUM(b.qty) AS total, CAST(AVG(b.qty) AS DECIMAL(5,2)) AS avgFROM emporders aINNER JOIN emporders b  ON a.empid=b.empid  AND b.ordermonth <= a.ordermonthWHERE DATE_FORMAT(a.ordermonth,'%Y')='2015' AND DATE_FORMAT(b.ordermonth,'%Y')='2015'GROUP BY a.empid,a.ordermonth,a.qtyHAVING total<1000ORDER BY a.empid,a.ordermonth 

这里并没有统计到达到1000时该月的情况,如果要进行统计,则情况又有点复杂。如果指定了total <= 1000,则只有该月订单数量正好为1000才进行统计,否则不会对该月进行统计。因此这个问题的过滤,可以从另外一个方面来考虑。当累积累积订单小于1000时,累积订单与上个月的订单之差是小于1000的,同时也能对第一个订单数量超过1000的月份进行统计。故该解决方案的SQL语句如下

  1. SELECT a.empid, a.ordermonth,a.qty AS thismonth, SUM(b.qty) AS total, CAST(AVG(b.qty) AS DECIMAL(5,2)) AS avgFROM emporders aINNER JOIN emporders b  ON a.empid=b.empid  AND b.ordermonth <= a.ordermonthWHERE DATE_FORMAT(a.ordermonth,'%Y')='2015' AND DATE_FORMAT(b.ordermonth,'%Y')='2015'GROUP BY a.empid,a.ordermonth,a.qtyHAVING total-a.qty < 1000ORDER BY a.empid,a.ordermonth 

运行结果如下

详解mysql数据库累积聚合原理与用法实例分析

如果只想返回达到累积订单数为1000的当月数据,不返回之前的月份,则可以对上述SQL语句

进一步过滤,再添加累积订单数量大于等于1000的条件。该问题的SQL语句如下,

  1. SELECT a.empid, a.ordermonth,a.qty AS thismonth, SUM(b.qty) AS total, CAST(AVG(b.qty) AS DECIMAL(5,2)) AS avgFROM emporders aINNER JOIN emporders b  ON a.empid=b.empid  AND b.ordermonth <= a.ordermonthWHERE DATE_FORMAT(a.ordermonth,'%Y')='2015' AND DATE_FORMAT(b.ordermonth,'%Y')='2015'GROUP BY a.empid,a.ordermonth,a.qtyHAVING total-a.qty < 1000 AND total >= 1000ORDER BY a.empid,a.ordermonth 

运行结果如下

详解mysql数据库累积聚合原理与用法实例分析

【编辑推荐】

  1. 一文搞懂各种数据库SQL执行计划:MySQL、Oracle等
  2. 一文总结MySQL数据库事件--定时任务实现方式
  3. MySQL数据库小项目日志表定期删除实现方案
  4. 关于数据库设计的总结
  5. 记一次生产数据库因磁盘空间满而引起的死锁故障及解决过程
【责任编辑:赵宁宁 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

Python应用场景实战手册

Python应用场景实战手册

Python应用场景实战手册
共3章 | KaliArch

2人订阅学习

一步到位玩儿透Ansible

一步到位玩儿透Ansible

Ansible
共17章 | 骏马金龙1

99人订阅学习

云架构师修炼手册

云架构师修炼手册

云架构师的必备技能
共3章 | Allen在路上

28人订阅学习

读 书 +更多

网络管理员考试考前冲刺预测卷及考点解析

本书依据最新版《网络管理员考试大纲》的考核要求,深入研究了历年网络管理员考试试题的命题风格和试题结构,对考查的知识点进行了提炼,并...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微