case 日期when 范围,使用CASE WHEN将语言代码整理为一个组合的区域代码，并计算组合的区域代码在某个日期出现的次数...

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;