SQL-агрегирование и группировка

Раскрытие возможностей суммирования данных

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

ЗНАТЬ ТАБЛИЦУ

Естественно, один сотрудник может работать более одного года, поэтому строк на одного и того же человека может быть много, обычно с разной зарплатой каждый год.

Заказ

заказы (order_id, customer_id, order_date, Ship_date, total_sum)

38. Сортировка строк — ПОРЯДОК ПО.

Вы уже достаточно опытны в фильтрации строк, но задумывались ли вы, как они сортируются в результате запроса SQL? Что ж, ответ прост — по умолчанию они вообще не сортируются. Последовательность появления строк произвольна, и каждая база данных может вести себя по-разному. Вы даже можете выполнить одну и ту же инструкцию SQL несколько раз и каждый раз получать разный порядок — если, конечно, вы не попросите базу данных отсортировать строки.

SELECT *
FROM orders
ORDER BY customer_id;

В приведенном выше примере мы добавили новый фрагмент: ORDER BY. После этого выражения можно просто указать столбец, по которому будут отсортированы данные.

В данном случае мы указываем имя столбца customer_id, поэтому все заказы будут отсортированы по идентификатору customer_id.

• Упражнение. Попробуйте сами. Выберите все столбцы из таблицы employees и отсортируйте их по зарплате.

SELECT * 
FROM employees 
ORDER BY salary;

39. ЗАКАЗАТЬ ПО с условиями

Отличный! Теперь вы можете легко проверить, у кого самая низкая и самая высокая зарплата. Это не так уж и сложно, как видите.

Мы можем фильтровать строки и сортировать их одновременно. Просто взгляните:

SELECT *
FROM orders
WHERE customer_id = 100
ORDER BY total_sum;

Предложение WHERE и ORDER BY хорошо работают вместе.

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

• Упражнение: выберите из таблицы employees только строки, относящиеся к 2011 году. Отсортируйте их по зарплате.

SELECT *
FROM employees
WHERE year = 2011
ORDER BY salary;

40. Восходящий и нисходящий порядок

Хороший. Как видите, самая низкая зарплата была показана первой, а самая высокая — последней. Этот возрастающий порядок результатов выполняется в SQL по умолчанию. Однако, если вы хотите быть точным и прояснить ситуацию, вы можете использовать ключевое слово ASC (сокращение от возрастающего порядка) после имени столбца:

SELECT *
FROM orders
ORDER BY total_sum ASC;

Добавление ключевого слова ASC ничего не изменит, но очень четко покажет ваше намерение.

Мы также можем обратить порядок и сделать так, чтобы наибольшие значения отображались первыми.

SELECT *
FROM orders
ORDER BY total_sum DESC;

Как видите, мы добавили слово DESC после имени столбца, что является сокращением от по убыванию. В результате первыми будут показаны самые высокие значения в столбце total_sum.

• Упражнение: выберите все строки из таблицы employees и отсортируйте их в убывающем порядке по столбцу last_name.

SELECT *
FROM employees
ORDER BY last_name DESC;

41. Сортировка по нескольким столбцам

Хорошо, еще кое-что, прежде чем мы продолжим: вы можете сортировать результаты по более чем одному столбцу, и каждый из них можно сортировать в различном порядке:

SELECT *
FROM order
ORDER BY customer_id ASC,
total_sum DESC;

Как видите, результаты сначала будут отсортированы customer_id в порядке возрастания (сначала самые низкие значения), а затем для каждого customer_id заказы будут отсортированы total_sum в порядке убывания (сначала самые большие значения).

• Упражнение: выберите все строки из таблицы employees и отсортируйте их в по возрастанию по отделу, а затем в по убыванию. по зарплате.

SELECT *
FROM employees
ORDER BY department asc,
salary DESC;

Устранение повторяющихся результатов

42. Повторяющиеся результаты

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

• Упражнение. Выберите столбец year для всех строк таблицы employees. Затем внимательно изучите результат.

SELECT year
FROM employees;

43. Выберите ОТЛИЧНЫЕ значения.

Можете ли вы увидеть проблему? Было много строк с одним и тем же годом, поэтому каждый год отображается в результатах много раз.

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

К счастью, мы можем легко это изменить.

SELECT DISTINCT customer_id
FROM orders;

