sql - 按期间结束而不是开始日期分组

Question

我希望在数据集的结束日期之前聚合数据，该数据集具有一些领先时期而不是开始时间。例如，我想查询一个表并返回结果中显示日期的结束日期之前 30 天匹配结果的计数。原始表格将仅包含销售日期(时间戳)。示例:

sales_timestamp
------------------
2015-08-05 12:00:00
2015-08-06 13:00:00
2015-08-25 12:31:00
2015-08-26 01:02:00
2015-08-27 02:03:00
2015-08-29 04:23:00
2015-09-01 12:00:00
2015-09-02 12:00:00
2015-09-08 00:00:00

结果 查询输出的一个例子是:

date_period  |   count_of_sales
--------------------------------
2015-08-24   |        2
2015-08-31   |        6
2015-09-07   |        6

其中 2015-09-07 的 date_period 表示该公司在 2015 年 9 月 7 日结束的 30 天内售出了 6 件商品(如果是真正的 30 天，则从 2015 年 8 月 7 日开始)。

我一直在研究 date_trunc() 函数的变体，但似乎无法在 end date 上应用截断，而不是按开始。

此数据将存储在 PostgreSQL 9.1 上。

Answer 1

此查询满足您的所有要求:

SELECT day::date AS date_period, count_of_sales
FROM (
   SELECT *, sum(ct) OVER (ORDER BY day ROWS 30 PRECEDING) AS count_of_sales
   FROM   generate_series(date '2015-08-24' - 30  -- start 30 days earlier
                        , date '2015-09-07'
                        , interval '1 day') day
   LEFT JOIN (
      SELECT date_trunc('day', sales_timestamp) AS day, count(*)::int AS ct
      FROM   sales
      GROUP  BY 1
      ) s USING (day)
   ) sub
JOIN  generate_series(date '2015-08-24'
                    , date '2015-09-07 '
                    , interval '1 week') day USING (day);

SQL Fiddle.

说明

1. 生成一整套相关日期(第一个generate_series())
2. LEFTJOIN 到每天的聚合计数。 LEFT 保证每天 行，这允许我们根据行数使用窗口函数。

3. 使用 sum() 作为窗口聚合函数，自定义帧为 30 天前。 (您可能想改用 29，不清楚您是如何计算的。)

4. 将结果与您希望在结果中显示的实际日期相结合。 (第二个 generate_series()，每周 1 天)。

请注意，如果您使用 timestamptz，“天”的定义源自 session 的当前时区设置。不同时区的结果可能不同。不适用于 timestamp，它不依赖于当前时区。基础知识:

• Ignoring timezones altogether in Rails and PostgreSQL

有关带有自定义框架定义的窗口函数的解释的相关答案:

• Select finishes where athlete didn't finish first for the past 3 events