Window function to merge rows that meet a condition

Question

I have the following schema

DROP TABLE IF EXISTS messages;
DROP TABLE IF EXISTS chats;

CREATE TABLE chats (
  client integer NOT NULL,
  provider integer NOT NULL,
  PRIMARY KEY (client, provider)
);

CREATE TABLE messages (
  id serial PRIMARY KEY,
  client integer NOT NULL,
  provider integer NOT NULL,
  sender integer NOT NULL CHECK(sender = client OR sender = provider),
  created_at timestamp with time zone NOT NULL,
  FOREIGN KEY (client, provider) REFERENCES chats(client, provider) ON UPDATE CASCADE ON DELETE CASCADE
);

INSERT INTO chats VALUES (1,2), (3,2), (1,3);
INSERT INTO messages (client, provider, sender, created_at) VALUES
  (1, 2, 1, '2017-06-13 17:00:00+0'),
  (1, 2, 1, '2017-06-13 17:00:10+0'),
  (1, 2, 1, '2017-06-13 17:01:00+0'),
  (1, 2, 2, '2017-06-13 17:10:00+0'),
  (1, 2, 2, '2017-06-13 17:10:10+0'),
  (1, 2, 2, '2017-06-13 17:10:20+0'),
  (1, 2, 1, '2017-06-13 17:11:00+0'),
  (1, 2, 2, '2017-06-13 17:11:10+0');

INSERT INTO messages (client, provider, sender, created_at) VALUES
  (3, 2, 2, '2017-06-13 17:05:00+0'),
  (3, 2, 2, '2017-06-13 17:05:10+0'),
  (3, 2, 2, '2017-06-13 17:05:20+0'),
  (3, 2, 3, '2017-06-13 17:12:00+0'),
  (3, 2, 2, '2017-06-13 17:12:10+0'),
  (3, 2, 2, '2017-06-13 17:12:15+0'),
  (3, 2, 3, '2017-06-13 17:12:30+0'),
  (3, 2, 3, '2017-06-13 17:14:00+0');

INSERT INTO messages (client, provider, sender, created_at) VALUES
  (1, 3, 1, '2017-06-13 17:05:00+0'),
  (1, 3, 1, '2017-06-13 17:05:10+0');

What I want is to calculate the average time a provider takes to answer to a client with the following rules:

compare the time from the first message of a group of messages of client to next message of the provider
if the client messages and never receives an answer, get the time as CURRENT_TIMESTAMP - {the first message of the client}

These are obvious (?)

~~if the provider messages first, don't consider it~~
~~if the client messages last, don't consider this time~~

I thought starting with grouping a thread of messages by the same sender as a single entity. By this I mean:

Turning this:

postgres=# table messages order by client, provider, created_at;
 id | client | provider | sender |       created_at
----+--------+----------+--------+------------------------
  1 |      1 |        2 |      1 | 2017-06-13 13:00:00-04
  2 |      1 |        2 |      1 | 2017-06-13 13:00:10-04
  3 |      1 |        2 |      1 | 2017-06-13 13:01:00-04
--
  4 |      1 |        2 |      2 | 2017-06-13 13:10:00-04
  5 |      1 |        2 |      2 | 2017-06-13 13:10:10-04
  6 |      1 |        2 |      2 | 2017-06-13 13:10:20-04
--
  7 |      1 |        2 |      1 | 2017-06-13 13:11:00-04
--
  8 |      1 |        2 |      2 | 2017-06-13 13:11:10-04
--
 17 |      1 |        3 |      1 | 2017-06-13 13:05:00-04
 18 |      1 |        3 |      1 | 2017-06-13 13:05:10-04
--
  9 |      3 |        2 |      2 | 2017-06-13 13:05:00-04
 10 |      3 |        2 |      2 | 2017-06-13 13:05:10-04
 11 |      3 |        2 |      2 | 2017-06-13 13:05:20-04
--
 12 |      3 |        2 |      3 | 2017-06-13 13:12:00-04
--
 13 |      3 |        2 |      2 | 2017-06-13 13:12:10-04
 14 |      3 |        2 |      2 | 2017-06-13 13:12:15-04
--
 15 |      3 |        2 |      3 | 2017-06-13 13:12:30-04
 16 |      3 |        2 |      3 | 2017-06-13 13:14:00-04
(18 rows)

Into something like:

 client | provider | sender |    first_created_at
