如何获取会员完成指定比例对应的最早FinalExpectedDay

问题分析 #

你遇到的核心问题是PERCENT_RANK()函数的计算逻辑不符合需求：它的公式是(排名值 - 1) / (分组内总行数 - 1)，当会员有5个任务时，计算出的百分比是0、0.25、0.5、0.75、1，和你预期的每完成1个任务对应20%进度（即1/5=20%、3/5=60%等）不匹配。

解决方案 #

直接基于会员的任务完成顺序（按FinalExpectedDay升序，取最早完成的日期），通过任务序号与总任务数的比例来匹配目标进度，或者直接用任务序号对应3/5、4/5、5/5的要求。

方案1：按进度百分比筛选并输出对应日期 #

WITH MemberTaskProgress AS (
    SELECT 
        MemberID,
        MemberName,
        ReportingQuarter,
        FinalExpectedDay,
        -- 计算实际完成进度：当前任务序号/总任务数
        ROW_NUMBER() OVER (PARTITION BY MemberID ORDER BY FinalExpectedDay ASC) * 1.0 / 
        COUNT(*) OVER (PARTITION BY MemberID) AS CompletionRate
    FROM YourTable
)
SELECT 
    MemberID,
    MemberName,
    ReportingQuarter,
    FinalExpectedDay AS TargetCompletionDay,
    CONCAT(ROUND(CompletionRate*100,0),'%') AS CompletionPercentage
FROM MemberTaskProgress
WHERE CompletionRate IN (0.6, 0.8, 0.9) -- 匹配60%、80%、90%进度
ORDER BY MemberID, CompletionRate;

方案2：每个会员一行，汇总各进度的最早日期 #

如果需要将每个会员的三个目标进度日期放在同一行展示，用以下语句：

WITH MemberTaskProgress AS (
    SELECT 
        MemberID,
        MemberName,
        ReportingQuarter,
        FinalExpectedDay,
        -- 按日期升序给每个会员的任务编号
        ROW_NUMBER() OVER (PARTITION BY MemberID ORDER BY FinalExpectedDay ASC) AS TaskOrder,
        COUNT(*) OVER (PARTITION BY MemberID) AS TotalTasks
    FROM YourTable
)
SELECT 
    MemberID,
    MemberName,
    ReportingQuarter,
    -- 3/5任务完成的最早日期（对应60%）
    MIN(CASE WHEN TaskOrder = 3 THEN FinalExpectedDay END) AS [60%_Completion_Day],
    -- 4/5任务完成的最早日期（对应80%）
    MIN(CASE WHEN TaskOrder = 4 THEN FinalExpectedDay END) AS [80%_Completion_Day],
    -- 5/5任务完成的最早日期（对应90%，按需求）
    MIN(CASE WHEN TaskOrder = 5 THEN FinalExpectedDay END) AS [90%_Completion_Day]
FROM MemberTaskProgress
GROUP BY MemberID, MemberName, ReportingQuarter
HAVING TotalTasks >= 5; -- 仅保留任务数≥5的会员

关键说明 #

• 用ROW_NUMBER()按FinalExpectedDay升序编号，确保序号1是会员完成的第一个任务，序号3对应第3个任务（即3/5进度）。
• 若存在同一日期完成多个任务的情况，可根据需求替换ROW_NUMBER()为RANK()或DENSE_RANK()来处理并列情况。

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