mysql滑动聚合/年初至今聚合原理与用法实例分析

本文实例讲述了mysql滑动聚合/年初至今聚合原理与用法。分享给大家供大家参考，具体如下：

滑动聚合是按顺序对滑动窗口范围内的数据进行聚合的操作。下累积聚合不同，滑动聚合并不是统计开始计算的位置到当前位置的数据。

这里以统计最近三个月中员工第月订单情况为例来介绍滑动聚合。

滑动聚合和累积聚合解决方案的主要区别在于连接的条件不同。滑动聚合条件不再是b.ordermonth <= a.ordermonth,而应该是b.ordermonth大于前三个月的月份，并且小于当前月份。因此滑动聚合的解决方案的SQL语句如下

SELECT
 a.empid,
 DATE_FORMAT(a.ordermonth, '%Y-%m') AS ordermonth,
 a.qty AS thismonth,
 SUM(b.qty) AS total,
 CAST(AVG(b.qty) AS DECIMAL(5,2)) AS avg
FROM emporders a
INNER JOIN emporders b
 ON a.empid=b.empid
 AND b.ordermonth > DATE_ADD(a.ordermonth, INTERVAL -3 MONTH)
 AND b.ordermonth <= a.ordermonth
WHERE DATE_FORMAT(a.ordermonth,'%Y')='2015' AND DATE_FORMAT(b.ordermonth,'%Y')='2015'
GROUP BY a.empid,DATE_FORMAT(a.ordermonth, '%Y-%m'),a.qty
ORDER BY a.empid,a.ordermonth

运行结果如下

该解决方案返回的是三个月为一个周期的滑动聚合，但是每个用户包含前两个月并且未满3个月的聚合。如果只希望返回满3个月的聚合，不返回未满3个月的聚合，可以使用HAVING过滤器进行过滤，过滤的条件为MIN(b.ordermonth)=DATE_ADD(a.ordermonth, INTERVAL -2 MONTH),例如

SELECT
 a.empid,
 a.ordermonth AS ordermonth,
 a.qty AS thismonth,
 SUM(b.qty) AS total,
 CAST(AVG(b.qty) AS DECIMAL(5,2)) AS avg
FROM emporders a
INNER JOIN emporders b
 ON a.empid=b.empid
 AND b.ordermonth > DATE_ADD(a.ordermonth, INTERVAL -3 MONTH)
 AND b.ordermonth <= a.ordermonth
WHERE DATE_FORMAT(a.ordermonth,'%Y')='2015' AND DATE_FORMAT(b.ordermonth,'%Y')='2015' AND a.empid=1
GROUP BY a.empid,DATE_FORMAT(a.ordermonth, '%Y-%m'),a.qty
HAVING MIN(b.ordermonth)=DATE_ADD(a.ordermonth, INTERVAL-2 MONTH)
ORDER BY a.empid,a.ordermonth

运行结果如下

年初至今聚合和滑动聚合类似，不同的地方仅在于统计的仅为当前一年的聚合。唯一的区别体现在下限的开始位置上。在年初至今的问题中，下限为该年的第一天，而滑动聚合的下限为N个月的第一天。因此，年初至今的问题的解决方案如下图所示，得到的结果

SELECT
 a.empid,
 DATE_FORMAT(a.ordermonth, '%Y-%m') AS ordermonth,
 a.qty AS thismonth,
 SUM(b.qty) AS total,
 CAST(AVG(b.qty) AS DECIMAL(5,2)) AS avg
FROM emporders a
INNER JOIN emporders b
  ON a.empid=b.empid
  AND b.ordermonth >= DATE_FORMAT(a.ordermonth, '%Y-01-01')
  AND b.ordermonth <= a.ordermonth
  AND DATE_FORMAT(b.ordermonth,'%Y')='2015'
GROUP BY a.empid,a.ordermonth,a.qty
ORDER BY a.empid,a.ordermonth

运行结果如下

更多关于MySQL相关内容感兴趣的读者可查看本站专题：《MySQL查询技巧大全》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》

希望本文所述对大家MySQL数据库计有所帮助。

赞 (0)

