基于条件/列值BigQuery创建分区

Question

假设我有一个列，它具有正负值的随机间隔。如何划分每个负值区间？

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

期望输出：

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

Answer 1

您可以使用以下查询：

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
)

输出：

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