sql - Postgres 窗口函数：对满足基于当前行的条件的窗口框架的记录应用聚合函数

Question

这个问题是关于 Postgres 12 的 SQL 语法的。

让我们假设一个stock_prices包含以下列的表：ticker, date, price。

我有兴趣在前 10 天的窗口上执行计算，例如：

SELECT 
  ticker,
  date, 
  price, 
  AVG(price) 
    OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 10 PRECEDING AND 1 PRECEDING) as avg_previous_10d
FROM stock_prices

除了上面的简单平均计算之外，我还想对前 10 天窗口内满足当前记录相关条件的记录执行计算。具体来说，在之前的 10 天窗口内：

• 计算价格高于当前行价格的次数
• 计算高于当前行价格的平均价格
• 查找价格第一次高于当前行的价格

等等。

我知道我可以执行自我加入，例如对这个问题的回答。我的问题是 - 有没有一种使用窗口函数语法的简单方法来做到这一点？还是自我加入是唯一的出路？

谢谢！

标签： sqlpostgresqlpostgresql-12

Answer 1

你为什么不使用self join如下：

SELECT count(1) as num_times,
       Avg(s10.price) as avg_price,
       Min(s10.date) as first_time_higher_price     
  FROM stock_prices scur
  Join stock_prices s10 on s10.ticker = scur.ticker
 Where scur.date = current_date
   And s10.date >= current_date - interval '10 day' 
   And s10.date < current_date
   And s10.price >= scur.price