PostgreSQL:区间“10天"和当前行之间的范围 [英] PostgreSQL: RANGE BETWEEN INTERVAL '10 DAY' AND CURRENT ROW
问题描述
我有一张表,用于存储每件商品的每日价格.如果价格未更新,则当天没有该商品的记录.
I have a table which stores, for every item, the daily price. If the price hasn't been updated, there isn't a record for that item on that day.
我需要编写一个查询,为每个项目检索从当前行日期起 10 天回顾窗口的最新价格,否则返回 NULL
.我正在考虑使用 RANGE BETWEEN INTERVAL
语句来实现这一点.类似的东西:
I need to write a query which retrieves, for every item, the most recent price with a lookback window of 10 days from the current row date otherwise return NULL
. I was thinking to achieve that using a RANGE BETWEEN INTERVAL
statement. Something like:
SELECT
DATE(datetime),
item_id,
LAST(price) OVER(
PARTITION BY item_id
ORDER BY datetime DESC
RANGE BETWEEN INTERVAL '10 DAYS' AND CURRENT ROW
) AS most_recent_price_within_last_10days
FROM ...
GROUP BY
date,
item_id,
price
不幸的是,这个查询引发了一个错误:
Unfortunately this query raises an error:
LINE 20: RANGE BETWEEN INTERVAL '10 DAY' PRECEDING AND CURRENT ROW
^
我看到一篇旧博客文章说在 Postgres 中不可能进行这样的操作.这仍然准确吗?
I came across an old blog post saying such operation is not possible in Postgres. Is this still accurate?
推荐答案
您可以使用 ROW_NUMBER() 为每个项目提取最近 10 天内的最新记录:
You could use ROW_NUMBER() to pull out the most recent record within the last 10 days for each item:
SELECT *
FROM (
SELECT
DATE(datetime),
item_id,
price AS most_recent_price_within_last_10days,
ROW_NUMBER() OVER(PARTITION BY item_id ORDER BY datetime DESC) rn
FROM ...
WHERE datetime > NOW() - INTERVAL '10 DAY'
) x WHERE rn = 1
在子查询中,WHERE 子句对日期范围进行过滤;ROW_NUMBER() 为具有相同 item_id 的记录组中的每条记录分配一个排名,最近的记录在前.然后,外部查询只过滤行号为 1 的记录.
In the subquery, the WHERE clause does the filtering on the date range; ROW_NUMBER() assigns a rank to each record within groups of records having the same item_id, with the most recent record first. Then, the outer query just filters in records having row number 1.
这篇关于PostgreSQL:区间“10天"和当前行之间的范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!