Подготовьтесь к интервью Data Science

Фон

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

Это первая часть серии статей об оконных функциях SQL. В этом блоге мы узнаем об основах оконных функций SQL и их приложений.

Зачем нужны оконные функции?

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

Теперь предположим, что мы хотим добавить 2 столбца в эту таблицу данных:

  1. ИТОГО: Этот столбец содержит общую заработную плату всех сотрудников. Это равно сумме столбца зарплаты.
  2. TOTAL_JOB: этот столбец содержит общую заработную плату всех сотрудников в рамках должности, соответствующей строке (специалист по данным, аналитик данных, инженер по данным). Например, для строк с «JOB» в качестве Data Scientist столбец TOTAL_JOB равен 12 000 (сумма зарплат Data Scientist: 3800+4900+3300).

Можем ли мы добавить эти столбцы без выполнения GROUP BY и самообъединения?

Да, мы легко делаем это с помощью функции окна.

Что такое функция Window в SQL?

Оконная функция выполняет вычисления для одной или нескольких строк таблицы данных и возвращает значения для всех строк таблицы. В отличие от функций агрегирования (использующих предложение GROUP BY), где отдельные строки «теряются», оконные функции не объединяют результаты нескольких строк в одну строку, и каждая строка сохраняет свою первоначальную идентичность.

Синтаксис оконной функции

Ниже приведен синтаксис функции окна:

SELECT
<column_1>, <column_2>,
<window_function>(expression)OVER
(PARTITION BY<partition_list> 
ORDER BY<order_list>)
FROM
<table_name>

Давайте подробно разберем каждое из ключевых слов:

  • Функция окна – это имя функции окна, которую мы хотим применить, например, сумма, среднее значение, номер строки и т. д.
  • Выражение – это имя столбца, к которому следует применить оконную функцию. В зависимости от функции окна, которую мы используем, это может потребоваться или не потребоваться. Например, оконная функция номера строки не требует выражения.
  • OVER просто указывает, что функция является оконной.
  • PARTITION BY разделяет строки таблицы данных, позволяя нам определить, какие строки использовать для вычисления оконной функции.
  • Список разделов — это имя столбца (столбцов), по которому мы хотим разделить. Это обязательно с предложением PARTITION BY.
  • ORDER BY используется для сортировки строк в каждом разделе. Это необязательный пункт.
  • Список порядка – это имя столбца(ов), которые нужно упорядочить, обязательно с условием ORDER BY.

Некоторые примеры

Чтобы увидеть оконные функции в действии, давайте рассмотрим несколько примеров:

  1. Предложение OVER без PARTITION BY

Чтобы добавить столбец (ВСЕГО), содержащий сумму зарплат всех сотрудников в нашей таблице сотрудников, мы будем использовать функцию суммы как оконную функцию, столбец зарплаты как выражение и предложение OVER().

Поскольку мы находим сумму зарплаты по всем сотрудникам (строкам), нам не нужно разбивать наши данные.

## SQL Query
select EMPID, NAME, JOB, SALARY, 
sum(SALARY) over() as TOTAL
FROM
employee_table

2. Предложение OVER с PARTITION BY

Теперь, чтобы добавить столбец с общей заработной платой всех сотрудников в рамках должности, соответствующей строке (ученый по данным, аналитик данных, инженер по данным), нам нужно разделить наши данные по столбцу JOB.

Чтобы получить вывод, мы будем использовать функцию суммы как оконную функцию, столбец зарплаты как выражение, а в предложении OVER() мы разделим нашу таблицу данных по столбцу JOB.

## SQL Query
select EMPID, NAME, JOB, SALARY, 
sum(SALARY) over(partition by JOB)
as TOTAL_JOB
FROM
employee_table

Заключение

Итак, мы рассмотрели, как легко добавить агрегированные значения ко всем строкам таблицы, используя оконную функцию в SQL.

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

Спасибо!

Вы можете получать все мои сообщения в свой почтовый ящик. Сделайте это здесь!
Если вам нравится работать с Medium, поддержите меня и тысячи других авторов с помощью запись на членство. Это стоит всего 5 долл. США в месяц, это очень поддерживает нас, писателей, и у вас также есть возможность зарабатывать на своих произведениях.