postgresql - 具有已删除属性的历史表的累积计数

Question

我有一个记录更新的历史表，我想计算累积总数，其中可以将值添加或删除到集合中。（即一个月的累计总数可能会少于前一个月）。

例如，下面是一个表格，其中包含人员记录的标签更新历史记录。（id 是人员记录的 id）。

我想计算在任何给定月份有多少人拥有“已建立”标签，并考虑上一个月添加或删除它的时间。

+----+------------------------+---------------------+
| id |          tags          |     created_at      |
+----+------------------------+---------------------+
|  1 | ["vip", "established"] | 2017-01-01 00:00:00 |
|  2 | ["established"]        | 2017-01-01 00:00:00 |
|  3 | ["established"]        | 2017-02-01 00:00:00 |
|  1 | ["vip"]                | 2017-03-01 00:00:00 |
|  4 | ["established"]        | 2017-05-01 00:00:00 |
+----+------------------------+---------------------+

在这些 帖子的帮助下，我已经做到了这一点：

SELECT 
  item_month,
  sum(count(distinct(id))) OVER (ORDER BY item_month)
FROM (
  SELECT 
    to_char("created_at", 'yyyy-mm') as item_month,
    id
  FROM person_history 
  WHERE tags ? 'established'
) t1
GROUP BY item_month;

这给了我：

month   count
2017-01 2
2017-02 3
2017-05 4 <--- should be 3

它还缺少 2017-03 的条目，应该是 2。

（2017-04 的条目也不错，但如果需要，UI 总是可以从上个月推断出来）

标签： postgresqlcount

Answer 1

这是分步教程，您可以尝试折叠所有这些 CTE：

with 
  -- Example data
  person_history(id, tags, created_at) as (values
    (1, '["vip", "est"]'::jsonb, '2017-01-01'::timestamp),
    (2, '["est"]', '2017-01-01'), -- Note that Person 2 changed its tags several times per month 
    (2, '["vip"]', '2017-01-02'),
    (2, '["vip", "est"]', '2017-01-03'),
    (3, '["est"]', '2017-02-01'),
    (1, '["vip"]', '2017-03-01'),
    (4, '["est"]', '2017-05-01')),
  -- Get the last tags for each person per month
  monthly as (
    select distinct on (id, date_trunc('month', created_at))
      id,
      date_trunc('month', created_at) as month,
      tags,
      created_at
    from person_history
    order by 1, 2, created_at desc),
  -- Retrieve tags from previous month
  monthly_prev as (
    select
      *,
      coalesce((lag(tags) over (partition by id order by month)), '[]') as prev_tags
    from monthly),
  -- Calculate delta: if "est" was added then 1, removed then -1, nothing heppens then 0
  monthly_delta as (
    select
      *,
      case
        when tags ? 'est' and not prev_tags ? 'est' then 1
        when not tags ? 'est' and prev_tags ? 'est' then -1
        else 0
      end as delta
    from monthly_prev),
  -- Sum all deltas for each month
  monthly_total as (
    select month, sum(delta) as total
    from monthly_delta
    group by month)
-- Finally calculate cumulative sum
select *, sum(total) over (order by month) from monthly_total
order by month;

结果：

┌──────────────────────┬────────┬─────┐
│ 月 │ 合计 │ 总和 │
├──────────────────────┼────────┼──────┤
│ 2017-01-01 00:00:00 │ 2 │ 2 │
│ 2017-02-01 00:00:00 │ 1 │ 3 │
│ 2017-03-01 00:00:00 │ -1 │ 2 │
│ 2017-05-01 00:00:00 │ 1 │ 3 │
└──────────────────────┴────────┴─────┘