kazminaviktoriya1
Пригласите друга
В профиль
Выход
![](data:image/svg+xml,<svg xmlns="http://www.w3.org/2000/svg" width="42" height="42"><rect fill-opacity="0"/></svg>)
Добавить аккаунт
Чат поддержки
Практикум
Привет! Это поддержка Практикума 👋 Если что-то не получается — постараемся помочь. Пишите в чат или выберите другой способ связипт, 2 декабряв 17:21
Мы уже начали заниматься вашим вопросом, ответим в ближайшее время
Практикум
Здравствуйте!
Поддержка не сможет помочь с вопросами, связанными с теорией или задачам из курса «Системный аналитик» — в этой ситуации рекомендуем вам обратиться к вашему преподавателю в чате в Slack.
Если же у вас возникнут иные вопросы — обязательно пишите, будем рады помочь :)
17:24
Выбрать способ связи
Обзор тренажера
Применение оператора GROUP BY
Группировка помогает анализировать данные. Например, с помощью группировки можно сравнить выручку в разных городах. В SQL данные группируют оператором
GROUP BY — его указывают после условного оператора WHERE. Если WHERE в запросе нет, оператор GROUP BY пишут после FROM.SQL
-- нужно указать поля и необходимые вычисления
SELECT billing_city,
ROUND(AVG(total))
-- не забыть таблицу
FROM invoice
-- здесь указывают поле, в котором группируют данные
GROUP BY billing_city
LIMIT 5; | billing_city | round |
|---|---|
| Porto | 5 |
| Budapest | 7 |
| Reno | 5 |
| London | 5 |
| Paris | 6 |
Не забудьте вывести поле, по которому группируете данные. Иначе таблица получится неинформативной.
SQL
SELECT ROUND(AVG(total))
FROM invoice
GROUP BY billing_city
LIMIT 5; | round |
|---|
| 5 |
| 7 |
| 5 |
| 5 |
| 6 |
Схема базы данных
1.
Группировка помогает сравнивать данные. Но сначала нужно получить срез. Напишите запрос, который выгрузит общую выручку (поле
total) в США (англ. USA). Информацию о стране хранит поле billing_country.Подсказка
Посчитайте сумму продаж с помощью агрегирующей функции и добавьте условие.
2.
Теперь можно проверить, как отличаются данные по городам. Посчитайте общую выручку, количество заказов, среднюю выручку для каждого города США. Нужное поле —
billing_city.Подсказка
Добавьте ключевое слово
billing_city после оператора SELECT и запишите все необходимые агрегирующие функции. Примените группировку и не забудьте вывести поле, по которому группируете данные.3.
Проверьте, какую выручку в среднем приносит каждый покупатель. Выгрузите общую сумму выручки, число уникальных покупателей (поле
customer_id) и среднюю выручку на уникального пользователя для страны США.Подсказка
Функция
AVG тут не поможет, ведь нужно посчитать среднюю выручку на каждого уникального пользователя, а не на всех. Выход — поделить одну агрегацию на другую, вот так: SUM(...)/COUNT(...). Для подсчёта уникальных покупателей используйте DISTINCT.4.
Дополните предыдущий запрос. Сгруппируйте данные по неделе заказа. Для этого нужно усечь дату из поля
invoice_date до недели. Обратите внимание, поле invoice_date хранит данные типа varchar.Подсказка
Чтобы выделить неделю, используйте оператор
DATE_TRUNC. Синтаксис оператора такой: DATE_TRUNC('отрезок времени', поле). Необходимое значение отрезка времени — 'week'. Не забудьте преобразовать поле invoice_date в формат timestamp. Сгруппируйте данные по неделе заказа.5.
Посчитайте, сколько пользователей с почтовыми доменами
yahoo и gmail обслужил каждый сотрудник. В итоговой таблице должны быть два поля:- идентификатор сотрудника,
- количество пользователей.
Подсказка
Нужные электронные адреса содержат подстроки
yahoo или gmail. Используйте оператор LIKE, чтобы найти записи по шаблону.6.
Создайте новое поле с категориями:
- заказы на сумму меньше одного доллара получат категорию
'low cost'; - заказы на сумму от одного доллара и выше получат категорию
'high cost'.
Для каждой категории посчитайте сумму значений в поле
total, но только для тех заказов, при оформлении которых указан почтовый код. В итоговую таблицу должны войти только два поля.Подсказка
Код
xxxxxxxxxx1
SELECT DATE_TRUNC('week', CAST(invoice_date AS timestamp)),-- впишите поле с категориями2
SUM(total),3
COUNT(DISTINCT customer_id),4
SUM(total)/COUNT(DISTINCT customer_id)5
FROM invoice6
WHERE billing_country = 'USA'7
GROUP BY DATE_TRUNC('week', CAST(invoice_date AS timestamp));8
-- сгруппируйте данные;Результат
| date_trunc | sum | count | ?column? |
|---|---|---|---|
| 2009-01-05 00:00:00 | 13.86 | 1 | 13.86 |
| 2009-02-16 00:00:00 | 0.99 | 1 | 0.99 |
| 2009-03-02 00:00:00 | 13.86 | 4 | 3.465 |
| 2009-04-13 00:00:00 | 13.86 | 1 | 13.86 |
| 2009-06-01 00:00:00 | 9.9 | 2 | 4.95 |
| 2009-06-08 00:00:00 | 8.91 | 1 | 8.91 |
| 2009-09-07 00:00:00 | 14.85 | 2 | 7.425 |
| 2009-10-19 00:00:00 | 0.99 | 1 | 0.99 |
| 2009-11-02 00:00:00 | 3.96 | 2 | 1.98 |
| 2009-12-07 00:00:00 | 8.91 | 1 | 8.91 |
| 2009-12-14 00:00:00 | 13.86 | 1 | 13.86 |
| 2010-01-25 00:00:00 | 0.99 | 1 | 0.99 |
| 2010-02-08 00:00:00 | 7.92 | 3 | 2.64 |
| 2010-03-15 00:00:00 | 15.86 | 1 | 15.86 |
| 2010-04-26 00:00:00 | 0.99 | 1 | 0.99 |
| 2010-05-10 00:00:00 | 13.86 | 4 | 3.465 |
| 2010-06-21 00:00:00 | 13.86 | 1 | 13.86 |
| 2010-08-09 00:00:00 | 11.88 | 3 | 3.96 |
| 2010-08-16 00:00:00 | 8.91 | 1 | 8.91 |
| 2010-09-20 00:00:00 | 13.86 | 1 | 13.86 |
| 2010-11-15 00:00:00 | 14.85 | 2 | 7.425 |
| 2010-12-27 00:00:00 | 0.99 | 1 | 0.99 |
| 2011-01-10 00:00:00 | 1.98 | 1 | 1.98 |
| 2011-02-14 00:00:00 | 8.91 | 1 | 8.91 |
| 2011-04-04 00:00:00 | 0.99 | 1 | 0.99 |
| 2011-04-18 00:00:00 | 7.92 | 3 | 2.64 |
| 2011-05-23 00:00:00 | 27.77 | 2 | 13.885 |
| 2011-07-04 00:00:00 | 0.99 | 1 | 0.99 |
| 2011-07-18 00:00:00 | 13.86 | 4 | 3.465 |
| 2011-08-29 00:00:00 | 13.86 | 1 | 13.86 |
| 2011-10-17 00:00:00 | 11.88 | 3 | 3.96 |
| 2011-11-28 00:00:00 | 13.86 | 1 | 13.86 |
| 2012-01-23 00:00:00 | 14.85 | 2 | 7.425 |
| 2012-03-05 00:00:00 | 0.99 | 1 | 0.99 |
| 2012-03-19 00:00:00 | 1.98 | 1 | 1.98 |
| 2012-04-23 00:00:00 | 8.91 | 1 | 8.91 |
| 2012-06-11 00:00:00 | 0.99 | 1 | 0.99 |
| 2012-06-25 00:00:00 | 7.92 | 3 | 2.64 |
| 2012-07-30 00:00:00 | 34.77 | 2 | 17.385 |
| 2012-09-10 00:00:00 | 1.99 | 1 | 1.99 |
| 2012-09-24 00:00:00 | 27.86 | 4 | 6.965 |
| 2012-11-05 00:00:00 | 13.86 | 1 | 13.86 |
| 2012-12-24 00:00:00 | 13.86 | 4 | 3.465 |
| 2013-02-04 00:00:00 | 13.86 | 1 | 13.86 |
| 2013-04-01 00:00:00 | 18.81 | 3 | 6.27 |
| 2013-05-13 00:00:00 | 0.99 | 1 | 0.99 |
| 2013-07-01 00:00:00 | 14.85 | 2 | 7.425 |
| 2013-08-19 00:00:00 | 0.99 | 1 | 0.99 |
| 2013-09-02 00:00:00 | 3.96 | 2 | 1.98 |
| 2013-10-07 00:00:00 | 22.77 | 2 | 11.385 |
| 2013-11-18 00:00:00 | 0.99 | 1 | 0.99 |
| 2013-12-02 00:00:00 | 7.92 | 3 | 2.64 |