在BigQuery中,选择每个分组的第一行有几种方法,以下是其中两种:
方法1:
SELECT * FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY column_to_group ORDER BY column_to_order) row_num FROM your_table ) WHERE row_num = 1
示例代码:
SELECT * FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY country ORDER BY salary DESC) row_num FROM employees ) WHERE row_num = 1;
该代码将从employees表中选择每个国家的最高薪资。
方法2:
SELECT * FROM your_table WHERE column_to_order = ( SELECT MIN(column_to_order) FROM your_table WHERE column_to_group = your_table.column_to_group )
示例代码:
SELECT * FROM employees WHERE salary = ( SELECT MIN(salary) FROM employees WHERE country = employees.country );
该代码将从employees表中选择每个国家的最低薪资。