sql - 使用具有不同 GROUP BY 的聚合函数后如何摆脱 JOIN 的重复结果

问题描述

我正在尝试在表上使用聚合函数来跟踪分配给用户的任务的时间进度。

表的一部分看起来像这样：

表tasks：

TaskID | UserID | RegistrationDate | StartDate  |   EndDate  | Status
--------------------------------------------------------------------------
 tId1  |  uId1  |     dd/10/2000   | dd/10/2000 | dd/10/2000 | completed    
 tId2  |  uId2  |     dd/10/2000   | dd/10/2000 | dd/12/2000 | completed
 tId3  |  uId3  |     dd/10/2000   | dd/12/2000 | dd/02/2001 | completed  
 tId4  |  uId4  |     dd/10/2000   | dd/11/2000 | dd/11/2000 | cancelled
 tId5  |  uId5  |     dd/11/2000   | dd/11/2000 | dd/01/2001 | completed
 tId6  |  uId6  |     dd/12/2000   |    NULL    |    NULL    | registered
 tId7  |  uId7  |     dd/12/2000   | dd/12/2000 |    NULL    | in progress    
 tId8  |  uId8  |     dd/01/2001   | dd/01/2001 | dd/01/2001 | cancelled    
 tId9  |  uId9  |     dd/01/2001   | dd/01/2001 | dd/02/2001 | completed    
 tId10 |  uId10 |     dd/02/2001   |    NULL    |    NULL    | registered
 tId11 |  uId11 |     dd/02/2001   | dd/02/2001 | dd/02/2001 | completed
 tId12 |  uId12 |     dd/02/2001   | dd/02/2001 | dd/02/2001 | completed
......

我的目标是能够根据不同的标准使用聚合器，这样我就可以得到以下几点：

  Year | Month | NewR | Completed_A| Completed_B | Cancelled_A | Cancelled_B | OpenR | 
---------------------------------------------------------------------------------------
 2000  |   10  |   4  |      3     |      1      |      1      |      0      |   0   |
 2000  |   11  |   1  |      1     |      0      |      0      |      1      |   0   |
 2000  |   12  |   2  |      0     |      1      |      0      |      0      |   2   |
 2001  |   1   |   2  |      1     |      1      |      1      |      1      |   0   |
 2001  |   2   |   3  |      1     |      4      |      0      |      0      |   3   |
......

RegistrationDate，StartDate并且EndDate都是时间戳，我正在尝试按时间顺序每月对我的结果进行排序。我遇到的问题是我正在使用GROUP BY声明来执行此操作，虽然我确实希望时间进度基于注册日期，但我不一定想按它对我的输出进行分组。例如，在上表NewR中，所有发生在对应月份的注册RegistrationDate，Completed_A计算在任何时间注册RegistrationDate并完成的任务，同时Completed_B计算在任何时间注册但在完成的任务EndDate，从而获得A我需要的标准GROUP BY RegistrationDate，并获得B我需要的标准GROUP BY EndDate。

我需要能够在单个查询中执行此操作，我目前这样做的方式是嵌套在一个凌乱SELECT的 aJOIN中，但是我得到了我想摆脱的冗余列并且无法弄清楚如何. 我的代码看起来像这样：

SELECT * 
FROM
 (SELECT 
    TO_CHAR(RegistrationDate, 'yyyy-mm')                    AS MonthYear    
,   DATE_PART('year',RegistrationDate)                      AS rYear
,   DATE_PART('month',RegistrationDate)                     AS rMonth
,   COUNT(*)                                                AS NewR
, SUM(CASE WHEN Status = 'Completed' THEN 1 ELSE 0 END)     AS Completed_A
, SUM(CASE WHEN Status IN ('Registered','In Progress')
     THEN 1 ELSE 0 END)                                     AS OpenR
FROM Tasks 
GROUP BY MonthYear, rYear, rMonth)      AS  T1

LEFT JOIN

(SELECT 
    TO_CHAR(EndDate, 'yyyy-mm')                             AS MonthYear    
,   DATE_PART('year', EndDate)                              AS EndYear
,   DATE_PART('month', EndDate)                             AS EndMonth
, SUM(CASE WHEN Status = 'Completed' THEN 1 ELSE 0 END)     AS Completed_B
FROM Tasks
GROUP BY MonthYear, EndYear, EndMonth)  AS  T2

ON T1.MonthYear = T2.MonthYear
ORDER BY MonthYear

我添加了一个同时包含月份和年份的列，因为这是我可以弄清楚如何简化最终连接输出的正确排序的唯一方法，而且我还得到了一些累积总和，所以它真的可以帮助我SUM (stuff) OVER (ORDER BY MonthYear)

总体上是否有更好的方法在具有不同GROUP BYs 的单个语句上使用聚合？
如何摆脱重复的月份、年份和'yyyy-mm'列？我真的不想不使用SELECT *顶部的，因为在我的实际表中，我有更多的列我无法一一选择，而且我也在为其他一些与时间相关的标准这样做（所以实际上我的表有与我使用的许多标准一样多的重复冗余月份和年份列）。我试过添加一个DISTINCT，但没有帮助。

哦，我目前正在使用 PostgreSQL，但有可能将其迁移到 SQL Server。（不过，我试图尽可能多地使用标准 SQL 并避免使用特定的 SQL Server 功能）。

我确实注意到有类似的问题，但答案并没有真正帮助我解决这个问题。

标签： sqlpostgresqlgroup-byaggregate-functions

SELECT Year , Month , SUM(CASE WHEN DTYPE='R' THEN 1 ELSE 0 END) AS NewR , SUM(CASE WHEN DTYPE='R' AND Status = 'Completed' THEN 1 ELSE 0 END) AS Completed_A , SUM(CASE WHEN DTYPE='E' AND Status = 'Completed' THEN 1 ELSE 0 END) AS Completed_B , SUM(CASE WHEN DTYPE='R' AND Status IN ('Registered','In Progress') THEN 1 ELSE 0 END) AS OpenR FROM (SELECT 'R' AS DTYPE , DATE_PART('year',RegistrationDate) AS Year , DATE_PART('month',RegistrationDate) AS Month , Status FROM Tasks UNION ALL SELECT 'E' AS DTYPE , DATE_PART('year',EndDate) , DATE_PART('month',EndDate) , Status FROM Tasks) T WHERE Year IS NOT NULL GROUP BY Year, Month ORDER BY Year, Month;

sql - 使用具有不同 GROUP BY 的聚合函数后如何摆脱 JOIN 的重复结果

问题描述

解决方案

推荐阅读