如何按周转置结果集和分组? [英] How do I transpose a result set and group by week?
问题描述
我有一个基于查询的视图:
I have a view based on query:
SELECT CONVERT(VARCHAR(10), date, 103) AS date,
eventid, name, time, pts
FROM results
WHERE DATEPART(yy, date) = 2019;
这提供了这样的数据集:
This provides a data set such as this:
Date EventID Name Time Points
24/04/2019 10538 Fred Flintstone 22:27 10
24/04/2019 10538 Barney Rubble 22:50 9
24/04/2019 10538 Micky Mouse 23:17 8
24/04/2019 10538 Yogi Bear 23:54 7
24/04/2019 10538 Donald Duck 24:07 6
01/05/2019 10541 Barney Rubble 21:58 10
01/05/2019 10541 Fred Flintstone 22:00 9
01/05/2019 10541 Donald Duck 23:39 8
01/05/2019 10541 Yogi Bear 23:43 7
12/06/2019 10569 Fred Flintstone 22:06 10
12/06/2019 10569 Barney Rubble 22:22 9
12/06/2019 10569 Micky Mouse 23:05 8
12/06/2019 10569 Donald Duck 23:55 7
我需要每个名字的输出行，列出每轮的点数和以下形式的总数:
I need an output row for each name listing the pts per round and a total in the form:
Name 24/04/2019 01/05/2019 12/06/2019 total
Fred Flintstone 10 9 10 29
Barney Rubble 9 10 9 28
Yogi Bear 7 7 7 21
Micky Mouse 8 8 16
Donald Duck 6 8 14
一年中最多可以有 16 个非连续的事件日期.
There could be up to 16 non-consecutive event dates for the year.
推荐答案
PIVOT 没有错，但对我来说，最简单、最高效的方法是执行 交叉表.语法更简洁、更易于移植且更易于理解.
Nothing wrong with PIVOT but, for me, the easiest and most performant way to do this would be to perform a Cross Tab. The syntax is less verbose, more portable, and easier to understand.
首先是一些 DDL 和易于使用的示例数据.<<<<了解如何做到这一点，它会更快地为您提供更好的答案.
First for some DDL and easily consumable sample data. <<< Learn how to do this it will get you better answers more quickly.
SET NOCOUNT ON;
SET DATEFORMAT dmy; -- I need this because I'm American
-- DDL and easily consumable sample data
DECLARE @Results TABLE
(
[Date] DATE,
EventId INT,
[Name] VARCHAR(40), -- if indexed, go as narrow as possible
[Time] TIME,
Points INT,
INDEX uq_poc_results CLUSTERED([Name],[EventId]) -- a covering index is vital for a query like this
); -- note: ^^^ this bad clustered index candidate, I went this route for simplicity
INSERT @Results VALUES
('4/04/2019', 10538, 'Fred Flintstone', '22:27',10),
('24/04/2019',10538, 'Barney Rubble', '22:50',9),
('24/04/2019',10538, 'Micky Mouse ', '23:17',8),
('24/04/2019',10538, 'Yogi Bear', '23:54',7),
('24/04/2019',10538, 'Donald Duck', '2307',6),
('01/05/2019',10541, 'Barney Rubble', '21:58',10),
('01/05/2019',10541, 'Fred Flintstone', '22:00',9),
('01/05/2019',10541, 'Donald Duck', '23:39',8),
('01/05/2019',10541, 'Yogi Bear', '23:43',7),
('12/06/2019',10569, 'Fred Flintstone', '22:06',10),
('12/06/2019',10569, 'Barney Rubble', '22:22',9),
('12/06/2019',10569, 'Micky Mouse', '23:05',8),
('12/06/2019',10569, 'Donald Duck', '23:55',7);
请注意，我在 (Name,EventId) 上创建了一个聚集索引 - 我将使用一个非聚集索引来覆盖您在现实世界中需要的列.如果您有很多行，那么您将需要该索引.
Note that I created a clustered index on (Name,EventId) - I would use a non-clustered index that covered the columns you need in the real world. If you have a lot of rows then you will want that index.
基本交叉表
SELECT [Name] = r.[Name],
[24/04/2019] = MAX(CASE r.[Date] WHEN '24/04/2019' THEN r.Points ELSE 0 END),
[01/05/2019] = MAX(CASE r.[Date] WHEN '01/05/2019' THEN r.Points ELSE 0 END),
[12/06/2019] = MAX(CASE r.[Date] WHEN '12/06/2019' THEN r.Points ELSE 0 END)
FROM @Results AS r
GROUP BY r.[Name];
结果:
Name 24/04/2019 01/05/2019 12/06/2019
-------------------- ------------ ------------ ------------
Barney Rubble 9 10 9
Donald Duck 6 8 7
Fred Flintstone 0 9 10
Micky Mouse 8 0 8
Yogi Bear 7 7 0
要获得总数，我们可以将其包装在子查询中的逻辑中并添加如下列:
To get the total we can wrap this in logic in a subquery and add the columns like this:
SELECT
[Name] = piv.N,
[24/04/2019] = piv.D1,
[01/05/2019] = piv.D2,
[12/06/2019] = piv.D3,
Total = piv.D1+piv.D2+piv.D3
FROM
(
SELECT r.[Name],
MAX(CASE r.[Date] WHEN '24/04/2019' THEN r.Points ELSE 0 END),
MAX(CASE r.[Date] WHEN '01/05/2019' THEN r.Points ELSE 0 END),
MAX(CASE r.[Date] WHEN '12/06/2019' THEN r.Points ELSE 0 END)
FROM @Results AS r
GROUP BY r.[Name]
) AS piv(N,D1,D2,D3);
退货:
Name 24/04/2019 01/05/2019 12/06/2019 Total
------------------- ----------- ----------- ----------- -------
Barney Rubble 9 10 9 28
Donald Duck 6 8 7 21
Fred Flintstone 0 9 10 19
Micky Mouse 8 0 8 16
Yogi Bear 7 7 0 14
这不仅可以用很少的 SQL 获得您需要的东西，还可以从子查询内的预聚合中受益.这种方法相对于 PIVOT 的一个巨大好处是您可以在一个查询中进行多个聚合.以下是如何将这种方法用于多个聚合的两个示例；这首先使用标准 GROUP BY 两次，另一个使用窗口聚合函数 (.. OVER (partition by, order by..):
Not only does this get you what you need with very little SQL, you benefit from pre-aggregation inside the subquery. A huge benefit of this approach over PIVOT is how you can do multiple aggregations in one query. Below are two examples of how to use this approach for multiple aggregations; this first using a standard GROUP BY twice, the other using window aggregate functions (.. OVER (partition by, order by..):
--==== Traditional Approach
SELECT
[Name] = piv.N,
[24/04/2019] = MAX(piv.D1),
[01/05/2019] = MAX(piv.D2),
[12/06/2019] = MAX(piv.D3),
Total = MAX(f.Ttl),
Avg1 = AVG(piv.D1), -- 1st date (24/04/2019)
Avg2 = AVG(piv.D2), -- 2nd date...
Avg3 = AVG(piv.D3), -- 3rd date...
TotalAvg = AVG(f.Ttl) ,
Mn = MIN(f.Ttl) ,
Mx = MAX(f.Ttl)
FROM
(
SELECT r.[Name],
MAX(CASE r.[Date] WHEN '24/04/2019' THEN r.Points ELSE 0 END),
MAX(CASE r.[Date] WHEN '01/05/2019' THEN r.Points ELSE 0 END),
MAX(CASE r.[Date] WHEN '12/06/2019' THEN r.Points ELSE 0 END)
FROM @Results AS r
GROUP BY r.[Name]
) AS piv(N,D1,D2,D3)
CROSS APPLY (VALUES(piv.D1+piv.D2+piv.D3)) AS f(Ttl)
GROUP BY piv.N;
--==== Leveraging Window Aggregates
SELECT
[Name] = piv.N,
[24/04/2019] = piv.D1,
[01/05/2019] = piv.D2,
[12/06/2019] = piv.D3,
Total = f.Ttl,
Avg1 = AVG(piv.D1) OVER(PARTITION BY piv.N ORDER BY (SELECT NULL)), -- 1st date (24/04/2019)
Avg2 = AVG(piv.D2) OVER(PARTITION BY piv.N ORDER BY (SELECT NULL)), -- 2nd date...
Avg3 = AVG(piv.D3) OVER(PARTITION BY piv.N ORDER BY (SELECT NULL)), -- 3rd date...
TotalAvg = AVG(f.Ttl) OVER(PARTITION BY piv.N ORDER BY (SELECT NULL)),
Mn = MIN(f.Ttl) OVER(PARTITION BY piv.N ORDER BY (SELECT NULL)),
Mx = MAX(f.Ttl) OVER(PARTITION BY piv.N ORDER BY (SELECT NULL))
FROM
(
SELECT r.[Name],
MAX(CASE r.[Date] WHEN '24/04/2019' THEN r.Points ELSE 0 END),
MAX(CASE r.[Date] WHEN '01/05/2019' THEN r.Points ELSE 0 END),
MAX(CASE r.[Date] WHEN '12/06/2019' THEN r.Points ELSE 0 END)
FROM @Results AS r
GROUP BY r.[Name]
) AS piv(N,D1,D2,D3)
CROSS APPLY (VALUES(piv.D1+piv.D2+piv.D3)) AS f(Ttl);
两者都返回:
Name 24/04/2019 01/05/2019 12/06/2019 Total Avg1 Avg2 Avg3 TotalAvg Mn Mx
----------------- ----------- ----------- ----------- ------ ------ ------ ------ ---------- ------ ------
Barney Rubble 9 10 9 28 9 10 9 28 28 28
Donald Duck 6 8 7 21 6 8 7 21 21 21
Fred Flintstone 0 9 10 19 0 9 10 19 19 19
Micky Mouse 8 0 8 16 8 0 8 16 16 16
Yogi Bear 7 7 0 14 7 7 0 14 14 14
要动态处理列，您需要查看:交叉标签和透视，第 2 部分 -动态交叉表 作者:Jeff Moden.
To handle the columns dynamically you need to have a look at: Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs by Jeff Moden.
这篇关于如何按周转置结果集和分组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
