Агрегации агрегаций не допускаются Bigquery
Я пытаюсь запросить мою таблицу аналитики google bigquery. Поля, которые меня интересуют, являются вложенными. Структура, которую я хочу получить, соответствует: Категория > Подкатегория > Подподкатегория.
Я попытался сделать следующее:
select
event_param1.value.string_value AS category,
event_param2.value.string_value AS action,
ARRAY_AGG(DISTINCT event_param3.value.string_value) AS label
FROM `analytics.events_20*` AS t,
UNNEST(event_params) as event_param1,
UNNEST(event_params) as event_param2,
UNNEST(event_params) as event_param3
where
parse_date('%y%m%d', _table_suffix) between DATE_sub(current_date(), interval 30 day) and DATE_sub(current_date(), interval 1 day) AND
event_param1.key = 'category' and
event_param2.key = 'action' and
event_param3.key = 'label'
group by category, action
order by category, action
Но это возвращает одну строку с одной категорией, одной подкатегорией и массивом всех подподкатегорий.
Я хочу иметь одну строку с одной категорией, всеми подкатегориями, всеми подподкатегориями в каждой подкатегории.
Это пример того, что я получаю:
{
"category": "Apple Watch",
"action": "Apple Badge Clicked",
"label": [
"User Landing Page",
"Attract",
"Guest Landing Page",
"Guest In Workout",
"User In Workout"
]
},
{
"category": "Apple Watch",
"action": "CONNECTED",
"label": [
"User Landing Page",
"Attract",
"Guest Landing Page",
"Guest In Workout",
"User In Workout"
]
}
И это то, что я хочу:
{
"category": "Apple Watch",
"action": {
"Apple Badge Clicked": {
"label": [
"User Landing Page",
"Attract",
"Guest Landing Page",
"Guest In Workout",
"User In Workout"
]
},
"CONNECTED": {
"label": [
"User Landing Page",
"Attract",
"Guest Landing Page",
"Guest In Workout",
"User In Workout"
]
}
}
}
Если я попробую ARRAY_AGG внутри другого ARRAY_AGG, я получу Aggregations of aggregations are not allowed Bigquery.
Я знаю, что то, что я прошу, не так просто, но подобное решение тоже сработает.
Ответы 1
Вам нужно сначала агрегировать в массив на самом высоком уровне. После этого вы можете переупорядочить данные с помощью подзапросов:
Это не точно отражает желаемый результат, но гибок со всеми типами действий:
WITH test AS (
SELECT * FROM UNNEST([
STRUCT('Apple Watch' AS category, 'Apple Badge Clicked' as action, 'User Landing Page' as label),
('Apple Watch','Apple Badge Clicked','Attract'),
('Apple Watch','Apple Badge Clicked','Guest Landing Page'),
('Apple Watch','CONNECTED','User Landing Page'),
('Apple Watch','CONNECTED','Attract'),
('Apple Watch','CONNECTED','User In Workout')
])
),
-- first level of aggregation, prepare for fine tuning
catAgg as (
SELECT
category,
ARRAY_AGG(struct(action, label)) AS catInfo
FROM test
GROUP BY 1
)
SELECT
category,
-- feed sub-query output into an array "action"
array(SELECT AS STRUCT
action as actionType, -- re-group data within the array by field "action"
array_agg(distinct label) as label
FROM UNNEST(catInfo)
GROUP BY 1
) as action
FROM catAgg
надеюсь это поможет
Пробовал для запроса данных за один день. Работал! Но когда я пытаюсь получить данные за последние 30 дней, Big Query жалуется: Resources exceeded during query execution: The query could not be executed in the allotted memory. Peak usage: 119% of limit. Top memory consumer(s): aggregate functions and GROUP BY clauses: 98% other/unattributed: 2%
Хотя я ничего не знаю о BigQuery, вам, похоже, нужно вложить действие и снова вложить этикетка (т. е. трехуровневый запрос) для достижения конечного результата. Попробуйте этот запрос с CTE: pastebin.com/DEacBHtv.