于SQL的数据分析--免费.pptVIP

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * SELECT objectid, MAX(CASE WHEN attribute = attr1 THEN value END) AS attr1, MAX(CASE WHEN attribute = attr2 THEN value END) AS attr2, MAX(CASE WHEN attribute = attr3 THEN value END) AS attr3, MAX(CASE WHEN attribute = attr4 THEN value END) AS attr4, MAX(CASE WHEN attribute = attr5 THEN value END) AS attr5 FROM dbo.OpenSchema GROUP BY objectid; * SELECT objectid, attr1, attr2, attr3, attr4, attr5 FROM dbo.OpenSchema PIVOT(MAX(value) FOR attribute IN([attr1],[attr2],[attr3],[attr4],[attr5])) AS P; * USE tempdb; GO IF OBJECT_ID(dbo.Groups) IS NOT NULL DROP TABLE dbo.Groups; GO CREATE TABLE dbo.Groups ( groupid VARCHAR(10) NOT NULL, memberid INT NOT NULL, string VARCHAR(10) NOT NULL, val INT NOT NULL, PRIMARY KEY (groupid, memberid) ); 窗口聚集 * USE tempdb; GO IF OBJECT_ID(dbo.EmpOrders) IS NOT NULL DROP TABLE dbo.EmpOrders; GO CREATE 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); * empid ordmonth qty 1 1997-11 172 1 1997-12 583 1 1998-01 397 1 1998-02 566 1 1998-03 467 1 1998-04 586 1 1998-05 299 2 1996-07 50 2 1996-08 94 2 1996-09 137 * --累积聚集 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