由於缺少行，用於按子查詢計算跨組滾動平均值的時間序列視窗查詢不准確

我正在嘗試根據生成的時間序列和時間序列的每一天的子查詢來生成滾動平均值。但是，子查詢有時沒有給定日期的結果，因此它不會返回該日期的行。這打破了滾動平均計算，因為它基於前面/後面的行。它仍在計算，但計算是錯誤的，因為它不計算“0”天，因為這些行不存在。

SELECT
   d::date AS day,
   updates.login as login,
   avg(coalesce(updates.counts, 0)) over (order by date(d) rows between 3 preceding and 3 following) as rollingavg
from generate_series(date '${from}' - 3, date '${to}' + 3, interval '1' DAY) AS t(d)
LEFT JOIN (
   SELECT
       date(item.updated_at) AS day,
       u.login,
       count(date(item.updated_at)) AS counts
   FROM
       user_item as item
       inner join user u ON u.id = item.user_id
   WHERE
       item.updated_at >= (date '${from}' - 3)
       item.updated_at <= (date '${to}' + 3)
   GROUP by day, login 
) updates ON updates.day = t.d

如果我login從 group by 子句中刪除並修改查詢以適應它，它似乎可以工作，但這只是因為有足夠的數據，沒有一天沒有至少一次更新。如果有一天沒有更新，這將遇到同樣的問題。

我最初的想法是加入一個生成的表，該表本質上是時間序列和每個可能的作者之間的交叉連接，或者可能修改我from generate_series以表示這樣一個表，其中包括每個使用者每天的基線“0”計數，但是有些感覺不對。

我在正確的道路上嗎？

編輯：一些簡化的範例數據，使其更明顯我所得到的。這完全集中在子查詢上，因為這是問題開始的地方：

使用者：

使用者項：

期望的結果如下：

但我得到的是：

正如 Lennart 所提到的，您需要CROSS JOIN與您的使用者一起生成系列以創建所需的組合(Login,Date)：

SELECT
 U.Login
,t.Date::date AS Day
,COUNT(UserItem.UserId) AS Count
,AVG(COUNT(UserItem.UserId)) OVER (PARTITION BY U.Login ORDER By t.Date ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS RollingAvg
FROM
 "User" U
CROSS JOIN
 generate_series(date '2020-01-01' - 3, date '2020-01-01' + 3, interval '1' DAY) AS t(date)
LEFT JOIN
 UserItem UserItem
   ON UserItem.UserId = U.UserId
       AND UserItem.Updated_At = t.Date
GROUP BY
 U.Login
,t.Date
ORDER BY
 U.Login
,t.Date

您可以通過添加以下內容來限制僅在日期範圍內有活動的使用者：

WHERE
 U.UserId IN
   (
     SELECT
       UserId
     FROM
       UserItem
     WHERE
       Updated_At >= date '2020-01-01' - 3 
       AND Updated_At <= date '2020-01-01' + 3
   )

在這里拉小提琴。

引用自：https://dba.stackexchange.com/questions/282612