google-bigquery - 在大查询中为一组 set_id 查找第二个最大 start_date

Question

我有一个包含如下数据的表格



我想要状态为 C 的每组的第二个最高开始日期。
例如
答案应该是

1 2015-05-01
2 2015-05-01
3 2015-06-01

在bigquery中是否有更简单的方法来做到这一点？

Answer 1

首先只过滤 status = 'C' 的记录，然后使用窗口函数按 set_id 对数据进行分区，并按 start_date 在每个集合内排序。然后取第二个值。

SELECT 
  set_id, 
  NTH_VALUE(start_date, 2) OVER(PARTITION BY set_id ORDER BY start_date DESC)
FROM table WHERE status = 'C'