Collate language codes into one combined locale co

问题:

Learning about CASE WHEN, and I've come across a use case when I see multiple locale codes in Analytics. This is a much simpler question that is much easier to answer and read, than my prior question that was posted.

Examples are: en-us (english US) en-au (english australia) en-br (english brazil) es-es (spanish spain) es-419 (spanish-latam) pt-br (portuguese brazil) pt-pt (portugal)

How do I aggregate these in BigQuery so that instead of counting distinct values, I can count the number of times where only the first two characters of the locale are found?

The second part of this question is: How do I structure my table in such a way so that I am able to plot these counts by date?

Currently, the output is: date:language_code:CombinedLocale

Link to example data table: https://docs.google.com/spreadsheets/d/1XZp1nhNZySWI39kKhb3ydYYIImmrfAMcGJDS6ASThqg/edit?usp=sharing

I've tried:

SELECT date, COUNT(language_code),
CASE 
    WHEN language_code like '%af%' THEN 'AF'
    WHEN language_code like '%en%' THEN 'EN'
    WHEN language_code like '%ar%' THEN 'AR'
    WHEN language_code like '%ba%' THEN 'BA'
ELSE "Others"
END AS CombinedLocale
FROM date_locales

And:

Select date, COUNT(language_code)
FROM date_locales
WHERE CASE 
WHEN language_code like '%af%' THEN 'AF'
WHEN language_code like '%en%' THEN 'EN'
WHEN language_code like '%ar%' THEN 'AR'
WHEN language_code like '%ba%' THEN 'BA'
ELSE "Others"
END

This is the working code I have:

SELECT date, language_code,
CASE 
    WHEN language_code like '%af%' THEN 'AF'
    WHEN language_code like '%en%' THEN 'EN'
    WHEN language_code like '%ar%' THEN 'AR'
    WHEN language_code like '%ba%' THEN 'BA'
ELSE "Others"
END AS CombinedLocale
FROM date_locales

I expected the results to display a count of CombinedLocale table over time as such:

    Jan AF 3
    JAN EN 5
    FEB AF 5
    FEB EN 6
    MAR EN 2
    MAR EN 3

but I get an error message stating: SELECT list expression references column date which is neither grouped nor aggregated (Line: 1, Column: 8)

I believe I will need to aggregate the dates into Months first? I am under the impression BigQuery integration w/ DataStudio will automatically aggregate the date column.

回答1:

Are you just looking for an aggregation query?

SELECT date,
       (CASE WHEN language_code like '%af%' THEN 'AF'
             WHEN language_code like '%en%' THEN 'EN'
             WHEN language_code like '%ar%' THEN 'AR'
             WHEN language_code like '%ba%' THEN 'BA'
             ELSE 'Others'
        END) AS CombinedLocale,
       COUNT(*)
FROM date_locales
GROUP BY date, CombinedLocale;

回答2:

Below is for BigQuery Standard SQL and answers both items in your question

#standardSQL
SELECT 
  FORMAT_DATE('%b %Y', PARSE_DATE('%m/%d/%Y', dt)) month_year, 
  REGEXP_EXTRACT(code, r'(.*?)-') code, 
  COUNT(1) cnt
FROM `project.dataset.date_locales`
GROUP BY month_year, code   

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

#standardSQL
WITH `project.dataset.date_locales` AS (
  SELECT '3/14/2019' dt, 'af-ZA' code UNION ALL
  SELECT '3/14/2019', 'am-ET' UNION ALL
  SELECT '5/7/2019', 'ar-AE' UNION ALL
  SELECT '5/19/2019', 'ar-BH' UNION ALL
  SELECT '3/5/2019', 'ar-DZ' UNION ALL
  SELECT '1/1/2019', 'ar-EG' UNION ALL
  SELECT '3/31/2019', 'ar-IQ' UNION ALL
  SELECT '4/20/2019', 'ar-JO' UNION ALL
  SELECT '3/17/2019', 'ar-KW' UNION ALL
  SELECT '1/8/2019', 'ar-LB' UNION ALL
  SELECT '3/26/2019', 'ar-LY' UNION ALL
  SELECT '5/7/2019', 'ar-MA' UNION ALL
  SELECT '3/12/2019', 'arn-CL' UNION ALL
  SELECT '5/19/2019', 'ar-OM' UNION ALL
  SELECT '4/19/2019', 'ar-QA' UNION ALL
  SELECT '4/20/2019', 'ar-SA' UNION ALL
  SELECT '5/22/2019', 'ar-SY' UNION ALL
  SELECT '5/23/2019', 'ar-TN' UNION ALL
  SELECT '3/10/2019', 'ar-YE' UNION ALL
  SELECT '4/6/2019', 'as-IN' UNION ALL
  SELECT '2/5/2019', 'az-Cyrl' UNION ALL
  SELECT '3/1/2019', 'az-Latn' UNION ALL
  SELECT '3/25/2019', 'ba-RU' UNION ALL
  SELECT '1/1/2019', 'be-BY' UNION ALL
  SELECT '2/1/2019', 'bg-BG' UNION ALL
  SELECT '5/3/2019', 'bn-BD' UNION ALL
  SELECT '5/2/2019', 'bn-IN' UNION ALL
  SELECT '3/19/2019', 'bo-CN' UNION ALL
  SELECT '1/19/2019', 'br-FR' 
)
SELECT 
  FORMAT_DATE('%b %Y', PARSE_DATE('%m/%d/%Y', dt)) month_year, 
  REGEXP_EXTRACT(code, r'(.*?)-') code, 
  COUNT(1) cnt
FROM `project.dataset.date_locales`
GROUP BY month_year, code   

with result as

Row month_year  code    cnt  
1   Jan 2019    ar      2    
2   Mar 2019    ar      5    
3   Mar 2019    af      1    
4   Feb 2019    az      1    
5   Mar 2019    am      1    
6   Apr 2019    as      1    
7   May 2019    ar      6    
8   Mar 2019    ba      1    
9   May 2019    bn      2    
10  Feb 2019    bg      1    
11  Mar 2019    arn     1    
12  Mar 2019    bo      1    
13  Mar 2019    az      1    
14  Jan 2019    br      1    
15  Apr 2019    ar      3    
16  Jan 2019    be      1