主页 / dba / 问题 / 303858
Accepted
Péter Szabó-tóth
Asked: 2021-12-13 14:29:14 +0800 CST

按 id 分组并获取分组行之间的平均时间增量

  • 772

我有一个带有 ids 和时间戳的表：

postgres=> select id_user, date_seconds from orders.orders order by id_user limit 10;
           id_user            |    date_seconds
------------------------------+---------------------
 00D4jzHHesfCXGO6VQfUun9JcCW2 | 2021-09-20 02:00:00
 00D4jzHHesfCXGO6VQfUun9JcCW2 | 2021-10-06 02:00:00
 00D4jzHHesfCXGO6VQfUun9JcCW2 |
 00d9QGWjmbYdRTGw4h4GynhDp2K2 | 2021-10-01 02:00:00
 00OI3ruCKeOFdIro11eVNcfp84A2 | 2021-10-06 02:00:00
 00OI3ruCKeOFdIro11eVNcfp84A2 | 2021-08-24 02:00:00
 00qwpdw23BSEBGkfJhR4Hb7Kg1v2 | 2021-09-17 02:00:00
 00qwpdw23BSEBGkfJhR4Hb7Kg1v2 | 2021-09-20 02:00:00
 00qwpdw23BSEBGkfJhR4Hb7Kg1v2 | 2021-10-04 02:00:00
 00qwpdw23BSEBGkfJhR4Hb7Kg1v2 | 2021-08-20 02:00:00
(10 rows)

如您所见，一个id_user可以在此表中出现多次。

我想按 对这张表进行分组id_user。但是，我还希望在该特定的行之间获得列的平均增量时间。date_secondsid_user

我想我可能需要使用LAGpsql 函数，但我不确定我如何或是否在正确的轨道上。

评论中建议的示例：

假设我有下表：

INSERT INTO orders(id_user, ordered_at) VALUES
('1', '2021-09-20 02:00:00'),
('1', '2021-10-06 02:00:00'),
('2', '2021-10-01 02:00:00'),
('3', '2021-10-06 02:00:00'),
('3', '2021-08-24 03:00:00'),
('4', '2021-08-20 02:00:00'),
('4', '2021-09-17 02:00:00'),
('4', '2021-09-20 02:00:00'),
('4', '2021-10-04 02:00:00');

期望的结果如下： 用户订单之间的平均时间可以计算如下： AVG( 订单之间的时间间隔 )

example: id_user 1: AVG( 2021-09-20 02:00:00 - 2021-10-06 02:00:00) which equals 16 days and 0 hours interval

example: id user 4: ACG( (2021-08-20 02:00:00 - 2021-09-17 02:00:00) , (2021-09-17 02:00:00 - 2021-09-20 02:00:00) , (2021-09-20 02:00:00 - 2021-10-04 02:00:00)  )
example: id user 4: AVG( (interval '28 days') , (interval '3 days') , (interval '14 days')  )
example: id user 4: 28+3+14 / 3 = 15 --> result should be interval '15 days'

postgres=> SELECT magic(ordered_at) as avg_timedelta, id_user from orders where id_user='4' group by id_user;
            id_user            |    avg_timedelta
------------------------------+---------------------
              4                |      15 days
postgresql aggregate

1 个回答

  1. Best Answer

    就像是：

    select id_user, (max(ordered_at)-min(ordered_at)) / (count(1)-1)
from orders
group by id_user
having count(1) > 1;

    如果您想改用领先（或滞后），您可以确定两个连续行之间的持续时间，然后在该集合上应用 avg：

    with t as (
    select id_user, ordered_at
         , lead(ordered_at) over (partition by id_user order by ordered_at) as ld_ordered_at
    from orders
)
select id_user, avg(ld_ordered_at - ordered_at) 
from t
where ld_ordered_at is not null
group by id_user;

    小提琴

    第一个解决方案背后的想法是：

    (x2-x1)+(x3-x2)+...+(x_n+1-x_n) <=>
(-x1+(x2-x2)+(x3-x3)+...+(x_n-1-x_n-1)+x_n) <=>
(x_n-x1)

    然后除以 n-1 得到平均值

    • 2

相关问题