Сегодня должен был выйти урок про разведку данных. Но проверяя домашку, я решил подробнее рассказать, что делать, если не получается решить задания.
Есть два основных затыка: вы не знаете, как подступиться к задаче и ваши запросы к базе данных не работают. Объясняю, как с этим справиться.
Непонятно, с чего начать
Разберём задачу из домашнего задания:
Выгрузите имена, фамилии и адреса электронной почты пяти пользователей, сделавших больше всего заказов.
Есть две таблицы: с данными о пользователях (users) и с данными о заказах (orders). О каждом пользователе мы знаем имя, фамилию, почту, имейл и страну. В таблице с заказами есть номер покупателя, дата и сумма покупки.
Прежде, чем писать запрос, я советую своими словами описывать примерный вариант решения.
Например:
1. Чтобы получить список самых активных покупателей, нужно посчитать, сколько заказов сделал каждый пользователь.
2. Затем нужно отфильтровать 5 самых активных.
3. Для каждого из выбранных пользователей нужно выгрузить имя, фамилию и имейл.
Теперь я пишу запрос для решения первой подзадачи. Для решения хватит данных таблицы с заказами (orders). В таблице есть номер покупателя — значит, можно посчитать, сколько заказов совершил каждый покупатель. Вспоминайте третий урок о простых запросах.
Запрос
SELECT
user_id,
count(*) AS orders
FROM
[analytics-one:Examples.orders] orders
GROUP BY
user_id
По-русски
ДОСТАНЬ
номер пользователя, количество заказов
ИЗ ТАБЛИЦЫ
заказы
СГРУППИРУЙ ПО
номеру пользователя
Следующий шаг — найти 5 самых активных покупателей. Для этого не нужно писать новый запрос. Достаточно отсортировать данные и вывести первые 5 строк.
Запрос
SELECT
user_id,
count(*) as orders
FROM
[analytics-one:Examples.orders] orders
GROUP BY
user_id
ORDER BY
orders DESC
LIMIT 5
По-русски
ДОСТАНЬ
номер пользователя, количество заказов
ИЗ ТАБЛИЦЫ
заказы
СГРУППИРУЙ ПО
номеру пользователя
ОТСОРТИРУЙ ПО
Количеству заказов
ЛИМИТ 5 записей
Мы получили список из 5 самых активных покупателей. Пока без имён и имейлов. Чтобы их добавить, объединим получившуюся таблицу с таблицей «пользователи» (users) по номеру пользователя. Подробнее об этом — в четвёртом урок о внутренних джоинах.
Запрос
SELECT
first_name,
last_name,
email,
count(*) as orders
FROM
orders
INNER JOIN users
ON orders.user_id = users.id
GROUP BY
first_name,
last_name,
email
ORDER BY
orders DESC
LIMIT 5
По-русски
ДОСТАНЬ
имя, фамилию, имейл и количество заказов
ИЗ ТАБЛИЦ
заказы и пользователи, объединенных через ВНУТРЕННИЙ ДЖОИН по полю «номер пользователя»
СГРУППИРУЙ ПО
имени, фамилии и имейлу
ОТСОРТИРУЙ ПО
Количеству заказов
ЛИМИТ 5 записей
Готово.
Ещё пример, чуть усложнённая версия второго задания.Посчитаем средний чек в Швеции и Индонезии в 2015 году.
Сначала опишем вариант решения:
1. Выберем всех пользователей из Швеции и Индонезии.
2. Посчитаем выручку и количество заказов от этих пользователей в 2015 году.
3. Посчитаем средний чек: поделим выручку на количество заказов.
Первый шаг. Получим список пользователей.
SELECT
*
FROM
users
WHERE
country IN (‘Sweden’, ‘Indonesia’)
Новыйоператор:
country IN (‘Sweden’, ‘Indonesia’)
Это более компактная версия
country = ‘Sweden’ OR country = ‘Indonesia’
В скобках можно добавлять больше значений.
Второй шаг. Получим заказы этих пользователей, объединив users и orders.
SELECT
*
FROM
users INNER JOIN orders ON users.id = orders.user_id
WHERE
country IN (‘Sweden’, ‘Indonesia’)
Оставим только заказы 2015 года:
SELECT
*
FROM
users
INNER JOIN orders
ON users.id = orders.user_id
WHERE
users.country IN (‘Sweden’, ‘Indonesia’)
AND YEAR(orders.order_date) = 2015
Третий шаг. Посчитаем общую выручку, количество заказов и средний чек.
SELECT
users.country,
sum(order_sum) as revenue,
count(*) as orders,
sum(order_sum)/count(*) as avg_check
FROM
users INNER JOIN orders ON users.id = orders.user_id
WHERE
country IN (‘Sweden’, ‘Indonesia’)
AND YEAR(orders.order_date) = 2015
GROUP BY
users.country
Запомнить:
1. Своими словам опишите, как решить задачу.
2. Разбейте задачу на небольшие подзадачи.
3. Напишите простой запрос для первой подзадачи.
4. Дополняйте запрос, пока не получите нужный результат.
Запрос не работает — выдаёт ошибку
Бигквери — как тётка на почте. Если вы хотите, чтобы она что-то сделала, извольте заполнить бланк по форме. Если бланк заполнен неправильно, тётка вас отправит исправлять. В Бигквери запрос тоже должен быть написан без ошибок. Но Бигквери подскажет, где и что нужно поправить. Ну и на обед не закроется в самый неподходящий момент.
Самые частые ошибки в запросах — грамматические и синтаксические. Где-то столбец не так назвали, где-то запятую пропустили. Часто встречаются ошибки в структуре запроса, когда вы написали разделы в неправильном порядке.
Например, запрос:
SELECT
regitration_date,
country
FROM
[analytics-one:Examples.e1_users]
WHERE
YEAR(registration_date) = 2015
LIMIT 10
Сходу незаметно, но во второй строке в полеregitration_date пропущена буква s. Бигквери попытается найти в таблице столбец regitration_date. У него не получится, и он скажет: Error: Field ‘regitration_date’ not found in table ‘analytics-one:Examples.e1_users’; did you mean ‘registration_date’?
Это значит: «Я не нашёл столбец ‘regitration_date’. Может вы имели в виду ‘registration_date’?»
Скопируйте и вставьте исправленное название столбца — и запрос заработает.
Другой пример. Вы пытаетесь вывести на экран несколько заказов:
SELECT
id as order_id
order_sum as revenue
FROM
[analytics-one:Examples.e1_orders]
WHERE
YEAR(registration_date) = 2015
LIMIT 10
Запускаете запрос, Бигквери выдаст ошибку: Error:Encountered » <ID> «order_sum» at line 3, column 3. Was expecting: <EOF>.
Расшифровывается как: «неожиданно в третьей колонке третьей строки я увидел order_sum» (третья колонка потому что первые две — отступ). Значит, ошибка где-то до третьей строки. Посмотрите внимательно — там пропущена запятая между id as order_id и order_sum as revenue.
Такую же ошибку вы получите, если укажете разделы запроса не в том порядке.
Например:
SELECT
id as order_id,
sum(order_sum) as revenue
FROM
[analytics-one:Examples.e1_orders]
WHERE
YEAR(registration_date) = 2015
ORDER BY
id
GROUP BY
id
Ошибка: Error: Encountered «GROUP» «GROUP» at line 10, column 1. Was expecting: <EOF>.
Бигквери подсказывает, что в десятой строке что-то не то (почему он пишет GROUP два раза — загадка). Раздел ORDER BY должен стоять после GROUP BY.
Посмотрите ещё
У Бигквери есть хорошая англоязычная справка.
Например, статья, как начать использовать Бигквери. В ней проходится путь от создания проекта и написания первого запроса до добавления собственных данных.
Есть хорошая инструкция по языку запросов. Там рассказывается о правильной структуре и описывается каждая функция.
А ещё у Бигквери есть сообщество на популярном форуме для разработчиков Стек Оверфлоу. Если столкнулись с проблемой, попробуйте поискать там, решение наверняка есть.
Попробуйте сами
Пока я заканчиваю писать следующий урок, вот шесть задач на закрепление материала. Работаем с теми же таблицами, что и в предыдущем задании: users, orders.
1. Сколько в базе пользователей по имени Тереза? (‘Teresa’)
2. Сколько всего заказов сделали пользователи по имени Тереза?
3. Какая из Терез заказала больше всех?
4. В какой стране она живёт и когда зарегистрировалась?
5. Выведите количество заказов, выручку и средний чек страны Терезы за каждый год. Результат отсортируйте в хронологическом порядке.
6. (задача со звездочкой) Посчитайте, сколько пользователей сделали свой первый заказ на следующий день после регистрации. Подсказка: используйте функцию datediff().