代码之家  ›  专栏  ›  技术社区  ›  S31

合并两列并创建新行

  •  0
  • S31  · 技术社区  · 8 年前

    我当前的表如下所示:

    +-------+--------------------------------------+------+-----+
    | id    | time                        | color1 |color2|type |
    +-------+--------------------------------------+------+-----+
    | 10954 | 2018-09-09 23:20:01.074 UTC | yellow | blue | 1   |
    +-------+--------------------------------------+------+-----+
    | 10954 | 2018-10-09 20:38:61.151 UTC | red    | blue | 1   |
    +-------+--------------------------------------+------+-----+
    | 20562 | 2018-08-09 19:49:14.391 UTC | green  | red  | 0   |
    +-------+--------------------------------------+------+-----+
    | 20562 | 2017-09-09 17:02:22.903 UTC | green  | red  | 1   |
    +-------+--------------------------------------+------+-----+
    

    我的目标是:

    +-------+--------------------------------------+------+
    | id    | time                        | color  | type |     
    +-------+--------------------------------------+------+
    | 10954 | 2018-09-09 23:20:01.074 UTC | yellow |  1   |     
    +-------+--------------------------------------+------+
    | 10954 | 2018-10-09 20:38:61.151 UTC | red    |  1   |     
    +-------+--------------------------------------+------+
    | 10954 | 2018-09-09 23:20:01.074 UTC | blue   |  0   |     
    +-------+--------------------------------------+------+
    | 20562 | 2018-08-09 19:49:14.391 UTC | green  |  0   |     
    +-------+--------------------------------------+------+
    | 20562 | 2017-09-09 17:02:22.903 UTC | green  |  1   |     
    +-------+--------------------------------------+------+
    | 20562 | 2017-09-09 17:02:22.903 UTC | red    |  0   |     
    +-------+--------------------------------------+------+
    

    通过这样做,这将为 color2 ,在哪里 id 会被复制, time 会是 最小 type = 0 . 有没有可能在短时间内做到这一点 case when 创建新颜色列或使用CTE时的语句?

    1 回复  |  直到 8 年前
        1
  •  1
  •   Mikhail Berlyant    8 年前

    下面是BigQuery标准SQL

    #standardSQL
    SELECT id, time, color1 AS color, type
    FROM `project.dataset.table`
    UNION ALL
    SELECT id, MIN(time) AS time, color2 AS color, 0 type
    FROM `project.dataset.table`
    GROUP BY id, color2
    

    你可以测试,玩上面使用虚拟dta从你的问题如下

    #standardSQL
    WITH `project.dataset.table` AS (
      SELECT 10954 id, '2018-09-09 23:20:01.074 UTC' time, 'yellow' color1, 'blue' color2, 1 type UNION ALL
      SELECT 10954, '2018-10-09 20:38:61.151 UTC', 'red', 'blue', 1 UNION ALL
      SELECT 20562, '2018-08-09 19:49:14.391 UTC', 'green', 'red', 0 UNION ALL
      SELECT 20562, '2017-09-09 17:02:22.903 UTC', 'green', 'red', 1
    )
    SELECT id, time, color1 AS color, type
    FROM `project.dataset.table`
    UNION ALL
    SELECT id, MIN(time) AS time, color2 AS color, 0 type
    FROM `project.dataset.table`
    GROUP BY id, color2
    -- ORDER BY id 
    

    Row id      time                        color   type     
    1   10954   2018-09-09 23:20:01.074 UTC yellow  1    
    2   10954   2018-10-09 20:38:61.151 UTC red     1    
    3   10954   2018-09-09 23:20:01.074 UTC blue    0    
    4   20562   2018-08-09 19:49:14.391 UTC green   0    
    5   20562   2017-09-09 17:02:22.903 UTC green   1    
    6   20562   2017-09-09 17:02:22.903 UTC red     0