如何在ClickHouse中基于时间序列检测vals列的数值变化？

我有如下结构的ClickHouse表：

需要基于created_at的时间顺序检测vals列的数值变化，在新值出现时标记为1，其余情况标记为0，期望输出如下：

我尝试了以下查询，但未达到预期效果：

SELECT
    vals,
    created_at,
    lagInFrame(vals) OVER (
      PARTITION BY 
      vals
      order by created_at desc
    ) AS hasChanged

FROM taable
ORDER BY created_at DESC

请问如何实现需求中的数值变化检测？

你之前的查询错误在于按vals分区，这会把相同vals的记录分到一组，无法跨组对比相邻时间的vals变化。正确的做法是不分区，直接按created_at倒序排序，用lagInFrame获取上一条记录的vals，再对比当前vals是否与上一条不同，同时处理第一条记录（没有上一条，标记为1）。

具体查询如下：

SELECT
    vals,
    created_at,
    CASE
        -- 第一条记录（最新时间）标记为1
        WHEN lagInFrame(vals) OVER (ORDER BY created_at DESC) IS NULL THEN 1
        -- 当前vals与上一条不同则标记为1，否则0
        WHEN vals != lagInFrame(vals) OVER (ORDER BY created_at DESC) THEN 1
        ELSE 0
    END AS HasChanged
FROM taable
ORDER BY created_at DESC

或者可以简化为用if函数，逻辑一致：

SELECT
    vals,
    created_at,
    if(
        lagInFrame(vals) OVER (ORDER BY created_at DESC) IS NULL OR vals != lagInFrame(vals) OVER (ORDER BY created_at DESC),
        1,
        0
    ) AS HasChanged
FROM taable
ORDER BY created_at DESC

逻辑说明 #

1. 不使用PARTITION BY，窗口函数会遍历所有按created_at倒序排列的记录。
2. lagInFrame(vals)获取当前记录的上一条（时间更早一条）的vals值。
3. 第一条记录没有上一条，lagInFrame返回NULL，直接标记为1。
4. 后续记录如果当前vals和上一条不同，标记为1；相同则标记为0，完全匹配需求中的输出。

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