你真的会玩SQL吗？你所不知道的数据聚合-阿里云开发者社区

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

View Code

这里使用的是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

View Code

所有聚合函数都会忽略NULL值，只有一个例外：Count(*)

聚合中常见的函数为分组函数GROUP BY ，要注意的是 GROUP BY 原则 select后面所有列中没有使用聚合函数的列必须出现在GROUP BY 后面

文章标签：

SQL

数据库

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

热门文章

最新文章

相关课程

相关电子书

相关实验场景

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