sql - 取消嵌套重复字段时在某列上设置不同的值

Question

我有下表：

| Id |         Services          | Value | Type |
-------------------------------------------------
|  1 |  ['One', 'Two', 'Three']  |  30   |   A  |
|  2 |      ['One', 'Two']       |  20   |   A  |
|  3 |         ['One']           |  40   |   B  |

我需要一个取消嵌套字段Services并Value仅保留未嵌套的第一个元素的查询（哪个元素获取值并不重要，但我只需要一次）。

结果将是这样的：

| Id | Services | Value | Type |
--------------------------------
|  1 |  'One'   |  30   |   A  |
|  1 |  'Two'   |   0   |   A  |
|  1 | 'Three'  |   0   |   A  |
|  2 |  'One'   |  20   |   A  |
|  2 |  'Two'   |   0   |   A  |
|  3 |  'One'   |  40   |   B  |

我正在使用标准 SQL。

标签： sqlgoogle-bigquery

Answer 1

关键是使用WITH OFFSET子句，看下面的查询

WITH Test AS (
    SELECT 1 Id, ['One', 'Two', 'Three'] Services, 30 Value, 'A' Type UNION ALL
    SELECT 2, ['One', 'Two'], 20, 'A' UNION ALL
    SELECT 3, ['One'], 40, 'B'
) SELECT Id, Service, IF(offset = 0, Value, 0) Value, Type
  FROM Test, UNNEST(Services) Service WITH OFFSET AS offset

你有