如何在SQL中创建含完整日期及周均活动填充的视图？

核心调整思路 #

内连接（JOIN）会自动过滤掉无匹配数据的日期，要保留DATE_TABLE的所有日期，需将DATE_TABLE作为主表，改用**左连接（LEFT JOIN）**关联其他CTE表，再通过条件判断填充缺失的星期几平均值。

修改后的代码示例 #

WITH CTE2 AS (
    -- 预先计算各活动类型+星期几的平均值
    SELECT 
        Activity,
        Day_of_Week,
        AVG(Trans) AS Avg_Trans,
        AVG(Items) AS Avg_Items,
        AVG(Scans) AS Avg_Scans
    FROM 每日活动表
    GROUP BY Activity, Day_of_Week
)
SELECT 
    b.[date],
    b.[Day_Name],
    ISNULL(c.[Activity], d.[Activity]) AS Activity,
    b.[Day_of_Week],
    -- 优先取实际数据，无数据则用对应星期的平均值
    COALESCE(SUM(c.[Trans]), d.[Avg_Trans]) AS Trans,
    COALESCE(SUM(c.[Items]), d.[Avg_Items]) AS Items,
    COALESCE(SUM(c.[Scans]), d.[Avg_Scans]) AS Scans
FROM [LOG].[dbo].[DATE_TABLE] b
-- 左连接CTE1，保留DATE_TABLE所有日期
LEFT JOIN CTE1 c
    ON b.[date] = c.[date]
-- 左连接CTE2，关联活动类型和星期几，用于补全平均值
LEFT JOIN CTE2 d
    ON c.[Activity] = d.[Activity]
    AND b.[Day_of_Week] = d.[Day_of_Week]
GROUP BY b.[date], b.[Day_Name], ISNULL(c.[Activity], d.[Activity]), b.[Day_of_Week], d.[Avg_Trans], d.[Avg_Items], d.[Avg_Scans]

关键说明 #

• 替换JOIN为LEFT JOIN：确保DATE_TABLE中所有日期都被纳入结果，即使CTE1或CTE2无对应数据。
• COALESCE/ISNULL函数：优先使用CTE1中的实际活动统计值，当实际数据为空（无活动日期）时，自动切换到CTE2中预先计算的对应星期几的活动平均值。
• CTE2预计算：提前统计每个活动类型在对应星期几的平均数据，避免重复计算，提升视图性能。

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