1

Given a table such as the one below, how can I ask PostgreSQL to return the first set of n rows in which one column has a certain value (code=0) and the values of the second column (named segment) are all the same, and the sequence of IDs is not broken.

For n=4, the result should be id's 12, 13, 14, 15 (i.e. the first four id's where code=0 and the values of segment are the same - in this case 2).

CREATE TABLE foo
AS
  SELECT *
  FROM ( VALUES
    (  1, 0, 0 ),
    (  2, 0, 0 ),
    (  3, 0, 0 ),
    (  4, 0, 1 ),
    (  5, 0, 1 ),
    (  6, 0, 1 ),
    (  7, 1, 1 ),
    (  8, 0, 1 ),
    (  9, 1, 2 ),
    ( 10, 0, 2 ),   
    ( 11, 1, 2 ),
    ( 12, 0, 2 ), -- HERE
    ( 13, 0, 2 ), -- HERE
    ( 14, 0, 2 ), -- HERE
    ( 15, 0, 2 ), -- HERE
    ( 16, 0, 3 )
  ) AS t(id,code,segment);
  • note that it can't return 10, 12, 13, 14, you mean note that it can't return 10, 11, 12, 13? – Evan Carroll Apr 3 '17 at 5:58
  • Also, PostgreSQL doesn't have gappless sequences, so and the sequence of IDs is not broken is going to be near-useless. You sure that isn't supposed to read and the sequence of "segment" is not broken – Evan Carroll Apr 3 '17 at 6:16
  • is your n limited? Or it is a known number not greater then a certain value (say 10)? – cha Apr 3 '17 at 6:36
  • Thanks to the both of you for answering my question! I am grateful that kind folks like you exert so much effort and care to help others. As both of your solutions yield correct results, I am presently torn as to which to mark "correct". – user664833 Apr 4 '17 at 21:51
  • I did mean that the values returned could not be 10, 12, 13, 14 - but I now see why that only served to confuse, since it was already clear from the question title and first sentence. I will remove that note. – user664833 Apr 4 '17 at 21:57
3

You can do something like this..

SELECT
  array_agg(id),
  code,
  segment,
  grp,
  count(*)
FROM (
  SELECT
    id,
    code,
    segment,
    count(is_reset) OVER (ORDER BY id) AS grp
  FROM (
    SELECT
      id,
      code,
      segment,
      CASE
        WHEN
          code <> lag(code) OVER (ORDER BY id)
          OR segment <> lag(segment) OVER (ORDER BY id)
        THEN 1
      END AS is_reset
    FROM foo
  ) AS t
) AS g
GROUP BY code, segment, grp;

   array_agg   | code | segment | grp | count 
---------------+------+---------+-----+-------
 {11}          |    1 |       2 |   6 |     1
 {10}          |    0 |       2 |   5 |     1
 {1,2,3}       |    0 |       0 |   0 |     3
 {7}           |    1 |       1 |   2 |     1
 {4,5,6}       |    0 |       1 |   1 |     3
 {16}          |    0 |       3 |   8 |     1
 {8}           |    0 |       1 |   3 |     1
 {12,13,14,15} |    0 |       2 |   7 |     4
 {9}           |    1 |       2 |   4 |     1
(9 rows)

To query this, just search for code = 0 AND count >= 4.

Explanation

First we generate a reset

SELECT
  id,
  code,
  segment,
  CASE
    WHEN
      code <> lag(code) OVER (ORDER BY id)
      OR segment <> lag(segment) OVER (ORDER BY id)
    THEN 1
  END AS is_reset
FROM foo

 id | code | segment | is_reset 
----+------+---------+----------
  1 |    0 |       0 |         
  2 |    0 |       0 |         
  3 |    0 |       0 |         
  4 |    0 |       1 |        1
  5 |    0 |       1 |         
  6 |    0 |       1 |         
  7 |    1 |       1 |        1
  8 |    0 |       1 |        1
  9 |    1 |       2 |        1
 10 |    0 |       2 |        1
 11 |    1 |       2 |        1
 12 |    0 |       2 |        1
 13 |    0 |       2 |         
 14 |    0 |       2 |         
 15 |    0 |       2 |         
 16 |    0 |       3 |        1
(16 rows)

Then we create groups,

  SELECT
    id,
    code,
    segment,
    count(is_reset) OVER (ORDER BY id) AS grp
  FROM (
    SELECT
      id,
      code,
      segment,
      CASE
        WHEN
          code <> lag(code) OVER (ORDER BY id)
          OR segment <> lag(segment) OVER (ORDER BY id)
        THEN 1
      END AS is_reset
    FROM foo
  ) AS t;
 id | code | segment | grp 
----+------+---------+-----
  1 |    0 |       0 |   0
  2 |    0 |       0 |   0
  3 |    0 |       0 |   0
  4 |    0 |       1 |   1
  5 |    0 |       1 |   1
  6 |    0 |       1 |   1
  7 |    1 |       1 |   2
  8 |    0 |       1 |   3
  9 |    1 |       2 |   4
 10 |    0 |       2 |   5
 11 |    1 |       2 |   6
 12 |    0 |       2 |   7
 13 |    0 |       2 |   7
 14 |    0 |       2 |   7
 15 |    0 |       2 |   7
 16 |    0 |       3 |   8
(16 rows)

Now you'll notice that the groups a fn(code,segment), reseting when either changes. Now we group by the grp, and do a count(*), but because you want the ids we run them through array_agg and store them as an array.

Note, you say and the sequence of IDs is not broken. I'm working off the assumption that this is not what you meant. PostgreSQL doesn't have a concept of gapless sequences, so that's not a good idea anyway. I assume you wanted what I've done.

1

Here is my solution. It is different to Evan's, as I am using the lag() function to get the previous values, and then using the recursive cte to check if the previous values are the same.

The first foorn cte is where I initialise the previous code and segment. I also use the rn column for the row_number() in case your ids are not continuous.

The second cte a is where I check if the previous code/segment equal to the current code/segment and increase the level lvl value.

The third cte b is basically where you set what level you need. There you can pass your variable to select the first set for the required level n

with recursive foorn as(
select *
,lag(code, 1) over(order by id) pre_code
,lag(segment, 1) over(order by id) pre_segment
,row_number() over(order by id) rn
from foo)
, a (id, code, segment, pre_code, pre_segment, rn, min_rn, lvl) as(
select *
,rn as min_rn
,1 as lvl
from foorn where rn=1
union all 
select foorn.id, foorn.code, foorn.segment 
,foorn.pre_code
,foorn.pre_segment
,foorn.rn
,case when foorn.code=foorn.pre_code and foorn.segment=foorn.pre_segment then a.min_rn else foorn.rn end as min_rn
,case when foorn.code=foorn.pre_code and foorn.segment=foorn.pre_segment then a.lvl+1 else 1 end as lvl
from a inner join foorn on a.rn+1=foorn.rn
)
, b as(
select * 
from a 
where lvl = 4 --- <- set the `n` here
order by id limit 1)
select foorn.id, foorn.code, foorn.segment 
from foorn
cross join b
where foorn.rn >= b.min_rn and foorn.rn <= b.rn;

results for n=4:

 id | code | segment
----+------+---------
 12 |    0 |       2
 13 |    0 |       2
 14 |    0 |       2
 15 |    0 |       2

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.