GROUP BY选择另一列的最小元素

Question

假设我有一个这样的表:

tbody> <<tr>

Timestamp1	Timestamp2	Diff
2015-03-17 20:33:00	2015-03-17 20:00:00	33
2015-03-17 20:33:00	2015-03-17 21:00:00	27
2015-03-18 19:17:00	2015-03-18 20:00:00	43
2015-03-18 19:17:00	2015-03-18 19:00:00	17

您可以使用ROW_NUMBER窗口函数为表中的每个差异构建一个增量排序值，然后使用QUALIFY子句将该值设置为1，这样您将只获得第一行(最小差异)，忽略关系。

SELECT * 
FROM tab 
QUALIFY ROW_NUMBER() OVER(PARTITION BY Timestamp1 ORDER BY Diff) = 1 

窗口函数可以提供帮助，因为您可以使用FIRST_VALUE函数

获取第一个值https://cloud.google.com/bigquery/docs/reference/standard-sql/navigation_functions first_value

查询可以像这样:

SELECT 
FIRST_VALUE(Diff)
OVER (PARTITION BY Timestamp1 ORDER BY Diff DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS min-diff
FROM tableA);