你真的会玩SQL吗?你所不知道的 数据聚合

你真的会玩SQL吗?系列目录

你真的会玩SQL吗?之逻辑查询处理阶段

你真的会玩SQL吗?和平大使 内连接、外连接

你真的会玩SQL吗?三范式、数据完整性

你真的会玩SQL吗?查询指定节点及其所有父节点的方法

你真的会玩SQL吗?让人晕头转向的三值逻辑

你真的会玩SQL吗?EXISTS和IN之间的区别

你真的会玩SQL吗?无处不在的子查询

你真的会玩SQL吗?Case也疯狂

你真的会玩SQL吗?表表达式,排名函数

你真的会玩SQL吗?简单的 数据修改

你真的会玩SQL吗?你所不知道的 数据聚合

你真的会玩SQL吗?透视转换的艺术

你真的会玩SQL吗?冷落的Top和Apply

你真的会玩SQL吗?实用函数方法汇总

你真的会玩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 后面

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据和云

SQL为王:oracle标量子查询和表连接改写

小鱼(邓秋爽) 云和恩墨专家,有超过5年超大型数据库专业服务经验,擅长oracle 数据库优化、SQL优化和troubleshooting 编辑手记:如何提高数...

4736
来自专栏影子

PostgreSQL=>递归查询

3488
来自专栏杨建荣的学习笔记

通过图表简化sql语句的表关联(r4笔记第70天)

在之前的博文中分享过一个执行了两天的一条sql语句,走了两个大表的扫描,导致执行时间很长,通过简化sql做了不小的改进,今天我们来看看还可以做些什么。 上次简化...

3644
来自专栏禁心尽力

一次浴火重生的MySQL优化(EXPLAIN命令详解)

一直对SQL优化的技能心存无限的向往,之前面试的时候有很多面试官都会来一句,你会优化吗?我说我不太会,这时可能很多人就会有点儿说法了,比如会说不要使用通配符*...

2035
来自专栏小怪聊职场

MySQL(八)|MySQL中In与Exists的区别(2)

1853
来自专栏杨建荣的学习笔记

简单对比MySQL和Oracle中的一个sql解析细节 (r5笔记第40天)

SQL的语法解析器是一个很强大的内置工具集,里面会涉及到很多的编译原理的相关知识,语法分析,词法分析。。一大堆看起来很理论的东东,不过看起来枯燥之余,它们的价值...

3558
来自专栏影子

PostgreSQL=>递归查询

1545
来自专栏Kevin-ZhangCG

Oracle学习笔记二

格式: select * from 表1,表2   select * from emp;   select * from dept;

1573
来自专栏java达人

SQL索引优化

序言 数据库的优化方法有很多种,在应用层来说,主要是基于索引的优化。本次秘笈根据实际的工作经验,在研发原来已有的方法的基础上,进行了一些扩充,总结了基于索引的S...

2138
来自专栏青玉伏案

OracleDBA之表管理

  下面是Oracle表管理的部分,用到的测试表是oracle数据库中scott用户下的表做的测试,有的实验也用到了hr用户的数据,以下这些东西是我的麦库上存的...

2068

扫码关注云+社区

领取腾讯云代金券