Over partition by: Предложение OVER (Transact-SQL) — SQL Server

Содержание

Обзор предложения SQL PARTITION BY

В этой статье будет рассмотрено предложение SQL PARTITION BY и, в частности, разница с GROUP BY в операторе select. Мы также рассмотрим различные варианты использования SQL PARTITION BY.

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

Подготовка данных образца

Давайте создадим таблицу Orders в моей тестовой базе данных 9.0009 SQLShackDemo и вставьте записи для написания дальнейших запросов.

10 3

3

Используйте SQLSHACKDEMO

CREATE TABLE [DBO]. [Заказы]

(

[ORDERID] INT,

[ORDERDATE] Дата,

[CustomerNam0002 [Customercity] VARCHAR(100),

[Сумма заказа] MONEY

)

Я использую ApexSQL Generate для вставки примеров данных в эту статью. Щелкните правой кнопкой мыши таблицу Orders и Generate test data .

Он запускает ApexSQL Generate. Я создал скрипт для вставки данных в таблицу Orders. Выполните этот скрипт, чтобы вставить 100 записей в таблицу Orders.

USE [SQLShackDemo]

INSERT [dbo].[Orders] VALUES (216090, CAST(N’1826-12-19′ AS Date), N’Edward’, N’Phoenix’, 4713.8900)

INSERT [dbo].[Orders] VALUES (508220, CAST(N’1826-12-09′ AS Date), N’Aria’, N’San Francisco’, 9832.7200)

…

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

Мы используем предложение SQL GROUP BY для группировки результатов по указанному столбцу и используем агрегатные функции, такие как Avg(), Min(), Max(), для вычисления необходимых значений.

Группировать по синтаксису функций

Выражение SELECT, агрегатная функция ()

FROM таблиц

Условия WHERE

Выражение GROUP BY

Предположим, мы хотим найти следующие значения в таблице Orders

Минимальная стоимость заказа в городе
Максимальная стоимость заказа в городе
Средняя стоимость заказа в городе

Выполните следующий запрос с предложением GROUP BY, чтобы вычислить эти значения.

ВЫБЕРИТЕ Customercity,

AVG(Orderamount) AS AvgOrderAmount,

MIN(OrderAmount) AS MinOrderAmount,

SUM(Orderamount) TotalOrderAmount

FROM Customer [dbo].[Ordercity] UP

3 GRO;

На следующем снимке экрана мы видим среднее, минимальное и максимальное значения, сгруппированные по CustomerCity.

Теперь мы хотим добавить в вывод столбцы CustomerName и OrderAmount . Давайте добавим эти столбцы в оператор select и выполним следующий код.

Выберите CustomerCity, CustomerName, OrderAmount,

AVG (OrderAmount) в качестве AvgorderAmount,

мин (orderAmount) в качестве MinOrderAmount,

SUM (OrderAmount) Total OrderMount

из [DBO]. [Заказатели]

Группа по CustomerCity;

Как только мы выполним этот запрос, мы получим сообщение об ошибке. В предложении SQL GROUP BY мы можем использовать столбец в операторе select, если он также используется в предложении Group by. В предложении select не разрешен ни один столбец, который не является частью предложения GROUP BY.

Мы можем использовать предложение SQL PARTITION BY для решения этой проблемы. Давайте рассмотрим это подробнее в следующем разделе.

РАЗДЕЛ SQL ПО

Мы можем использовать предложение SQL PARTITION BY с предложением OVER , чтобы указать столбец, для которого нам нужно выполнить агрегирование. В предыдущем примере мы использовали столбец Group By with CustomerCity и рассчитали среднее, минимальное и максимальное значения.

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

Select CustomerCity,

AVG (OrderAmount) Over (раздел CustomerCity) как AvgorderAmount,

мин (OrderAmount). ОТ [dbo].[Заказы];

На выходе мы получаем агрегированные значения, аналогичные предложению GROUP By. Вы можете заметить разницу в выводе предложений SQL PARTITION BY и GROUP BY.

Группа по

РАЗДЕЛ SQL ПО

Мы получаем ограниченное количество записей, используя предложение Group By

Мы получаем все записи в таблице, используя предложение PARTITION BY.

Это дает одну строку на группу в наборе результатов. Например, мы получаем результат для каждой группы CustomerCity в предложении GROUP BY.

Он дает агрегированные столбцы с каждой записью в указанной таблице.

У нас есть 15 записей в таблице Orders. В выводе запроса SQL PARTITION BY мы также получаем 15 строк вместе с минимальными, максимальными и средними значениями.

В предыдущем примере мы получаем сообщение об ошибке, если пытаемся добавить столбец, который не является частью предложения GROUP BY.

Мы можем добавить необходимые столбцы в оператор select с помощью предложения SQL PARTITION BY. Давайте добавим столбцы CustomerName и OrderAmount и выполним следующий запрос.

Select CustomerCity,

CustomerName,

OrderAmount,

AVG (OrderAmount) Over (Разделение CustomerCity) как AvgorderAmount,

мин. (РАЗДЕЛ ПО Customercity) TotalOrderAmount

ОТ [dbo].[Заказы];

Мы получаем столбцы CustomerName и OrderAmount вместе с выходными данными агрегированной функции. Мы также получаем все строки, доступные в таблице Orders.

На следующем снимке экрана вы можете увидеть CustomerCity Chicago , он выполняет агрегирование (Avg, Min и Max) и дает значения в соответствующих столбцах.

Точно так же мы можем использовать другие агрегатные функции, такие как count, чтобы узнать общее количество заказов в конкретном городе с помощью SQL 9.0009 РАЗДЕЛ BY пункт.

Выберите CustomerCity,

CustomerName,

OrderAmount,

COUNT (ORDERID) OUT (раздел по CustomerCity) в качестве CountOforders,

AVG (OrderAmount). (РАЗДЕЛЕНИЕ ПО Customercity) AS MinOrderAmount,

SUM(Orderamount) OVER(PARTITION BY Customercity) TotalOrderAmount

FROM [dbo].[Orders];

Мы можем видеть количество заказов для определенного города. Например, у нас есть два заказа из города Остин, поэтому; он показывает значение 2 в столбце CountofOrders .

Предложение PARTITION BY с ROW_NUMBER()

Мы можем использовать предложение SQL PARTITION BY с функцией ROW_NUMBER(), чтобы иметь номер строки для каждой строки. Мы определяем следующие параметры для использования ROW_NUMBER с предложением SQL PARTITION BY.

РАЗДЕЛЕНИЕ ПО столбцу — в этом примере мы хотим разделить данные по столбцу CustomerCity
Порядок: В столбце ORDER BY мы определяем столбец или условие, которое определяет номер строки. В этом примере мы хотим отсортировать данные в столбце OrderAmount .

Выберите CustomerCity,

CustomerName,

ROW_NUMBER () OUT (раздел по CustomerCity

Заказ по порядку детья) как «номер строки»,

OrderAmount,

Count (OrderID). ,

AVG(Orderamount) OVER(PARTITION BY Customercity) AS AvgOrderAmount,

MIN(OrderAmount) OVER(PARTITION BY Customercity) AS MinOrderAmount,

SUM(Orderamount) OVER(PARTITION BY Customercity) TotalOrderAmount

FROM [dbo].[Orders];

На следующем снимке экрана мы видим для CustomerCity Chicago , у нас есть строка номер 1 для заказа с наибольшей суммой 7577,90. он предоставляет номер строки с убывающим значением OrderAmount.

Предложение PARTITION BY с совокупным общим значением

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

Например, в городе Чикаго у нас четыре заказа.

CustomerCity	Имя Клиента	Классифицировать	Сумма заказа	Совокупное общее количество строк	Нарастающий итог
Чикаго	Марвин	1	7577,9	Ранг 1 +2	14777,51
Чикаго	Лоуренс	2	7199,61	Ранг 2+3	14047.21
Чикаго	Алекс	3	6847,66	Ранг 3+4	8691,49
Чикаго	Джером	4	1843,83	Ранг 4	1843,83

В следующем запросе мы указали предложение ROWS для выбора текущей строки (используя CURRENT ROW) и следующую строку (используя 1 FOLLOWING). Далее он вычисляет сумму по этим строкам, используя sum(Orderamount) с разделением на CustomerCity (используя OVER(PARTITION BY Customercity ORDER BY OrderAmount DESC).

Выберите CustomerCity,

CustomerName,

OrderAmount,

ROW_NUMBER () OUT (раздел CustomerCity

Заказ по порядку DESC) в качестве «номера строки»,

Конвертируйте (VARCHAR (20), сумма (OrderAmount). (РАЗДЕЛЕНИЕ ПО Customercity

ORDER BY OrderAmount DESC ROWS МЕЖДУ ТЕКУЩЕЙ СТРОКОЙ И 1 СЛЕДУЮЩЕЙ), 1) AS CumulativeTotal,

Точно так же мы можем вычислить кумулятивное среднее, используя следующий запрос с предложением SQL PARTITION BY .

SELECT Customercity,

CustomerName,

OrderAmount,

ROW_NUMBER() OVER(РАЗДЕЛ ПО Customercity

ORDER BY OrderAmount DESC) AS «Номер строки»,

CONVERT(VARCHAR(20), AVG(orderamount) OVER(PARTITION BY Customercity

ORDER BY OrderAmount) ASMULTIVE ROWS IN 1 DESC ROWS IN 1 CURRENT

ROWS UNBOUNDED PRECEDING с пунктом PARTITION BY

Мы можем использовать ROWS UNBOUNDED PRECEDING с предложением SQL PARTITION BY, чтобы выбрать строку в разделе перед текущей строкой и строку с наибольшим значением после текущей строки.

В следующей таблице мы видим для строки 1; в этом разделе нет ни одной строки с высоким значением. Таким образом, кумулятивное среднее значение такое же, как и в строке 1 OrderAmount.

Для Row2 выполняется поиск текущего значения строки (7199,61) и строки с наибольшим значением 1 (7577,9). Он вычисляет среднее значение для этих двух сумм.

Для строки 3 выполняется поиск текущего значения (6847,66) и более высокого значения суммы, чем это значение, то есть 7199,61 и 7577,9.0. Он вычисляет среднее из них и возвращает результат.

CustomerCity	Имя Клиента	Классифицировать	Сумма заказа	Совокупное среднее количество строк	Совокупное среднее
Чикаго	Марвин	1	7577,9	Ранг 1	7577,90
Чикаго	Лоуренс	2	7199,61	Ранг 1+2	7388,76
Чикаго	Алекс	3	6847,66	Ранг 1+2+3	7208,39
Чикаго	Джером	4	1843,83	Ранг 1+2+3+4	5867,25

Выполните следующий запрос, чтобы получить этот результат с нашими примерами данных.

SELECT Customercity,

CustomerName,

OrderAmount,

ROW_NUMBER() OVER(РАЗДЕЛ ПО Customercity

Заказ по порядку desc) как «номер строки»,

преобразовать (varchar (20), avg (orderamount) над (раздел от Customercity

Порядок по orderAmount desce -necned rows предшествует), 1) как cumulativeavg

из [ dbo].[Заказы];

Заключение

В этой статье мы рассмотрели предложение SQL PARTIION BY и его сравнение с предложением GROUP BY. Мы также изучили его использование на нескольких примерах. Я надеюсь, что вы найдете эту статью полезной и не стесняйтесь задавать любые вопросы в комментариях ниже.

Автор
Последние сообщения

Раджендра Гупта

Привет! Я Раджендра Гупта, специалист по базам данных и архитектор, помогаю организациям быстро и эффективно внедрять решения Microsoft SQL Server, Azure, Couchbase, AWS, устранять связанные проблемы и настраивать производительность с более чем 14-летним опытом.

Я автор книги «DP-300 Administering Relational Database on Microsoft Azure». Я опубликовал более 650 технических статей о MSSQLTips, SQLShack, Quest, CodingSight и MultipleNines.

Я создатель одной из крупнейших бесплатных онлайн-коллекций статей по одной теме, включая серию из 50 статей о группах доступности SQL Server Always On.

Основываясь на моем вкладе в сообщество SQL Server, я неоднократно признавался престижным Лучшим автором года в 2019, 2020 и 2021 годах (2-е место) в SQLShack и наградой чемпионов MSSQLTIPS в 2020 году.

Личный блог: https://www.dbblogger.com
Меня всегда интересуют новые задачи, поэтому, если вам нужна консультационная помощь, свяжитесь со мной по адресу [email protected]

Просмотреть все сообщения Раджендры Гупты

Последние сообщения Раджендры Гупты (посмотреть все)

Различные функции SQL TimeStamp в SQL Server — 25 ноября 2022 г.
Как определить подходящие SKU для базы данных SQL Azure, управляемого экземпляра (MI) ) или SQL Server на виртуальной машине Azure — 28 октября 2022 г.
Копирование данных из AWS RDS SQL Server в базу данных SQL Azure — 21 октября 2022 г.

SQL PARTITION BY Пункт: Когда и как его использовать — Управление базой данных — Блоги

В этой статье мы рассмотрим, когда и как использовать предложение SQL PARTITION BY, и сравним его с использованием предложения GROUP BY.

Понимание функции окна

Пользователи баз данных используют агрегатные функции, такие как MAX(), MIN(), AVERAGE() и COUNT(), для выполнения анализа данных. Эти функции работают со всей таблицей и возвращают отдельные агрегированные данные с помощью предложения GROUP BY. Иногда нам требуются агрегированные значения по небольшому набору строк. В этом случае оконная функция в сочетании с агрегатной функцией помогает достичь желаемого результата. Функция Window использует предложение OVER() и может включать следующие функции:

Разделить по: Разделяет строки или набор результатов запроса на небольшие разделы.
Упорядочить по: Упорядочивает строки в возрастающем или убывающем порядке для окна раздела. Порядок по умолчанию — возрастающий.
Строка или диапазон: Вы можете дополнительно ограничить количество строк в разделе, указав начальную и конечную точки.

В этой статье мы сосредоточимся на изучении предложения SQL PARTITION BY.

Подготовка данных образца

Предположим, у нас есть таблица [SalesLT].[Orders], в которой хранится информация о заказах клиентов. В нем есть столбец [Город], в котором указан город клиента, в котором был размещен заказ.

 СОЗДАТЬ ТАБЛИЦУ [SalesLT].[Заказы]
(
порядковый номер INT,
дата заказа ДАТА,
имя_клиента VARCHAR(100),
Город ВАРЧАР(50),
сумма ДЕНЬГИ
)
ВСТАВЬТЕ В [SalesLT].[Заказы]
ВЫБЕРИТЕ 1, '01.01.2021', 'Мохан Гупта', 'Алвар', 10000
СОЮЗ ВСЕХ
ВЫБЕРИТЕ 2, '04.02.2021', 'Счастливчик Али', 'Кота', 20000
СОЮЗ ВСЕХ
ВЫБЕРИТЕ 3, '02.03.2021', 'Радж Кумар', 'Джайпур', 5000
СОЮЗ ВСЕХ
ВЫБЕРИТЕ 4, 02. 04.2021, Джйоти Кумари, Джайпур, 15000
СОЮЗ ВСЕХ
ВЫБЕРИТЕ 5, '03.05.2021', 'Рахул Гупта', 'Джайпур', 7000
СОЮЗ ВСЕХ
ВЫБЕРИТЕ 6, '04.06.2021', 'Мохан Кумар', 'Алвар', 25000
СОЮЗ ВСЕХ
ВЫБЕРИТЕ 7, '02.07.2021', 'Кашиш Агарвал', 'Алвар', 15000
СОЮЗ ВСЕХ
ВЫБЕРИТЕ 8, «03.08.2021», «Нагар Сингх», «Кота», 2000 г.
СОЮЗ ВСЕХ
ВЫБЕРИТЕ 9,'04.09.2021','Анил КГ','Алвар',1000
Go

Допустим, мы хотим узнать общую стоимость заказов по местоположению (Город). Для этого мы используем функции SUM() и GROUP BY, как показано ниже.

 ВЫБЕРИТЕ город КАК CustomerCity
,sum(amount) AS totalamount FROM [SalesLT].[Orders]
СГРУППИРОВАТЬ ПО ГОРОДУ
ORDER BY city

В результирующем наборе мы не можем использовать неагрегированные столбцы в инструкции SELECT. Например, мы не можем отобразить [CustomerName] в выходных данных, поскольку оно не включено в предложение GROUP BY.

SQL Server выдает следующее сообщение об ошибке при попытке использовать неагрегированный столбец в списке столбцов.

 ВЫБЕРИТЕ город КАК CustomerCity, CustomerName, сумма,
SUM(сумма) OVER(РАЗБИВКА ПО городам) TotalOrderAmount
FROM [SalesLT].[Orders]

Как показано ниже, предложение PARTITION BY создает меньшее окно (набор строк данных), выполняет агрегирование и отображает его. Вы также можете просмотреть неагрегированные столбцы в этом выводе.

Точно так же вы можете использовать функции AVG(), MIN(), MAX() для вычисления среднего, минимального и максимального количества строк в окне.

 ВЫБЕРИТЕ город КАК CustomerCity, CustomerName, сумма,
СУММА(сумма) ПРЕВЫШЕНИЕ(РАЗДЕЛ ПО ГОРОДУ) TotalOrderAmount,
Avg(сумма) OVER(PARTITION BY city) AvgOrderAmount,
Мин.(сумма) БОЛЕЕ(РАЗДЕЛ ПО ГОРОДУ) MinOrderAmount,
MAX(сумма) OVER(РАЗБИВКА ПО городам) MaxOrderAmount
FROM [SalesLT].[Orders]

Использование предложения SQL PARTITION BY с функцией ROW_NUMBER()

Раньше мы получали агрегированные значения в окне с помощью предложения PARTITION BY. Предположим, что вместо итога нам требуется совокупный итог в разделе.

Нарастающий итог работает следующим образом.

Ряд	Итого
1	Ранг 1+ 2
2	Ранг 2+3
3	Ранг 3+4

Ранг строки вычисляется с помощью функции ROW_NUMBER(). Давайте сначала воспользуемся этой функцией и просмотрим ранги строк.

Функция ROW_NUMBER() использует предложения OVER и PARTITION BY и сортирует результаты в порядке возрастания или убывания. Он начинает ранжировать строки с 1 в соответствии с порядком сортировки.

 ВЫБЕРИТЕ город КАК CustomerCity, CustomerName, сумма,
ROW_NUMBER() OVER(PARTITION BY city ORDER BY amount DESC) AS [Номер строки]
ОТ [SalesLT].[Заказы]

Например, в городе [Алвар] строка с наибольшей суммой (25000,00) находится в строке 1. Как показано ниже, она ранжирует строки в окне, заданном предложением PARTITION BY. Например, у нас есть три разных города [Алвар], [Джайпур] и [Кота], и каждое окно (город) получает свои ряды рангов.

Для расчета нарастающего итога мы используем следующие аргументы.

ТЕКУЩАЯ СТРОКА: Указывает начальную и конечную точки в указанном диапазоне.
1 следующий: Указывает количество строк (1), следующих за текущей строкой.

 ВЫБЕРИТЕ город КАК CustomerCity, CustomerName, сумма,
ROW_NUMBER() OVER(PARTITION BY city ORDER BY amount DESC) AS [Номер строки],
СУММА(сумма) БОЛЕЕ(РАЗДЕЛЕНИЕ ПО ГОРОДАМ ПО УСЛОВИЯМ ПО УИСШЕНИЮ СТРОК МЕЖДУ
ТЕКУЩАЯ СТРОКА И 1 СЛЕДУЮЩАЯ) AS CumulativeSUM
FROM [SalesLT].[Orders]

На следующем изображении показано, что вы получаете совокупный итог вместо общего итога в окне, заданном предложением PARTITION BY.

Если мы используем ROWS UNBOUNDED PRECEDING в предложении SQL PARTITION BY, он вычисляет совокупный итог следующим образом. Он использует текущие строки вместе со строками, имеющими самые высокие значения в указанном окне.

Ряд	Итого
1	Ранг 1
2	Ранг 1+2
3	Ранг 1+2+3

 ВЫБЕРИТЕ город КАК CustomerCity, CustomerName, сумма,
ROW_NUMBER() OVER(PARTITION BY city ORDER BY amount DESC) AS [Номер строки],
СУММА(сумма) БОЛЕЕ(РАЗБИВКА ПО ГОРОДАМ ПО УСЛОВИЯМ ПО УИСЛАМ
ROWS UNBOUNDED PRECEDING) AS CumulativeSUM
ОТ [SalesLT].[Заказы]

Сравнение предложения GROUP BY и SQL PARTITION BY

GROUP BY	РАЗДЕЛ ПО
Возвращает одну строку на группу после вычисления совокупных значений.	Возвращает все строки из оператора SELECT вместе с дополнительными столбцами агрегированных значений.
Мы не можем использовать неагрегированный столбец в операторе SELECT.	Мы можем использовать обязательные столбцы в операторе SELECT, и он не выдает никаких ошибок для неагрегированного столбца. Назад: Microsoft sql server 2018 server management studio: Материалы для скачивания по SQL Server Вперед: 64 aida что это: AIDA64 – что это за программа, как ей пользоваться, для чего нужна, сравнение версий Extreme, Engineer, Network Audit, Business Imacros \| Все права защищены © 2021