SQL FIRST_VALUE函数导致重复记录，求周度积分统计正确实现方案

问题根源 #

你当前的SQL逻辑存在两个核心问题：

• FIRST_VALUE窗口函数放在GROUP BY之后完全无效，聚合后每个(t2.date, t2.category)已经是唯一行，窗口函数无法正确实现「每周初始积分」的逻辑。
• 你要的初始积分应该是该周开始前的最后积分，而非该周内变动记录里的第一个previous_points，原逻辑完全搞错了初始积分的来源。

正确实现步骤 #

我们需要拆分逻辑，先计算每个品类的历史积分余额，再关联周表统计周度数据：

WITH daily_points AS (
    -- 计算每个品类每日的累计积分余额
    SELECT 
        category,
        order_date,
        -- 累计计算当前积分余额：加积分取正值，扣积分取负值
        SUM(CASE WHEN sign(event) = 1 THEN event_value WHEN sign(event) = -1 THEN -event_value ELSE 0 END) 
            OVER (PARTITION BY category ORDER BY order_date) AS current_balance,
        -- 获取前一天的积分余额，用于计算每周初始值
        LAG(current_balance) OVER (PARTITION BY category ORDER BY order_date) AS previous_day_balance
    FROM table1
),
weekly_initial_points AS (
    -- 提取每个品类每周的初始积分（周第一天前一天的余额）
    SELECT 
        category,
        -- 按周截断日期，不同数据库语法有差异：PostgreSQL用date_trunc，MySQL用DATE_FORMAT+YEARWEEK
        date_trunc('week', order_date) AS week_start,
        -- 取每周最后一条记录的前一天余额，作为该周初始积分
        FIRST_VALUE(previous_day_balance) OVER (PARTITION BY category, week_start ORDER BY order_date DESC) AS week_initial_points
    FROM daily_points
    UNION ALL
    -- 处理无历史记录的品类周，初始积分设为0
    SELECT DISTINCT category, date, 0 AS week_initial_points
    FROM table2
)
-- 关联周表统计最终周度数据
SELECT 
    t2.date AS week_end_date,
    t2.category,
    COALESCE(wip.week_initial_points, 0) AS previous_points,
    COALESCE(SUM(CASE WHEN sign(t1.event) = 1 THEN ABS(t1.event_value) ELSE 0 END), 0) AS points_added,
    COALESCE(SUM(CASE WHEN sign(t1.event) = -1 THEN ABS(t1.event_value) ELSE 0 END), 0) AS points_removed,
    -- 计算最终积分：初始积分 + 新增 - 扣除
    COALESCE(wip.week_initial_points, 0) + COALESCE(SUM(CASE WHEN sign(t1.event) = 1 THEN ABS(t1.event_value) ELSE -ABS(t1.event_value) END), 0) AS final_points
FROM table2 t2
LEFT JOIN weekly_initial_points wip 
    ON t2.category = wip.category 
    AND wip.week_start = t2.date - 6 -- 假设t2.date是周日（周最后一天），周起始为前6天的周一
LEFT JOIN table1 t1 
    ON t2.category = t1.category 
    AND t1.order_date BETWEEN t2.date - 6 AND t2.date
GROUP BY t2.date, t2.category, wip.week_initial_points
ORDER BY t2.category, t2.date;

关键说明 #

• 日期处理需适配你的数据库：比如MySQL要替换date_trunc为STR_TO_DATE(CONCAT(YEARWEEK(order_date, 1), ' Monday'), '%X%V %W')来获取周起始日期。
• 如果品类有未在table1中记录的初始积分，需要在daily_points CTE中加入初始化记录（例如UNION ALL SELECT category, '1970-01-01', initial_points, 0 FROM category_initial_table）。
• 原SQL的重复记录本质是窗口函数与GROUP BY混用导致的逻辑错误，拆分逻辑后每个(t2.date, t2.category)只会生成一条统计记录。

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