Postgresql – How to get row_number() within a group

gaps-and-islandspostgresql

I have a table event_seating_lookup with 3 columns: (id int, event_id int, lookup_is_successful boolean).

For every event_seating_lookup record, I want to get a number of previous (lag) records with the same lookup_is_successful value, i.e.

Assuming the following setup:

CREATE TABLE event_seating_lookup  (id int, event_id int, lookup_is_successful boolean);

INSERT INTO event_seating_lookup (id, event_id, lookup_is_successful) VALUES (1, 1, TRUE);
INSERT INTO event_seating_lookup (id, event_id, lookup_is_successful) VALUES (2, 1, TRUE);
INSERT INTO event_seating_lookup (id, event_id, lookup_is_successful) VALUES (3, 1, TRUE);
INSERT INTO event_seating_lookup (id, event_id, lookup_is_successful) VALUES (4, 1, FALSE);
INSERT INTO event_seating_lookup (id, event_id, lookup_is_successful) VALUES (5, 1, FALSE);
INSERT INTO event_seating_lookup (id, event_id, lookup_is_successful) VALUES (6, 1, TRUE);
INSERT INTO event_seating_lookup (id, event_id, lookup_is_successful) VALUES (7, 1, TRUE);
INSERT INTO event_seating_lookup (id, event_id, lookup_is_successful) VALUES (8, 1, TRUE);
INSERT INTO event_seating_lookup (id, event_id, lookup_is_successful) VALUES (9, 1, TRUE);
INSERT INTO event_seating_lookup (id, event_id, lookup_is_successful) VALUES (10, 1, TRUE);

Using a simple row_number() function gives absolute number within the group:

SELECT
  esl1.id,
  esl1.event_id,
  esl1.lookup_is_successful,
  row_number() OVER (PARTITION BY event_id, lookup_is_successful ORDER BY id ASC) consecutive_lookup_is_successful_without_change
FROM event_seating_lookup esl1
WHERE esl1.event_id = 1
ORDER BY esl1.id DESC;

id  event_id  lookup_is_successful  consecutive_lookup_is_successful_without_change
--  --------  --------------------  -----------------------------------------------
10  1         true                  8
9   1         true                  7
8   1         true                  6
7   1         true                  5
6   1         true                  4
5   1         false                 2
4   1         false                 1
3   1         true                  3
2   1         true                  2
1   1         true                  1

http://www.sqlfiddle.com/#!15/7f550/2

Meanwhile, I want to get a table:

id  event_id  lookup_is_successful  consecutive_lookup_is_successful_without_change
--  --------  --------------------  -----------------------------------------------
10  1         true                  5
9   1         true                  4
8   1         true                  3
7   1         true                  2
6   1         true                  1
5   1         false                 2
4   1         false                 1
3   1         true                  3
2   1         true                  2
1   1         true                  1

The ultimate objective is to get a table that tells me for every event (event_id) in event_seating_lookup, how many failed lookups (lookup_is_successful=FALSE) there are, i.e.

event_id  consecutive_lookup_is_successful_without_change
--------  -----------------------------------------------
5         5

Best Answer

Thanks to @Colin's recommendation to read an article about using lag/sum to create virtual groups, I come up with the following solution:

WITH
  lookup_is_successful_change AS (
    SELECT
      esl1.id,
      esl1.event_id,
      esl1.lookup_is_successful,
      CASE
        WHEN (
          lag(esl1.lookup_is_successful) OVER (PARTITION BY esl1.event_id ORDER BY id ASC) != esl1.lookup_is_successful OR
          lag(esl1.lookup_is_successful) OVER (PARTITION BY esl1.event_id ORDER BY id ASC) IS NULL
        )
        THEN 1
        ELSE 0
      END lookup_is_successful_change
    FROM event_seating_lookup esl1
    ORDER BY esl1.id DESC
  ),
  look_is_successful_change_group AS (
    SELECT
      lisc1.id,
      lisc1.event_id,
      lisc1.lookup_is_successful,
      sum(lisc1.lookup_is_successful_change) OVER (PARTITION BY lisc1.event_id ORDER BY id ASC) group_vid
    FROM lookup_is_successful_change lisc1
    ORDER BY lisc1.id DESC
  )
SELECT DISTINCT ON (liscg1.event_id)
  liscg1.event_id,
  liscg1.lookup_is_successful,
  row_number() OVER (PARTITION BY liscg1.event_id, liscg1.group_vid ORDER BY id ASC) consecutive_lookup_is_successful_without_change
FROM look_is_successful_change_group liscg1
ORDER BY liscg1.event_id, liscg1.id DESC;

http://www.sqlfiddle.com/#!15/7f550/5

This query is not very performant, though.

Related Question