在学习sql中,看到一个很不错的例子:累积聚合,滑动聚合
累积聚合(从序列内第一个元素到当前元素的数据)创建测试表USE tempdb;
GOIF OBJECT_ID('dbo.EmpOrders') IS NOT NULL
DROP TABLE dbo.EmpOrders;
GOCREATE TABLE dbo.EmpOrders
(
empid INT NOT NULL,
ordmonth DATETIME NOT NULL,
qty INT NOT NULL,
PRIMARY KEY(empid, ordmonth)
);INSERT INTO dbo.EmpOrders(empid, ordmonth, qty)
SELECT O.EmployeeID,
CAST(CONVERT(CHAR(6), O.OrderDate, 112) + '01'
AS DATETIME) AS ordmonth,
SUM(Quantity) AS qty
FROM Northwind.dbo.Orders AS O
JOIN Northwind.dbo.[Order Details] AS OD
ON O.OrderID = OD.OrderID
GROUP BY EmployeeID,
CAST(CONVERT(CHAR(6), O.OrderDate, 112) + '01'
AS DATETIME);-- Content of EmpOrders Table
SELECT empid, CONVERT(VARCHAR(7), ordmonth, 121) AS ordmonth, qty
FROM dbo.EmpOrders
ORDER BY empid, ordmonth;
GO
累积聚合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 DECIMAL(12, 2)) AS avgqty
FROM dbo.EmpOrders AS O1
JOIN dbo.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;
滑动聚合(按顺序对滑动窗口计算的聚合)
累积聚合(从序列内第一个元素到当前元素的数据)创建测试表USE tempdb;
GOIF OBJECT_ID('dbo.EmpOrders') IS NOT NULL
DROP TABLE dbo.EmpOrders;
GOCREATE TABLE dbo.EmpOrders
(
empid INT NOT NULL,
ordmonth DATETIME NOT NULL,
qty INT NOT NULL,
PRIMARY KEY(empid, ordmonth)
);INSERT INTO dbo.EmpOrders(empid, ordmonth, qty)
SELECT O.EmployeeID,
CAST(CONVERT(CHAR(6), O.OrderDate, 112) + '01'
AS DATETIME) AS ordmonth,
SUM(Quantity) AS qty
FROM Northwind.dbo.Orders AS O
JOIN Northwind.dbo.[Order Details] AS OD
ON O.OrderID = OD.OrderID
GROUP BY EmployeeID,
CAST(CONVERT(CHAR(6), O.OrderDate, 112) + '01'
AS DATETIME);-- Content of EmpOrders Table
SELECT empid, CONVERT(VARCHAR(7), ordmonth, 121) AS ordmonth, qty
FROM dbo.EmpOrders
ORDER BY empid, ordmonth;
GO
累积聚合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 DECIMAL(12, 2)) AS avgqty
FROM dbo.EmpOrders AS O1
JOIN dbo.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;
滑动聚合(按顺序对滑动窗口计算的聚合)
解决方案 »
- 为何java就是不能执行这句查询呢?
- 建学生信息表时学生ID为什么都是用CHAR
- 请问这样的约束应该如何写?我读了好几本SQL编程书都没看到
- sql分组查询最后3条记录~~~
- 用循环添加数据快还是用insert into ()select × from快?
- 请教复合查询得到重复结果的解决方法!
- 一个算术公式的SQL编程,请高手出来一下,谢谢!
- 这样的查询语句怎么写???
- 急!!在线等.设置里安全性标签下的允许跨数据库所有权链接为什么是灰的?
- ▓▓▓▓各位大侠请教,关于用BestCrypt v.7加密,但忘记密码,如何办??急急急急急急急急急▓▓▓▓▓
- 在线等待。。菜鸟级问题。。高分
- 问题重新描述
技术内幕上好像有