Trino (PrestoSQL)的前一年到日期窗口函数

Question

我试图弄清楚如何获得"前一年到目前为止"这段时间的总和和平均值。只使用一个窗口函数，因为我有很多窗口函数，并希望保持我的代码尽可能简单。我不知道其他人是否会发现这有用，但在这里展示我自己的解决方案，以防它有帮助。

去年至今，前一年，去年等(不同公司使用的术语)

Answer 1

我相信这就是你想要的:

SELECT
sum(metric) OVER prior_year_to_date,
avg(metric) OVER prior_year_to_date
FROM orders
WINDOW prior_year_to_date AS (
ORDER by your_date
RANGE BETWEEN 
your_date - date_trunc('year', your_date - interval '1' year) PRECEDING AND 
interval '1' year PRECEDING
)

一些观察:

• 您可以使用WINDOW子句一次定义窗口，并在许多函数中重用它。
• RANGE窗口框架可以直接操作日期时间类型。在这种情况下，范围被定义为当前行的your_date之前N天开始的间隔(其中N是将该日期抵消到前一年的开始所需的天数)和当前行的your_date之前1年开始的间隔。

您可以在这里找到有关WINDOW和RANGE在Trino中使用的更多详细信息。

Answer 2

我在EMR上运行PrestoSQL (Trino)版本350.0。我认为这只有在版本346及以后才有可能。

select
avg(your_metric) 
over (
partition by your_partition_columns
order by (to_unixtime(your_date)/86400) 
range between 
((to_unixtime(your_date)/86400)  - (to_unixtime(last_day_of_month(date_trunc('year',date_add('year',-1,your_date))))/86400))
preceding 
and 
((to_unixtime(your_date)/86400) - (to_unixtime(last_day_of_month(date_add('year',-1,your_date)))/86400)) 
preceding
) 
as your_metric_avg_prior_year_to_date
from your_table