如何在MariaDB中基于历史记录值按page_id递增更新count列

问题背景 #

现有Visitor表数据如下：

其中id为1、2的记录count值正确，需要基于2022年各page_id对应的最后count值，对2023年及以后的记录count列进行递增更新，使同page_id的记录count依次加1，最终得到目标表：

解决方案 #

以MySQL为例，可通过CTE（公共表表达式）结合窗口函数实现批量更新：

-- 提取每个page_id在2022年的最终count值作为递增基数
WITH base_counts AS (
    SELECT page_id, MAX(count) AS base_count
    FROM Visitor
    WHERE date <= '2022-12-31'
    GROUP BY page_id
),
-- 为每个page_id的后续记录按id排序分配递增序号
ranked_records AS (
    SELECT id, page_id,
           ROW_NUMBER() OVER (PARTITION BY page_id ORDER BY id) AS increment
    FROM Visitor
    WHERE date > '2022-12-31'
)
-- 执行更新操作
UPDATE Visitor v
JOIN base_counts bc ON v.page_id = bc.page_id
JOIN ranked_records rr ON v.id = rr.id
SET v.count = bc.base_count + rr.increment;

逻辑说明 #

• base_counts：筛选2022年的记录，按page_id分组取最大count值，作为后续递增的起始值
• ranked_records：对2023年及以后的记录，按page_id分组、id排序，分配从1开始的递增序号
• 最后通过自连接将基数与序号相加，覆盖原表中count为0的记录值

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