SQL查询优化求助：将邮箱与组的连接状态数据聚合为指定格式

我有一张用于跟踪邮箱（Mailbox）和组（Group）的表，每个邮箱对应3个不同的组。我希望查看每日所有邮箱及组的连接状态（Connected）。我编写了如下查询，但返回多行数据，想要将数据聚合为指定的预期格式，恳请帮忙！

现有查询 #

Select cast (CreatedDate as Date), Connected, GroupOrMbx, GroupType 
from [dbo].[Mbx_test]
group by cast (CreatedDate as Date), Connected, GroupOrMbx, GroupType

预期输出 #

表结构与测试数据 #

CREATE TABLE [dbo].[Mbx_test](
    [GroupOrMbx] [varchar](10) NOT NULL,    
    [GroupName] [varchar](255) NULL,
    [GroupEmail] [varchar](255) NULL,
    [GroupType] [varchar](10) NULL,
    [MBXName] [varchar](255) NULL,
    [MBXEmail] [varchar](255) NULL,
    [Connected] [bit] NOT NULL,
    [CreatedDate] [datetime] NOT NULL
) 

INSERT INTO Mbx_test
VALUES 
('mbx', NULL, NULL,NULL,'mbx1','mbx1@test.com',1,'2022-09-22'),
('group', 'group1','group1@test.com','W','mbx1','mbx1@test.com',1,'2022-09-22'),
('group', 'group2','group2@test.com','M','mbx1','mbx1@test.com',1,'2022-09-22'),
('group', 'group3','group3@test.com','R','mbx1','mbx1@test.com',1,'2022-09-22'),

('mbx', NULL, NULL,NULL,'mbx2','mbx2@test.com',1,'2022-09-22'),
('group', 'group4','group4@test.com','W','mbx2','mbx2@test.com',1,'2022-09-22'),
('group', 'group5','group5@test.com','M','mbx2','mbx2@test.com',1,'2022-09-22'),
('group', 'group6','group6@test.com','R','mbx2','mbx2@test.com',1,'2022-09-22'),

('mbx', NULL, NULL,NULL,'mbx3','mbx3@test.com',0,'2022-09-22'),
('group', 'group7','group7@test.com','W','mbx3','mbx3@test.com',0,'2022-09-22'),
('group', 'group8','group8@test.com','M','mbx3','mbx3@test.com',0,'2022-09-22'),
('group', 'group9','group9@test.com','R','mbx3','mbx3@test.com',0,'2022-09-22'),

('mbx', NULL, NULL,NULL,'mbx4','mbx4@test.com',0,'2022-09-22'),
('group', 'group10','group10@test.com','W','mbx4','mbx4@test.com',0,'2022-09-22'),
('group', 'group11','group11@test.com','M','mbx4','mbx4@test.com',0,'2022-09-22'),
('group', 'group12','group12@test.com','R','mbx4','mbx4@test.com',0,'2022-09-22')

解决方案 #

要实现预期的聚合格式，可使用条件聚合，通过CASE语句结合聚合函数统计不同类型的数量：

SELECT
    CAST(CreatedDate AS DATE) AS [Date],
    Connected,
    COUNT(CASE WHEN GroupOrMbx = 'mbx' THEN 1 END) AS Mailbox,
    COUNT(CASE WHEN GroupType = 'W' THEN 1 END) AS [W Group],
    COUNT(CASE WHEN GroupType = 'M' THEN 1 END) AS [M Group],
    COUNT(CASE WHEN GroupType = 'R' THEN 1 END) AS [R Group]
FROM [dbo].[Mbx_test]
GROUP BY CAST(CreatedDate AS DATE), Connected
ORDER BY CAST(CreatedDate AS DATE), Connected DESC;

说明 #

• COUNT(CASE WHEN ... THEN 1 END)仅统计满足条件的行数，不满足条件的返回NULL，COUNT会自动忽略NULL值，从而得到对应类型的数量。
• 按日期和连接状态分组，可将同一日期、同一连接状态下的邮箱及各组数量聚合到一行。

内容的提问来源于stack exchange，提问作者POSH Guy