--------+----------+--------+------------------------
      1 |        2 |      1 | 2017-06-13 13:00:00-04
      1 |        2 |      2 | 2017-06-13 13:10:00-04
      1 |        2 |      1 | 2017-06-13 13:11:00-04
      1 |        2 |      2 | 2017-06-13 13:11:10-04
      1 |        3 |      1 | 2017-06-13 13:05:00-04
      3 |        2 |      2 | 2017-06-13 13:05:00-04
      3 |        2 |      3 | 2017-06-13 13:12:00-04
      3 |        2 |      2 | 2017-06-13 13:12:10-04
      3 |        2 |      3 | 2017-06-13 13:12:30-04

Separating the messages in groups of sequential messages by the same sender and keeping the first created_at.

After that I think the rest of the query should be relatively easy. I think this is done with window functions, but I'm not sure how to get it working.

After getting this, the idea would be to subtract each first_created_at of the provider from the previous first_created_at (which would be of the client), and average this interval for each provider.

EXAMPLE RESULTS

For the chat client = 1, provider = 2

postgres=# SELECT * FROM messages WHERE (client, provider) = (1,2) ORDER BY created_at;
 id | client | provider | sender |       created_at
----+--------+----------+--------+------------------------
  1 |      1 |        2 |      1 | 2017-06-13 13:00:00-04
  2 |      1 |        2 |      1 | 2017-06-13 13:00:10-04
  3 |      1 |        2 |      1 | 2017-06-13 13:01:00-04
--
  4 |      1 |        2 |      2 | 2017-06-13 13:10:00-04
  5 |      1 |        2 |      2 | 2017-06-13 13:10:10-04
  6 |      1 |        2 |      2 | 2017-06-13 13:10:20-04
--
  7 |      1 |        2 |      1 | 2017-06-13 13:11:00-04
--
  8 |      1 |        2 |      2 | 2017-06-13 13:11:10-04
(8 rows)

The group 1 (where sender = client) gives created_at = 2017-06-13 13:00:00-04
The group 2 (where sender = provider) gives created_at = 2017-06-13 13:10:00-04
The group 3 (where sender = client) gives created_at = 2017-06-13 13:11:00-04
The group 4 (where sender = provider) gives created_at = 2017-06-13 13:11:10-04

That gives us the intervals:

group2.created_at - group1.created_at = 00:10:00
group4.created_at - group3.created_at = 00:00:10

Then, for the chat client = 3, provider = 2

postgres=# SELECT * FROM messages WHERE (client, provider) = (3,2) ORDER BY created_at;
 id | client | provider | sender |       created_at
----+--------+----------+--------+------------------------
  9 |      3 |        2 |      2 | 2017-06-13 13:05:00-04
 10 |      3 |        2 |      2 | 2017-06-13 13:05:10-04
 11 |      3 |        2 |      2 | 2017-06-13 13:05:20-04
--
 12 |      3 |        2 |      3 | 2017-06-13 13:12:00-04
--
 13 |      3 |        2 |      2 | 2017-06-13 13:12:10-04
 14 |      3 |        2 |      2 | 2017-06-13 13:12:15-04
--
 15 |      3 |        2 |      3 | 2017-06-13 13:12:30-04
 16 |      3 |        2 |      3 | 2017-06-13 13:14:00-04
(8 rows)

The group 1 (where sender = provider) gives created_at = 2017-06-13 13:05:00-04
The group 2 (where sender = client) gives created_at = 2017-06-13 13:12:00-04
The group 3 (where sender = provider) gives created_at = 2017-06-13 13:12:10-04
The group 4 (where sender = client) gives created_at = 2017-06-13 13:12:30-04

That gives us the intervals:

We don't subtract group 1 because we have no previous client group to use
group3.created_at - group2.created_at = 00:00:10
We don't subtract group 4 because we have no following provider group to use

With these two conversations we can get the average the three intervals for the provider 2 = 00:03:26.666667.

Finally, for the chat client = 1, provider = 3

postgres=# SELECT * FROM messages WHERE (client, provider) = (1,3) ORDER BY created_at;
 id | client | provider | sender |       created_at
----+--------+----------+--------+------------------------
 17 |      1 |        3 |      1 | 2017-06-13 13:05:00-04
 18 |      1 |        3 |      1 | 2017-06-13 13:05:10-04
(2 rows)

The group 1 (where sender = client) gives created_at = 2017-06-13 13:05:00-04

That gives us the intervals:

The provider never answered, so we use CURRENT_TIMESTAMP - group1.created_at = 01:00:00 (this times obviously varies over time)

