postgresql - 如何将 SQL 连接重写为窗口函数？

Question

数据库是 HP Vertica 7 或 PostgreSQL 9。

create table test (
id int,
card_id int,
tran_dt date,
amount int
);

insert into test values (1, 1, '2017-07-06', 10);
insert into test values (2, 1, '2017-06-01', 20);
insert into test values (3, 1, '2017-05-01', 30);
insert into test values (4, 1, '2017-04-01', 40);
insert into test values (5, 2, '2017-07-04', 10);

过去 1 天使用的支付卡中，该卡在过去 90 天内收取的最高金额是多少。

select t.card_id, max(t2.amount) max
from test t
join test t2 on t2.card_id=t.card_id and t2.tran_dt>='2017-04-06'
where t.tran_dt>='2017-07-06'
group by t.card_id
order by t.card_id;

结果正确

card_id    max
-------    ---
1          30

我想将查询重写为 sql 窗口函数。

select card_id, max(amount) over(partition by card_id order by tran_dt range between '60 days' preceding and current row) max
from test
where card_id in (select card_id from test where tran_dt>='2017-07-06')
order by card_id;

但是结果集不匹配，怎么办？

测试数据在这里: http://sqlfiddle.com/#!17/db317/1

Answer 1

我不能尝试 PostgreSQL，但在 Vertica 中，您可以应用 ANSI 标准 OLAP 窗口函数。

但您需要嵌套两个查询:只有在结果集中包含需要计算的所有行时，窗口函数才会返回合理的结果。

但您只希望显示“2017-07-06”中的行。

因此您必须在外部查询中过滤该日期:

WITH olap_output AS (
  SELECT 
    card_id
  , tran_dt
  , MAX(amount) OVER (
      PARTITION BY card_id
      ORDER BY tran_dt
      RANGE BETWEEN '90 DAYS' PRECEDING AND CURRENT ROW
    ) AS the_max
  FROM test
)
SELECT
  card_id
, the_max
FROM olap_output
WHERE tran_dt='2017-07-06'
;

card_id|the_max
      1|     30