关联Sales表过滤后仍显示所有Employee Master表行的实现方案

现有表结构及数据 #

员工主表（employeeMaster） #

销售事实表（salesFact） #

当前问题 #

关联两表并按特定月份过滤时，无当月销售记录的员工不会被展示：

• 过滤2023年10月，仅显示员工1和2
• 过滤2023年11月，仅显示员工1和3

需求 #

创建视图，实现每个月份都显示所有员工，无销售记录的员工Amount字段为NULL。例如过滤2023年10月时，员工3的Amount为NULL。

现有方案痛点 #

当前采用每月手动关联两表并插入带MonthYear字段新表的方式，操作繁琐，需避免手动执行。

限制条件 #

Databricks工作区不支持递归CTE。

建表及插入数据SQL #

-- 创建员工主表
CREATE TABLE employeeMaster (
    empId INT,
    empName STRING
);

-- 插入员工数据
INSERT INTO employeeMaster VALUES
(1, 'Jack'),
(2, 'John'),
(3, 'Luke');

-- 创建销售事实表
CREATE TABLE salesFact (
    empId INT,
    SalesDate DATE,
    Amount INT
);

-- 插入销售数据
INSERT INTO salesFact VALUES
(1, '2023-10-12', 100),
(2, '2023-10-23', 200),
(1, '2023-11-12', 100),
(3, '2023-11-23', 200),
(2, '2023-12-12', 100),
(3, '2023-12-23', 200);

解决方案 #

通过生成员工与所有存在月份的笛卡尔积，再左连接销售表实现需求，无需手动维护：

创建视图的SQL语句 #

CREATE OR REPLACE VIEW employee_monthly_sales AS
WITH distinct_months AS (
    -- 提取销售表中所有唯一的年月（以当月第一天标识）
    SELECT DATE_TRUNC('month', SalesDate) AS month_start
    FROM salesFact
    GROUP BY DATE_TRUNC('month', SalesDate)
),
employee_month_cross AS (
    -- 生成所有员工与所有年月的笛卡尔积，确保每个月份都包含全部员工
    SELECT 
        e.empId,
        e.empName,
        dm.month_start
    FROM employeeMaster e
    CROSS JOIN distinct_months dm
)
-- 左连接销售表，匹配对应员工和月份的销售数据，无数据则Amount为NULL
SELECT 
    emc.empId,
    emc.empName,
    emc.month_start AS sales_month,
    sf.Amount
FROM employee_month_cross emc
LEFT JOIN salesFact sf
    ON emc.empId = sf.empId
    AND DATE_TRUNC('month', sf.SalesDate) = emc.month_start;

视图查询示例 #

查询2023年10月的全员工销售数据：

SELECT * 
FROM employee_monthly_sales
WHERE sales_month = '2023-10-01';

查询结果：

说明 #

1. distinct_months 自动提取销售表中所有有记录的月份，无需手动指定；
2. 笛卡尔积保证每个月份都包含所有员工，避免遗漏无销售记录的员工；
3. 视图会自动同步源表的数据更新，无需每月手动执行插入操作。

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