google-bigquery - 如何在 Google bigquery 的记录中找到最高值

Question

我有一个名为 sample 的表。它包含公司利润。我想在 Google bigquery 中显示每个公司的最高利润。

company   2014      2015      2016      2017
companyA  3453564   5343333   2876345   3465234
companyB  5743231   3276438   1645738   2453657
companyC  3245876   2342879   5876324   7342564

提前感谢任何帮助

Answer 1

一种方法是使用 GREATEST(参见 here):

WITH
  input AS (
  SELECT
    'companyA' AS company,
    3453564 AS y2014,
    5343333 AS y2015,
    2876345 AS y2016,
    3465234 AS y2017
  UNION ALL
  SELECT
    'companyB' AS company,
    5743231 AS y2014,
    3276438 AS y2015,
    1645738 AS y2016,
    2453657 AS y2017
  UNION ALL
  SELECT
    'companyC' AS company,
    3245876 AS y2014,
    2342879 AS y2015,
    5876324 AS y2016,
    7342564 AS y2017)
SELECT
  company,
  GREATEST(y2014,y2015,y2016,y2017) AS max_profit
FROM
  input

输出:

Row company     max_profit   
1   companyA    5343333  
2   companyB    5743231  
3   companyC    7342564