Группируем данные

Представим, что есть такая таблица:

id	Name	genre	author	date_pub	pages	price	rating
1	Капитанская дочка	5	Александр Пушкин	1836-01-01	130	150	4.7
2	Отцы и дети	1	Иван Тургенев	1861-01-01	240	207	4.7
3	Вишнёвый сад	7	Антон Чехов	1903-01-01	60	138	4.4
4	Война и мир	1	Лев Толстой	1869-01-01	1274
5	Анна Каренина	1	Лев Толстой	1878-01-01	1000	126	4.6
6	Цветы для Элджернона	4	Дэниел Киз	1959-01-01	250	280	4.7
7	Похороните меня за плинтусом...	5	Павел Санаев	1994-01-01	184	198	4.1
8	Зелёная миля	9	Стивен Кинг	1996-01-01	420	190	4.8
9	Унесённые ветром	1	Маргарет Митчелл	1936-01-01	624	358
10	Три товарища	1	Эрих Мария Ремарк	1929-01-01	470	196	4.7
11	На Западном фронте без перемен	4	Эрих Мария Ремарк	1945-01-01	200	273	4.6
12	Триумфальная арка	1	Эрих Мария Ремарк	1956-01-01	520	220	4.7
13	Чёрный обелиск	1	Эрих Мария Ремарк	1961-01-01	480	420	4.6
14	Ночь в Лиссабоне	1	Эрих Мария Ремарк	1959-01-01	250	187	4.7
15	Жизнь взаймы	1	Эрих Мария Ремарк	1927-01-01	240	240	4.6
16	Станция на горизонте	1	Эрих Мария Ремарк	1830-01-01	210		4.1
17	Евгений Онегин	1	Александр Пушкин	1820-01-01	736	330	4.7

Так подсчитали количество книг, написанных Ремарком:

Скопировать кодSQL
SELECT 
    COUNT(name) AS cnt
FROM
    books
WHERE
    author = 'Эрих Мария Ремарк';

Скопировать кодSQL
7

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

Команду GROUP BY (англ. «группировать по») применяют, когда данные нужно разделить на группы по значениям полей.

Пример формата запроса с агрегирующей функцией и группировкой:

Скопировать кодSQL
 SELECT 
    поле_1, 
    поле_2, 
    ..., 
    поле_n, 
    АГРЕГИРУЮЩАЯ_ФУНКЦИЯ(поле) AS here_you_are
FROM
    таблица
WHERE -- если необходимо
    условие 
GROUP BY 
    поле_1, 
    поле_2, 
    ..., 
    поле_n;

После команды GROUP BY перечисляют все поля из блока SELECT. Саму агрегирующую функцию включать в блок GROUP BY не нужно — с ней запрос не выполнится.

Вернёмся к примеру. В нашем случае агрегирующая функция — COUNT(). Команда GROUP BY поможет найти количество строк в разрезе авторов:

Скопировать кодSQL
SELECT 
    author, 
    COUNT(name) AS cnt
FROM
    books
GROUP BY 
    author;

author	cnt
Александр Пушкин	2
Антон Чехов	1
Дэниел Киз	1
Иван Тургенев	1
Лев Толстой	2
Маргарет Митчелл	1
Павел Санаев	1
Стивен Кинг	1
Эрих Мария Ремарк	7

Теперь мы знаем, сколько книг написал каждый из авторов таблицы.

Подсчитаем количество книг в разрезе авторов и рейтинга:

Скопировать кодSQL
SELECT 
    author, 
    rating, 
    COUNT(name) AS cnt
FROM
    books
GROUP BY 
    author, 
    rating;

author	rating	cnt
Лев Толстой		1
Маргарет Митчелл		1
Павел Санаев	4.1	1
Эрих Мария Ремарк	4.1	1
Антон Чехов	4.4	1
Лев Толстой	4.6	1
Эрих Мария Ремарк	4.6	3
Александр Пушкин	4.7	2
Дэниел Киз	4.7	2
Иван Тургенев	4.7	1
Эрих Мария Ремарк	4.7	3
Стивен Кинг	4.8	1

