CODEWITHSUNDEEP
sqlpostgresqlconditional-statementswindow-functions
user-2147482428
user-2147482428

Reputation: 167

Adding conditional statements to a SQL window function

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.

Upvotes: 0

Views: 258

Answers (2)

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

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;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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;

Upvotes: 2

Related Questions