SQL Server生成含全客户及年月产品组合的销售视图求助

需求说明 #

• 必须展示所有客户，包含无任何销售记录的客户
• 对每个客户，若某产品在某年份存在销售记录，则需列出该年份的所有年月与该产品的组合，无销售的年月销售额填充为0

实现思路 #

1. 提取客户-产品-年份的有效组合：从销售事实表中筛选出所有有销售记录的客户、产品及对应年份，确保每个组合只保留唯一的年份信息
2. 生成完整展示框架：将上述有效组合与日期表中对应年份的所有年月做关联，得到需要展示的全量行
3. 关联并填充销售数据：左连接销售事实表，存在的销售额保留，无销售的用0填充
4. 关联维度表：替换业务键为客户名称和产品名称

完整T-SQL代码 #

测试代码（基于用户提供的变量表） #

-- 初始化测试表变量
DECLARE @DIM_CUSTOMERS TABLE([BusinessKey] INT,[Customer] NVARCHAR(255))
INSERT INTO @DIM_CUSTOMERS VALUES 
(10000, 'Kevin N.V.'),
(10001, 'V.Z.W. Frederik'),
(10002, 'Klaas N.V.')

DECLARE @DIM_PRODUCTS TABLE([BusinessKey] INT, [Product] NVARCHAR(255))
INSERT INTO @DIM_PRODUCTS VALUES
(9000, 'PH114'),
(9001, 'PH272'),
(9002, 'PH878')

DECLARE @DIM_DATES TABLE([BusinessKey] INT, [Year] INT, [Month] INT, [YearMonth] INT, [YearMonthText] NVARCHAR(20))
INSERT INTO @DIM_DATES VALUES
(202301, 2023, 1, 202301, '2023.01'),
(202302, 2023, 2, 202302, '2023.02'),
(202303, 2023, 3, 202303, '2023.03'),
(202304, 2023, 4, 202304, '2023.04'),
(202305, 2023, 5, 202305, '2023.05'),
(202306, 2023, 6, 202306, '2023.06'),
(202307, 2023, 7, 202307, '2023.07'),
(202308, 2023, 8, 202308, '2023.08'),
(202309, 2023, 9, 202309, '2023.09'),
(202310, 2023, 10, 202310, '2023.10'),
(202311, 2023, 11, 202311, '2023.11'),
(202312, 2023, 12, 202312, '2023.12'),
(202401, 2024, 1, 202401, '2024.01'),
(202402, 2024, 2, 202402, '2024.02'),
(202403, 2024, 3, 202403, '2024.03'),
(202404, 2024, 4, 202404, '2024.04'),
(202405, 2024, 5, 202405, '2024.05'),
(202406, 2024, 6, 202406, '2024.06'),
(202407, 2024, 7, 202407, '2024.07'),
(202408, 2024, 8, 202408, '2024.08'),
(202409, 2024, 9, 202409, '2024.09'),
(202410, 2024, 10, 202410, '2024.10'),
(202411, 2024, 11, 202411, '2024.11'),
(202412, 2024, 12, 202412, '2024.12')

DECLARE @FACT_SALES TABLE([ID] INT, [FK_Product] INT, [FK_Customer] INT, [FK_Date] INT, [Sales] FLOAT)
INSERT INTO @FACT_SALES VALUES
(1, 9000, 10000, 202303, 90.48),
(2, 9000, 10000, 202304, 20.40),
(3, 9002, 10000, 202305, 250.85),
(4, 9002, 10000, 202303, 100.50),
(5, 9000, 10000, 202403, 38.40),
(6, 9000, 10000, 202406, 474.50),
(7, 9001, 10000, 202403, 128.60),
(8, 9001, 10000, 202404, 144.97),
(9, 9000, 10002, 202303, 199.60),
(10, 9001, 10002, 202302, 58.97),
(11, 9001, 10002, 202402, 40.88)

-- 核心查询逻辑
SELECT
    c.Customer,
    p.Product,
    d.YearMonthText,
    ISNULL(SUM(f.Sales), 0) AS Sales
FROM
    @DIM_CUSTOMERS c
    -- 左连接有销售记录的客户-产品-年份组合
    LEFT JOIN (
        SELECT DISTINCT
            fs.FK_Customer,
            fs.FK_Product,
            dt.Year
        FROM @FACT_SALES fs
        JOIN @DIM_DATES dt ON fs.FK_Date = dt.BusinessKey
    ) cp_year ON c.BusinessKey = cp_year.FK_Customer
    -- 关联产品维度表
    LEFT JOIN @DIM_PRODUCTS p ON cp_year.FK_Product = p.BusinessKey
    -- 关联对应年份的所有日期，生成全量年月行
    LEFT JOIN @DIM_DATES d ON 
        (cp_year.Year = d.Year OR cp_year.Year IS NULL)
    -- 匹配销售数据
    LEFT JOIN @FACT_SALES fs ON 
        c.BusinessKey = fs.FK_Customer
        AND p.BusinessKey = fs.FK_Product
        AND d.BusinessKey = fs.FK_Date
-- 分组确保每个客户-产品-年月唯一
GROUP BY
    c.Customer,
    p.Product,
    d.YearMonthText
-- 排序便于查看结果
ORDER BY
    c.Customer,
    p.Product,
    d.YearMonthText

正式视图创建代码（基于实际数据库表） #

CREATE VIEW vw_Sales_Complete
AS
SELECT
    c.Customer,
    p.Product,
    d.YearMonthText,
    ISNULL(SUM(f.Sales), 0) AS Sales
FROM
    DIM_CUSTOMERS c
    LEFT JOIN (
        SELECT DISTINCT
            fs.FK_Customer,
            fs.FK_Product,
            dt.Year
        FROM FACT_SALES fs
        JOIN DIM_DATES dt ON fs.FK_Date = dt.BusinessKey
    ) cp_year ON c.BusinessKey = cp_year.FK_Customer
    LEFT JOIN DIM_PRODUCTS p ON cp_year.FK_Product = p.BusinessKey
    LEFT JOIN DIM_DATES d ON 
        (cp_year.Year = d.Year OR cp_year.Year IS NULL)
    LEFT JOIN FACT_SALES fs ON 
        c.BusinessKey = fs.FK_Customer
        AND p.BusinessKey = fs.FK_Product
        AND d.BusinessKey = fs.FK_Date
GROUP BY
    c.Customer,
    p.Product,
    d.YearMonthText

关键细节说明 #

• 用DISTINCT提取唯一的客户-产品-年份组合，避免重复生成行
• 多层左连接保证无销售记录的客户被完整保留；若需隐藏无对应产品的行，可在WHERE子句添加p.Product IS NOT NULL
• ISNULL(SUM(f.Sales), 0)确保无销售的年月销售额显示为0，同时聚合同一组合的多条销售记录
• 分组操作保证每个客户-产品-年月组合只返回一行

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