如何在SQL语句中获取非分组列(类似于MySQL) [英] How to get non grouped-by columns in SQL statement (similar to in MySQL)

问题描述

在MySQL中，我可以执行以下查询:

In MySQL, I can do the following query:

select first_name from `table` group by age

但是，如果我在BigQuery中执行类似的操作，则会收到以下错误:

However, if I do something similar in BigQuery, I get the following error:

SELECT列表表达式引用的列first_name既未分组也未聚集

SELECT list expression references column first_name which is neither grouped nor aggregated

我们将如何在BigQuery中编写等效的mysql聚合查询?

How would we write the equivalent mysql aggregation query in BigQuery?

推荐答案

以下内容适用于BigQuery标准SQL，如下所示

Below is for BigQuery Standard SQL and as simple as below

#standardSQL
SELECT ANY_VALUE(first_name) first_name
FROM `project.dataset.table`
GROUP BY age

如您所见，您只缺少聚合函数-它可以是任何函数-MAX，MIN等.我选择了ANY_VALUE作为示例

As you can see you were missing just aggregation function - it can be any - MAX, MIN, etc. I've chosen ANY_VALUE as an example

您可以使用下面的示例中的一些简化的虚拟数据进行测试，操作

You can test, play with above using some simplified dummy data like in example below

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'Thomas' first_name, 25 age UNION ALL
  SELECT 'Mike', 25 UNION ALL
  SELECT 'Thomas', 30 UNION ALL
  SELECT 'Mark', 40
)
SELECT ANY_VALUE(first_name) first_name
FROM `project.dataset.table`
GROUP BY age

有结果

Row first_name   
1   Thomas   
2   Thomas   
3   Mark     

这篇关于如何在SQL语句中获取非分组列(类似于MySQL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！