Chronological data from postgresql

Question

I have a table Table1 with two columns: time:datetime, address:varchar with data like this:

2018-01-03-11:25:30, A
2018-01-04-14:34:43, A
2018-01-08-06:25:36, A
2018-01-08-11:14:30, B
2018-01-09-19:10:21, B
2018-01-10-10:18:50, B
2018-01-12-23:17:30, A
2018-01-13-06:24:40, A
2018-01-14-15:18:10, C
2018-01-18-13:44:20, C

I want to extract chronological data from Table1 like this:

A, 3, 2018-01-03-11:25:30, 2018-01-08-06:25:36
B, 3, 2018-01-08-11:14:30, 2018-01-10-10:18:50
A, 2, 2018-01-12-23:17:30, 2018-01-13-06:24:40
C, 2, 2018-01-14-15:18:10, 2018-01-18-13:44:20

can I do that using a SQL query?

Yes, you can do that in a SQL query. Have you tried something yet? — Tim Biegeleisen
– Tim Biegeleisen, Commented May 26, 2018 at 8:12
Look for "gaps and islands". Essentially, you are trying to find islands. — Laurenz Albe
– Laurenz Albe, Commented May 26, 2018 at 8:12
@TimBiegeleisen thanks. I tried group by query but I don't have any idea about how to get first and last item in a group... — Farvardin
– Farvardin, Commented May 26, 2018 at 8:15

Tim Biegeleisen · Accepted Answer · 2018-05-26 08:22:37Z

2

Here is one approach using the difference in row numbers method:

WITH cte AS (
    SELECT *,
        ROW_NUMBER() OVER (ORDER BY time) -
        ROW_NUMBER() OVER (PARTITION BY address ORDER BY time) rn
    FROM yourTable
)

SELECT
    address,
    COUNT(*) AS cnt,
    MIN(time) AS start,
    MAX(time) AS end
FROM cte
GROUP BY
    address, rn
ORDER BY
    start;

Demo

This method uses a trick to separate islands of address records. The two row numbers let us detect these islands, because the row number difference for each island will always be the same. But the same difference might appear for more than one address. However, since we ultimately GROUP BY both these difference and the address, it works.

answered May 26, 2018 at 8:22

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Chronological data from postgresql

1 Answer 1

Demo

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related