how to find highest value in a record in Google bigquery

David Published at Dev

david

I have a table called sample. It contains companies profits. I would like to display the highest profit of each company in Google bigquery.

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

Any help thanks in advance

Mikhail Berlyant

Below is for BigQuery Standard SQL and gives best profit value along with year and also has no dependency on number of year columns and their (columns) names

#standardSQL
WITH `project.dataset.companies` AS (
  SELECT 'companyA' company, 3453564 year_2014, 5343333 year_2015, 2876345 year_2016, 3465234 year_2017 UNION ALL
  SELECT 'companyB', 5743231, 3276438, 1645738, 2453657 UNION ALL
  SELECT 'companyC', 3245876, 2342879, 5876324, 7342564 
)
SELECT 
  company,   
  highest_profit.*
FROM `project.dataset.companies` t, UNNEST(
  ARRAY(
    SELECT AS STRUCT 
      SPLIT(REGEXP_REPLACE(kv, r'[{"}]', ''), ':')[OFFSET(0)] year,
      SAFE_CAST(SPLIT(REGEXP_REPLACE(kv, r'[{"}]', ''), ':')[OFFSET(1)] AS INT64) profit
    FROM UNNEST(SPLIT(TO_JSON_STRING(t), ',"')) kv
    ORDER BY profit DESC
    LIMIT 1
  )
) highest_profit

with result as

Row company     year        profit   
1   companyA    year_2015   5343333  
2   companyB    year_2014   5743231  
3   companyC    year_2017   7342564

Update: decided to borrow idea from Mosha's Star Rating post and apply it to time series here and adding kind of sparklines chart

#standardSQL
CREATE TEMP FUNCTION sparklines(arr ARRAY<INT64>) AS ((
  SELECT STRING_AGG(CODE_POINTS_TO_STRING([code]), '') 
  FROM UNNEST(arr) el, 
  UNNEST([(SELECT MAX(el) FROM UNNEST(arr) el)]) mx, 
  UNNEST([(SELECT MIN(el) FROM UNNEST(arr) el)]) mn
  JOIN UNNEST([9602, 9603, 9605, 9606, 9607]) code WITH OFFSET pos
  ON pos = CAST(IF(mx = mn, 1, (el - mn) / (mx - mn)) * 4 AS INT64) 
)); 
WITH `project.dataset.companies` AS (
  SELECT 'companyA' company, 3453564 year_2014, 5343333 year_2015, 2876345 year_2016, 3465234 year_2017 UNION ALL
  SELECT 'companyB', 5743231, 3276438, 1645738, 2453657 UNION ALL
  SELECT 'companyC', 3245876, 2342879, 5876324, 7342564 
)
SELECT 
  company,   
  highest_profit.*,
  sparklines(
    ARRAY(
      SELECT SAFE_CAST(SPLIT(REGEXP_REPLACE(kv, r'[{"}]', ''), ':')[OFFSET(1)] AS INT64) profit
      FROM UNNEST(SPLIT(TO_JSON_STRING(t), ',"')) kv
      WHERE NOT SAFE_CAST(SPLIT(REGEXP_REPLACE(kv, r'[{"}]', ''), ':')[OFFSET(1)] AS INT64) IS NULL
      ORDER BY SPLIT(REGEXP_REPLACE(kv, r'[{"}]', ''), ':')[OFFSET(0)]
    )
  ) history
FROM `project.dataset.companies` t, UNNEST(
  ARRAY(
    SELECT AS STRUCT 
      SPLIT(REGEXP_REPLACE(kv, r'[{"}]', ''), ':')[OFFSET(0)] year,
      SAFE_CAST(SPLIT(REGEXP_REPLACE(kv, r'[{"}]', ''), ':')[OFFSET(1)] AS INT64) profit
    FROM UNNEST(SPLIT(TO_JSON_STRING(t), ',"')) kv
    ORDER BY profit DESC
    LIMIT 1
  )
) highest_profit

with result as below

Row company     year        profit      history  
1   companyA    year_2015   5343333     ▃▇▂▃     
2   companyB    year_2014   5743231     ▇▅▂▃     
3   companyC    year_2017   7342564     ▃▂▆▇

Collected from the Internet

Please contact [email protected] to delete if infringement.