CODEWITHSUNDEEP
sqlteradata
cs0815
cs0815

Reputation: 17418

split data into homogeneous subgroups in teradata

As part of my select I am using the following:

1 + FLOOR((ROW_NUMBER() OVER (ORDER BY Category, Id) - 1) / 100) AS SubGroup

to split my data into SubGroups of 100. The issue I have is that SubGroups can contain more than 1 Category. Is it possible to ensure that SubGroups contain only one Category even if the number of rows are less than 100 in a SubGroup. I tried to use PARTITION BY but this did not work.

Upvotes: 0

Views: 451

Answers (1)

dnoeth
dnoeth

Reputation: 60482

A maximum of 100 rows assigned to a SubGroup and within a SubGroup only rows from single Category?

You probably need a two-step approach:

SELECT ...
   -- start a new sub group for every category or after 100 rows
   Sum(CASE WHEN rn MOD 100 = 1 THEN 1 ELSE 0 END)
   Over (ORDER BY category, rn 
         ROWS Unbounded Preceding) AS SubGroup
FROM 
 (
   SELECT ...
      -- row number per category
      Row_Number() Over (PARTITION  BY Category ORDER BY Id) AS rn
   FROM mytab
 ) AS dt

Upvotes: 1

Related Questions