Перед названием столбца мы добавили слово DISTINCT. Теперь база данных будет удалять дубликаты и отображать только отдельные значения. Каждый customer_id появится только один раз.

• Упражнение.Выберите столбец year из таблицы employees таким образом, чтобы каждый год отображался только один раз.

SELECT DISTINCT year
FROM employees;

Вы также можете использовать DISTINCT для группы столбцов. Взглянем:

SELECT DISTINCT
  customer_id,
  order_date
FROM orders;

Один клиент может размещать много заказов каждый день, но если мы просто хотим знать, в какие дни каждый клиент действительно разместил хотя бы один заказ, приведенный выше запрос проверит это.

• Упражнение: Проверьте, какие должности есть в каждом отделе. Для этого выберите столбцы department и position из таблицы employees и устраните дубликаты.

SELECT DISTINCT
  department,
	position
FROM employees;

Агрегация

44. Посчитайте ряды

Мы уже знаем, что ваша база данных может выполнять вычисления, поскольку мы уже добавляли или вычитали значения в инструкциях SQL. База данных может сделать гораздо больше. Он может вычислять статистику для нескольких строк. Эта операция называется агрегацией. Начнем с чего-то простого:

SELECT COUNT(*)
FROM orders;

Вместо звездочки (*), которая по сути означает «все», мы поставили выражение COUNT(*).

COUNT(*) — это функция. Функция в SQL всегда имеет имя, за которым следуют круглые скобки. В скобках можно указать информацию, необходимая для работы функции. Например, COUNT() вычисляет количество строк, указанное в скобках.

В данном случае мы использовали COUNT(*), что по сути означает «подсчитать все строки». В результате мы получим просто количество всех строк в таблицах, а не их содержимое.

• Упражнение: посчитайте все строки в таблице employees.

SELECT COUNT(*)
FROM employees;

45. Подсчитайте строки, игнорируйте NULLS

Естественно, звездочка (*) — не единственная опция, доступная в функции COUNT(). Например, мы можем попросить базу данных подсчитать значения в определенном столбце:

SELECT COUNT(customer_id)
FROM orders;

В чем разница между COUNT(*) и COUNT(customer_id)? Итак, первый вариант подсчитывает все строки в таблице, а второй вариант подсчитывает все строки, где столбец customer_id имеет указанное значение. Другими словами, если в столбце customer_id есть NULL, эта строка не будет учтена.

• Упражнение:Проверьте, сколько значений, отличных от NULL, в столбце position в таблице employees. Назовите столбец non_null_no.

SELECT COUNT(position) 
AS non_null_no
FROM  employees;

46. ​​Подсчитайте отличительные значения в столбце

Большой. Как вы, вероятно, ожидаете, мы также можем добавить ключевое слово DISTINCT в нашу функцию COUNT():

SELECT COUNT(DISTINCT customer_id) AS distinct_customers
FROM orders;

На этот раз мы подсчитываем все строки, имеющие отличительное значение в столбце customer_id. Другими словами, эта инструкция сообщает нам, сколько разных клиентов разместили заказ на данный момент. Если клиент разместит 5 заказов, он будет засчитан только один раз.

• Упражнение: посчитайте, сколько разных позиций содержится в таблице employees. Назовите столбец distinct_positions.

SELECT COUNT(DISTINCT position) AS distinct_positions
FROM employees;

47. Найдите минимальное и максимальное значение.

Хорошая работа. Конечно, COUNT() — не единственная функция. Давайте научимся другим!

SELECT MIN(total_sum)
FROM orders;

Функция MIN(total_sum) возвращает наименьшее значение столбца total_sum. Таким образом, мы можем найти самый дешевый заказ в нашей таблице. Удобно, да?

Вы также можете использовать аналогичную функцию, а именно MAX(). Правильно, он возвращает самое большое значение указанного столбца. Проверьте это сами.

• Упражнение: выберите самую высокую зарплату из таблицы employees.

SELECT MAX(salary)
FROM employees;

48. Найдите среднее значение

Хорошо, теперь, когда вы знаете, какая самая высокая зарплата, давайте обсудим еще одну функцию:

SELECT AVG(total_sum)
FROM orders
WHERE customer_id = 100;

Функция AVG() находит среднее значение указанного столбца.

В нашем примере мы получим среднюю стоимость заказа для клиента с идентификатором 100.

