Partition by: Оконные функции SQL простым языком с примерами / Хабр

Partition By Clause в Oracle

Просмотреть все статьи > Partition By Clause в Oracle

Вводное обсуждение использования Partition by Clause в Oracle.

Пункт Partition By в Oracle — Swadhin Ray

Что такое пункт «partition by» в Oracle?

Используется для разбиения данных на небольшие разделы и разделения их границей или простым разделением ввода на логические группы. Аналитические функции выполняются внутри этих разделов. Поэтому, когда границы пересекаются, функция перезапускается для разделения данных. Предложение «partition by» похоже на предложение «GROUP BY», которое используется в агрегатных функциях. Они также известны как предложение раздела запроса в Oracle.

Синтаксис PARTITION BY CLAUSE:

# АНАЛИТИЧЕСКАЯ ФУНКЦИЯ или ИМЯ ФУНКЦИИ () OVER ( PARTITION BY COLUMN NAME )

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

Пример РАЗДЕЛЕНИЯ ПО ПУНКТАМ:

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

 выбрать * из СОТРУДНИКОВ;

При выполнении выше мы увидим приведенный ниже результат для разработчика SQL.

Если мы возьмем пример для отдела номер 30, то мы должны получить общее количество как «24900»:

ОТДЕЛ ЗАРПЛАТА
30 11000
30 2500
30 3100
30 2900
30 2800
30 2600
ИТОГО/СУММА 24900

Ниже SQL получит приведенный выше результат:

 SQL> SELECT
id_отдела,
имя,
фамилия,
зарплата,
СУММА(зарплата) БОЛЕЕ(
РАЗДЕЛ ПО Department_id
) total_cost_of_department
ОТ
сотрудники
ГДЕ
id_отдела = 30
СОРТИРОВАТЬ ПО
ИД_отдела;
 
 DEPARTMENT_ID FIRST_NAME           LAST_NAME                     SALARY TOTAL_COST_OF_DEPARTMENT
---------------------------- -------------------- ----------------- -------- ---------- ---------
30 Den                                                                                                  24900
30 Александр            Ху                          3100                    24900
30 Шелли               Байда                         2900                      24900
30 Сигал                Тобиас                        2800                      24900
30 Гай                                          2600                    24900
30 Карен                                                2500                      24900
Выбрано 6 рядов.

Чтобы понять, каковы границы, мы должны запустить тот же SQL без предложения where:

 SQL> SELECT
id_отдела,
имя,
фамилия,
зарплата,
СУММА(зарплата) БОЛЕЕ(
РАЗДЕЛ ПО Department_id
) total_cost_of_department
ОТ
сотрудники
СОРТИРОВАТЬ ПО
ИД_отдела;

Вывод: 

 DEPARTMENT_ID FIRST_NAME           LAST_NAME                               SALARY TOTAL_COST_OF_DEPARTMENT
---------------------------- -------------------- ----------------- -------- ---------- ---------
10 Дженнифер             Уэлен                        4400                       4400
20 Майкл              Хартштейн                    13000                    19000
20 Пэт                                                                                                                                                        
  30 Den                                                                                                        00 
30 Карен                                                2500                      24900
30 Александр            Ху                          3100                    24900
30 Шелли               Байда                         2900                      24900
30 Сигал                Тобиас
  30 Гай                                                      2600                    24900 
  40 Сьюзан                                                                                                                             6500 
50 Дональд                                                                                                                                                                                                

Из приведенного выше вывода мы видим, что перерыв происходит для каждого отдела, поэтому функции каждого отдела начинаются снова, поскольку граница находится внутри каждого отдела, и мы должны получить общую заработную плату. Выделенные жирным шрифтом буквы — это начальная и конечная границы для отдела 30, а затем функция запускается снова, когда начинается новая граница для следующего отдела, т. е. 40.

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

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

Я буду с нетерпением ждать ответа от вас – Свадхин Рэй (Слоба)

Для получения дополнительной информации обо мне, пожалуйста, посетите мою страницу профиля Exchange Experts.

Crack SQL Вопрос интервью: Оконные функции с Partition-By | Аарон Чжу

Опубликовано в

·

Чтение: 7 мин.

·

21 сентября 2022 г.

Фото Марвина Мейера на Unsplash

В этой статье мы рассмотрим вопрос SQL из интервью Amazon по науке о данных. Надеюсь, процедура, описанная в этой статье, поможет вам более эффективно писать SQL-запросы.

SQL Вопрос:

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

Таблица: marketing_campaign

Изображение автора

