Сопоставьте коды языков в один комбинированный код локали, используя CASE WHEN, и подсчитайте, сколько раз комбинированный код локали встречается в день
Узнав о CASE WHEN, я столкнулся с вариантом использования, когда я вижу несколько кодов локали в Analytics. Это гораздо более простой вопрос, на который гораздо легче ответить и прочитать, чем мой предыдущий вопрос, который был опубликован.
Примеры: en-us (английский США) en-au (английская Австралия) en-br (английский бразильский) es-es (испанский испания) es-419 (испанский-латам) pt-br (португальский, бразильский) pt-pt (португалия)
Как мне агрегировать их в BigQuery, чтобы вместо подсчета различных значений я мог подсчитывать количество раз, когда встречаются только первые два символа локали?
Вторая часть этого вопроса: как мне структурировать мою таблицу таким образом, чтобы я мог отображать эти подсчеты по дате?
В настоящее время выход: дата: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 с течением времени:
Jan AF 3
JAN EN 5
FEB AF 5
FEB EN 6
MAR EN 2
MAR EN 3но я получаю сообщение об ошибке, в котором говорится: Выражение списка SELECT ссылается на дату столбца, которая не сгруппирована и не агрегирована (строка: 1, столбец: 8).
Я считаю, что мне нужно сначала объединить даты в месяцы? У меня сложилось впечатление, что интеграция BigQuery с DataStudio автоматически агрегирует столбец даты.
Ответы 2
Вы просто ищете запрос агрегации?
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;
Ниже приведен стандартный SQL BigQuery и ответы на оба вопроса в вашем вопросе.
#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
Вы можете протестировать, поиграть с приведенным выше, используя некоторые фиктивные данные, как в приведенном ниже примере.
#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
с результатом как
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
Ах, похоже, я просто не знал, куда поместить COUNT(*) Спасибо за помощь!