如何跟踪周环比增长，同时考虑遗漏的周数

Question

让我们假设我们有一个match表，该表的精简版本如下所示：

Column                             |           Type           | Collation | Nullable |      Default
-----------------------------------+--------------------------+-----------+----------+--------------------
uuid                               | uuid                     |           | not null | uuid_generate_v4()
present_state                      | text                     |           |          |
created_at                         | timestamp with time zone |           | not null |
updated_at                         | timestamp with time zone |           |          |

我试图从过去的某个任意日期开始，每周都能得到活跃比赛的数量。

基于该模式，我们可以通过查看created_at列、对其进行分组和计数来了解一周内开始了多少新的活动匹配。对于已完成的匹配，我们可以通过查看updated_at列并确保present_state"已完成"来执行类似的操作。

另一件需要考虑的事情是，可能有几周根本没有比赛发生，比赛可以无限期地进行。我一直在试图强迫一些CTE提供一个表，其中包含比赛开始的每个日期的条目，一个相反的表，其中包括每场比赛结束的日期，以及一个生成的时间序列，我可以加入该时间序列来解释缺席的几周。这就是我目前所掌握的：

; WITH matches_begun AS 
(
SELECT 
date_trunc('day', created_at)::date AS date 
FROM matches
)
, matches_finished AS 
(
SELECT 
date_trunc('day', updated_at)::date AS date 
FROM matches 
WHERE present_state = 'finished'
)
, weekly_time_series AS 
(
select  generate_series( '2018-01-01', current_date, interval '1 day' )
)

我无法理解如何返回数据，以显示从过去到本周每周有多少场活跃的比赛。

Answer 1

这可能就是您想要的。

with weekly_time_series(report_date) AS (
select  generate_series( '2018-01-01', current_date, interval '1 day' )
), weekly_counts(report_week, weekly_count) as (
select date_trunc('week', report_date) 
, count(distinct uuid) 
from weekly_time_series
left join matches
on date_trunc('day', created_at) <= report_date
and (present_state <> 'finished' or 
report_date < date_trunc('day', updated_at))
group by date_trunc('week', report_date)
)
select report_week
, weekly_count
, weekly_count - coalesce(lag(weekly_count) over (order by report_week), 0) delta
from weekly_counts;