主页 / user-12559207

Soenderby's questions

Martin Hope
Soenderby
Asked: 2025-02-08 02:22:56 +0800 CST
  • 5

clickhouse文档指出：

lagInFrame 行为与标准 SQL 滞后窗口函数不同。Clickhouse 窗口函数 lagInFrame 尊重窗口框架。

这个窗口框架是什么，它如何影响输出？

示例：我想在时间序列中找到连续两行的时间列值差大于给定阈值的行。

我想将每一行与前一行进行比较。

以下内容使我相信下面的查询是正确的方法。

为了获得与滞后相同的行为，请使用 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

SELECT lag, startdate, diff
FROM  (SELECT startdate,
              lagInFrame(startdate)
                OVER(
                  ORDER BY startdate ASC ROWS BETWEEN UBOUNDED PRECEDING AND
                  UNBOUNDED FOLLOWING) AS lag,
              Date_diff('minute', lag, startdate) AS diff
       FROM   <table>
       ORDER  BY startdate ASC)
WHERE  diff > 15

但是，以下查询给出了完全相同的结果并且占用更少的内存。

SELECT lag, startdate, diff
FROM  (SELECT startdate,
              lagInFrame(startdate)
                OVER(
                  ORDER BY startdate ASC ROWS BETWEEN 1 PRECEDING AND
                  CURRENT ROW) AS lag,
              Date_diff('minute', lag, startdate) AS diff
       FROM   <table>
       ORDER  BY startdate ASC)
WHERE  diff > 15

区别在于
OVER(ORDER BY startdate ASC ROWS BETWEEN UBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
：
OVER(ORDER BY startdate ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)

查询的输出是否存在差异的情况？

clickhouse