• Упражнение. Найдите среднюю зарплату в таблице employees за 2013 год.

SELECT AVG(salary)
FROM employees
WHERE year = 2013;

49. Найдите сумму

Это верно. Последняя функция, которую мы обсудим, — SUM().

Изучите пример:

SELECT SUM(total_sum)
FROM orders
WHERE customer_id = 100;

Приведенная выше инструкция найдет общую сумму всех заказов, размещенных клиентом с идентификатором 100.

• Упражнение. Найдите сумму всех зарплат в отделе маркетинга за 2014 год. Не забудьте указать название отдела в кавычках!

SELECT SUM(salary)
FROM employees
WHERE year = 2014
AND
department ='Marketing';

Группировка

50. Сгруппируйте ряды и посчитайте их.

В предыдущем разделе мы научились подсчитывать статистику для всех строк. Теперь мы перейдем к изучению еще более сложной статистики. Посмотрите на следующее утверждение:

SELECT
  customer_id,
  COUNT(*)
FROM orders
GROUP BY customer_id;

Новый фрагмент здесь — GROUP BY, за которым следует имя столбца (customer_id). GROUP BY сгруппирует все строки, имеющие одинаковое значение в указанном столбце.

В нашем примере все заказы, сделанные одним и тем же клиентом, будут сгруппированы в одну строку. Затем функция COUNT(*) подсчитает все строки для конкретных клиентов. В результате мы получим таблицу, в которой каждый customer_id будет показан вместе с количеством заказов, размещенных этим клиентом. Взгляните на следующую таблицу:

• Упражнение. Найдите количество сотрудников в каждом отделе в 2013 году. Покажите название отдела вместе с количеством сотрудников. Назовите второй столбец employees_no.

SELECT
department, COUNT(*) as employees_no
FROM employees
WHERE year = 2013
GROUP BY department;

51. Найдите минимальные и максимальные значения в группах.

Отличный! Конечно, COUNT(*) — не единственный вариант. Фактически, GROUP BY используется вместе со многими другими функциями. Взглянем:

SELECT
  customer_id,
  MAX(total_sum)
FROM orders
GROUP BY customer_id;

Мы заменили COUNT(*) на MAX(total_sum). Можете ли вы догадаться, что сейчас произойдет?

Правильно, вместо того, чтобы считать все заказы для конкретных клиентов, мы найдем заказ с наибольшей ценностью для каждого клиента.

• Упражнение. Покажите все отделы вместе с их самой низкой и самой высокой зарплатой в 2014 году.

SELECT 
  department, 
  MAX(salary), 
  MIN(salary)
FROM employees
WHERE YEAR = 2014
GROUP BY department;

52. Найдите среднее значение по группам.

Разберем еще один пример такого рода:

SELECT
  customer_id,
  AVG(total_sum)
FROM orders
WHERE order_date >= '2019-01-01'
  AND order_date < '2020-01-01'
GROUP BY customer_id;

Как видите, теперь мы используем функцию AVG(total_sum), которая будет подсчитывать среднюю стоимость заказа для каждого из наших клиентов, но только для их заказов, размещенных в 2019 году.

• Упражнение. Найдите среднюю зарплату в каждом отделе в 2015 году.

SELECT 
	department, AVG(salary)
FROM employees
WHERE year = 2015
GROUP BY department;

53. Группировка по нескольким столбцам

Есть еще одна вещь о GROUP BY, которую мы хотим обсудить. Иногда нам нужно сгруппировать строки по нескольким столбцам. Давайте представим, что у нас есть несколько клиентов, которые размещают тонны заказов каждый день, поэтому мы хотели бы знать ежедневную сумму их заказов.

SELECT
  customer_id,
  order_date,
  SUM(total_sum)
FROM orders
GROUP BY customer_id, order_date;

Как видите, мы группируемся по двум столбцам: customer_id и order_date. Эти столбцы мы выбираем вместе с функцией SUM(total_sum).

Помните: в таких запросах каждый столбец в части SELECT должен либо использоваться позже для группировки, либо должен использоваться с одной из функций.

Примечание. Нет смысла выбирать какой-либо другой столбец. Например, каждый заказ, сделанный в один и тот же день одним и тем же клиентом, может иметь разную дату доставки. Если вы хотите выбрать столбец ship_date, в этом случае база данных не будет знать, какую дату отгрузки выбрать для всей группы, поэтому выдаст ошибку.