Шаг 1 : давайте сначала проверим необработанные данные.

  • user_id: это поле идентифицирует уникального пользователя. Нам определенно нужно это поле для вычисления количества отдельных пользователей.
  • created_at: в этом поле указывается дата совершения транзакции. Это можно использовать для определения того, была ли данная транзакция совершена в первый день (когда была сделана первая покупка в приложении) или во время маркетинговой кампании (начиная через день после даты первой покупки в приложении)
  • product_id: это поле идентифицирует уникальный продукт. В сочетании с created_at их можно использовать для определения того, был ли данный продукт куплен в первый день или во время маркетинговой кампании, или в обоих случаях.
  • количество и цена: эти два поля представляют количество и цену, связанные с данной транзакцией. Они не имеют отношения к этому вопросу.

Шаг 2 : давайте обсудим, как решить проблему.

Самая важная задача в этом упражнении — выяснить, как идентифицировать

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

Изображение автора

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

Шаг 3 : давайте подготовим данные и подготовим их для анализа.

Метод 1: использование функции окна, MIN() с OVER-PARTITION-BY

Нам понадобится создать две новые переменные

  • first_purchase_date: это поле даст нам первый день, когда данный пользователь совершил покупку в приложении.
  • first_product_purchase_date: это поле даст нам первый день, когда пользователь купил данный продукт

Для создания этих двух переменных можно запустить следующий сценарий.

 SELECT *,MIN(created_at) OVER (PARTITION BY user_id) AS first_purchase_date,MIN(created_at) OVER (PARTITION BY user_id, product_id) AS first_product_purchase_dateFROM marketing_campaign

Давайте разберем код подробнее:

    90 168 МИН() : это оконная функция, которую мы будем использовать для вычисления самой ранней даты покупки в каждом разделе. Имеются и другие оконные функции, такие как макс., сумма, номер строки, ранг и т. д.
  • OVER : это указывает, что используемая здесь функция является оконной, а не агрегатной функцией.
  • PARTITION BY : это разделяет строки в таблице данных, чтобы мы могли определить, к каким строкам будет применяться оконная функция. Для этого упражнения «first_purchase_date» вычисляется в разделах user_id, а «first_product_purchase_date» вычисляется в разделах user_id и product_id.

Когда мы запускаем приведенный выше код, мы можем создать таблицу, подобную следующей. Давайте проверим, имеют ли смысл новые переменные. Например, user_id 10 совершил первую покупку в приложении 01.01.2019. Этот пользователь приобрел три разных продукта: 101, 111 и 119 с самыми ранними датами покупки 01.01.2019, 31.03.2019 и 02.01.2019 соответственно. С этими двумя переменными мы можем легко сделать вывод, что user_id 10 купил два новых продукта во время маркетинговой кампании.

Изображение автора

После того, как мы создадим эти две новые переменные, мы сможем помечать новые продукты, которые пользователи приобрели во время маркетинговой кампании (начиная со дня первой покупки в приложении), используя ГДЕ first_purchase_date < first_product_purchase_date .

Метод 2: использование функции окна, DENSE_RANK() с OVER-PARTITION-BY

9000 2 Точно так же нам нужно построить две новые переменные.

  • user_date_rank: это поле даст нам порядок дат покупки для данного пользователя. Например, user_date_rank = 1 представляет собой первый день, когда для данного пользователя была совершена покупка в приложении. user_date_rank = 2 представляет вторую раннюю дату и так далее. Таким образом, user_date_rank > 1 представляет записи о покупках во время маркетинговой кампании.
  • product_date_rank: это поле даст нам порядок дат покупки для данного пользователя и продукта. Например, product_date_rank = 1 представляет собой первый день, когда данный продукт был куплен пользователем.

Для создания этих двух переменных можно запустить следующий сценарий.

 SELECT *,DENSE_RANK() OVER (РАЗДЕЛЕНИЕ ПО user_id ПОРЯДОК ПО created_at) AS user_date_rank,DENSE_RANK() OVER (РАЗДЕЛЕНИЕ ПО user_id, product_id ORDER BY created_at) AS product_date_rankFROM marketing_campaign

Давайте разберемся в коде подробнее:

  • DENSE_RANK : это обычно используемая оконная функция. Эта функция дает ранг каждой строки в каждом разделе с последовательными значениями ранжирования, даже если есть равенство. Например, 1, 2, 2, 3, …. RANK — функция ранжирования чередования. Разница в том, что последний вызовет разрыв в значениях рейтинга, если будет ничья. Например, 1, 2, 2, 4, … Для упражнения лучше использовать DENSE_RANK .
  • ORDER BY используется для сортировки наблюдений в каждом разделе. В этом упражнении мы отсортируем даты покупок в каждом разделе.

Когда мы запускаем приведенный выше код, мы можем создать таблицу, подобную следующей. Давайте проверим эти новые переменные. Например, user_id 10 совершил покупки в три разные даты, 01.01.2019., 02.01.2019 и 31.03.2019, которые отсортированы от самого раннего до самого позднего с порядковым номером (т. е. «user_date_rank») равным 1, 2, 3 соответственно. Этот пользователь приобрел три разных продукта: 101, 119 и 111.