如何将REPORTS_MASTER_LIST整合至执行次数统计矩阵以显示零值？

问题背景 #

现有三张数据表：

• REPORTS_MASTER_LIST：包含REPORT_NAME、REPORT_CATEGORY字段，存储所有可执行报表及其分类；
• USER_REPORT_EXECUTION：包含USERNAME、REPORT_NAME、REPORT_CATEGORY字段，每条记录代表用户执行的一次报表操作；
• USER_DEPARTMENTS_LIST：包含USERNAME、USER_DEPARTMENT字段，存储所有用户及其所属部门。

需要构建统计矩阵：

• 行维度：REPORT_CATEGORY（可展开显示REPORT_NAME）
• 列维度：USER_DEPARTMENT
• 矩阵值：对应部门对各报表的执行次数

当前实现仅使用USER_REPORT_EXECUTION和USER_DEPARTMENTS_LIST，存在问题：从未被执行的报表不会显示在矩阵中，需要这些报表显示并在所有部门列下显示0。

实现思路 #

要包含所有报表，需以REPORTS_MASTER_LIST为基础，先生成所有报表与部门的全量组合，再关联执行记录表统计次数，未匹配到执行记录的条目用0填充。

具体SQL实现 #

步骤1：生成报表-部门全量组合

先提取唯一的部门列表，再与所有报表做交叉连接，得到所有可能的报表-部门配对：

SELECT 
    rml.REPORT_CATEGORY,
    rml.REPORT_NAME,
    udl.USER_DEPARTMENT
FROM REPORTS_MASTER_LIST rml
CROSS JOIN (SELECT DISTINCT USER_DEPARTMENT FROM USER_DEPARTMENTS_LIST) udl

步骤2：关联执行记录统计次数

将全量组合表左关联到部门-报表执行统计结果，用COALESCE()把无执行记录的NULL转为0：

SELECT 
    full_combo.REPORT_CATEGORY,
    full_combo.REPORT_NAME,
    full_combo.USER_DEPARTMENT,
    COALESCE(execution_count.exec_count, 0) AS EXECUTION_COUNT
FROM (
    -- 生成所有报表-部门的全量组合
    SELECT 
        rml.REPORT_CATEGORY,
        rml.REPORT_NAME,
        udl.USER_DEPARTMENT
    FROM REPORTS_MASTER_LIST rml
    CROSS JOIN (SELECT DISTINCT USER_DEPARTMENT FROM USER_DEPARTMENTS_LIST) udl
) full_combo
LEFT JOIN (
    -- 统计各部门对各报表的执行次数
    SELECT 
        udl.USER_DEPARTMENT,
        ure.REPORT_CATEGORY,
        ure.REPORT_NAME,
        COUNT(*) AS exec_count
    FROM USER_REPORT_EXECUTION ure
    JOIN USER_DEPARTMENTS_LIST udl ON ure.USERNAME = udl.USERNAME
    GROUP BY udl.USER_DEPARTMENT, ure.REPORT_CATEGORY, ure.REPORT_NAME
) execution_count 
ON full_combo.REPORT_CATEGORY = execution_count.REPORT_CATEGORY
AND full_combo.REPORT_NAME = execution_count.REPORT_NAME
AND full_combo.USER_DEPARTMENT = execution_count.USER_DEPARTMENT
ORDER BY full_combo.REPORT_CATEGORY, full_combo.REPORT_NAME, full_combo.USER_DEPARTMENT;

关键说明 #

• CROSS JOIN：确保所有报表（无论是否被执行）都能和每个部门生成一条记录，解决未执行报表不显示的问题。
• LEFT JOIN：保留全量组合中的所有记录，仅匹配有执行记录的条目，无匹配时返回NULL。
• COALESCE()：将NULL转换为0，满足未执行报表显示0的需求。

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