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
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.
解决方案
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;