使用CASE WHEN将语言代码整理为一个组合的区域代码，并计算组合的区域代码在某个日期出现的次数 [英] Collate language codes into one combined locale code using CASE WHEN, and count the number of times the combined locale code occurs on a date

问题描述

了解CASE WHEN，并且在Google Analytics(分析)中看到多个语言环境代码时遇到了一个用例.与我之前发布的问题相比，这是一个更简单的问题，更易于回答和阅读.

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.

示例是: en-us(英语) en-au(英语:澳大利亚) en-br(英语:巴西) es-es(西班牙) es-419(西班牙语-拉丁美洲) pt-br(葡萄牙巴西) pt-pt(葡萄牙语)

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)

如何在BigQuery中汇总这些内容，以便我可以计算仅发现语言环境的前两个字符的次数，而不是计算不同的值?

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?

当前，输出为: 日期:language_code:CombinedLocale

Currently, the output is: date:language_code:CombinedLocale

链接到示例数据表: https://docs.google .com/spreadsheets/d/1XZp1nhNZySWI39kKhb3ydYYIImmrfAMcGJDS6ASThqg/edit?usp = sharing

我尝试过:

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

并且:

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

这是我的工作代码:

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

我希望结果随时间推移显示CombinedLocale表的计数，如下所示:

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

但是我收到一条错误消息，指出: SELECT列表表达式引用既不分组也不汇总的列日期(第1行，第8列)

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

我认为我首先需要将日期汇总到月"中?我的印象是BigQuery与DataStudio集成将自动聚合日期列.

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;

这篇关于使用CASE WHEN将语言代码整理为一个组合的区域代码，并计算组合的区域代码在某个日期出现的次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！