sql - 如何计算数组GBQ中的重复值？

Question

如何计算 Google-Big-Query 中每个数组的重复值？

数据：

WITH temp AS (SELECT 1 id, ["a","b", "a"] name )
SELECT * FROM temp

怎么获得：

1 | name | count|
  |  a   |   2  |
  |  b   |   1  |

标签： sqlarraysgoogle-bigquery

Answer 1

考虑以下方法

select id, array(
    select as struct name, count(1) `count`
    from t.name name
    group by name
  ) stats
from temp t             

带输出