sql - 如何根据事件日志表获取一天结束时每种状态的用户总数？

Question

我有一个事件日志表，它捕获所有用户状态的变化，比如状态 A、状态 B 和状态 C。他们可以随时更改它。我怎样才能在每天结束时(从事件日志表中最早的一天到最晚的一天)获取每种状态下有多少用户的快照

如果有人能告诉我如何用 PostsgreSQL 以一种优雅的方式做到这一点，我将不胜感激。谢谢!

编辑:事件日志表捕获了每个用户的一堆事件(其中一个是状态更改)，log_id 记录了该特定用户的事件日志的顺序。

 user_id  |     log_time     | status | event_A | log_id |
----------------------------------------------------------
   456    | 2019-01-05 15:00 |   C    |         |   5    |
   123    | 2019-01-05 14:00 |   C    |         |   4    |
   123    | 2019-01-05 13:00 |        |   xxx   |   3    |
   456    | 2019-01-04 22:00 |   B    |         |   4    |
   456    | 2019-01-04 10:00 |   C    |   xxx   |   3    |
   987    | 2019-01-04 05:00 |   C    |         |   3    |
   123    | 2019-01-03 23:00 |   B    |         |   2    |
   987    | 2019-01-03 15:00 |        |   xxx   |   2    |
   456    | 2019-01-02 22:00 |   A    |   xxx   |   2    |
   123    | 2019-01-01 23:00 |   C    |         |   1    |
   456    | 2019-01-01 09:00 |   B    |   xxx   |   1    |
   987    | 2019-01-01 04:00 |   A    |         |   1    |

所以我想在一天结束时获取每个状态的用户总数:

   Date    | status A | status B | status C |
---------------------------------------------
2019-01-05 |     0    |     0    |     3    |
2019-01-04 |     0    |     2    |     1    |
2019-01-03 |     2    |     1    |     0    |
2019-01-02 |     2    |     0    |     1    |
2019-01-01 |     1    |     1    |     1    |

Answer 1

这是一项非常具有挑战性的工作 :)。我试图将子查询分段以获得良好的可读性。这可能不是一种非常有效的方式来做你想做的事，但它可以完成工作。

-- collect all days to make sure there are no missing days
WITH all_days_cte(dt) as (
    SELECT
        generate_series(
        (SELECT min(date_trunc('day', log_time)) from your_table),
        (SELECT max(date_trunc('day', log_time)) from your_table),
        '1 day'
        )::DATE
),
-- collect all useres
all_users_cte as (
    select distinct
        user_id
    from your_table
),
-- setup the table with infos needed, i.e. only the last status by day and user_id
infos_to_aggregate_cte as (
    select
        s.user_id,
        s.dt,
        s.status
    from (
        select
            user_id,
            date_trunc('day', log_time)::DATE as dt,
            status,
            row_number() over (partition by user_id, date_trunc('day', log_time) order by log_time desc) rn
        from your_table
        where status is not null
    ) s
-- only the last status of the day
    where s.rn = 1
),
-- now we still have a problem, we need to find the last status, if there was no change on a day
completed_infos_cte as (
    select
        u.user_id,
        d.dt,
        -- not very efficient, but found no other way (first_value(...) would be nice, but there is no simple way to exclude nulls
        (select
            status
        from infos_to_aggregate_cte i2 
        where i2.user_id = u.user_id 
             and i2.dt <= d.dt 
             and i2.status is not null 
        order by i2.dt desc 
        limit 1) status
    from all_days_cte d
    -- cross product for all dates and users (that is what we need for our aggregation)
    cross join all_users_cte u
    left outer join infos_to_aggregate_cte i on u.user_id = i.user_id
        and d.dt = i.dt
)
select
    c.dt,
    sum(case when status = 'A' then 1 else 0 end) status_a,
    sum(case when status = 'B' then 1 else 0 end) status_b,
    sum(case when status = 'C' then 1 else 0 end) status_c    
from completed_infos_cte c
group by c.dt
order by c.dt desc