过去15天的滚动平均数

Question

ANSI-SQL/Postgres-sql

对于带OVER()子句的聚合函数的行为，我有点困惑。我试着在这里做postgres练习：

https://pgexercises.com/questions/aggregates/rollingavg.html

以下是表的定义：

​

​

问题:2012年8月的每一天，计算前15天总收入的滚动平均值。输出应包含按日期排序的日期和收入列。

我写了以下查询：

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
order by starttime::date

当我运行这个程序时，我会得到一个错误：ERROR: column "b.slots" must appear in the GROUP BY clause or be used in an aggregate function. --当我向组中添加插槽时，我被要求添加memid、访客成本、成员成本。

当这些函数是avg计算的一部分时，我不完全理解为什么需要将它们添加到组中，特别是当我不必在这里添加它们时：https://pgexercises.com/questions/aggregates/facrev.html我对这个特定问题的查询：

编制了一份设施清单以及它们的总收入。

正确工作：

select name, sum(slots * case when
                 memid = 0 then guestcost
                 else membercost
                end) revenue
from cd.facilities A inner join cd.bookings B
on A.facid = B.facid
group by A.facid
order by revenue

但是，正如查询引擎对第一个问题的建议，我将所有询问的列按by添加到组中，并得到多个行：

starttime   revenue
2012-08-01  83.1333333333333333
2012-08-01  83.1333333333333333
...
2012-08-02  137.7666666666666667
2012-08-02  137.7666666666666667
2012-08-02  137.7666666666666667
...

因为我认为它分别为所有的starttime值提供了结果，但是结果是按日期生成的。

我知道在练习中有一个答案，但我更关心的是我的思维哪里错了，以及我是否能用我脑子里的方法去做。这里提到的答案是使用generate_series函数或视图。range BETWEEN INTERVAL '14 day' PRECEDING AND CURRENT ROW可以用来计算正确的结果吗？如果是，我做错什么了？

更新：

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

默认情况下，在内部查询中具有范围可以确保整个日期有一个结果。执行不同的操作会给每个日期带来一行。然后应用外部查询进行滚动日期计算。到目前为止这是我的进步。这与练习链接上的结果不匹配，但会产生以下结果：

date        revenue sum_revenue    avg_revenue
2012-07-15  570     570            38.0000000000000000
2012-07-16  1005.0  1575.0         105.0000000000000000
2012-07-17  1712.0  3287.0         219.1333333333333333
2012-07-18  2379.0  5666.0         377.7333333333333333
2012-07-19  3519.5  9185.5         612.3666666666666667
2012-07-20  4348.5  13534.0        902.2666666666666667
2012-07-21  5532.0  19066.0        1271.0666666666666667
2012-07-22  6556.0  25622.0        1708.1333333333333333
2012-07-23  7366.0  32988.0        2199.2000000000000000
2012-07-24  8741.0  41729.0        2781.9333333333333333
2012-07-25  10212.0 51941.0        3462.7333333333333333
2012-07-26  11137.0 63078.0        4205.2000000000000000
2012-07-27  12566.0 75644.0        5042.9333333333333333
2012-07-28  13756.0 89400.0        5960.0000000000000000
2012-07-29  14750.0 104150.0       6943.3333333333333333
2012-07-30  15981.5 119561.5       7970.7666666666666667
2012-07-31  17317.5 135874.0       9058.2666666666666667
2012-08-01  18614.5 152776.5       10185.1000000000000000
2012-08-02  19674.0 170071.5       11338.100000000000
2012-08-03  20963.0 187515.0       12501.000000000000
2012-08-04  22009.0 205175.5       13678.366666666667
2012-08-05  22946.0 222589.5       14839.300000000000
2012-08-06  24337.0 240370.5       16024.700000000000
2012-08-07  25109.0 258113.5       17207.566666666667
2012-08-08  26330.0 275702.5       18380.166666666667
2012-08-09  27499.0 292989.5       19532.633333333333
2012-08-10  28762.5 310615.0       20707.666666666667
2012-08-11  30215.5 328264.5       21884.300000000000
2012-08-12  31691.0 346199.5       23079.966666666667
2012-08-13  33020.0 364469.5       24297.966666666667
2012-08-14  34693.5 383181.5       25545.433333333333
2012-08-15  36429.0 402293.0       26819.533333333333
2012-08-16  37833.0 421511.5       28100.766666666667
2012-08-17  39541.0 441378.5       29425.233333333333
2012-08-18  41569.0 461984.5       30798.966666666667
2012-08-19  43100.0 483075.5       32205.033333333333
2012-08-20  44352.0 504481.5       33632.100000000000
2012-08-21  46092.0 526236.5       35082.433333333333
2012-08-22  48204.5 549332.0       36622.133333333333
2012-08-23  49839.5 572841.5       38189.433333333333
2012-08-24  51384.0 596726.5       39781.766666666667
2012-08-25  52988.0 620952.0       41396.800000000000
2012-08-26  54683.5 645420.0       43028.000000000000
2012-08-27  56582.5 670311.5       44687.433333333333
2012-08-28  58325.0 695616.5       46374.433333333333
2012-08-29  59963.0 720886.0       48059.066666666667
2012-08-30  61298.0 745755.0       49717.000000000000

当我签入excel时，我的结果是一致的。对于使用平均函数的平均字段，我也得到了相同的值：avg(revenue) over (order by date RANGE between INTERVAL '14 day' PRECEDING AND CURRENT ROW) avg_revenue

Answer 1

在我看来，你的方法是假设八月份的每一天都会有预订--这不是我愿意做的假设。首先，我会在8月份的每一天创建一个临时数据集，可以用来加入每日的收入汇总。

With Recursive AugustDays AS (
     select '2012-07-17'::date as daysend, ('2012-07-17'::date - interval '14 days')::date as daysstart
     UNION ALL
     Select daysend + 1, daysstart + 1
     FROM AugustDays
     WHERE daysend<'2012-08-31`)

然后，我会用8月份的数据汇总我在未预订的日子的日零收入。

With DailyRevenue AS (
    Select daysend as day
         , NullIf(sum(slots*(Case When memid=0 then guestcost else membercost end)),0) as revenue
From AugustDays A Left Outer Join (cd.bookings B inner join cd.facilities F
                                    on B.facid = F.facid)
                  On starttime:date=daysend
Group By daysend)

最后，我会把这两个人结合在一起生产滚动平均.

Select daysend as day, Avg(revenue) as Rolling15Day
From AugustDays Left Outer Join DailyRevenue On day between daysstart and daysend
Where daysend>='2012-08-01'
Group By daysend
Order By daysend