相关推荐

  • MySQL常用聚合函数详解

    一.AVG AVG(col) 返回指定列的平均值 二.COUNT COUNT(col) 返回指定列中非NULL值的个数 三.MIN/MAX MIN(col):返回指定列的最小值 MAX(col):返回指定列的最大值 四.SUM SUM(col) 返回指定列的所有值之和 五.GROUP_CONCAT GROUP_CONCAT([DISTINCT] expr [,expr ...]              [ORDER BY {unsigned_integer | col_name | expr}

  • Mysql无法选取非聚合列的解决方法

    Mysql无法选取非聚合列的解决方法

    1. 前言 最近升级博客,给文章页面底部增加了两个按钮,可以直接跳转到上一篇和下一篇. 如下图所示: 实现这个功能的难点在于:数据库怎么选取出一条记录的前后两条相邻的记录? 2. 数据库设计 关于我文章数据库的设计如下图所示: 可以看到,每条记录的身份是索引Id.因为之前有很多文章记录被删除了,所以,Id并不是连续的. 如果当前文章的索引值是33,那么可以通过以下命令来得到前后相邻的 2 篇文章: select * from passage where id in (select case wh

  • MySql 中聚合函数增加条件表达式的方法

    Mysql 与聚合函数在一起时候where条件和having条件的过滤时机 where 在聚合之前过滤 当一个查询包含了聚合函数及where条件,像这样的情况 select max(cid) from t where t.id<999 这时候会先进行过滤,然后再聚合.先过滤出ID<999的记录,再查找最大的cid返回. having 在聚合之后过滤 having在分组的时候会使用,对分组结果进行过滤,通常里面包含聚合函数. SELECT ip,MAX(id) FROM app GROUP BY

  • mysql连续聚合原理与用法实例分析

    mysql连续聚合原理与用法实例分析

    本文实例讲述了mysql连续聚合原理与用法.分享给大家供大家参考,具体如下: 连续聚合是按时间顺序对有序数据进行聚合的操作. 在下面的救示例中将使用EmpOrders表,该表用于存放每位员工每月发生的订购数量. 运行如下 代码创建EmpOrders表并填充示例数据. CREATE TABLE EmpOrders ( empid INT NOT NULL, ordermonth DATE NOT NULL, qty INT NOT NULL,test PRIMARY KEY (empid,orde

  • php+mysql开源XNA 聚合程序发布 下载

    php+mysql开源XNA 聚合程序发布 下载

    PHP+MYSQL (php5 +mysql 4.1) 的一个简单的开源XNA聚合,效果如:http://xna.spvrk.com 一个简单的聚合程序,用phpmyadmin 导入cms_xna.sql,修改config.inc.php中的数据库地址即可使用,后台为/admin,因时间关系,暂时没写用户名和密码验证,大家可免费使用本程序,也可在本程序的基础上二次开发,但不得用于任何商业用途,不提供技术支持,但有好的新版本也可回馈给我,本程序使用通用公共授权GPL3,想做成一个开源的项目,不知有

  • MySQL单表查询操作实例详解【语法、约束、分组、聚合、过滤、排序等】

    本文实例讲述了MySQL单表查询操作.分享给大家供大家参考,具体如下: 语法 一.单表查询的语法 SELECT 字段1,字段2... FROM 表名                   WHERE 条件                   GROUP BY field                   HAVING 筛选                   ORDER BY field                   LIMIT 限制条数 二.关键字的执行优先级(重点) 重点中的重点:关键

  • mysql累积聚合原理与用法实例分析

    mysql累积聚合原理与用法实例分析

    本文实例讲述了mysql累积聚合原理与用法.分享给大家供大家参考,具体如下: 累积聚合为聚合从序列内第一个元素到当前元素的数据,如为每个员工返回每月开始到现在累积的订单数量和平均订单数量 行号问题有两个解决方案,分别是为使用子查询和使用连接.子查询的方法通常比较直观,可读性强.但是在要求进行聚合时,子查询需要为每个聚合扫描一次数据,而连接方法通常只需要扫描一次就可以得到结果.下面的查询使用连接来得到结果 SELECT a.empid, a.ordermonth,a.qty AS thismont

  • MySQL中聚合函数count的使用和性能优化技巧

    本文的环境是Windows 10,MySQL版本是5.7.12-log 一. 基本使用 count的基本作用是有两个: 统计某个列的数据的数量: 统计结果集的行数: 用来获取满足条件的数据的数量.但是其中有一些与使用中印象不同的情况,比如当count作用一列.多列.以及使用*来表达整行产生的效果是不同的. 示例表如下: CREATE TABLE `NewTable` ( `id` int(11) NULL DEFAULT NULL , `name` varchar(30) NULL DEFAUL

  • MySQL查询排序与查询聚合函数用法分析

    本文实例讲述了MySQL查询排序与查询聚合函数用法.分享给大家供大家参考,具体如下: 排序 为了方便查看数据,可以对数据进行排序 语法: select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...] 说明 将行数据按照列1进行排序,如果某些行列1的值相同时,则按照列2排序,以此类推 默认按照列值从小到大排列(asc) asc从小到大排列,即升序 desc从大到小排序,即降序 例1:查询未删除男生信息,按学号降序 select * from st

  • mysql滑动聚合/年初至今聚合原理与用法实例分析

    mysql滑动聚合/年初至今聚合原理与用法实例分析

    本文实例讲述了mysql滑动聚合/年初至今聚合原理与用法.分享给大家供大家参考,具体如下: 滑动聚合是按顺序对滑动窗口范围内的数据进行聚合的操作.下累积聚合不同,滑动聚合并不是统计开始计算的位置到当前位置的数据. 这里以统计最近三个月中员工第月订单情况为例来介绍滑动聚合. 滑动聚合和累积聚合解决方案的主要区别在于连接的条件不同.滑动聚合条件不再是b.ordermonth <= a.ordermonth,而应该是b.ordermonth大于前三个月的月份,并且小于当前月份.因此滑动聚合的解决方案的

  • mysql游标的原理与用法实例分析

    本文实例讲述了mysql游标的原理与用法.分享给大家供大家参考,具体如下: 本文内容: 什么是游标 创建游标 使用游标 首发日期:2018-04-18 什么是游标: 如果你前面看过mysql函数,会发现无法使用返回多行结果的语句.但如果你又确实想要使用时,就需要使用到游标,游标可以帮你选择出某个结果(这样就可以做到返回单个结果). 另外,使用游标也可以轻易的取出在检索出来的行中前进或后退一行或多行的结果. 游标可以遍历返回的多行结果. 补充: Mysql中游标只适用于存储过程以及函数. 创建游标

  • mysql自定义函数原理与用法实例分析

    本文实例讲述了mysql自定义函数原理与用法.分享给大家供大家参考,具体如下: 本文内容: 什么是函数 函数的创建 函数的调用 函数的查看 函数的修改 函数的删除 首发日期:2018-04-18 什么是函数: 函数存储着一系列sql语句,调用函数就是一次性执行这些语句.所以函数可以降低语句重复.[但注意的是函数注重返回值,不注重执行过程,所以一些语句无法执行.所以函数并不是单纯的sql语句集合.] mysql函数有自己的自定义函数(已经定义好了的函数),想了解更多的可以参考我的另一篇博文:mys

  • mysql触发器原理与用法实例分析

    本文实例讲述了mysql触发器原理与用法.分享给大家供大家参考,具体如下: 本文内容: 什么是触发器 创建触发器 单条触发器语句 多条触发器语句 查看触发器 删除触发器 触发器的新旧记录引用 首发日期:2018-04-14 什么是触发器: 触发器用来在某些操作之后,"自动"执行一些操作.(比如插入了新的学生信息,那么在班级表中应该修改学生数). 当insert delete update设置触发器之后,执行insert delete update操作就会自动触发设置的内容. 一张表最大

  • mysql索引原理与用法实例分析

    本文实例讲述了mysql索引原理与用法.分享给大家供大家参考,具体如下: 本文内容: 什么是索引 创建索引 普通索引 唯一索引 全文索引 单列索引 多列索引 查看索引 删除索引 首发日期:2018-04-14 什么是索引: 索引可以帮助快速查找数据 而基本上索引都要求唯一(有些不是),所以某种程度上也约束了数据的唯一性. 索引创建在数据表对象上,由一个或多个字段组成,这若干个字段组成"键"存储到数据结构中(B树或者哈希表).[可以根据数据结构分类成B树索引(innodb\myisam引

  • Python设计模式之迭代器模式原理与用法实例分析

    Python设计模式之迭代器模式原理与用法实例分析

    本文实例讲述了Python设计模式之迭代器模式原理与用法.分享给大家供大家参考,具体如下: 迭代器模式(Iterator Pattern):提供方法顺序访问一个聚合对象中各元素,而又不暴露该对象的内部表示. 下面是一个迭代器模式的demo: #!/usr/bin/env python # -*- coding:utf-8 -*- __author__ = 'Andy' """ 大话设计模式 设计模式--迭代器模式 迭代器模式(Iterator Pattern):提供方法顺序访

  • javascript设计模式 – 迭代器模式原理与用法实例分析

    本文实例讲述了javascript设计模式 – 迭代器模式原理与用法.分享给大家供大家参考,具体如下: 介绍:迭代器模式是一种使用频率非常高的设计模式,通过引入迭代器,可以将数据的遍历功能从聚合对象中分离出来.迭代器模式用于顺序访问集合对象的元素,不需要知道集合对象的底层表示. 定义:提供一种方法来访问聚合对象,而不用暴露这个对象的内部表示,其别名为游标(Cursor).迭代器模式是一种对象行为型模式. 场景:我们做一个百家姓的迭代器 示例: function NameRepository(){

  • flask框架单元测试原理与用法实例分析

    本文实例讲述了flask框架单元测试原理与用法.分享给大家供大家参考,具体如下: 为什么要测试? Web程序开发过程一般包括以下几个阶段:[需求分析,设计阶段,实现阶段,测试阶段].其中测试阶段通过人工或自动来运行测试某个系统的功能.目的是检验其是否满足需求,并得出特定的结果,以达到弄清楚预期结果和实际结果之间的差别的最终目的. 测试的分类: 测试从软件开发过程可以分为:单元测试.集成测试.系统测试等.在众多的测试中,与程序开发人员最密切的就是单元测试,因为单元测试是由开发人员进行的,而其他测试

随机推荐