google-cloud-platform - Bigquery 中数组元素的总和

Question

我必须从表中计算出正数和（负+空或空值）的总数，基本上是 2 个值。我有以下查询来列出负值、空值和正值..但我想要整个计数。请协助。

SELECT
ARRAY(
SELECT count(value),
FROM UNNEST(event_data_results) where REGEXP_CONTAINS(name, r'data.result.result') and ((REGEXP_CONTAINS(value, r'^-?\d+$') and SAFE_CAST(value AS INT64) <= 0 ))) AS negative_attributes,
ARRAY(
SELECT count(value) as neg_val,
FROM UNNEST(event_data_results) where  value = 'null' or value='' ) AS null_attributes,
ARRAY(
SELECT count(value),
FROM UNNEST(event_data_results) where REGEXP_CONTAINS(name, r'data.result.result') and (REGEXP_CONTAINS(value, r'^-?\d+$') and SAFE_CAST(value AS INT64) > 0 )) AS positive_attributes
FROM `table` where EXISTS (SELECT 1 FROM UNNEST(event_keys) as keys , UNNEST(event_data_results) as results WHERE keys.value = "attribute")

event_keys,event_data_results , data_metrics 都是可重复的结构

结果应该是正数：4 负数+空数：4

标签： google-cloud-platformgoogle-bigquery

Answer 1

以下是 BigQuery 标准 SQL

#standardSQL
SELECT
  COUNTIF(result.value > 0) positive_attributes,
  COUNTIF(result.value < 0) negative_attributes,
  COUNTIF(IFNULL(result.value, 0) = 0) null_or_zero_attributes
FROM `project.dataset.table`,
UNNEST(event_data_results) AS result   
WHERE EXISTS (
  SELECT 1 
  FROM UNNEST(event_keys) AS key
  WHERE key.value = "attribute"
)

您可以在此处添加您需要的任何条件

此外，如果result.value是一个字符串 - 你可以SAFE_CAST(result.value AS INT64)像你已经这样做的那样使用，所以我没有关注你案例的这方面