Исследование мощного шаблона SQL: ARRAY_AGG, STRUCT и UNNEST

1656640213 issledovanie moshhnogo shablona sql array agg struct i unnest

Лак Лакшмонан

Использовать вложенные поля, а не сводить все данные, может быть очень рентабельным (как с точки зрения хранения, так и с точки зрения времени запроса). Вложенные повторяющиеся поля очень мощны, но SQL, необходимый для запроса к ним, выглядит несколько незнакомым. Следовательно, следует потратить немного времени на STRUCT, UNNEST и ARRAY_AGG. Использование этих трех в сочетании также делает некоторые типы запросов намного, гораздо более легкими для написания.

YBD8Jpszpmuf7POYIgYrbfByuZLwxaia5dfm
Одним из мощных шаблонов SQL является создание массива структур, а затем разложение.

Задача

Давайте возьмем таблицу BigQuery тропических циклонов. Вот предварительный просмотр таблицы:

A3D0RoogzGHZiPHVDiGvz-Rn1boHXqeNFM16
Входная таблица

Задание найти максимум usa_sshs (более известный как «категория»), достигнутый каждым североамериканским ураганом (basin=NA) сезона 2010 года и время, когда эта категория была впервые достигнута. Я хочу иметь возможность сказать что-то вроде «Ураган Даниэль достиг категории 4 в 18:00 UTC 2010-08-27, когда он был на (27,1, -60,1)».

l0f57RzxyBwDffbMQ4tkZZ2RTV5BcaLVaJjy
Ураган «Даниэль» достиг категории 4 в 18:00 UTC 2010–08–27, когда он был (27,1, -60,1)

Вот запрос на решение. В этой статье я буду строить его по частям.

Где ураган?

Моим первым шагом было создать историю мест ураганов. По сути, я хочу достичь:

oTV14rnO5XlFpmHbqTE2TWNgj5jjHnSPmQtB
История каждого урагана

Мы можем фильтровать по бассейну и сезону:

#standardsqlWITH hurricanes AS (SELECT  NAME, iso_time, latitude, longitude, usa_sshsFROM  `bigquery-public-data.noaa_hurricanes.hurricanes`WHERE  season = '2010' AND basin = 'NA')SELECT * from hurricanes LIMIT 5

Но это дает нам кучу строк, которые отвечают необходимым критериям. Нам нужно получить упорядоченный список мест для каждого урагана. Просто добавив a GROUP BY на вышеприведенный запрос не будет работать. (Почему бы и нет? Попробуйте!)

Однако этот запрос работает:

#standardsqlWITH hurricanes AS (SELECT  MIN(NAME) AS name,  ARRAY_AGG(STRUCT(iso_time, latitude, longitude, usa_sshs) ORDER BY iso_time ASC) AS trackFROM  `bigquery-public-data.noaa_hurricanes.hurricanes`WHERE  season = '2010' AND basin = 'NA'GROUP BY  sid)
SELECT * from hurricanes LIMIT 5

Давайте разберем запрос на части:

  1. Мы группируем по идентификатору шторма, но когда мы группируем, мы получаем кучу строк. Часто мы делаем агрегацию, например SUM() или AVG() строк в группе, чтобы уменьшить только одно значение на строку набора результатов.
  2. Чтобы сохранить все строки в группе, используйте ARRAY_AGG(). В этом массиве нам нужно не только одно поле, а четыре. Итак, я делаю четыре поля (время, широта, долгота, сила урагана) по структуре. Структура позволяет мне сохранить поэлементную связь между этими четырьмя столбцами.
  3. Упорядочить массив по времени.

Максимальная категория

Теперь, когда у нас есть история каждого урагана, давайте узнаем, какой максимальной категории достигает ураган. Мы хотим:

bdI7YqSjFVRpVU0blYugjN6jySidLTdVwmfp
Максимальная категория, достигнутая ураганом

Вот дополнительное WITH:

WITH hurricanes AS (  ...),
cat_hurricane AS (SELECT name,track, (SELECT MAX(usa_sshs) FROM UNNEST(track))  AS categoryfrom hurricanesORDER BY category DESC)
SELECT * from cat_hurricane

Выбор названия из таблицы ураганов вполне очевиден. Это просто колонка. Но что значит выбор track делать? Поскольку track является массивом, вы получите весь массив.

Чтобы получить одну строчку из массива дорожек, нам нужно пройти UNNEST(). Когда звонишь UNNEST(track)это создает таблицу, следовательно UNNEST() можно использовать только в FROM точку BigQuery. Как только вы это поймете UNNEST(track) создает таблицу с четырьмя столбцами (четыре столбца в STRUCT), вы это видите MAX(usa_sshs) просто вычисляет максимальную силу каждого урагана.

Время достижения максимальной категории

Как найти время достижения максимальной категории? По сути, найдите все строчки в UNNEST(track) стол, для которого usa_sshs столбец является максимальной категорией и ограничьте ее до 1, чтобы получить первую строку, в которой встречается категория:

SELECT   name,   category,   (SELECT AS STRUCT iso_time, latitude, longitude   FROM UNNEST(track)    WHERE usa_sshs = category ORDER BY iso_time LIMIT 1).*FROM cat_hurricaneORDER BY category DESC, name ASC

Вот полный запрос. Поиграйте с некоторыми вариантами, чтобы понять, что происходит:

  1. Почему я имею .*? Поиграйте с запросом, чтобы увидеть, что произойдет, если я не включаю .*? (Подсказка: это связано с названием столбца).
  2. Что произойдет, если я не сделаю AS STRUCT выше?
  3. Что произойдет, если я не сделаю LIMIT 1?

Добавить комментарий

Ваш адрес email не будет опубликован.