当前位置：首页 > article >正文

SQL 数据汇总与透视的实用案例

article 2024/10/30 19:52:05

SQL 数据汇总与透视的实用案例

一、前言
- 1. 案例背景
- 2. 数据准备
- 3. 数据透视
- 4. 主查询整合数据
- 5. 结果分析
二、总结

一、前言

在数据分析和报表生成中，SQL 查询的灵活性和强大功能使其成为不可或缺的工具。在许多实际场景中，我们需要从复杂的数据集中提取有用的信息，并以可读的形式展示。本文将通过一个具体的案例，讲解如何使用 SQL 的常见操作，如数据汇总和透视。

1. 案例背景

假设我们有两个数据表：

hga_ptss：记录设备的状态信息，包括设备编号、状态（如在用、闲置、维修）和数量。
hga_zcmmwh：存储设备的基本信息，如设备编号、名称和其他相关属性。

我们的目标是统计每种状态的设备数量，并与设备的基本信息结合，生成一个综合报表。

2. 数据准备

首先，我们需要从 hga_ptss 表中筛选出有效的设备状态。有效状态包括“在用”、“闲置”和“维修”，而“报废”和“出售”状态的设备将被排除。接着，我们对这些状态进行汇总。

WITH ct1 AS (
    SELECT glpm, zzzt, SUM(shul) AS sl 
    FROM hga_ptss
    WHERE zzzt NOT IN ('报废', '出售')
    GROUP BY glpm, zzzt
)

在这个 Common Table Expression (CTE) 中，我们使用 SUM 函数计算每种设备在不同状态下的总数量，并按设备编号和状态分组。

3. 数据透视

接下来，我们需要将状态数据转换成列，以便更直观地查看每种状态的设备数量。使用 PIVOT 操作，我们可以将设备状态（zzzt）变为列名，并显示每种状态对应的数量。

CT2 AS (
    SELECT * 
    FROM ct1
    PIVOT (MAX(sl) FOR zzzt IN ([在用], [闲置], [维修])) AS whc01
)

在此部分，我们使用 MAX 函数，因为每个设备状态的数量是唯一的，这样可以避免在透视过程中出现聚合错误。

4. 主查询整合数据

最后，我们将透视后的数据与设备基本信息表进行连接。通过左连接，我们确保即使某些设备在状态表中没有记录，也能保留设备的基本信息。

SELECT
    t0.*,
    ISNULL([在用], 0) AS zysl_v,
    ISNULL([闲置], 0) AS kysl_v,
    ISNULL([维修], 0) AS wzsl_v,
    ISNULL([在用], 0) + ISNULL([闲置], 0) + ISNULL([维修], 0) AS zsl_v
FROM hga_zcmmwh t0
LEFT JOIN ct2 ON t0.id = ct2.glpm