了解窗口函数

Question

Postgres-sql：

我无法找到可以帮助我理解如何正确使用带有/内部窗口函数的聚合以及如何正确使用 RANGE/ROWS frame_clause 的资源。我在网上查看了一些资源，例如： https://www.compose.com/articles/metrics -maven-window-frames-in-postgresql/
（我真的很喜欢这篇文章，它解决了一些疑问）

1. 在窗口函数中正确使用聚合 总和() 超过(...) 有时会在我最不期望的时候生成多行，或者要求我向 groupby 添加更多列

例如： 对于这里的问题：https://pgexercises.com/questions/aggregates/fachours4.html

select facid, total from
(select facid, sum(slots) total, RANK() OVER (order by sum(slots) desc) pos
from cd.bookings
group by facid
 ) A
where pos = 1

我写了这个查询并且它工作正常。但为此： https://pgexercises.com/questions/aggregates/rollingavg.html，我写了这个查询：

select starttime::date, sum(slots * case when memid=0 then guestcost
                        else membercost end) over (order by starttime::date asc
                                                   range BETWEEN INTERVAL '14 day' 
                                                   PRECEDING AND 
                                                   CURRENT ROW)::decimal/15 revenue
from cd.bookings B inner join cd.facilities F
on B.facid = F.facid
where starttime >= '2012-08-01' and starttime <= '2012-08-31' 
group by starttime::date, slots, memid, guestcost, membercost
order by starttime::date

它要求我将slots、memid、guestcost、membercost添加到groupby中，当它们是平均计算的一部分时。 我还没有正确解决这个问题。

2. 不同的窗口函数如何与 RANGE/ROWS 一起操作？ 例如，rank() 似乎在没有为其指定框架子句 UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 的情况下产生正确的结果，但对于此处的 last_value() 计算，需要指定此子句

SELECT
    product_name,
    group_name,
    price,
    LAST_VALUE (price) OVER (
        PARTITION BY group_name
        ORDER BY
            price RANGE BETWEEN UNBOUNDED PRECEDING
        AND UNBOUNDED FOLLOWING
    ) AS highest_price_per_group
FROM
    products
INNER JOIN product_groups USING (group_id);

：(这个例子取自这里： https://www.postgresqltutorial.com/postgresql-window-function/ - 抱歉，无法在页面中选择要链接到此处的确切区域）

vs

SELECT
    RANK () OVER ( 
        ORDER BY price
    ) rank_number 
FROM
    sales;

->;无需指定 BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 子句即可正确排名。

Answer 1

https://momjian.us/main/writings/pgsql/window.pdf（幻灯片 35 和幻灯片 36）
https://modern-sql.com/caniuse/over_range_ Between_(numeric)< br>
我希望下面的图片可以帮助您理解over rows与over range之间的区别。