你真的会玩SQL吗?你所不知道的 数据聚合
你真的会玩SQL吗?系列目录
本章的内容与 你真的会玩SQL吗?透视转换内容 非常重要,非常重要,非常重要 ,不理解的可以慢慢看,回头看,过几天再看,以后很多思想需要以此为基础而演变。
此后用到的用例数据库是SQL2008里面的,若看过本系列之前的文章,创建过基础样例数据库就不用再创建。
若没有创建过的,用例数据库文件:链接:http://pan.baidu.com/s/1qW1QxA0 密码:dqxx
连续聚合
下面的例子将使用一个EmpOrdersr汇总表,每位雇员在每个月占一行,包含该雇员在一个月内处理过的订单数量,运行下代码创建数据:
CREATE TABLE EmpOrders ( empid INT NOT NULL , ordmonth DATE NOT NULL ,--只取到月份2015-07-07 qty INT NOT NULL , PRIMARY KEY ( empid, ordmonth ) ) go INSERT INTO EmpOrders ( empid , ordmonth , qty ) SELECT o.empid , DATEADD(MONTH, DATEDIFF(MONTH, 0, o.orderdate), 0) AS ordmonth , SUM(qty) AS qty FROM Sales.Orders AS o JOIN Sales.OrderDetails AS od ON o.orderid = od.orderid GROUP BY empid , DATEADD(MONTH, DATEDIFF(MONTH, 0, o.orderdate), 0)
查询:
SELECT empid , ordmonth , qty FROM EmpOrders ORDER BY empid , ordmonth
将输出以下内容
接下来讲讲各类聚合……
1.累积聚合
为每个雇员和每个月,返回从其开始有订单操作以来到该月份处理过的订单总量和每月的平均量,结果如下,怎么做?
SELECT o1.empid , o1.ordmonth , o1.qty AS qtythismonth , SUM(o2.qty) AS totalqty , CAST(AVG(1. * o2.qty) AS NUMERIC(12, 2)) AS avgqty FROM EmpOrders AS o1 JOIN EmpOrders AS o2 ON o2.empid = o1.empid AND o2.ordmonth <= o1.ordmonth GROUP BY o1.empid , o1.ordmonth , o1.qty ORDER BY o1.empid , o1.ordmonth
若想得到雇员达到累积总量<1000之前的每月聚合值,怎么做?
SELECT o1.empid , o1.ordmonth , o1.qty AS qtythismonth , SUM(o2.qty) AS totalqty , CAST(AVG(1. * o2.qty) AS NUMERIC(12, 2)) AS avgqty FROM EmpOrders AS o1 JOIN EmpOrders AS o2 ON o2.empid = o1.empid AND o2.ordmonth <= o1.ordmonth GROUP BY o1.empid , o1.ordmonth , o1.qty HAVING SUM(o2.qty)<1000 ORDER BY o1.empid , o1.ordmonth
对总量做一次HAVING过滤 HAVING SUM(o2.qty)<1000,而不是用WHERE,因为过滤是的聚合,而不是属性。
2.滑动聚合
滑动聚合是对序列内的一个滑动窗口进行的聚合计算,而不是从序列的开始计算到当前位置。
求雇员最近三个月(包括本月)的平均订单量(移动平均数),得到以下结果:

SELECT o1.empid , o1.ordmonth , o1.qty AS qtythismonth , SUM(o2.qty) AS totalqty , CAST(AVG(1. * o2.qty) AS NUMERIC(12, 2)) AS avgqty FROM EmpOrders AS o1 JOIN EmpOrders AS o2 ON o2.empid = o1.empid AND (o2.ordmonth <= o1.ordmonth AND o2.ordmonth > DATEADD(MONTH,-3,o1.ordmonth)) GROUP BY o1.empid , o1.ordmonth , o1.qty ORDER BY o1.empid , o1.ordmonth
这里使用的是o2.ordmonth> 3个月之前的月份 and o2.ordmonth<=o1.当前月份
3.年初至今
聚合按年单位算,如求每个雇员每年内的每个月份的聚合,该怎样写?结果如下:

SELECT o1.empid , CONVERT( VARCHAR(7),o1.ordmonth ,121) AS ordmonth, o1.qty AS qtythismonth , SUM(o2.qty) AS totalqty , CAST(AVG(1. * o2.qty) AS NUMERIC(12, 2)) AS avgqty FROM EmpOrders AS o1 JOIN EmpOrders AS o2 ON o2.empid = o1.empid AND ( o2.ordmonth <= o1.ordmonth AND o2.ordmonth >= CAST(CAST(YEAR(o1.ordmonth) AS CHAR(4)) + '0101' AS DATETIME) ) GROUP BY o1.empid , o1.ordmonth , o1.qty ORDER BY o1.empid , o1.ordmonth
所有聚合函数都会忽略NULL值,只有一个例外:Count(*)
聚合中常见的函数为分组函数GROUP BY ,要注意的是 GROUP BY 原则 select后面所有列中 没有使用聚合函数的列必须出现在GROUP BY 后面
公众号【一个码农的日常】 技术群:319931204 1号群: 437802986 2号群: 340250479
出处:http://zhangs1986.cnblogs.com/
码云:https://gitee.com/huanzui
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· Linux系列:如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列(二):开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识(上)
· 阿里最新开源QwQ-32B,效果媲美deepseek-r1满血版,部署成本又又又降低了!
· 单线程的Redis速度为什么快?
· SQL Server 2025 AI相关能力初探
· 展开说说关于C#中ORM框架的用法!
· AI编程工具终极对决:字节Trae VS Cursor,谁才是开发者新宠?