时间戳

首页 > 解决方案 > 如何在考虑缺失周数的同时跟踪周周增长

sql - 如何在考虑缺失周数的同时跟踪周周增长

问题描述

假设我们有一张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' )
        )

我不知道如何返回数据，这些数据将显示从过去的某个时间点到本周每周有多少活跃的比赛。

标签： sqlpostgresql

解决方案

这可能是您正在寻找的。

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;

推荐阅读