如何将行聚合数据转为列？SQL分组聚合行转列实现求助

核心需求拆解 #

• 按Location（城市）维度分组
• 统计每组的四类数据：
  • 在线计量设备数量
  • 离线计量设备数量
  • 串联（Tandem）设备数量
  • 非串联设备数量

最佳实现方案：条件聚合 #

不用复杂的PARTITION OVER窗口函数，直接用**CASE WHEN结合聚合函数**就能简洁实现行转列式的汇总，这是这类分组统计场景的标准写法。

样例数据假设 #

假设你的设备表名为meter_devices，结构与数据如下：

| Location | Status   | Is_Tandem |
|----------|----------|-----------|
| 北京     | 在线     | 1         |
| 北京     | 在线     | 0         |
| 北京     | 离线     | 1         |
| 上海     | 在线     | 0         |
| 上海     | 离线     | 0         |
| 广州     | 在线     | 1         |

期望输出格式 #

| Location | 在线设备数 | 离线设备数 | 串联设备数 | 非串联设备数 |
|----------|------------|------------|------------|--------------|
| 北京     | 2          | 1          | 2          | 1            |
| 上海     | 1          | 1          | 0          | 2            |
| 广州     | 1          | 0          | 1          | 0            |

可直接运行的SQL代码 #

SELECT
    Location,
    -- 统计在线设备：仅当Status为'在线'时计数
    COUNT(CASE WHEN Status = '在线' THEN 1 END) AS 在线设备数,
    -- 统计离线设备：仅当Status为'离线'时计数
    COUNT(CASE WHEN Status = '离线' THEN 1 END) AS 离线设备数,
    -- 统计串联设备：仅当Is_Tandem为1时计数（根据实际枚举值调整）
    COUNT(CASE WHEN Is_Tandem = 1 THEN 1 END) AS 串联设备数,
    -- 统计非串联设备：仅当Is_Tandem为0时计数
    COUNT(CASE WHEN Is_Tandem = 0 THEN 1 END) AS 非串联设备数
FROM meter_devices
GROUP BY Location
ORDER BY Location;

原PARTITION OVER方案失败的常见原因 #

1. 窗口函数误用：PARTITION OVER是给每行添加分组统计标签，而非直接生成汇总行，强行用它做汇总需要嵌套子查询+DISTINCT，逻辑冗余且容易出错。
2. 条件判断不严谨：比如Status的枚举值拼写错误（如大小写、中英文不一致）、Is_Tandem的取值判断错误（比如实际是'Y'/'N'而非1/0）。
3. 分组逻辑冲突：窗口函数不需要GROUP BY，如果混合使用会导致结果行数不符合预期。

特殊场景适配 #

如果存在Status或Is_Tandem为NULL的情况，可调整CASE WHEN逻辑：

-- 排除NULL状态的设备
COUNT(CASE WHEN Status = '在线' AND Status IS NOT NULL THEN 1 END) AS 在线设备数
-- 将NULL状态视为离线
COUNT(CASE WHEN Status = '在线' THEN 1 END) AS 在线设备数,
COUNT(CASE WHEN Status = '离线' OR Status IS NULL THEN 1 END) AS 离线设备数

内容的提问来源于stack exchange，提问作者brian pleshek