When you say "don't consider this time", and "don't consider it" are you referring to the whole group or just that entry? — Evan Carroll, Commented Jun 13, 2017 at 23:28
The whole group, the provider messaging first would have no group to measure against for the average. And if the client messages last (and did receive messages from the provider), he may just be saying goodbye. @EvanCarroll — chamini2, Commented Jun 14, 2017 at 0:32
You should so what these examples are that you don't want. Or client messages and doesn't receive an answer.. Can't that be determined so long as the group has more than two messages? — Evan Carroll, Commented Jun 14, 2017 at 1:22

Evan Carroll · Accepted Answer · 2017-06-14 01:28:19Z

I'm still kind of confused at what you want, but I think you want this..

SELECT client, provider, sender, min(created_at) AS first_created_at
FROM (
  SELECT count(CASE WHEN is_reset THEN 1 END) OVER (ORDER BY created_at) AS grp,
    client,
    provider,
    sender,
    created_at
  FROM (
    SELECT
      (client,provider,sender) <> lag( (client,provider,sender) ) OVER (ORDER BY created_at) AS is_reset,
      client,
      provider,
      sender,
      created_at
    FROM messages
  ) AS t1
) AS t2
GROUP BY grp, client, provider, sender
HAVING count(*) > 1;

Breaking that down we first create an is_reset using PostgreSQL's row-comparison feature. We could have just written this other ways.

SELECT
  (client,provider,sender) <> lag( (client,provider,sender) ) OVER (ORDER BY created_at) AS is_reset,
  client,
  provider,
  sender,
  created_at
FROM messages

 is_reset | client | provider | sender |       created_at       
----------+--------+----------+--------+------------------------
          |      1 |        2 |      1 | 2017-06-13 12:00:00-05
 f        |      1 |        2 |      1 | 2017-06-13 12:00:10-05
 f        |      1 |        2 |      1 | 2017-06-13 12:01:00-05
 t        |      3 |        2 |      2 | 2017-06-13 12:05:00-05
 t        |      1 |        3 |      1 | 2017-06-13 12:05:00-05
 f        |      1 |        3 |      1 | 2017-06-13 12:05:10-05
 t        |      3 |        2 |      2 | 2017-06-13 12:05:10-05
 f        |      3 |        2 |      2 | 2017-06-13 12:05:20-05
 t        |      1 |        2 |      2 | 2017-06-13 12:10:00-05
 f        |      1 |        2 |      2 | 2017-06-13 12:10:10-05
 f        |      1 |        2 |      2 | 2017-06-13 12:10:20-05
 t        |      1 |        2 |      1 | 2017-06-13 12:11:00-05
 t        |      1 |        2 |      2 | 2017-06-13 12:11:10-05
 t        |      3 |        2 |      3 | 2017-06-13 12:12:00-05
 t        |      3 |        2 |      2 | 2017-06-13 12:12:10-05
 f        |      3 |        2 |      2 | 2017-06-13 12:12:15-05
 t        |      3 |        2 |      3 | 2017-06-13 12:12:30-05
 f        |      3 |        2 |      3 | 2017-06-13 12:14:00-05
(18 rows)

Then we count() to get groups.

SELECT count(CASE WHEN is_reset THEN 1 END) OVER (ORDER BY created_at) AS grp,
  client,
  provider,
  sender,
  created_at
FROM (
  SELECT
    (client,provider,sender) <> lag( (client,provider,sender) ) OVER (ORDER BY created_at) AS is_reset,
    client,
    provider,
    sender,
    created_at
  FROM messages
) AS t1;
 grp | client | provider | sender |       created_at       
-----+--------+----------+--------+------------------------
   0 |      1 |        2 |      1 | 2017-06-13 12:00:00-05
   0 |      1 |        2 |      1 | 2017-06-13 12:00:10-05
   0 |      1 |        2 |      1 | 2017-06-13 12:01:00-05
   2 |      3 |        2 |      2 | 2017-06-13 12:05:00-05
   2 |      1 |        3 |      1 | 2017-06-13 12:05:00-05
   3 |      1 |        3 |      1 | 2017-06-13 12:05:10-05
   3 |      3 |        2 |      2 | 2017-06-13 12:05:10-05
   3 |      3 |        2 |      2 | 2017-06-13 12:05:20-05
   4 |      1 |        2 |      2 | 2017-06-13 12:10:00-05
   4 |      1 |        2 |      2 | 2017-06-13 12:10:10-05
   4 |      1 |        2 |      2 | 2017-06-13 12:10:20-05
   5 |      1 |        2 |      1 | 2017-06-13 12:11:00-05
   6 |      1 |        2 |      2 | 2017-06-13 12:11:10-05
   7 |      3 |        2 |      3 | 2017-06-13 12:12:00-05
   8 |      3 |        2 |      2 | 2017-06-13 12:12:10-05
   8 |      3 |        2 |      2 | 2017-06-13 12:12:15-05
   9 |      3 |        2 |      3 | 2017-06-13 12:12:30-05
   9 |      3 |        2 |      3 | 2017-06-13 12:14:00-05
