大查询标准 分组时获取第一个非空值

Question

>我有这样的表格：

CUSTOMERS_ID  DATE_SALES  DIMENSION
MARIO1        20200201    NULL
MARIO1        20200113    Spain
MARIO2        20200131    NULL
MARIO3        20200101    France
MARIO3        20191231    Spain

我需要按CUSTOMERS_ID和DATE_SALESDESC 字段排序。然后我想按字段分组CUSTOMERS_ID并首先获取维度字段的非空值。 输出表将为：

CUSTOMERS_ID  DIMENSION
MARIO1        Spain
MARIO2        NULL
MARIO3        France

有什么想法吗？我已经尝试了COALESCE函数，FIRST_VALUE，但我没有得到我预期的结果。

提前感谢！

Answer 1

您可以按客户 ID 分组并通过忽略 NULL 来使用ARRAY_AGG，也可以在该字段中按日期排序。 限制 1 将使用更少的 RAM 存储来提高效率。 然后， OFFSET(0( 将使其成为非嵌套字段， 因此您可以轻松使用该字段。

WITH 
raw_data AS
(
SELECT 'MARIO1' CUSTOMERS_ID, 20200201 DATE_SALES, NULL as DIMENSION UNION ALL
SELECT 'MARIO1' CUSTOMERS_ID, 20200113 DATE_SALES, 'Spain' as DIMENSION UNION ALL
SELECT 'MARIO2' CUSTOMERS_ID, 20200131 DATE_SALES, NULL as DIMENSION UNION ALL
SELECT 'MARIO3' CUSTOMERS_ID, 20200101 DATE_SALES, 'France' as DIMENSION UNION ALL
SELECT 'MARIO3' CUSTOMERS_ID, 20191231 DATE_SALES, 'Spain' as DIMENSION
)
SELECT CUSTOMERS_ID, ARRAY_AGG(DIMENSION IGNORE NULLS ORDER BY DATE_SALES DESC LIMIT 1)[OFFSET(0)] as DIMENSION
FROM raw_data
GROUP BY 1

Answer 2

下面是 BigQuery Standard SQL

#standardSQL
SELECT AS VALUE ARRAY_AGG(t ORDER BY IF(DIMENSION IS NULL, NULL, DATE_SALES) DESC LIMIT 1)[OFFSET(0)]
FROM `project.dataset.table` t
GROUP BY CUSTOMERS_ID   

如果应用于问题中的样本数据 - 结果为

Row CUSTOMERS_ID    DATE_SALES  DIMENSION    
1   MARIO1          20200113    Spain    
2   MARIO2          20200131    null     
3   MARIO3          20200101    France   

Answer 3

我们可以在这里使用一个ROW_NUMBER技巧：

WITH cte AS (
SELECT CUSTOMERS_ID,
ROW_NUMBER() OVER (PARTITION BY CUSTOMERS_ID
ORDER BY -1.0*UNIX_SECONDS(DATE_SALES) DESC) rn
FROM yourTable
)
SELECT CUSTOMERS_ID, DIMENSION
FROM cte
WHERE rn = 1
ORDER BY CUSTOMERS_ID;

逻辑是按自纪元以来的负秒数降序行号。 这会将最近的销售额放在首位，并将NULL放在最后，因此，只有在没有非NULL维度数据可用的情况下，NULL值才会接收第 1 行。