窗口函数中的按日期范围分区 [英] Partition by Date Range in Window Function

问题描述

我正在尝试编写一个查询，为过去44天中的每一天返回在该天之前的7天窗口中所做的租金计数。

这很棘手，因为并非集合中的所有日期都是连续的，并且没有租赁的日期不是数据集中的行。

以下是我下载数据的位置： https://www.postgresqltutorial.com/postgresql-sample-database/

我知道这需要使用窗口函数，最有可能的是同时使用Forecast By和ORDER BY子句，但我的结果返回重复的日期，而Count列为每一行显示相同的值。以下是我的代码：

SELECT DISTINCT date_trunc('day', rental_date), count(rental_id) OVER w
FROM rental
GROUP BY rental_date, rental_id
WINDOW w AS (PARTITION BY (rental_date BETWEEN DATE '2005-08-23' - INTERVAL '44days' AND DATE '2005-08-23') 
             ORDER BY rental_date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW)
ORDER BY date_trunc('day', rental_date) DESC;

预期输出如下所示：

       Col1                            Col2                               
date_trunc1                count(rental_id) 
2006-02-21 00:00:00                     182
2006-02-20 00:00:00                     182
2006-02-19 00:00:00                     182
2006-02-18 00:00:00                     182
2006-02-17 00:00:00                     182
2006-02-16 00:00:00                     182                           
2006-02-15 00:00:00                     182
2005-08-30 00:00:00                     598
2005-08-29 00:00:00                    1224
2005-08-28 00:00:00                    1883
2005-08-27 00:00:00                    2507  
2005-08-26 00:00:00                    3135
2005-08-25 00:00:00                    3756    
2005-08-24 00:00:00                    4349
2005-08-23 00:00:00                    3374
2005-08-22 00:00:00                    3148
2005-08-21 00:00:00                    2489
2005-08-20 00:00:00                    1865
2005-08-19 00:00:00                    1237
2005-08-18 00:00:00                     616
2005-08-17 00:00:00                      23
2005-08-16 00:00:00                       0
2005-08-08 00:00:00                     671
2005-08-07 00:00:00                    1305

当然，不包括数据集中不是行的日期。这就是为什么在2006-02-14之前的7天都被算作0。

推荐答案

由于您没有发布任何测试数据，我将按照您的描述进行操作：

SELECT rental_date, count
FROM (SELECT rental_date::date,
             count(*) OVER (ORDER BY rental_date::date
                            RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW)
      FROM rental
      WHERE rental_date::date BETWEEN current_date - (44 + 7)
                              AND current_date - 1
     ) subq
WHERE rental_date >= current_date - 44
ORDER BY rental_date;

当然，行将被连续数次，因为这是您所说的您想要的。

这篇关于窗口函数中的按日期范围分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！