(18 rows)

And, now we GROUP BY grp, and make sure there are at least two messages (with HAVING) which I assume takes care of your, "compare the time from the first message of a group of messages of client to next message of the provider".

I'm not sure of your other criteria or what you want. You did a great job showing data, perhaps you should continue the trend and show what you want to exclude on your data. Also, please, in the future, draw out a desired result form that data if you can.

Added the example results!
– chamini2
Commented Jun 14, 2017 at 1:37 — chamini2, Commented Jun 14, 2017 at 1:37

chamini2 · Accepted Answer · 2017-06-14 16:25:48Z

First, thanks to Evan's answer, I finally understood the lag function.

In this case, I started using it like this:

postgres=# SELECT
  *,
  lag((client, provider, sender)) OVER chats_window
FROM messages
WINDOW chats_window AS (
  PARTITION BY client, provider
  ORDER BY created_at
);
 id | client | provider | sender |       created_at       |   lag
----+--------+----------+--------+------------------------+---------
  1 |      1 |        2 |      1 | 2017-06-13 13:00:00-04 |
  2 |      1 |        2 |      1 | 2017-06-13 13:00:10-04 | (1,2,1)
  3 |      1 |        2 |      1 | 2017-06-13 13:01:00-04 | (1,2,1)
  4 |      1 |        2 |      2 | 2017-06-13 13:10:00-04 | (1,2,1)
  5 |      1 |        2 |      2 | 2017-06-13 13:10:10-04 | (1,2,2)
  6 |      1 |        2 |      2 | 2017-06-13 13:10:20-04 | (1,2,2)
  7 |      1 |        2 |      1 | 2017-06-13 13:11:00-04 | (1,2,2)
  8 |      1 |        2 |      2 | 2017-06-13 13:11:10-04 | (1,2,1)
 17 |      1 |        3 |      1 | 2017-06-13 13:05:00-04 |
 18 |      1 |        3 |      1 | 2017-06-13 13:05:10-04 | (1,3,1)
  9 |      3 |        2 |      2 | 2017-06-13 13:00:30-04 |
 10 |      3 |        2 |      2 | 2017-06-13 13:00:50-04 | (3,2,2)
 11 |      3 |        2 |      2 | 2017-06-13 13:05:20-04 | (3,2,2)
 12 |      3 |        2 |      3 | 2017-06-13 13:12:00-04 | (3,2,2)
 13 |      3 |        2 |      2 | 2017-06-13 13:12:10-04 | (3,2,3)
 14 |      3 |        2 |      2 | 2017-06-13 13:12:15-04 | (3,2,2)
 15 |      3 |        2 |      3 | 2017-06-13 13:12:30-04 | (3,2,2)
 16 |      3 |        2 |      3 | 2017-06-13 13:14:00-04 | (3,2,3)
(18 rows)

Now that we understand that it refers to the last row (it lags behind), we will use it to determine if a message starts a thread (a group of messages by the same sender):

postgres=# SELECT
  client, provider, sender, created_at,
  coalesce(lag((client, provider, sender)) OVER chats_window <> (client, provider, sender), true) AS thread_starter
FROM messages
WINDOW chats_window AS (
  PARTITION BY client, provider
  ORDER BY created_at
);
 client | provider | sender |       created_at       | thread_starter
