sql - 获取表中行对之间的平均间隔

Question

我有一个包含以下数据( Paypal 交易)的表格:

    txn_type    |            date            |   subscription_id
----------------+----------------------------+---------------------
 subscr_signup  | 2014-01-01 07:53:20        | S-XXX01
 subscr_signup  | 2014-01-05 10:37:26        | S-XXX02
 subscr_signup  | 2014-01-08 08:54:00        | S-XXX03
 subscr_eot     | 2014-03-01 08:53:57        | S-XXX01
 subscr_eot     | 2014-03-05 08:58:02        | S-XXX02

我想获得给定时间段内的总体平均订阅长度(subscr_eot 是订阅的结束)。对于仍在进行中的订阅 ('S-XXX03')，我希望它从开始日期到 现在 平均包含在内。
我将如何使用 Postgres 中的 SQL 语句执行此操作？

Answer 1

SQL Fiddle .每个订阅的订阅长度:

select
    subscription_id,
    coalesce(t2.date, current_timestamp) - t1.date as subscription_length
from
    (
        select *
        from t
        where txn_type = 'subscr_signup'
    ) t1
    left join
    (
        select *
        from t
        where txn_type = 'subscr_eot'
    ) t2 using (subscription_id)
order by t1.subscription_id

平均值:

select
    avg(coalesce(t2.date, current_timestamp) - t1.date) as subscription_length_avg
from
    (
        select *
        from t
        where txn_type = 'subscr_signup'
    ) t1
    left join
    (
        select *
        from t
        where txn_type = 'subscr_eot'
    ) t2 using (subscription_id)