窗口函数或公共表表达式：计算范围内的前几行

我想使用窗口函数来确定每一行满足特定条件的先前记录的总数。

一个具体的例子：

clone=# \d test
              Table "pg_temp_2.test"
 Column |            Type             | Modifiers 
--------+-----------------------------+-----------
 id     | bigint                      | 
 date   | timestamp without time zone | 

我想知道每个date'之前 1 小时内'的行数date.

我可以用窗口函数来做到这一点吗？或者我需要调查 CTE 吗？

我真的希望能够写出类似的东西（不起作用）：

SELECT id, date, count(*) OVER (HAVING previous_rows.date >= (date - '1 hour'::interval))
FROM test;

我可以通过加入针对自身的测试来编写此内容，如下所示 - 但这不会随着特别大的表而扩展。

SELECT a.id, a.date, count(b.*)-1 
FROM test a, test b 
WHERE (b.date >= a.date - '1 hour'::interval AND b.date < a.date)
GROUP BY 1,2
ORDER BY 2;

我可以用递归查询来做这件事吗？还是常规 CTE？ 我对 CTE 还不是很了解。我有预感我很快就会去。 :)

我认为你不能用简单的查询、CTE 和窗口函数便宜地做到这一点 - 它们的框架定义是静态的，但你需要一个动态框架（取决于列值）。

一般来说，您必须仔细定义窗口的下限和上限：以下查询exclude当前行和include下边框。
仍然有一个细微的差别：该函数包括当前行的前一个同级，而相关子查询则排除它们......

测试用例

Using ts而不是保留字date作为列名。

CREATE TABLE test (
  id  bigint
, ts  timestamp
);

ROM - 罗曼的查询 https://stackoverflow.com/a/18175342/939860

使用 CTE，将时间戳聚合到数组中，取消嵌套，计数...
虽然正确，但性能急剧恶化有一手多的行。这里有几个性能杀手。见下文。

ARR - 计数数组元素

我接受了 Roman 的查询并尝试简化它：

• 删除不必要的第二个 CTE。
• 将第一个 CTE 转换为子查询，速度更快。
• Direct count()而不是重新聚合到数组中并用array_length().

但数组处理成本高昂，而且性能仍然不佳严重恶化有更多行。

SELECT id, ts
     , (SELECT count(*)::int - 1
        FROM   unnest(dates) x
        WHERE  x >= sub.ts - interval '1h') AS ct
FROM (
   SELECT id, ts
        , array_agg(ts) OVER(ORDER BY ts) AS dates
   FROM   test
   ) sub;

COR-- 相关子查询

You could用一个简单的相关子查询来解决它。速度快了很多，但仍然...

SELECT id, ts
     , (SELECT count(*)
        FROM   test t1
        WHERE  t1.ts >= t.ts - interval '1h'
        AND    t1.ts < t.ts) AS ct
FROM   test t
ORDER  BY ts;

FNC-功能

按时间顺序循环行row_number() in plpgsql 函数并将其与cursor https://www.postgresql.org/docs/current/plpgsql-cursors.html在同一查询上，跨越所需的时间范围。然后我们可以减去行数：

CREATE OR REPLACE FUNCTION running_window_ct(_intv interval = '1 hour')
  RETURNS TABLE (id bigint, ts timestamp, ct int)
  LANGUAGE plpgsql AS
$func$
DECLARE
   cur   CURSOR FOR
         SELECT t.ts + _intv AS ts1, row_number() OVER (ORDER BY t.ts) AS rn
         FROM   test t ORDER BY t.ts;
   rec   record;
   rn    int;

BEGIN
   OPEN cur;
   FETCH cur INTO rec;
   ct := -1;  -- init

   FOR id, ts, rn IN
      SELECT t.id, t.ts, row_number() OVER (ORDER BY t.ts)
      FROM   test t ORDER BY t.ts
   LOOP
      IF rec.ts1 >= ts THEN
         ct := ct + 1;
      ELSE
         LOOP
            FETCH cur INTO rec;
            EXIT WHEN rec.ts1 >= ts;
         END LOOP;
         ct := rn - rec.rn;
      END IF;

      RETURN NEXT;
   END LOOP;
END
$func$;

默认一小时间隔的呼叫：

SELECT * FROM running_window_ct();

或任意间隔：

SELECT * FROM running_window_ct('2 hour - 3 second');

db<>fiddle
_{Old sqlfiddle http://www.sqlfiddle.com/#!17/5faffd/1}

基准

根据上面的表格，我在我的旧测试服务器上运行了一个快速基准测试：（Debian 上的 PostgreSQL 9.1.9）。

-- TRUNCATE test;
INSERT INTO test
SELECT g, '2013-08-08'::timestamp
         + g * interval '5 min'
         + random() * 300 * interval '1 min' -- halfway realistic values
FROM   generate_series(1, 10000) g;

CREATE INDEX test_ts_idx ON test (ts);
ANALYZE test;  -- temp table needs manual analyze

我改变了bold每轮比赛的一部分，并取得 5 场最佳成绩EXPLAIN ANALYZE.

100 rows
ROM：27.656 毫秒
ARR：7.834 毫秒
响应时间：5.488 毫秒
FNC: 1.115 ms

1000 行
ROM：2116.029 毫秒
ARR：189.679 毫秒
响应时间：65.802 毫秒
FNC: 8.466 ms

5000行
ROM：51347 毫秒！
ARR：3167 毫秒
响应时间：333 毫秒
FNC: 42 ms

100000 行
游戏ROM：地下城与勇士
到达时间：DNF
响应时间：6760 毫秒
FNC: 828 ms

函数是明显的胜利者。它速度最快一个数量级，并且扩展性最好。
数组处理无法竞争。