如何按日期与地点透视最近3次报表日期的UserReport SQL查询结果？

需求描述 #

我有一张UserReport表，用于记录缺少公司要求AD属性的用户。表中包含以下字段：

• ReportDate：每周生成报表的日期
• Site：用户所属站点，站点归属于Division分区

需要按站点生成最近3次报表日期的历史透视表，展示各站点在对应日期的用户数量。

当前执行的SQL查询 #

SELECT division, site, reportdate, COUNT(reportdate) as CountUsers 
FROM UserReport
WHERE division = 'US South'
GROUP BY division, site, reportdate

当前查询结果 #

期望的查询结果 #

解决方案 #

要实现透视表效果，需要通过条件聚合结合预筛选最近3个报表日期来实现。以下是适配当前数据的SQL语句：

-- 先筛选出US South分区的最近3个报表日期
WITH Latest3Dates AS (
    SELECT DISTINCT ReportDate
    FROM UserReport
    WHERE Division = 'US South'
    ORDER BY ReportDate DESC
    LIMIT 3
),
-- 生成所有站点与最近3个日期的组合，确保每个站点在每个日期都有记录
SiteDateCombination AS (
    SELECT DISTINCT ur.Division, ur.Site, l3d.ReportDate
    FROM UserReport ur
    CROSS JOIN Latest3Dates l3d
    WHERE ur.Division = 'US South'
)
SELECT
    sdc.Division,
    sdc.Site,
    -- 按日期分别统计用户数，无数据则显示0
    SUM(CASE WHEN sdc.ReportDate = '2023-05-19' THEN COALESCE(ur.CountUsers, 0) ELSE 0 END) AS `2023-05-19`,
    SUM(CASE WHEN sdc.ReportDate = '2023-05-24' THEN COALESCE(ur.CountUsers, 0) ELSE 0 END) AS `2023-05-24`,
    SUM(CASE WHEN sdc.ReportDate = '2023-05-26' THEN COALESCE(ur.CountUsers, 0) ELSE 0 END) AS `2023-05-26`
FROM SiteDateCombination sdc
-- 关联原统计数据，补全用户数
LEFT JOIN (
    SELECT Division, Site, ReportDate, COUNT(*) AS CountUsers
    FROM UserReport
    WHERE Division = 'US South'
    GROUP BY Division, Site, ReportDate
) ur ON sdc.Division = ur.Division 
    AND sdc.Site = ur.Site 
    AND sdc.ReportDate = ur.ReportDate
GROUP BY sdc.Division, sdc.Site
ORDER BY sdc.Site;

说明 #

1. 若需要动态适配最近3个日期（无需手动修改日期值），需根据你使用的数据库类型编写动态SQL：

   • MySQL：使用预处理语句拼接日期列
   • SQL Server：使用PIVOT结合动态SQL
   • PostgreSQL：使用crosstab函数或动态拼接条件聚合语句

2. COALESCE函数用于将NULL值转换为0，确保无数据的日期显示0而非空白。

内容的提问来源于stack exchange，提问作者John FNG