1

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
  • 1
    How can you determine which group is which? Does a group start when lookup_is_succesful changes? – Colin 't Hart Aug 12 '18 at 20:02
  • Your assumption is correct: group starts with lookup_is_succesful change. – Gajus Aug 12 '18 at 20:03
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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