时间戳

首页 > 解决方案 > 如何结合 BigQuery LAST_VALUE() 和 ARRAY_AGG()

sql - 如何结合 BigQuery LAST_VALUE() 和 ARRAY_AGG()

问题描述

这是一个玩具示例：

select * 
from(
select 1 as row_num,298807 as id1,104 as id2,'2018-07-10' as date
union all
select 2,298807,104,'2018-08-02'
union all
select 3,298807,104,'2018-08-06'
union all
select 4,298807,104,'2018-08-08'
union all
select 5,298807,104,'2018-08-24'
union all
select 6,298807,104,'2018-09-28'
union all
select 7,298807,104,'2018-10-01'
union all
select 8,298807,104,'2018-10-28'
union all
select 9,298807,300,'2018-10-30'
union all
select 10,298807,104,'2018-11-12'
union all
select 11,298807,300,'2018-11-20'
union all
select 12,298807,104,'2018-11-30'
union all
select 13,298807,104,'2018-12-02'
union all
select 14,298807,104,'2018-12-03')

对于每一行，我想在 id1 中找到不同 id2 的最后一行。例如，对于第 11 行，输出应该是一个包含两个元素“第 9 行，第 10 行”的数组，而对于第 14 行，它是“第 11 行，第 13 行”。

这是一个示例输出： 在此处输入图像描述

标签： sqlgoogle-bigquery

解决方案

以下是 BigQuery 标准 SQL

#standardSQL
SELECT * EXCEPT(candidates),
  ARRAY_TO_STRING(ARRAY(
    SELECT CAST(MAX(row_num) AS STRING) row_num
    FROM t.candidates
    GROUP BY id2
    ORDER BY row_num
  ), ',') AS output
FROM (
  SELECT *, ARRAY_AGG(STRUCT(id2, row_num)) OVER(win) candidates
  FROM `project.dataset.table` 
  WINDOW win AS (PARTITION BY id1 ORDER BY row_num ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
) t
-- ORDER BY row_num

如果适用于您的问题的样本数据 - 输出是

Row row_num id1     id2 date        output   
1   1       298807  104 2018-07-10       
2   2       298807  104 2018-08-02  1    
3   3       298807  104 2018-08-06  2    
4   4       298807  104 2018-08-08  3    
5   5       298807  104 2018-08-24  4    
6   6       298807  104 2018-09-28  5    
7   7       298807  104 2018-10-01  6    
8   8       298807  104 2018-10-28  7    
9   9       298807  300 2018-10-30  8    
10  10      298807  104 2018-11-12  8,9  
11  11      298807  300 2018-11-20  10,9     
12  12      298807  104 2018-11-30  10,11    
13  13      298807  104 2018-12-02  11,12    
14  14      298807  104 2018-12-03  11,13

推荐阅读