如何按周转置结果集和分组?

编程入门行业动态更新时间:2024-10-09 11:21:08

本文介绍了如何按周转置结果集和分组?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述

我有一个基于查询的视图:

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.