• Упражнение. Найдите среднюю зарплату каждого сотрудника. Укажите фамилию, имя и среднюю зарплату. Сгруппируйте таблицу по фамилии и имени.

SELECT 
  AVG(salary),
  last_name, 
  first_name
FROM employees
GROUP BY  last_name, first_name;

HAVING: Фильтрация и упорядочивание групп.

54. Фильтровать группы

В этом разделе мы рассмотрим, как можно фильтровать группы. Для этого зарезервировано специальное ключевое слово HAVING.

SELECT
  customer_id,
  order_date,
  SUM(total_sum)
FROM orders
GROUP BY customer_id, order_date
HAVING SUM(total_sum) > 2000;

Новая часть здесь подходит к концу. Мы использовали ключевое слово HAVING, а затем указали условие для фильтрации результатов. В данном случае мы хотим показать только тех клиентов, которые в отдельные дни заказывали товары общей дневной стоимостью более 2000.

Кстати, наверное, сейчас самое время отметить важную вещь: в SQL определенные фрагменты всегда должны располагаться в правильном порядке. Например, нельзя ставить WHERE перед FROM. Аналогично, HAVING всегда должно следовать за GROUP BY, а не наоборот. Имейте это в виду, когда пишете запросы, особенно длинные.

• Упражнение: Найдите таких сотрудников, которые проработали в компании более 2 лет. Выберите их фамилию и имя вместе с количеством отработанных лет (назовите этот столбец years).

SELECT COUNT(DISTINCT year) as years, last_name,first_name 
FROM employees
GROUP BY last_name,first_name
HAVING COUNT(DISTINCT year) >2;

• Упражнение. Найдите такие отделы, где средняя зарплата в 2012 году была выше 3000. Покажите название отдела со средней зарплатой.

SELECT  department , AVG(salary)
FROM employees
WHERE year = 2012
GROUP BY department
HAVING (AVG(salary)>3000) ;

55. Группы заказов

Правильный! Прежде чем вы уйдете, есть еще одна вещь. Группы можно сортировать так же, как строки. Взглянем:

SELECT
  customer_id,
  order_date,
  SUM(total_sum)
FROM orders
GROUP BY customer_id, order_date
ORDER BY SUM(total_sum) DESC;

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

• Упражнение. Отсортируйте сотрудников по их суммарной зарплате. Самые высокие значения должны появиться первыми. Покажите фамилию, имя и сумму.

SELECT last_name, first_name, SUM(salary)
FROM employees
GROUP BY last_name, first_name
ORDER BY SUM(salary) DESC;

Примените свои навыки на практике

• Упражнение:Покажите столбцы last_name и first_name из таблицы employees вместе с средней зарплатой каждого человека и количеством лет, в течение которых он (проработал) компания.

Используйте следующие псевдонимы: average_salary для средней зарплаты каждого человека и years_worked для количества лет, проработанных в компании. Показывать только тех сотрудников, которые проработали в компании более 2 лет. Упорядочите результаты по средней зарплате в по убыванию.

SELECT
  last_name,
  first_name,
  AVG(salary) AS average_salary,
  COUNT(DISTINCT year) AS years_worked
FROM employees
GROUP BY last_name, first_name
HAVING COUNT(DISTINCT year) > 2
ORDER BY AVG(salary) DESC;

В итоге

Вы отправились в увлекательное путешествие по продвинутым концепциям SQL! Вы научились сортировать строки с помощью ORDER BY, агрегировать статистику с помощью таких функций, как COUNT(), SUM(), AVG(), и находить экстремальные значения с помощью MIN() и MAX(). Вы также изучили группировку GROUP BY и фильтрацию сгруппированных данных с помощью HAVING.

Эти навыки дадут вам возможность создавать подробные отчеты, анализировать распределение данных и делать ценные выводы из больших наборов данных. Продолжая совершенствовать свое мастерство SQL, помните, что практика ведет к совершенству. Поэкспериментируйте с этими концепциями в своих проектах, чтобы закрепить свое понимание. Удачных запросов!

Вы пропустили отчет 1.2? Не волнуйтесь, просто нажмите здесь.

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