CODEWITHSUNDEEP
sqlteradata
xiaodai
xiaodai

Reputation: 16074

How to count the number of times an element appears consecutively in a table in Teradata?

I have a table that looks like this

ID, Order, Segment
1, 1, A
1, 2, B
1, 3, B
1, 4, C
1, 5, B
1, 6, B
1, 7, B
1, 8, B

Basically by ordering the data using the Order column. I would like to understand the number of consecutive B's for each of the ID's. Ideally the output I would like is

ID, Consec
1, 2
1, 4

Because the segment B appears consecutively in row 2 and 3 (2 times), and then again in row 5,6,7,8 (4 times).

I can't think of a solution in SQL since there is no loop facility in SQL.

Are there elegant solutions in Teradata SQL?

P.S. The data I am dealing with has ~20 million rows.

The way to do it in R has been published here.

How to count the number of times an element appears consecutively in a data.table?

Upvotes: 1

Views: 1044

Answers (1)

Brian DeMilia
Brian DeMilia

Reputation: 13248

It is easy to do with analytic functions. While I don't know anything about teradata, quickly googling makes it appear as though it does support analytic functions.

In any case, I've tested the following in Oracle --

  select id,
         count(*)
    from (select x.*,
                 row_number() over(partition by id order by ord) -
                 row_number() over(partition by id, seg order by ord) as grp
            from tbl x) x
   where seg = 'B'
group by id, grp
order by grp

The trick is establishing the 'groups' of Bs.

Fiddle: http://sqlfiddle.com/#!4/4ed6c/2/0

Upvotes: 4

Related Questions