Эта конструкция вернула таблицу с количеством книг и их рейтингом определённого автора. Например, Эрих Мария Ремарк написал три книги с рейтингом 4.6, одну — с оценкой 4.1 и три с 4.7. А у Льва Толстого одно произведение с 4.6 баллами рейтинга и одно — вовсе без оценки.

Конструкция GROUP BY работает для всех агрегирующих функций: COUNT(), AVG(), SUM(), MAX(), MIN(). Можно вызывать несколько функций сразу. Например, для каждого автора найдём среднее количество страниц в его произведениях и максимальное количество страниц:

Скопировать кодSQL
SELECT 
    author, 
    AVG(pages) AS avg_pages,
    MAX(pages) AS max_pages
FROM
    books
GROUP BY 
    author;

author	avg_pages	max_pages
Александр Пушкин	433	736
Антон Чехов	60	60
Дэниел Киз	250	250
Иван Тургенев	240	240
Лев Толстой	1137	1274
Маргарет Митчелл	624	624
Павел Санаев	184	184
Стивен Кинг	420	420
Эрих Мария Ремарк	338,571428571429	520

Задача 1 / 4

Напишите запрос, который посчитает общее и количество уникальных продуктов в каждом магазине (name_store), информация о котором есть в таблице products_data_all. Назовите переменные name_cnt, name_uniq_cnt соответственно. Выведите название магазина, общее количество продуктов, количество уникальных продуктов.

Подсказка

Примените GROUP BY к полю name_store.

Напишите запрос, который подсчитает значение максимального веса в каждой категории (category). Назовите переменную max_weight. Выведите категорию и максимальный вес.

Подсказка

Примените GROUP BY к полю category. Внутри агрегирующей функции приведите вес (weight) к типу real.

Напишите запрос, который посчитает среднюю, максимальную и минимальную цены (price) товаров для каждого магазина (name_store) в таблице products_data_all. Назовите переменные соответственно average_price, max_price, min_price.

Выведите название магазина, среднюю, максимальную и минимальную цены.

Подсказка

Примените функции AVG(), MAX(), MIN() к полю price. Сгруппируйте данные по полю name_store.

Напишите запрос, который посчитает разницу между максимальной и минимальной ценой каждого продукта в категории 'масло сливочное и маргарин' на 10 июня 2019 года. Назовите переменную max_min_diff. Переведите строковые значения дат в формат даты.

Выведите название продукта, разницу между максимальной и минимальной ценой.
Напомним названия нужных вам столбцов:
Название продукта — поле name Категория — поле category
Дата — поле date_upd
Цена — поле price.

Подсказка

Примените функции MAX() и MIN() к полю price. Найдите разницу между результатами их вычислений и сохраните её в переменной max_min_diff. Сделайте срез по категории category = 'масло сливочное и маргарин' и по дате date_upd :: date = '2019-06-10'. Сгруппируйте данные по названию продукта (name).

