Почніть запитувати дані за допомогою цієї простої мови запитів

Робота з даними стає все більш важливою навичкою на сучасному робочому місці.

Дані більше не належать до сфери аналітиків та інженерів програмного забезпечення. Завдяки сучасним технологіям кожен може працювати з даними для аналізу тенденцій та інформування про прийняття рішень.

Основним поняттям під час роботи з даними є "запитування" набору даних. Це буквально задавати питання щодо набору даних. Мова запитів - це мова програмного забезпечення, яка забезпечує синтаксис для задання таких питань.

Якщо у вас немає досвіду написання запитів, вони можуть здатися трохи лякаючими. Однак, трохи попрактикувавшись, ви зможете освоїти ази.

Ось як ви можете розпочати роботу в Google Таблицях.

Мова запитів API візуалізації Google

Можливо, ви вже використовуєте Google Таблиці більшу частину своєї повсякденної роботи. Можливо, ви знайомі з його використанням для створення діаграм та графіків.

Мова запитів API візуалізації Google - це магія, яка працює за кадром, щоб зробити це можливим.

Але чи знаєте ви, що можете отримати доступ до цієї мови за допомогою QUERY()функції? Це може бути потужним інструментом для роботи з великими аркушами даних.

Існує багато подібностей між мовою запитів та SQL.

В обох випадках ви визначаєте набір даних стовпців і рядків і вибираєте різні стовпці та рядки, вказуючи різні критерії та умови.

У цій статті приклади даних надходитимуть із великого файлу CSV, що містить результати міжнародних футбольних змагань між 1872 і 2019 рр. Ви можете завантажити дані з Kaggle.

У новій таблиці Google завантажте файл CSV. Ви можете вибрати всі дані за допомогою Ctrl + A (або Cmd + A на Mac).

На стрічці меню виберіть Дані> Іменовані діапазони ... і назвіть вибраний діапазон приблизно на зразок «дані». Це полегшить роботу.

Тепер ви готові розпочати запит даних. Створіть нову вкладку в електронній таблиці, а в комірці А1 створіть нову QUERY()формулу.

Отримати всі матчі Англії

Цей перший запит знаходить усі рядки набору даних, де Англія є або господарем команди, або командою гостей.

QUERY()Формула приймає принаймні два аргументи. Перший - це іменований діапазон, який буде запитом набору даних. Другий - це рядок, що містить фактичний запит.

=QUERY(data, "SELECT * WHERE B = 'England' OR C = 'England'")

Давайте розберемо це.

SELECT *просить повернути всі стовпці набору даних. Якби ви хотіли лише стовпці A, B і C, ви б писали SELECT A, B, C.

Далі ви включаєте фільтр, щоб знайти лише ті рядки, де стовпець B або стовпець C містять команду 'England'. Обов’язково використовуйте одинарні лапки для рядків усередині запиту. Подвійні лапки використовуються для відкриття та закриття самого запиту.

Ця формула повертає всі рядки, де грала Англія. Якщо ви хочете шукати іншу команду, просто змініть умову у фільтрі.

Підрахувати всі товариські матчі

Далі, давайте підрахуємо, скільки товариських матчів є в наборі даних.

=QUERY(data, "SELECT COUNT(A) WHERE F = 'Friendly'")

Це використовує функцію мови запитів COUNT(). Це приклад сукупної функції. Сукупні функції узагальнюють багато рядків в один.

Наприклад, у цьому наборі даних є 16 716 рядків, де стовпець F дорівнює 'Friendly'. Замість того, щоб повернути всі ці рядки, запит повертає один рядок, який натомість їх підраховує.

Інші приклади агрегованих функцій включають MAX(), MIN()і AVG(). Замість того, щоб повернути всі рядки, що відповідають запиту, він замість цього знаходить їх максимальне, мінімальне та середнє значення.

Групування за турніром

Сукупні функції можуть зробити більше, якщо ви використовуєте GROUP BYоператор поряд з ними. Цей запит з’ясовує, скільки матчів було зіграно кожним типом турніру.

