google-bigquery - BigQuery 按数组条目过滤行

Question

我需要创建从条目中的数组值派生的列。下面的示例表有 4 个条目，称为“会话”，每个会话都有多个访问过的应用程序值（在线商店或本地应用程序）。如果在此会话中至少有一个“nativeapp”条目，我希望在派生列中具有“1”值。在第二列中，如果所有条目都是“nativeapp”，我需要有“1”。否则该值应为“0”。

WITH TESTTABLE AS (
  SELECT 'session A' AS session, SPLIT("onlineshop,onlineshop") AS application, 8 AS point UNION ALL
  SELECT 'session B' AS session, SPLIT("onlineshop,nativeapp") AS application, 9 AS point UNION ALL
  SELECT 'session C' AS session, SPLIT("onlineshop,nativeapp") AS application, 5 AS point UNION ALL
  SELECT 'session D' AS session, SPLIT("nativeapp") AS application, 4 AS point
) 

我的尝试是创建一个子查询“select ...”来过滤条目......但是我需要让子查询引用它的条目，但实际上这只是一个丰满的“1”，如果其中有任何条目所有表都适合子查询。

SELECT *, (SELECT MAX(IF(appEntry LIKE "%nativeapp%", 1, 0 )) FROM TESTTABLE, UNNEST(application) as appEntry) as isNativeSession FROM TESTTABLE 

所以结果应该是：

标签： google-bigquery

Answer 1

以下是 BigQuery 标准 SQL

#standardSQL
SELECT *, 
  (SELECT SIGN(COUNT(1)) FROM UNNEST(application) value WHERE value = 'nativeapp') AS isNativeSession,
  (SELECT CAST(STRING_AGG(DISTINCT value) = 'nativeapp' AS INT64) FROM UNNEST(application) value) AS isNativeOnlySession 
FROM TESTTABLE  

结果