id_product	name	category	units	weight	price	date_upd	id_store	name_store
3	Молоко цельное пастеризованное Ваша Ферма 3,4-6,0%, 1 л	молоко и сливки	л	1	69	2019-06-01 00:00:00	0	Молочные вкусности
5	Молоко пастеризованное Искренне Ваш отборное 3,4-6,0%, 900 мл	молоко и сливки	мл	nan	78	2019-06-01 00:00:00	0	Молочные вкусности
8	Молоко стерилизованное Можайское 3,2%, 450 мл	молоко и сливки	мл	450	76	2019-06-01 00:00:00	0	Молочные вкусности
10	Молоко ультрапастеризованное Простоквашино 2,5%, 950 мл	молоко и сливки	мл	950	84	2019-06-01 00:00:00	0	Молочные вкусности
11	Молоко ультрапастеризованное Домик в деревне 0,5%, 950 г	молоко и сливки	г	950	99	2019-06-01 00:00:00	0	Молочные вкусности
12	Молоко ультрапастеризованное Вкуснотеево 2,5%, 200 г	молоко и сливки	г	200	23	2019-06-01 00:00:00	0	Молочные вкусности
16	Молоко ультрапастеризованное Parmalat 1,8%, 200 г	молоко и сливки	г	200	24	2019-06-01 00:00:00	0	Молочные вкусности
17	Молоко пастеризованное Домик в деревне отборное 3,5-4,5%, 1,4 л	молоко и сливки	л	1.4	139	2019-06-01 00:00:00	0	Молочные вкусности
18	Молоко пастеризованное Домик в деревне 2,5%, 1,4 л	молоко и сливки	л	1.4	129	2019-06-01 00:00:00	0	Молочные вкусности
20	Молоко ультрапастеризованное Авида 1,5%, 1 л	молоко и сливки	л	1	54	2019-06-01 00:00:00	0	Молочные вкусности
21	Молоко цельное Деревенская жизнь Айширское 2,5-4,5%, 0,95 л	молоко и сливки	л	0.95	92	2019-06-01 00:00:00	0	Молочные вкусности
25	Молоко ультрапастеризованное Parmalat Low Lactose низколактозное 1,8%, 200 мл	молоко и сливки	мл	200	29	2019-06-01 00:00:00	0	Молочные вкусности
26	Молоко ультрапастеризованное Parmalat Low Lactose низколактозное 3,5%, 1 л	молоко и сливки	л	1	99	2019-06-01 00:00:00	0	Молочные вкусности
27	Молоко низколактозное Parmalat Low Lactose 0,05%, 1 л	молоко и сливки	л	nan	84	2019-06-01 00:00:00	0	Молочные вкусности
28	Молоко топленое стерилизованное Можайское 6%, 0,45 л	молоко и сливки	л	0.45	89	2019-06-01 00:00:00	0	Молочные вкусности
29	Молоко топленое пастеризованное Домик в деревне 3,2%, 925 мл	молоко и сливки	мл	925	99	2019-06-01 00:00:00	0	Молочные вкусности
31	Молоко топленое Рузское 2,5%, 500 г	молоко и сливки	г	500	72	2019-06-01 00:00:00	0	Молочные вкусности
34	Молоко топленое Вологодское лето 4%, 930 мл	молоко и сливки	мл	930	69	2019-06-01 00:00:00	0	Молочные вкусности
35	Молоко топленое Ваша Ферма, 0,5 л	молоко и сливки	л	0.5	88	2019-06-01 00:00:00	0	Молочные вкусности
38	Молоко пастеризованное Простоквашино отборное 3,4-4,5%, 930 мл	молоко и сливки	мл	930	75	2019-06-01 00:00:00	0	Молочные вкусности
39	Молоко пастеризованное Домик в деревне отборное 3,7-4,5%, 930 мл	молоко и сливки	мл	930	64	2019-06-01 00:00:00	0	Молочные вкусности
41	Молоко цельное пастеризованное Рузское 3,2-4,0%, 1 кг	молоко и сливки	кг	nan	139	2019-06-01 00:00:00	0	Молочные вкусности
46	Молоко топленое Бежин луг 3,2%, 925 г	молоко и сливки	г	nan	74	2019-06-01 00:00:00	0	Молочные вкусности
49	Молоко пастеризованное Вологодское лето отборное 3,4-4,0%, 930 мл	молоко и сливки	мл	930	75	2019-06-01 00:00:00	0	Молочные вкусности
50	Молоко пастеризованное Углече Поле органическое отборное 3,5-5,2%, 1 л	молоко и сливки	л	1	137	2019-06-01 00:00:00	0	Молочные вкусности
58	Молоко ультрапастеризованное Домик в деревне 1,5%, 950 г	молоко и сливки	г	950	82	2019-06-01 00:00:00	0	Молочные вкусности
59	Молоко ультрапастеризованное Домик в деревне 3,2%, 1,45 кг	молоко и сливки	кг	1.45	156	2019-06-01 00:00:00	0	Молочные вкусности
60	Молоко стерилизованное Можайское 1,5%, 450 мл	молоко и сливки	мл	450	71	2019-06-01 00:00:00	0	Молочные вкусности
61	Молоко ультрапастеризованное Parmalat 3,5%, 1 л	молоко и сливки	л	1	71	2019-06-01 00:00:00	0	Молочные вкусности
62	Молоко ультрапастеризованное Parmalat 1,8%, 1 л	молоко и сливки	л	nan	59	2019-06-01 00:00:00	0	Молочные вкусности
63	Молоко ультрапастеризованное Parmalat 3,5%, 200 мл	молоко и сливки	мл	200	24	2019-06-01 00:00:00	0	Молочные вкусности
64	Молоко ультрапастеризованное Parmalat dietalat с витаминами 0,5%, 1 л	молоко и сливки	л	1	76	2019-06-01 00:00:00	0	Молочные вкусности
70	Молоко пастеризованное Parmalat отборное 3,4-6%, 1 л	молоко и сливки	л	1	77	2019-06-01 00:00:00	0	Молочные вкусности
73	Молоко ультрапастеризованное Домик в деревне 3,5%, 200 г	молоко и сливки	г	200	32	2019-06-01 00:00:00	0	Молочные вкусности
74	Молоко ультрапастеризованное Простоквашино 1,5%, 950 мл	молоко и сливки	мл	950	94	2019-06-01 00:00:00	0	Молочные вкусности
75	Молоко пастеризованное Простоквашино 2,5%, 930 мл	молоко и сливки	мл	930	84	2019-06-01 00:00:00	0	Молочные вкусности
76	Молоко ультрапастеризованное Домик в деревне 3,2%, 950 г	молоко и сливки	г	950	87	2019-06-01 00:00:00	0	Молочные вкусности
77	Молоко ультрапастеризованное Вологодское 3,2%, 970 мл	молоко и сливки	мл	970	66	2019-06-01 00:00:00	0	Молочные вкусности
78	Молоко ультрапастеризованное Домик в деревне 3,5%, 950 г	молоко и сливки	г	950	89	2019-06-01 00:00:00	0	Молочные вкусности
79	Молоко ультрапастеризованное Домик в деревне 2,5%, 950 г	молоко и сливки	г	nan	61	2019-06-01 00:00:00	0	Молочные вкусности
81	Молоко пастеризованное Домик в деревне 2,5%, 930 мл	молоко и сливки	мл	930	89	2019-06-01 00:00:00	0	Молочные вкусности
82	Молоко ультрапастеризованное Вкуснотеево 3,2%, 200 г	молоко и сливки	г	200	25	2019-06-01 00:00:00	0	Молочные вкусности
84	Молоко ультрапастеризованное Домик в деревне 6%, 950 г	молоко и сливки	г	950	128	2019-06-01 00:00:00	0	Молочные вкусности
96	Молоко пастеризованное Простоквашино 1,5%, 930 мл	молоко и сливки	мл	930	76	2019-06-01 00:00:00	0	Молочные вкусности
98	Молоко пастеризованное Вкуснотеево 3,8%, 900 г	молоко и сливки	г	900	66	2019-06-01 00:00:00	0	Молочные вкусности
104	Молоко пастеризованное Авида с крышечкой 3,2%, 1 л	молоко и сливки	л	1	62	2019-06-01 00:00:00	0	Молочные вкусности
105	Молоко ультрапастеризованное Авида 3,2%, 1 л	молоко и сливки	л	1	63	2019-06-01 00:00:00	0	Молочные вкусности
108	Молоко пастеризованное Рузское 2,5%, 1000 г	молоко и сливки	г	1000	144	2019-06-01 00:00:00	0	Молочные вкусности
119	Молоко Бежин луг пастеризованное 3,2%, 900 г	молоко и сливки	г	900	63	2019-06-01 00:00:00	0	Молочные вкусности
120	Масло сливочное Простоквашино 72,5%, 180 г	масло сливочное и маргарин	г	180	119	2019-06-01 00:00:00	0	Молочные вкусности
121	Масло сливочное Вологодское из Вологды 82,5%, 180 г	масло сливочное и маргарин	г	180	188	2019-06-01 00:00:00	0	Молочные вкусности
122	Масло сливочное Крестьянское из Вологды 72,5%, 180 г	масло сливочное и маргарин	г	180	143	2019-06-01 00:00:00	0	Молочные вкусности
125	Масло сливочное Традиционное из Вологды 82,5%, 180 г	масло сливочное и маргарин	г	180	214	2019-06-01 00:00:00	0	Молочные вкусности
126	Масло сливочное традиционное Рузское молоко высший сорт 82,5 %, 175 г	масло сливочное и маргарин	г	175	182	2019-06-01 00:00:00	0	Молочные вкусности
131	Масло сливочное любительское соленое из Вологды 80%, 180 г	масло сливочное и маргарин	г	180	214	2019-06-01 00:00:00	0	Молочные вкусности
133	Масло сливочное Крестьянское Вологодское лето 72,5 %	масло сливочное и маргарин	%	72.5	119	2019-06-01 00:00:00	0	Молочные вкусности
134	Масло сливочное традиционное Вологодское лето 82,5 %, 180 г	масло сливочное и маргарин	г	nan	116	2019-06-01 00:00:00	0	Молочные вкусности
135	Масло сливочное традиционное Вкуснотеево 82,5%, 200 г	масло сливочное и маргарин	г	200	152	2019-06-01 00:00:00	0	Молочные вкусности
143	Масло сливочное Бежин луг 72,5%, 180 г	масло сливочное и маргарин	г	180	114	2019-06-01 00:00:00	0	Молочные вкусности
146	Масло сливочное Тульское Тульский молочный комбинат 72,5 %, 400 г	масло сливочное и маргарин	г	400	212	2019-06-01 00:00:00	0	Молочные вкусности
149	Масло сливочное Традиционное Углече Поле органическое 82,5%, 200 г	масло сливочное и маргарин	г	200	273	2019-06-01 00:00:00	0	Молочные вкусности
163	Масло сливочное Простоквашино 82%, 400 г	масло сливочное и маргарин	г	400	316	2019-06-01 00:00:00	0	Молочные вкусности
166	Масло сливочное Вологодское удолье традиционное высший сорт 82,5%, 180 г	масло сливочное и маргарин	г	180	119	2019-06-01 00:00:00	0	Молочные вкусности
167	Масло сливочное Тульское крестьянское 72,5%, 180 г	масло сливочное и маргарин	г	180	112	2019-06-01 00:00:00	0	Молочные вкусности
170	Масло сливочное из Вологды шоколадное 62 %, 180 г	масло сливочное и маргарин	г	180	179	2019-06-01 00:00:00	0	Молочные вкусности
186	Масло сливочное Домик в деревне натуральное 82,5%, 180 г	масло сливочное и маргарин	г	180	136	2019-06-01 00:00:00	0	Молочные вкусности
10	Молоко ультрапастеризованное Простоквашино 2,5%, 950 мл	молоко и сливки	мл	950	82	2019-06-01 00:00:00	1	Семёрочка
11	Молоко ультрапастеризованное Домик в деревне 0,5%, 950 г	молоко и сливки	г	950	115	2019-06-01 00:00:00	1	Семёрочка
12	Молоко ультрапастеризованное Вкуснотеево 2,5%, 200 г	молоко и сливки	г	200	25	2019-06-01 00:00:00	1	Семёрочка
17	Молоко пастеризованное Домик в деревне отборное 3,5-4,5%, 1,4 л	молоко и сливки	л	1.4	144	2019-06-01 00:00:00	1	Семёрочка
18	Молоко пастеризованное Домик в деревне 2,5%, 1,4 л	молоко и сливки	л	1.4	131	2019-06-01 00:00:00	1	Семёрочка
29	Молоко топленое пастеризованное Домик в деревне 3,2%, 925 мл	молоко и сливки	мл	925	108	2019-06-01 00:00:00	1	Семёрочка
34	Молоко топленое Вологодское лето 4%, 930 мл	молоко и сливки	мл	930	74	2019-06-01 00:00:00	1	Семёрочка
38	Молоко пастеризованное Простоквашино отборное 3,4-4,5%, 930 мл	молоко и сливки	мл	930	89	2019-06-01 00:00:00	1	Семёрочка
39	Молоко пастеризованное Домик в деревне отборное 3,7-4,5%, 930 мл	молоко и сливки	мл	930	60	2019-06-01 00:00:00	1	Семёрочка
42	Молоко цельное пастеризованное Вологжанка отборное 3,4-4,0%, 1000 г	молоко и сливки	г	1000	80	2019-06-01 00:00:00	1	Семёрочка
46	Молоко топленое Бежин луг 3,2%, 925 г	молоко и сливки	г	nan	63	2019-06-01 00:00:00	1	Семёрочка
49	Молоко пастеризованное Вологодское лето отборное 3,4-4,0%, 930 мл	молоко и сливки	мл	930	82	2019-06-01 00:00:00	1	Семёрочка
58	Молоко ультрапастеризованное Домик в деревне 1,5%, 950 г	молоко и сливки	г	950	79	2019-06-01 00:00:00	1	Семёрочка
59	Молоко ультрапастеризованное Домик в деревне 3,2%, 1,45 кг	молоко и сливки	кг	1.45	146	2019-06-01 00:00:00	1	Семёрочка
73	Молоко ультрапастеризованное Домик в деревне 3,5%, 200 г	молоко и сливки	г	200	37	2019-06-01 00:00:00	1	Семёрочка
74	Молоко ультрапастеризованное Простоквашино 1,5%, 950 мл	молоко и сливки	мл	950	100	2019-06-01 00:00:00	1	Семёрочка
75	Молоко пастеризованное Простоквашино 2,5%, 930 мл	молоко и сливки	мл	930	92	2019-06-01 00:00:00	1	Семёрочка
76	Молоко ультрапастеризованное Домик в деревне 3,2%, 950 г	молоко и сливки	г	950	87	2019-06-01 00:00:00	1	Семёрочка
77	Молоко ультрапастеризованное Вологодское 3,2%, 970 мл	молоко и сливки	мл	970	71	2019-06-01 00:00:00	1	Семёрочка
78	Молоко ультрапастеризованное Домик в деревне 3,5%, 950 г	молоко и сливки	г	950	95	2019-06-01 00:00:00	1	Семёрочка
79	Молоко ультрапастеризованное Домик в деревне 2,5%, 950 г	молоко и сливки	г	nan	58	2019-06-01 00:00:00	1	Семёрочка
80	Молоко ультрапастеризованное Брест-Литовск 3,6%, 1 л	молоко и сливки	л	1	99	2019-06-01 00:00:00	1	Семёрочка
81	Молоко пастеризованное Домик в деревне 2,5%, 930 мл	молоко и сливки	мл	930	101	2019-06-01 00:00:00	1	Семёрочка
82	Молоко ультрапастеризованное Вкуснотеево 3,2%, 200 г	молоко и сливки	г	200	22	2019-06-01 00:00:00	1	Семёрочка
83	Молоко пастеризованное Вологжанка 2,5%, 500 г	молоко и сливки	г	500	41	2019-06-01 00:00:00	1	Семёрочка
84	Молоко ультрапастеризованное Домик в деревне 6%, 950 г	молоко и сливки	г	950	125	2019-06-01 00:00:00	1	Семёрочка
96	Молоко пастеризованное Простоквашино 1,5%, 930 мл	молоко и сливки	мл	930	75	2019-06-01 00:00:00	1	Семёрочка
98	Молоко пастеризованное Вкуснотеево 3,8%, 900 г	молоко и сливки	г	900	75	2019-06-01 00:00:00	1	Семёрочка
101	Молоко пастеризованное Вологжанка 2,5%, 1000 г	молоко и сливки	г	nan	71	2019-06-01 00:00:00	1	Семёрочка
102	Молоко пастеризованное Вологжанка 3,2%, 1 л	молоко и сливки	л	1	70	2019-06-01 00:00:00	1	Семёрочка
119	Молоко Бежин луг пастеризованное 3,2%, 900 г	молоко и сливки	г	900	62	2019-06-01 00:00:00	1	Семёрочка
120	Масло сливочное Простоквашино 72,5%, 180 г	масло сливочное и маргарин	г	180	116	2019-06-01 00:00:00	1	Семёрочка
121	Масло сливочное Вологодское из Вологды 82,5%, 180 г	масло сливочное и маргарин	г	180	165	2019-06-01 00:00:00	1	Семёрочка
122	Масло сливочное Крестьянское из Вологды 72,5%, 180 г	масло сливочное и маргарин	г	180	139	2019-06-01 00:00:00	1	Семёрочка

xxxxxxxxxx
 
SELECT
​
FROM
    products_data_all
GROUP BY