SQL实现按字段变化的计数器：test重置，test1递增

根据你的需求，我们可以通过SQL的窗口函数来实现这个计数器逻辑。核心思路是按test分组，追踪同一组内test1的变化，每次变化时递增计数器，相同值保持计数器不变。

实现代码 #

假设你的表名为your_table，可以用以下SQL查询得到目标结果：

SELECT 
    test,
    test1,
    -- 累计变化次数并减1，让初始值从0开始
    SUM(CASE 
            WHEN prev_test1 IS NULL OR test1 != prev_test1 THEN 1 
            ELSE 0 
        END) OVER (PARTITION BY test ORDER BY (SELECT NULL)) - 1 AS counter
FROM (
    -- 先获取同一test分组内前一行的test1值
    SELECT 
        test,
        test1,
        LAG(test1) OVER (PARTITION BY test ORDER BY (SELECT NULL)) AS prev_test1
    FROM your_table
) AS subquery;

代码解释 #

1. 内层子查询：使用LAG(test1) OVER (PARTITION BY test ORDER BY ...)窗口函数，为每一行获取同一test分组内前一行的test1值。PARTITION BY test确保只在相同test的行之间比较。
2. 外层查询：
   • 用CASE语句标记变化：如果是分组内第一行（prev_test1为NULL），或者当前test1和前一行不同，标记为1，否则标记为0。
   • 用SUM() OVER (PARTITION BY test ORDER BY ...)对标记值做累计求和，再减1让计数器从0开始。
3. 关于排序：代码中用ORDER BY (SELECT NULL)是因为你的原始数据没有给出明确的排序字段，默认按数据存储顺序处理。如果你的表有实际的排序字段（比如时间戳、自增ID），建议替换成该字段，这样结果顺序更稳定。

验证示例数据 #

将你的示例数据代入查询，得到的结果完全符合你需要的计数器规则：

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