Разбор полетов. Что делать, если ничего не получается

Сегодня должен был выйти урок про разведку данных. Но проверяя домашку, я решил подробнее рассказать, что делать, если не получается решить задания.

Есть два основных затыка: вы не знаете, как подступиться к задаче и ваши запросы к базе данных не работают. Объясняю, как с этим справиться.

 

Непонятно, с чего начать

 

Разберём задачу из домашнего задания:

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

Есть две таблицы: с данными о пользователях (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().