CODEWITHSUNDEEP
google-bigquery
Mikhail Berlyant
Mikhail Berlyant

Reputation: 173121

BigQuery: How to convert Year to Decade?

In this great article, I found the way to convert Year into Decade, but it looks too verbose to me!

Is there better way to do this? 

#standardSQL
WITH data AS (
  SELECT '1921' AS YEAR UNION ALL
  SELECT '1932' UNION ALL
  SELECT '1943' UNION ALL
  SELECT '1954' UNION ALL
  SELECT '1985' UNION ALL
  SELECT '2006' UNION ALL
  SELECT '2017' UNION ALL
  SELECT '2018' 
)
SELECT
  YEAR,
  CASE 
    WHEN YEAR LIKE "192%" THEN "20S"
    WHEN YEAR LIKE "193%" THEN "30S"
    WHEN YEAR LIKE "194%" THEN "40S"
    WHEN YEAR LIKE "195%" THEN "50S"
    WHEN YEAR LIKE "196%" THEN "60S"
    WHEN YEAR LIKE "197%" THEN "70S"
    WHEN YEAR LIKE "198%" THEN "80S"
    WHEN YEAR LIKE "199%" THEN "90S"
    WHEN YEAR LIKE "200%" THEN "2000S"
    WHEN YEAR LIKE "201%" THEN "2010S"
  END AS DECADES
FROM data
-- ORDER BY YEAR

Upvotes: 0

Views: 318

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173121

To "extract" Decade from Year - REGEXP_REPLACE function can be used as in example below 

#standardSQL
WITH data AS (
  SELECT '1921' AS YEAR UNION ALL
  SELECT '1932' UNION ALL
  SELECT '1943' UNION ALL
  SELECT '1954' UNION ALL
  SELECT '1985' UNION ALL
  SELECT '2006' UNION ALL
  SELECT '2017' UNION ALL
  SELECT '2028' 
)
SELECT
  YEAR,
  REGEXP_REPLACE(YEAR, r'(?:19)(\d)\d|(20\d)\d', '\\1\\20s') AS DECADES
FROM data
-- ORDER BY YEAR

Upvotes: 1

Related Questions