googlesheetsquery
1.0.0
Язык запросов Google — это быстрый и мощный способ анализа данных в таблицах Google, который заимствует многие шаблоны, используемые в SQL. В этом руководстве вы познакомитесь с основами использования запросов для ответа на конкретные вопросы из образца набора данных.
Сначала нам нужны образцы данных, с которыми можно поиграть. Мы воспользуемся функцией импорта, чтобы получить таблицу данных о населении мира из Википедии:
=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_countries_by_population_(United_Nations)", "table", 3)Эта функция находит третью таблицу по указанному выше URL-адресу, очищает данные из каждой ячейки и импортирует их в Google Sheet. Прохладный! Но давайте сделаем еще одну вещь, чтобы упростить запрос этих данных.
Щелкните любую ячейку с данными, чтобы выделить ее, затем нажмите CMD + A, чтобы выделить всю таблицу. Выделив всю таблицу, нажмите «Данные» > «Именованные диапазоны», чтобы открыть панель «Именованные диапазоны» — она появится справа.
Именованный диапазон — это псевдоним, который можно дать группе ячеек. Давайте назовем наши страны.
Большой! Теперь мы готовы начать запрос!
Выделите ячейку J1 — мы будем использовать эту ячейку для ввода всех запросов ниже:
Покажите мою копию всего набора данных:
=QUERY(countries, "select *",1)
Покажите мне только столбцы «Страны» и «Регион»:
=QUERY(countries, "select B, C",1)
Предложение WHERE — отличный способ начать задавать более конкретные вопросы по вашему набору данных.
Покажите мне страны с населением, превышающим или равным 100 миллионам:
=QUERY(countries, "SELECT B, E where E >= 100000000")
Покажите мне страны с буквой «es» в названии:
=QUERY(countries, "SELECT B where B contains 'es'")
Покажите мне страны, в названии которых нет буквы «es»:
=QUERY(countries, "SELECT B where not B contains 'es'")
Покажите мне страны, названия которых начинаются на букву «S»:
=QUERY(countries, "SELECT B where B STARTS WITH 'S'")
Покажите мне страны, которые заканчиваются на «s» (не забудьте учитывать регистр:
=QUERY(countries, "SELECT B where B ENDS WITH 's'")
Покажите мне страны, в которых есть слово United:
=QUERY(countries, "SELECT B where B CONTAINS 'United'")
Давайте еще более конкретно добавим в предложение AND!
Покажите мне все страны Америки с населением менее 100 000 человек:
=QUERY(countries, "select B, F WHERE C = 'Americas' AND F <= 100000", 1)
Предложения AND можно объединять до бесконечности.
Этот запрос возвращает все страны Америки с населением менее 100 000 и более 10 000 человек:
=QUERY(countries, "select B, F WHERE C = 'Americas' AND F <= 100000 AND F >= 10000", 1)
Возможно, первое, что вы захотите сделать при изучении набора данных, — это увидеть несколько простых агрегатов: максимальные, минимальные и средние значения в определенных полях. Давайте найдем максимальную, минимальную и среднюю численность населения в 2017 году для стран в нашем наборе данных:
=QUERY(countries, "select max(F), min(F), avg(F)", 1)
Это круто,
С небольшой модификацией мы можем увидеть максимальную, минимальную и среднюю численность населения каждого континента:
=QUERY(countries, "select C, max(F), min (F), avg(F) GROUP by C", 1)
«Счет» и «Группировка» вместе — еще один отличный способ изучить набор данных. В совокупности они позволяют подсчитывать элементы в одном или нескольких столбцах, а также просматривать и агрегировать данные.
Давайте воспользуемся этими двумя предложениями, чтобы увидеть, сколько стран в нашем списке находится в каждом регионе:
=QUERY(countries, "SELECT C, count(B) GROUP by C", 1)
Давайте добавим предложение ORDER BY, чтобы показать нам список всех регионов, упорядоченных по возрастанию от самого высокого к самому низкому:
=QUERY(countries, "SELECT C, count(B) GROUP by C ORDER by C asc", 1)
Предложение «PIVOT» объединяет значения в одну строку.
Например, если мы хотим показать сумму всего населения 2017 года по регионам, мы можем использовать:
=QUERY(countries, "SELECT sum(F) pivot C", 1)
Вы также можете анализировать свои данные, выполняя математические операции в рамках своих запросов.
Давайте теперь покажем список стран с их населением в 2017 году в процентах от общей численности населения мира:
=QUERY(countries, "select B, C, (F / 7550262101) * 100", 1)
Наши процентные данные выглядят круто, но заголовок столбца очень читаем. Мы могли бы щелкнуть ячейку и переименовать ее, но мы также можем задать имя как часть запроса, добавив параметр «метка»:
=QUERY(countries, "select B, C, (F / 7550262101) * 100 Label (F / 7550262101) * 100 'Percentage'", 1)
Документация по языку запросов Google: https://developers.google.com/chart/interactive/docs/querylanguage.
Обзор функции запроса Google Sheets от Coding Is For Losers: https://codingisforlosers.com/google-sheets-query-function/#why
