如何获取记录中列值的最大值? (大查询) [英] How to get max value of column values in a record ? (BigQuery)

问题描述

我想获取每行的最大值，而不是字段的最大值. 例如，当我有一个像这样的sample_table时:

I wanna get max value of each rows, not max value of a field. For example, when I have a sample_table like this:

sample_table

|col1|col2|col3|
|--------------|
| 1  | 0  | 0  |
| 0  | 2  | 0  |
| 2  | 0  | 0  |
| 0  | 0  | 3  |

我想要的查询和结果是这样的:

And the query and result I want is something like this:

查询

SELECT SOME_GET_MAX_VAL_FUNC(col1, col2, col3) AS max_val FROM sample_table;

结果

|max_val|
|-------|
|   1   |
|   2   |
|   2   |
|   3   |

我想要一些解决方案来替换SOME_GET_MAX_VAL_FUNC. 如果在SQL中几乎不可能，那为什么呢? (我是SQL和BigQuery的新手)

I want some solution to replace SOME_GET_MAX_VAL_FUNC . If it is nearly impossible in SQL, why is it? (I'm new to SQL and BigQuery)

注释

• col的数量可能非常大，例如col1, col2, ... col200. 因此，使用CASE的解决方案对我来说很难. 多列的SQL MAX?

• The number of cols maybe very big, like col1, col2, ... col200 . So the solution of using CASE would be hard to me. SQL MAX of multiple columns?

特别是，除最大值以外的其余列均等于0.

Particularly, the rest columns other than the max value are equal to 0.

推荐答案

您要GREATEST:

SELECT GREATEST(col1, col2, col3) 
FROM table t;

这篇关于如何获取记录中列值的最大值? (大查询)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！