如何基于日期条件重复数据行并填充Frequency字段？

现有数据 #

预期结果 #

规则说明 #

• 日期为当日：生成DTD、WTD、MTD、YTD四种行
• 日期为当周非当日：生成WTD、MTD、YTD三种行
• 日期为当月非当周：生成MTD、YTD两种行
• 日期为当年非当月：仅生成YTD行

解决方案 #

用CROSS JOIN结合频率选项表，再通过日期条件过滤匹配的频率值，比多次UNION更简洁高效。以下是SQL Server适配的示例代码：

-- 定义所有需要的频率类型
WITH FrequencyList AS (
    SELECT 'DTD' AS Frequency UNION ALL
    SELECT 'WTD' UNION ALL
    SELECT 'MTD' UNION ALL
    SELECT 'YTD'
),
-- 替换为你的实际数据表
RawData AS (
    SELECT Call, [Column B] AS DateCol
    FROM YourTableName
)
SELECT 
    rd.Call,
    rd.DateCol AS [Column B],
    fl.Frequency
FROM RawData rd
CROSS JOIN FrequencyList fl
WHERE
    (
        -- 当日：保留所有频率
        CONVERT(DATE, rd.DateCol) = CONVERT(DATE, GETDATE())
        OR
        -- 当周非当日：保留WTD、MTD、YTD
        (DATEPART(WEEK, rd.DateCol) = DATEPART(WEEK, GETDATE()) 
         AND DATEPART(YEAR, rd.DateCol) = DATEPART(YEAR, GETDATE())
         AND CONVERT(DATE, rd.DateCol) != CONVERT(DATE, GETDATE())
         AND fl.Frequency IN ('WTD', 'MTD', 'YTD'))
        OR
        -- 当月非当周：保留MTD、YTD
        (DATEPART(MONTH, rd.DateCol) = DATEPART(MONTH, GETDATE()) 
         AND DATEPART(YEAR, rd.DateCol) = DATEPART(YEAR, GETDATE())
         AND DATEPART(WEEK, rd.DateCol) != DATEPART(WEEK, GETDATE())
         AND fl.Frequency IN ('MTD', 'YTD'))
        OR
        -- 当年非当月：仅保留YTD
        (DATEPART(YEAR, rd.DateCol) = DATEPART(YEAR, GETDATE())
         AND DATEPART(MONTH, rd.DateCol) != DATEPART(MONTH, GETDATE())
         AND fl.Frequency = 'YTD')
    )
ORDER BY rd.Call, rd.DateCol, 
    CASE fl.Frequency 
        WHEN 'DTD' THEN 1 
        WHEN 'WTD' THEN 2 
        WHEN 'MTD' THEN 3 
        WHEN 'YTD' THEN 4 
    END;

适配说明 #

如果使用MySQL，只需替换：

• GETDATE() → CURDATE()
• DATEPART(WEEK, ...) → WEEK(...)
• DATEPART(MONTH, ...) → MONTH(...)

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