CODEWITHSUNDEEP
google-bigquery
KayEss
KayEss

Reputation: 419

Create partitions based on condition/column values BigQuery

Let's say I have a column with random intervals of positive and negative values. What would be the way to partition each interval of negative values?

Column1
-1
-2
-3
 2
 2
-1
-1
 4
-3
-3
-3

Desired output:

Column1  PartitionColumn1
-1       first
-2       first
-3       first
 2
 2
-1       second
-1       second
 4 
-3       third
-3       third
-3       thrid

Upvotes: 0

Views: 586

Answers (1)

Yun Zhang
Yun Zhang

Reputation: 5503

You can use query below:

WITH data AS (
SELECT -1 x
UNION ALL SELECT -2
UNION ALL SELECT -3
UNION ALL SELECT  2
UNION ALL SELECT  2
UNION ALL SELECT -1
UNION ALL SELECT -1
UNION ALL SELECT  4
UNION ALL SELECT -3
UNION ALL SELECT -3
UNION ALL SELECT -3)
SELECT x, IF (x < 0, SUM(first_negative) OVER (ORDER BY 0 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ), 0) PartitionColumn1
FROM (
  SELECT x, IF (x < 0 AND LAG(x, 1, 1) OVER (ORDER BY 0 ) > 0, 1, 0) first_negative
  FROM data
)

Outputs:

+----+------------------+
| x  | PartitionColumn1 |
+----+------------------+
| -1 |                1 |
| -2 |                1 |
| -3 |                1 |
|  2 |                0 |
|  2 |                0 |
| -1 |                2 |
| -1 |                2 |
|  4 |                0 |
| -3 |                3 |
| -3 |                3 |
| -3 |                3 |
+----+------------------+

Upvotes: 1

Related Questions