ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Grouping a contiguous sequence?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Grouping a contiguous sequence?

    Hi all,
    So we all know well that poorly architected tables can lead to us having to do double backflips in SQL to try to get the data, right??

    Does anyone have any tricks for grouping subsequent records that follow a contiguous sequence and then breaking to the next group when the sequence has a gap? In the example below. I need to group 110 - 113 together and 0117-0118 together somehow. My last good idea fizzled when I found that I could not use a sequence object inside a case statement (for next or previous value). It seems that Dense_Rank could work but without it tracking a sequential increment, it hasn't worked for me yet.

    Data Example:

    SeqCd Comnt
    1131 Now is the time
    1132 for all good men
    1133 to come to the aid
    1137 just a note to let
    1138 you know that we
    1139 won’t be able to
    1313 where did this come


    I have used LAG and LEAD to determine previous and next SeqCd values but it hasn’t helped me do much more than identify where the new groups would start.

    With mydata as(
    SELECT SeqCd,
    LAG(SeqCd,1) OVER(ORDER BY SeqCd) AS Prior_Seq,
    LEAD(SeqCd,1,-1) OVER(ORDER BY SeqCd) AS Next_Seq
    FROM rmcz
    ORDER BY SeqCd
    )
    ,foraft as(
    Select a.*,
    case when SeqCd = prior_seq +1
    then 'N'
    else 'Y'
    end as NewGroup
    from mydata a
    )
    select * from foraft ORDER BY SeqCd;


    SeqCD Prior Next NewGroup
    1131 - 1132 Y
    1132 1131 1133 N
    1133 1132 1137 N
    1137 1133 1138 Y
    1138 1137 1139 N
    1139 1138 1313 N
    1313 1139 -1 Y
    Thanks for any help!
    Cliff Dowell
    Last edited by CliffDowell; December 9, 2020, 04:10 PM. Reason: Edited to correct spacing in final result headers.

  • #2
    What do you want the output to look like?

    Out of curiosity, what problem are you trying to solve?

    Comment


    • #3
      Hi Ted,
      When I was looking to do this with a dense_rank() or sequence object, I envisioned a column added to each of the rows of the last example that would be a grouping number (GrpCode) to use as a key to the text groups. hopefully the spacing holds so the example below is readable.

      Whatever solution there is for this will become a cte to generate the groups of texts that need to be concatenated by their "virtue of continuous grouping".

      I gave the team facing this the recursive query to concatenate each of the corresponding texts together when I thought they already had a handle on segregating the data.

      When I find out that there are no particular keys or controls to this data other than the "contiguous nature of the sequence," it became my job (because now that it touched me...) to find some way to compartmentalize the text sets before I can put them to the second part that concatenates the texts within subsequent group.


      SeqCD Prior Next NewGroup GrpCode
      1131 - 1132 Y 1
      1132 1131 1133 N 1
      1133 1132 1137 N 1
      1137 1133 1138 Y 2
      1138 1137 1139 N 2
      1139 1138 1313 N 2
      1313 1139 -1 Y 3

      Thanks for your interest!
      Cliff

      Comment


      • #4
        I think I have it. I whittled the code down to indicate the breaks only by code, assigned the group code to the NewGroup = 'Y' recs, and then used a LAG with IGNORE NULLS to backfill the existing previous GrpCode. My missing link (other than proper data standards 15 years ago) was the IGNORE NULLS on the LAG.

        You making me say out loud what I wanted the output to look like made me see that intermediate step.

        Thanks for your help!
        Cliff

        Solution code -
        With mydata as(
        SELECT SeqCd,
        Case when (LAG(SeqCd,1) OVER(ORDER BY SeqCd)) = SeqCd- 1
        then 'N'
        else 'Y' end as NewGroup
        FROM rmcz
        ORDER BY SeqCd
        )
        ,Grouper as(
        Select a.*,
        rownumber() over (order by SeqCd) as GrpCode
        from mydata a
        where newgroup = 'Y'
        ORDER BY SeqCd
        )
        select a.SeqCd, a.newgroup,
        case when b.grpCode is null
        then LAG(grpcode,1) ignore nulls OVER(ORDER BY a.SeqCd)
        else b.grpCode
        end as GrpCode
        from mydata a
        left outer join grouper b
        on a.SeqCd = b.SeqCd
        ORDER BY SeqCd;


        Result Set
        SeqCd NewGrp GrpCd
        1131 Y 1
        1132 N 1
        1133 N 1
        1137 Y 2
        1138 N 2
        1139 N 2
        1313 Y 3

        Comment


        • #5
          That's clever, Cliff. Thanks for sharing. I'll have to play with it myself to be sure I understand how it works.

          Comment

          Working...
          X