如何修改SQL查询获取近30天各阶段数据及占比

我原本用下面的SQL查询当日各阶段数据的计数，现在需要修改成获取近30天的数据，并且计算每个阶段的占比。

原SQL #

DECLARE @date DATE
SET @date = CONVERT(DATE, GETDATE())

SELECT  CASE
                    WHEN @date > X.PresentationDate AND @date < GiveAwayDate THEN 'D3'
                    WHEN @date >= DATEADD(DD, -4, X.PresentationDate) AND @date <= X.PresentationDate THEN 'D2'
                    WHEN @date < DATEADD(DD, -4, X.PresentationDate) THEN 'D1'
                    ELSE 'Unknown'
        END AS 'Stage',
        COUNT(*) AS 'Count'
FROM(
        SELECT DISTINCT     GiveAwayDate,
                            CreatedD AS 'CreatedDate',
                            CONVERT(DATE, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Comm, 'Cancel Date: ', ''), 'th ', ' '), 'rd ', ' '), 'nd ', ' '), '1st ', '1 ')) AS 'PresentationDate'
        FROM                A_CD CD
        INNER JOIN(
                            SELECT DISTINCT
                                    Bran,
                                    Agent,
                                    Reference
                            FROM
                                    Loans
                            WHERE
                                    InsertDate = @date) A
        ON                  LEFT(CD.Bran, 1) = A.Bran
        AND                 CD.Agent = A.Agent
        AND                 CD.Reference = A.Reference
        WHERE               Comm2 = 'D1'
        AND     LTRIM(RTRIM(Done)) = ''
        AND     CreatedD              != @date
        AND     GiveAwayDate                > @date) X
GROUP BY CASE
                    WHEN @date > X.PresentationDate AND @date < GiveAwayDate THEN 'D3'
                    WHEN @date >= DATEADD(DD, -4, X.PresentationDate) AND @date <= X.PresentationDate THEN 'D2'
                    WHEN @date < DATEADD(DD, -4, X.PresentationDate) THEN 'D1'
                    ELSE 'Unknown'
        END

原SQL输出 #

Stage   Count
D1      818
D2      839
D3      56

期望输出 #

我需要获取近30天各阶段的计数及占比，期望输出如下：

Stage   Count Percentage
D1      818   47.75
D2      839   48.97
D3      56    3.38

我的尝试（未成功） #

DECLARE @date DATE
DECLARE @prevDate DATE
SET @date = CONVERT(DATE, GETDATE())
SET @prevDate = DATEADD(day,-30,@date)
    
    SELECT  CASE
                        WHEN @date > X.PresentationDate AND @date < GiveAwayDate THEN 'D3'
                        WHEN @date >= DATEADD(DD, -4, X.PresentationDate) AND @date <= X.PresentationDate THEN 'D2'
                        WHEN @date < DATEADD(DD, -4, X.PresentationDate) THEN 'D1'
                        ELSE 'Unknown'
            END AS 'Stage',
            COUNT(*) AS 'Count'
    FROM(
            SELECT DISTINCT     GiveAwayDate,
                                CreatedD AS 'CreatedDate',
                                CONVERT(DATE, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Comm, 'Cancel Date: ', ''), 'th ', ' '), 'rd ', ' '), 'nd ', ' '), '1st ', '1 ')) AS 'PresentationDate'
            FROM                A_CD CD
            INNER JOIN(
                                SELECT DISTINCT
                                        Bran,
                                        Agent,
                                        Reference
                                FROM
                                        Loans
                                WHERE
                                        InsertDate between @date AND @prevDate) A
            ON                  LEFT(CD.Bran, 1) = A.Bran
            AND                 CD.Agent = A.Agent
            AND                 CD.Reference = A.Reference
            WHERE               Comm2 = 'D1'
            AND     LTRIM(RTRIM(Done)) = ''
            AND     CreatedD              != @date
            AND     GiveAwayDate     between @date AND @prevDate) X
    GROUP BY CASE
                        WHEN @date > X.PresentationDate AND @date < GiveAwayDate THEN 'D3'
                        WHEN @date >= DATEADD(DD, -4, X.PresentationDate) AND @date <= X.PresentationDate THEN 'D2'
                        WHEN @date < DATEADD(DD, -4, X.PresentationDate) THEN 'D1'
                        ELSE 'Unknown'
            END

你的尝试存在两个核心问题：

1. BETWEEN 区间顺序错误：BETWEEN 要求左值小于等于右值，你写的 InsertDate between @date AND @prevDate 中 @date 是当前日期，@prevDate 是30天前，区间无效，应改为 InsertDate BETWEEN @prevDate AND @date；同理 GiveAwayDate 的条件也需修正顺序，但原逻辑是筛选 GiveAwayDate > @date，不需要改为 BETWEEN。
2. 缺少占比计算逻辑：需要用窗口函数 SUM(COUNT(*)) OVER() 计算总计数，再用阶段计数除以总计数得到占比，最后格式化保留两位小数。

修正后的SQL如下：

DECLARE @date DATE
DECLARE @prevDate DATE
SET @date = CONVERT(DATE, GETDATE())
SET @prevDate = DATEADD(day, -30, @date)

WITH StageCounts AS (
    SELECT  
        CASE
            WHEN @date > X.PresentationDate AND @date < X.GiveAwayDate THEN 'D3'
            WHEN @date >= DATEADD(DD, -4, X.PresentationDate) AND @date <= X.PresentationDate THEN 'D2'
            WHEN @date < DATEADD(DD, -4, X.PresentationDate) THEN 'D1'
            ELSE 'Unknown'
        END AS Stage,
        COUNT(*) AS Count
    FROM(
        SELECT DISTINCT 
            GiveAwayDate,
            CreatedD AS CreatedDate,
            CONVERT(DATE, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Comm, 'Cancel Date: ', ''), 'th ', ' '), 'rd ', ' '), 'nd ', ' '), '1st ', '1 ')) AS PresentationDate
        FROM A_CD CD
        INNER JOIN(
            SELECT DISTINCT
                Bran,
                Agent,
                Reference
            FROM Loans
            WHERE InsertDate BETWEEN @prevDate AND @date
        ) A ON LEFT(CD.Bran, 1) = A.Bran
            AND CD.Agent = A.Agent
            AND CD.Reference = A.Reference
        WHERE Comm2 = 'D1'
            AND LTRIM(RTRIM(Done)) = ''
            AND CreatedD != @date
            AND GiveAwayDate > @date
    ) X
    GROUP BY 
        CASE
            WHEN @date > X.PresentationDate AND @date < X.GiveAwayDate THEN 'D3'
            WHEN @date >= DATEADD(DD, -4, X.PresentationDate) AND @date <= X.PresentationDate THEN 'D2'
            WHEN @date < DATEADD(DD, -4, X.PresentationDate) THEN 'D1'
            ELSE 'Unknown'
        END
)
SELECT 
    Stage,
    Count,
    ROUND((Count * 100.0) / SUM(Count) OVER(), 2) AS Percentage
FROM StageCounts
ORDER BY Stage;

说明 #

• 用CTE StageCounts 先计算各阶段计数，逻辑更清晰；
• 修正 InsertDate 的 BETWEEN 区间顺序，确保筛选近30天的数据；
• 保留原 GiveAwayDate > @date 的条件，符合原统计逻辑；
• 通过窗口函数 SUM(Count) OVER() 获取总计数，计算占比后用 ROUND 保留两位小数；
• 添加 ORDER BY Stage 让结果更整齐，可按需移除。

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