SQL 数据汇总与透视的实用案例
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
在这个查询中,使用 ISNULL
函数处理可能为空的状态数量,确保结果集中每个状态都有一个默认值 0。此外,我们计算了设备的总状态数量。
5. 结果分析
最终的结果集将展示每个设备的基本信息及其在用、闲置、维修的数量,以及总数量。这种格式的数据能够帮助决策者快速了解设备的使用情况,优化资源配置。
二、总结
通过这个案例,我们可以看到 SQL 在数据汇总与透视方面的强大能力。灵活运用这些查询技巧,可以帮助我们从复杂的数据中提取出清晰、易读的信息,为业务决策提供支持。在日常的数据处理工作中,掌握这些技能无疑会提升工作效率和分析能力。