=QUERY(data, "SELECT F, COUNT(A) GROUP BY F")

Цей запит групує дані, встановлені кожним із значень у стовпці F. Потім підраховує, скільки рядків є в кожній групі.

Ви можете використовувати GROUP BYкілька колон. Наприклад, щоб дізнатись, скільки матчів було зіграно в кожній країні за турніром, використовуйте запит нижче:

=QUERY(data, "SELECT H, F, COUNT(A) GROUP BY H, F")

Спробуємо трохи вдосконаленішу фільтрацію.

Отримати всі матчі Англія - ​​Німеччина

Ви можете вказати більш складну логіку фільтру, використовуючи ключові слова ANDта OR. Для читабельності може допомогти використання дужок навколо кожної частини фільтра.

Наприклад, щоб знайти всі збіги між Англією та Німеччиною:

=QUERY(data, "SELECT * WHERE (B = 'England' AND C = 'Germany') OR (C = 'England' AND B ='Germany')")

Цей фільтр має два критерії - один, де господарями є Англія, а Німеччини немає, а інший - навпаки.

Використання перевірки даних полегшує вибір будь-яких двох команд у наборі даних.

Потім ви можете написати запит, який використовує значення різних комірок у своєму фільтрі. Не забувайте використовувати одинарні лапки для ідентифікації рядків у запиті, а подвійні лапки для відкриття та закриття різних частин запиту.

=QUERY(data, "SELECT * WHERE (B = '"&B1&"' AND C = '"&B2&"') OR (C = '"&B1&"' AND B ='"&B2&"')")

Шукаємо тенденції

Сукупні функції та фільтри роблять потужні інструменти, коли їх використовують у комбінації. Ознайомившись із тим, як вони працюють, ви можете розпочати пошук усіх видів цікавих тенденцій у своєму наборі даних.

Наприклад, у наведеному нижче запиті знайдено середні цілі за гру за кожен рік, починаючи з 1900 року.

=QUERY(data, "SELECT YEAR(A), AVG(D) + AVG(E) WHERE YEAR(A) >= 1900 GROUP BY YEAR(A)")

Якщо ви побудували результат запиту як лінійний графік, ви можете відразу ж побачити тенденції з часом.

Впорядкування результатів

Іноді вам нецікаво знайти всі відповідні рядки в наборі даних. Часто вам захочеться відсортувати їх за якимись критеріями. Можливо, ви хочете знайти лише десять найкращих записів.

Цей запит знаходить десятку найкращих збігів у наборі даних.

=QUERY(data, "SELECT * ORDER BY (D+E) DESC LIMIT 10")

Зверніть увагу на ORDER BYтвердження. Це сортує рядки відповідно до вказаних стовпців. Тут запит сортує результати за кількістю забитих голів у грі.

DESCКлючове слово вказує на сортування в порядку убування ( ASCключове слово буде відсортували їх в порядку зростання).

Нарешті, LIMITключове слово обмежує вихідні дані заданою кількістю рядків (у даному випадку десятьма).

Схоже, в Океанії було кілька досить однобічних ігор!

У яких містах було проведено найбільше матчів чемпіонату світу?

А тепер останній приклад, щоб об’єднати все і розгорнути свою уяву.

За цим запитом ви знайдете десятку міст, які приймали найбільшу кількість матчів чемпіонату світу з футболу.

=QUERY(data, "SELECT G, COUNT(A) WHERE F = 'FIFA World Cup' GROUP BY G ORDER BY COUNT(A) DESC LIMIT 10")

Тепер твоя черга

Сподіваємось, ця стаття виявилася вам корисною. Якщо вам приємна логіка в кожному прикладі, тоді ви готові випробувати справжній SQL.

Це введе такі поняття, як JOINS, вкладені запити та функції WINDOW. Коли ви освоїте їх, ваша сила маніпулювання даними пройде через дах.

Існує кілька моментів, з яких слід розпочати вивчення SQL. Спробуйте інтерактивні приклади на w3schools!