group连接字符串(行)

35g0bw71 于 2021-07-27 发布在 Java

关注(0)|答案(2)|浏览(374)

我正在使用google bigquery&我有一个如下所示的查询：

SELECT
        prod.abc
        uniqueid,
        variable2,
        cust.variable1,
        purch.variable2,
        from mydata.order
        left join
        UNNEST(purchases) as purch,
        UNNEST(codes_abs) as cod, UNNEST(cod.try_products) as prod

执行此操作时，将生成一个如下所示的表：

|prod.abc| uniqueid | variable2 | ...|
    |APP123  | customer1| value     | ...|
    |BLU155  | customer1| value     | ...|
    |TRI134  | customer1| value     | ...|
    |LO123   | customer2| value     | ...|
    |ZU9274  | customer2| value     | ...|
    |TO134   | customer3| value     | ...|

我想做的是连接“prod.”列中的值，按“uniqueid”对它们进行分组，并按“，”分隔。我在网上找到了许多解决方案，但是，由于我在查询中没有发现其他变量，所以我找到的所有解决方案似乎都不适用于我的情况。这些值不需要以任何方式排序。基本上，我想结束的是：

|prod.abc                  | uniqueid | variable2 | ...|
    |APP123, BLU155, TRI134    | customer1| value     | ...|
    |LO123, ZU9274             | customer2| value     | ...|
    |TO134                     | customer3| value     | ...|

也可以用这样的表来保存副本，因为我以后可以删除它们：

|prod.abc                  | uniqueid | variable2 | ...|
|APP123, BLU155, TRI134    | customer1| value     | ...|
|APP123, BLU155, TRI134    | customer1| value     | ...|
|APP123, BLU155, TRI134    | customer1| value     | ...|
|LO123, ZU9274             | customer2| value     | ...|
|LO123, ZU9274             | customer2| value     | ...|
|TO134                     | customer3| value     | ...|

非常感谢您的帮助。谢谢您！

sql concatenation group-by google-bigquery string-concatenation

来源：https://stackoverflow.com/questions/63159298/group-concatenate-strings-rows-in-bigquery

2条答案

按热度按时间

sgtfey8w1#

分别做每一个不必要的事情：聚合是否起作用？

SELECT STRING_AGG(item.abc, ',')
       uniqueid, variable2, cust.variable1, purch.variable2
FROM mydata.order LEFT JOIN
     UNNEST(purchases) as purch
     ON true LEFT JOIN
     UNNEST(codes_abs) as cod
     ON true LEFT JOIN
     UNNEST(cod.try_items) as item
     ON true
GROUP BY uniqueid, variable2, cust.variable1, purch.variable2;

赞(0）回复(0）举报 2021-07-27

w8f9ii692#

下面是bigquery标准sql


# standardSQL

SELECT
  STRING_AGG(prod.abc, ', ') AS abc
  uniqueid,
  variable2,
  cust.variable1,
  purch.variable2,
FROM mydata.order
LEFT JOIN UNNEST(purchases) AS purch
LEFT JOIN UNNEST(codes_abs) AS cod
LEFT JOIN UNNEST(cod.try_products) AS prod
GROUP BY uniqueid,
  variable2,
  cust.variable1,
  purch.variable2

赞(0）回复(0）举报 2021-07-27

我来回答

group连接字符串(行)

2条答案

相关问题

热门标签

最新问答