0

I have a table with ids and timestamps:

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)

As you can see, one id_user can appear multiple times in this table.

I would like to group this table by id_user. However, I would also want to have the average delta time of the date_seconds column between the rows of that particular id_user.

I figured I might need to use the LAG psql function, but I am unsure how or if I`m even on the right track here.

Examples as suggested by the comments:

Suppose I have the following table:

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');

The desired result would be the following: The avg time between a user's orders can be calculated as follows: AVG( time interval between orders )

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
4
  • 2
    Which id appears multiple times in your example?...can you add the table definition of the orders table to your post please?
    – J.D.
    Dec 12, 2021 at 23:27
  • updated the question Dec 13, 2021 at 8:26
  • 1
    Hi and welcome to dba.se! Please go to dbfiddle.uk and construct your table, some sample data and your desired result - help us to help you! And what does one do in the case where there's no date_seconds field value?
    – Vérace
    Dec 13, 2021 at 9:54
  • 1
    Thanks for the warm welcome. I added some examples in the question, I hope it helps. Dec 13, 2021 at 11:32

1 Answer 1

3

Something like:

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

If you want to use lead (or lag) instead, you can determine the duration between two consecutive rows and then apply avg on that set:

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;

Fiddle

The idea behind the first solution is that:

(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)

and then divide with n-1 to get the average

5
  • The simple first solution! (Plus how ever the OP want to deal with NULL values.) Dec 14, 2021 at 0:09
  • @ErwinBrandstetter, the first solution has to be slightly adjusted. I'll update Dec 14, 2021 at 8:10
  • Note that count(1) is actually slightly slower than count(*) in Postgres (and identical in performance for all other DBMS)
    – user1822
    Dec 14, 2021 at 11:09
  • Old habit, once upon a time in a galaxy far far away someone told me that COUNT(*) had to look up something in the catalogue for that DBMS and that it, therefore, should be avoided. I don't even know whether that was true or not back then, but I have been using COUNT(1) ever since:-) Dec 14, 2021 at 12:03
  • Thank you! Very 'elegant solution'. Dec 14, 2021 at 14:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.