--------+----------+--------+------------------------+----------------
      1 |        2 |      1 | 2017-06-13 13:00:00-04 | t
      1 |        2 |      1 | 2017-06-13 13:00:10-04 | f
      1 |        2 |      1 | 2017-06-13 13:01:00-04 | f
      1 |        2 |      2 | 2017-06-13 13:10:00-04 | t
      1 |        2 |      2 | 2017-06-13 13:10:10-04 | f
      1 |        2 |      2 | 2017-06-13 13:10:20-04 | f
      1 |        2 |      1 | 2017-06-13 13:11:00-04 | t
      1 |        2 |      2 | 2017-06-13 13:11:10-04 | t
      1 |        3 |      1 | 2017-06-13 13:05:00-04 | t
      1 |        3 |      1 | 2017-06-13 13:05:10-04 | f
      3 |        2 |      2 | 2017-06-13 13:00:30-04 | t
      3 |        2 |      2 | 2017-06-13 13:00:50-04 | f
      3 |        2 |      2 | 2017-06-13 13:05:20-04 | f
      3 |        2 |      3 | 2017-06-13 13:12:00-04 | t
      3 |        2 |      2 | 2017-06-13 13:12:10-04 | t
      3 |        2 |      2 | 2017-06-13 13:12:15-04 | f
      3 |        2 |      3 | 2017-06-13 13:12:30-04 | t
      3 |        2 |      3 | 2017-06-13 13:14:00-04 | f
(18 rows)

With this information, we can do the following:

postgres=# WITH thread_starts AS (
    SELECT
      client, provider, sender, created_at,
      coalesce(lag((client, provider, sender)) OVER chats_window <> (client, provider, sender), true) AS thread_starter
    FROM messages
    WINDOW chats_window AS (
      PARTITION BY client, provider
      ORDER BY created_at
    )
)
SELECT
  client, provider, sender, created_at,
  lead(created_at) OVER chats_window AS responded_at,
  count(*) OVER (PARTITION BY client, provider) chat_threads_count
FROM thread_starts
WHERE thread_starter
WINDOW chats_window AS (
  PARTITION BY client, provider
  ORDER BY created_at
);
 client | provider | sender |       created_at       |      responded_at      | chat_threads_count
--------+----------+--------+------------------------+------------------------+---------------
      1 |        2 |      1 | 2017-06-13 13:00:00-04 | 2017-06-13 13:10:00-04 |             4
      1 |        2 |      2 | 2017-06-13 13:10:00-04 | 2017-06-13 13:11:00-04 |             4
      1 |        2 |      1 | 2017-06-13 13:11:00-04 | 2017-06-13 13:11:10-04 |             4
      1 |        2 |      2 | 2017-06-13 13:11:10-04 |                        |             4
      1 |        3 |      1 | 2017-06-13 13:05:00-04 |                        |             1
      3 |        2 |      2 | 2017-06-13 13:00:30-04 | 2017-06-13 13:12:00-04 |             4
      3 |        2 |      3 | 2017-06-13 13:12:00-04 | 2017-06-13 13:12:10-04 |             4
      3 |        2 |      2 | 2017-06-13 13:12:10-04 | 2017-06-13 13:12:30-04 |             4
      3 |        2 |      3 | 2017-06-13 13:12:30-04 |                        |             4
 (9 rows)

Getting the table I was looking for in the question (the lead function works like the opposite of lag, looks ahead). Then, with this information, we can have a final query to make sure we are getting all the rules right:

postgres=# WITH thread_starts AS (
    SELECT
      client, provider, sender, created_at,
      coalesce(lag((client, provider, sender)) OVER chats_window <> (client, provider, sender), true) AS thread_starter
    FROM messages
    WINDOW chats_window AS (
      PARTITION BY client, provider
      ORDER BY created_at
    )
 ), response_intervals AS (
    SELECT
      client, provider, sender, created_at,
      lead(created_at) OVER chats_window AS responded_at,
      count(*) OVER (PARTITION BY client, provider) threads_count
    FROM thread_starts
    WHERE thread_starter
    WINDOW chats_window AS (
      PARTITION BY client, provider
      ORDER BY created_at
    )
  )
SELECT
  provider,
  avg(coalesce(responded_at, CURRENT_TIMESTAMP) - created_at) AS response_interval_avg
FROM response_intervals
WHERE sender = client AND (responded_at IS NOT NULL OR threads_count = 1)
GROUP BY provider;
 provider | response_interval_avg
----------+-----------------------
        2 | 00:03:26.666667
        3 | 23:19:46.228611
(2 rows)

Hope this helps someone in the future!

Stack Exchange Network

Window function to merge rows that meet a condition

EXAMPLE RESULTS

2 Answers 2

Your Answer

Not the answer you're looking for? Browse other questions tagged
postgresql
window-functions
gaps-and-islands
or ask your own question.

Hot Network Questions

Window function to merge rows that meet a condition

EXAMPLE RESULTS

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged postgresqlwindow-functionsgaps-and-islands or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
postgresql
window-functions
gaps-and-islands
or ask your own question.