Adding conditional statements to a SQL window function

Question

I want to use a series of conditions to dictate how a window function I have works. Currently, what I have is this:

SELECT col1, col2, 
   1=Row_number() OVER (PARTITION BY col1 ORDER BY col2 ASC) OR
   3=Row_number() OVER (PARTITION BY col1 ORDER BY col2 ASC)
   AS col3 
FROM   myTable;

What it's essentially doing is taking two columns of input, grouping by the values in col1, ordering by values in col2, and then splitting the data for each partition into two halves, and flagging the first row of each half as a true/1.

So, taking this input:

+------+------+
| col1 | col2 |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    1 |    3 |
|    1 |    4 |
|    2 |    1 |
|    2 |    2 |
|    2 |    3 |
|    2 |    4 |
+------+------+

We get this result:

+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    1 |    1 |    1 |
|    1 |    2 |    0 |
|    1 |    3 |    1 |
|    1 |    4 |    0 |
|    2 |    1 |    1 |
|    2 |    2 |    0 |
|    2 |    3 |    1 |
|    2 |    4 |    0 |
+------+------+------+

Now, obviously, this only works when there are exactly 4 rows of entries for each value in col1. How do I introduce conditional statements to make this work when there aren't exactly 4 rows?

The constraints I have are these:

a) there will always be an even number of rows (2,4,6..) when grouping by values in `col1`
b) there will be a minimum of 2 rows when grouping by values in `col1`

EDIT:

I think I need to clarify that I do not simply want alternating rows of 1's and 0's. For example, if I used this table instead...

+------+------+
| col1 | col2 |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    1 |    3 |
|    1 |    4 |
|    1 |    5 |
|    1 |    6 |
|    1 |    7 |
|    1 |    8 |
|    2 |    1 |
|    2 |    2 |
|    2 |    3 |
|    2 |    4 |
|    2 |    5 |
|    2 |    6 |
|    2 |    7 |
|    2 |    8 |
+------+------+

...then I'd expect this result:

+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    1 |    1 |    1 |
|    1 |    2 |    0 |
|    1 |    3 |    0 |
|    1 |    4 |    0 |
|    1 |    5 |    1 |
|    1 |    6 |    0 |
|    1 |    7 |    0 |
|    1 |    8 |    0 |
|    2 |    1 |    1 |
|    2 |    2 |    0 |
|    2 |    3 |    0 |
|    2 |    4 |    0 |
|    2 |    5 |    1 |
|    2 |    6 |    0 |
|    2 |    7 |    0 |
|    2 |    8 |    0 |
+------+------+------+

In the original example I gave, we grouped by col1 and saw that there were 4 rows for each partition. We take half of that, which is 2, and flag every 2nd row (every other row) as true/1.

In this second example, once we group by col1, we see that there are 8 rows for each partition. Splitting that in half gives us 4, so every 4th row should be flagged with a true/1.

Gordon Linoff · Accepted Answer · 2020-01-27 19:04:50Z

2

Use modulo arithmetic.

Many dialects of SQL use % for modulus:

SELECT col1, col2, 
       ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2) % 2 as col3
FROM mytable;

Some use the function MOD():

SELECT col1, col2, 
       MOD(ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2), 2) as col3
FROM mytable;

EDIT:

You don't want to alternate rows. You simply want two rows. For that, you can still use modulo arithmetic but with somewhat different logic:

SELECT col1, col2, 
       (ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2) %
        FLOOR(COUNT(*) OVER (PARTITION BY col1) / 2)
       ) as col3
FROM mytable;

edited Jan 27, 2020 at 19:04

answered Jan 27, 2020 at 18:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user-2147482428 Over a year ago

Please see my edit. This doesn't work as expected beyond my original example case.

Ankit Bajpai · Accepted Answer · 2020-01-27 19:14:19Z

1

I am just extending the Gordon's answer as his answer will not give you correct result -

SELECT col1, col2, 
       (CASE WHEN ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2) %
        FLOOR(COUNT(*) OVER (PARTITION BY col1) / 2) = 1 THEN 1 ELSE 0 END 
       ) as col3
FROM mytable;

answered Jan 27, 2020 at 19:14

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

Collectives™ on Stack Overflow

Adding conditional statements to a SQL window function

2 Answers 2

1 Comment

Comments

Your Answer

Collectives™ on Stack Overflow

Adding conditional statements to a SQL window function

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related