sql - 如何计算 postgresql 中每月重复事件的差距

Question

使用 postgresql 9.6

问题如下，我们有一个数据库来跟踪订阅业务中的订单。一旦有人订阅，每个月都会为他们生成一个订单。每个人都有能力跳过一个月(或跳过一个月的周期，或 3 等，有效地跳过 x 个月)。我正在尝试确定在给定月份“跳过”的人数。

我们有一个订单表，看起来像这样(简化):

CREATE TABLE orders (
  person_id varchar,
  timestamp_ timestamp
);

我可以使用窗口函数对每个人的订单进行排序，如下所示:

    select timestamp_, person_id, row_number() 
           over (partition by person_id order by timestamp_)
    from orders

输出:

 timestamp_             person_id                      row_number
2017-03-14 12:38:38 00050c43-08c5-11e7-b433-01007e15dd78    1
2017-04-14 10:04:13 00050c43-08c5-11e7-b433-01007e15dd78    2
2017-07-14 10:05:17 00050c43-08c5-11e7-b433-01007e15dd78    3
2017-08-14 10:02:37 00050c43-08c5-11e7-b433-01007e15dd78    4
2017-09-14 10:04:37 00050c43-08c5-11e7-b433-01007e15dd78    5
2017-10-14 10:02:08 00050c43-08c5-11e7-b433-01007e15dd78    6
2017-11-14 10:05:35 00050c43-08c5-11e7-b433-01007e15dd78    7
2017-12-14 10:02:52 00050c43-08c5-11e7-b433-01007e15dd78    8
2018-01-14 10:05:38 00050c43-08c5-11e7-b433-01007e15dd78    9
2017-11-15 03:54:57 000b5c80-c9b8-11e7-a1c1-0242ac110003    1
2017-12-14 10:00:34 000b5c80-c9b8-11e7-a1c1-0242ac110003    2
2018-01-14 10:07:17 000b5c80-c9b8-11e7-a1c1-0242ac110003    3
2016-12-24 10:15:58 0017c8ad-b252-11e6-b4db-0100ab184d8f    1
2017-01-24 10:54:49 0017c8ad-b252-11e6-b4db-0100ab184d8f    2

我也一直在玩这样的滞后函数:

    select timestamp_, person_id,
      (date_trunc('month', timestamp_) - date_trunc('month',timestamp_)) 
      over (partition by person_id order by timestamp_))
    from orders;

给我这样的结果:

timestamp_              person_id                           lag
2017-03-14 12:38:38 00050c43-08c5-11e7-b433-01007e15dd78    
2017-04-14 10:04:13 00050c43-08c5-11e7-b433-01007e15dd78    0 years 0 mons 31 days 0 hours 0 mins 0.00 secs
2017-07-14 10:05:17 00050c43-08c5-11e7-b433-01007e15dd78    0 years 0 mons 91 days 0 hours 0 mins 0.00 secs
2017-08-14 10:02:37 00050c43-08c5-11e7-b433-01007e15dd78    0 years 0 mons 31 days 0 hours 0 mins 0.00 secs
2017-09-14 10:04:37 00050c43-08c5-11e7-b433-01007e15dd78    0 years 0 mons 31 days 0 hours 0 mins 0.00 secs
2017-10-14 10:02:08 00050c43-08c5-11e7-b433-01007e15dd78    0 years 0 mons 30 days 0 hours 0 mins 0.00 secs
2017-11-14 10:05:35 00050c43-08c5-11e7-b433-01007e15dd78    0 years 0 mons 31 days 0 hours 0 mins 0.00 secs
2017-12-14 10:02:52 00050c43-08c5-11e7-b433-01007e15dd78    0 years 0 mons 30 days 0 hours 0 mins 0.00 secs
2018-01-14 10:05:38 00050c43-08c5-11e7-b433-01007e15dd78    0 years 0 mons 31 days 0 hours 0 mins 0.00 secs
2017-11-15 03:54:57 000b5c80-c9b8-11e7-a1c1-0242ac110003    
2017-12-14 10:00:34 000b5c80-c9b8-11e7-a1c1-0242ac110003    0 years 0 mons 30 days 0 hours 0 mins 0.00 secs
2018-01-14 10:07:17 000b5c80-c9b8-11e7-a1c1-0242ac110003    0 years 0 mons 31 days 0 hours 0 mins 0.00 secs
2016-12-24 10:15:58 0017c8ad-b252-11e6-b4db-0100ab184d8f    
2017-01-24 10:54:49 0017c8ad-b252-11e6-b4db-0100ab184d8f    0 years 0 mons 31 days 0 hours 0 mins 0.00 secs

我需要帮助组合这两个查询并应用按月分组来计算当月跳过的人数:

select month, count(person_id) as skips
from ( some inner query)
group by month

要得到这样的东西:

Month       Number of people who skipped subscription 
2017-03-1    14
2017-04-1    8
2017-05-1    4

Answer 1

想到的方法是在每个人的第一个时间戳和最后一个时间戳之间的每个月为每个人生成一行(您可能希望为此使用固定日期)。

然后，检查哪些有订单:

select m.mon, count(*) as num_missing
from generate_series('2017-03-01'::timestamp, '2017-05-01'::timestamp, interval '1 month') m(mon) join
     (select person_id, min(timestamp_) as mints, max(timestamp_) as maxts
      from orders
      group by person_id
     ) p
     on m.mon between date_trunc('month', mints) and date_trunc('month', maxtx) left join
     orders o
     on p.person_id = o.personid and m.mon = date_trunc('month', o.timestamp_)
where o.person_id is null
group by m.mon
order by m.mon;