Агрегатные оконные функции T-SQL

Работа с базами данных в .NET Framework --- Оконные функции T-SQL --- Агрегатные оконные функции

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

Эти статьи разбиты по категориям функций: функции агрегирования, функции ранжирования, аналитические функции и функции сдвига. В каждой категории функций я сначала объясню, какие элементы оконных функций поддерживаются категорией, после чего объясню особенности каждой функции. Если функция появилась или была существенно расширена в SQL Server 2012, я обычно рассказываю об альтернативных решениях, существовавших до SQL Server 2012, или даю ссылку на статью, где обсуждались такие альтернативы.

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

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

Поддерживаемые элементы

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

имя_функции(<аргументы>) OVER(
    [ <предложение секционирования окна> ]
    [ <предложение упорядочения окна> [ <предложение кадрирования окна> ] ] )

Задача этих трех элементов — фильтровать строки в окне. В SQL Server 2005 появилась поддержка элемента секционирования, в том числе агрегатов CLR-агрегатов (Common Language Runtime). В SQL Server 2012 появились возможности упорядочения и кадрирования, но поддержка CLR-агрегатов пока не появилась.

Если к функции не применять никаких ограничений — то есть, когда в скобках предложения OVER пусто, окно состоит из всех строк в результирующем наборе базового запроса. Точнее, начальное окно состоит из набора строк в виртуальной таблице, предоставленной в качестве входных данных на логической фазе обработки запроса, где находится оконная функция. Это означает, что если оконная функция присутствует в списке SELECT запроса, на фазу 5-1 в качестве входных данных поступает виртуальная таблица (смотрите рисунок из статьи "Запросы к оконным функциям T-SQL"). Эта фаза наступает после обработки предложений FROM, WHERE, GROUP BY и HAVING и до удаления дублирующихся строк, если задано предложение DISTINCT (фаза 5-2). Но это начальное окно до применения ограничений. В следующих разделах рассказывается, как далее сократить окно.

Секционирование

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

В качестве примера секционирования следующий запрос вызывает две функции актирования SUM одна без секционирования, а вторая с секционированием по custid:

SELECT orderid, custid, val,
  SUM(val) OVER() AS sumall,
  SUM(val) OVER(PARTITION BY custid) AS sumcust
FROM Sales.OrderValues AS O1;

Первая функция вычисляет для всех строк общую сумму val (атрибут sumall), а вторая - общую сумму val для каждого клиента (атрибут sumcust). На рисунке ниже развернуты три произвольных суммы и иллюстрируются окна, используемые для их вычисления:

Заметьте, что в случае атрибута sumall, вычисленного для заказа 10692, соответствующее окно состоит со всех строк результирующего набора базового запроса, потому что явно не указан элемент секционирования. Поэтому общая вызываемая сумма val для этой строки 1 265 793,22, как и для всех остальных строк. Что касается атрибута sumcust, то вычисляющая его оконная функция секционирована по custid, поэтому строки с другими значениями custid содержат другие, несвязанные подмножества своих соответствующих окон. Именно такая ситуация с двумя развернутыми заказами: 10643 и 10926. Первый принадлежит клиенту 1, поэтому соответствующее окно состоит из строк с идентификатором клиента custid = 1 и дает в сумме 4273,00. Второй принадлежит клиенту 2, поэтому соответствующее окно состоит из строк с идентификатором клиента custid = 2 и дает в сумме 1402,95.

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

SELECT orderid, custid, val,
  CAST(100. * val / SUM(val) OVER() AS NUMERIC(5, 2)) AS pctall,
  CAST(100. * val / SUM(val) OVER(PARTITION BY custid) AS NUMERIC(5, 2)) AS pctcust
FROM Sales.OrderValues AS O1;

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

На рисунке также сделана попытка отобразить мысль о том, что с точки зрения концепции все окна сосуществуют в одно время. Каждый прямоугольник показывает окно одной функции для одного конкретного заказа. Самый большой прямоугольник в самом низу — пример окна, сгенерированного для одного из заказов, когда внутри скобок предложения OVER пусто. Два меньших прямоугольника представляют окна двух заказов, при этом предложение OVER содержит строку PARTITION BY custid. Прямоугольник наверху относится к заказу со значением 1 атрибута custid, а прямоугольник внизу — со значением 2.

Упорядочение и кадрирование

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

Ранее я приводил общую форму оконной функции агрегирования. Напомню ее еще раз:

имя_функции(<аргументы>) OVER(
    [ <предложение секционирования окна> ]
    [ <предложение упорядочения окна> [ <предложение кадрирования окна> ] ] )

Предложение оконного кадра может содержать три компонента:

<единицы оконного кадра> <экстент оконного кадра> [ <исключение оконного кадра> ]

В единицах оконного кадра указывается ROWS или RANGE. Первый вариант означает, чтоб границы, или конечные точки, кадра могут выражаться как смещение в виде числа строк или разницы от текущей строки. Последнее означает, что смещения более динамичны и выражаются как логическая разница со значением атрибута упорядочения текущей строки (и только ее). Эта часть станет понятнее в примерах, которые я приведу чуть позже.

В экстенте оконного кадра указываются смещения границ по отношению к текущей строке.

В SQL Server 2012 реализован параметр ROWS со всеми связанными параметрами экстента оконного кадра функции, а также параметр RANGE с частичной реализацией связанных параметров экстента оконного кадра.

Наконец, исключение оконной функции позволяет указать, надо ли исключать текущую строку, ее «сотоварищей» или и ее, и других. Исключения оконных функций в SQL Server 2012 не реализованы.

Я начну с примеров использования предложения ROWS. Как уже говорилось, использование ROWS в качестве единицы оконного кадра означает, что вы указываете границы кадра как смещения в виде числа строк от текущей строки. Стандартное предложение ROWS поддерживает следующие параметры - все они реализованы в SQL Server 2012:

ROWS BETWEEN UNBOUNDED PRECEDING  |
             <n> PRECENDING  |
             <n> FOLLOWING  |
             CURRENT ROW
        AND
             UNBOUNDED FOLLOWING  |
             <n> PRECENDING  |
             <n> FOLLOWING  |
             CURRENT ROW

Эти параметры самоочевидны, но на тот случай, если это не так, я вкратце объясню их. В качестве нижней границы кадра значение UNBOUNDED PRECEDING означает, что нет никакой нижней границы; <n> PRECEDING и <n> FOLLOWING означает соответственно число строк перед и после текущей, a CURRENT ROW означает, что начальным является текущая строка.

Что касается верхней границы, то здесь параметры очень похожи, за исключением того, что если не нужно ограничения сверху, нужно указывать UNBOUNDED FOLLOWING.

В качестве примера посмотрите на следующий кадр:

PARTITION BY empid
ORDER BY ordermonth
ROWS BETWEEN UNBOUNDED PRECEDING
   AND CURRENT ROW;

Оконный кадр, создаваемый для каждой строки, содержит все строки от первого месяца до текущей строки. Заметьте, что вы можете использовать ROWS UNBOUNDED PRECEDING как в качестве команды для выбора строк от первой до текущей. Но если вообще опустить часть экстента оконного кадра, оставив только части секционирования и упорядочения, то по умолчанию вы получите немного другой результат. Мы поговорим об этом позже при обсуждении параметра RANGE.

В качестве первого примера использования параметра ROWS приведу следующий запрос представления Sales.EmpOrders (после запроса приводится сокращенный результат выполнения):

SELECT empid, ordermonth, qty,
  SUM(qty) OVER(PARTITION BY empid
                ORDER BY ordermonth
                ROWS BETWEEN UNBOUNDED PRECEDING
                         AND CURRENT ROW) AS runqty
FROM Sales.EmpOrders;

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

SELECT empid, ordermonth, qty,
  SUM(qty) OVER(PARTITION BY empid
                ORDER BY ordermonth
                ROWS UNBOUNDED PRECEDING) AS runqty
FROM Sales.EmpOrders;

Следующий рисунок иллюстрирует строки, стрелками показаны соответствующие им кадры.

В качестве второго примера использования параметра ROWS создадим три оконные функции с тремя определениями кадра:

SELECT empid, ordermonth, 
  MAX(qty) OVER(PARTITION BY empid
                ORDER BY ordermonth
                ROWS BETWEEN 1 PRECEDING
                         AND 1 PRECEDING) AS prvqty,
  qty AS curqty,
  MAX(qty) OVER(PARTITION BY empid
                ORDER BY ordermonth
                ROWS BETWEEN 1 FOLLOWING
                         AND 1 FOLLOWING) AS nxtqty,
  AVG(qty) OVER(PARTITION BY empid
                ORDER BY ordermonth
                ROWS BETWEEN 1 PRECEDING
                         AND 1 FOLLOWING) AS avgqty
FROM Sales.EmpOrders;

При вычислении атрибута prvqty определяется кадр, состоящий из строк между 1 предыдущей и 1 предыдущей. Это означает, что кадр содержит только предыдущую строку в секции. Агрегат MAX, применяемый здесь к атрибуту qty, излишен, потому что в кадре будет максимум одна строка. Максимальное значение qty будет значение qty в этой строке или NULL. Если в кадре строк нет (то есть, если текущая строка является первой в секции). На рисунке ниже показаны кадры, соответствующие каждой строке и содержащие не более одной строки (ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING):

Заметьте, что у первой строки в секции нет соответствующей предыдущей, поэтому значение prvqty в первой строке секции равно NULL.

Аналогично при вычислении атрибута nxtqty определяется кадр, состоящий из строк между 1 последующей и 1 последующей, то есть имеется в виду только следующая строка. Агрегат MAX(qty) возвращает значение qty из предыдущей строки. На следующем рисунке показаны кадры, соответствующие каждой строке (ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING):

Так как за последней строкой в секции никаких других строк нет, значение nxtqty в последней строке секции равно NULL.

В статье, посвященной функциям смещения, вы увидите более компактные способы получения значения одной строки, отстоящей от текущей на определенное значение смещения. В частности, вы увидите, как получить значение из предыдущей строки с помощью функции LAG и из следующей строки — с помощью функции LEAD.

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

Как и предыдущих примерах, нет строки, предшествующей первой строке, и строки, последующей за последней. Функция AVG корректно делит сумму на количество строк в кадре.

В совокупности элементы секционирования и упорядочения в представлении EmpOrders уникальны. Это означает, что одна комбинация значений empid и ordermonth в рамках представления не повторяется. А это в свою очередь означает, что три использованных в нашем запросе вычисления являются детерминистическими, то есть одному определенному состоянию входных данных запроса соответствует только один правильный результат.

Однако ситуация меняется, если комбинация элементов секционирования и упорядочения не уникальна. Тогда вычисления с параметром ROWS не являются детерминистическими. Продемонстрирую это поведение на примере. Выполните следующий код, чтобы создать и наполнить данными таблицу T1:

SET NOCOUNT ON;
USE TSQL2012;
IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1
(
  keycol INT         NOT NULL CONSTRAINT PK_T1 PRIMARY KEY,
  col1   VARCHAR(10) NOT NULL
);

INSERT INTO dbo.T1 VALUES
  (2, 'A'),(3, 'A'),
  (5, 'B'),(7, 'B'),(11, 'B'),
  (13, 'C'),(17, 'C'),(19, 'C'),(23, 'C');

Посмотрите на следующий запрос и результат его работы:

SELECT keycol, col1,
  COUNT(*) OVER(ORDER BY col1
                ROWS BETWEEN UNBOUNDED PRECEDING
                         AND CURRENT ROW) AS cnt
FROM dbo.T1;

Заметьте, что разным строкам с одинаковым секционированием (в данном случае неприменимо) и упорядочением назначаются разные порядковые номера. Причина в том, что упорядочение равноправных строк (то есть строк с одинаковыми параметрами секционирования и упорядочения) выполняется произвольно, иначе говоря, определяется реализацией. В SQL Server оно просто зависит от оптимизации. Например, если я создам следующий индекс:

CREATE UNIQUE INDEX idx_col1D_keycol ON dbo.T1(col1 DESC, keycol);

то при повторном выполнении запроса я получу следующий результат:

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

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

SELECT keycol, col1,
  COUNT(*) OVER(ORDER BY col1, keycol
                ROWS BETWEEN UNBOUNDED PRECEDING
                         AND CURRENT ROW) AS cnt
FROM dbo.T1;

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

Параметр RANGE экстента оконного кадра

Стандартный SQL также позволяет определять экстент оконного кадра с использованием параметра RANGE. Вот перечень возможностей для верхней и нижней границ, или конечных точек, в кадре:

RANGE BETWEEN UNBOUNDED PRECEDING |
             <n> PRECENDING |
             <n> FOLLOWING |
             CURRENT ROW
        AND
             UNBOUNDED FOLLOWING |
             <n> PRECENDING |
             <n> FOLLOWING |
             CURRENT ROW

Этот параметр предназначен для более динамического назначения верхней и нижней границ — как логической разницы между текущим порядковым значением строки и значением границы. О разнице можно говорить так: «Дайте мне общее количество за последние три периода активности». Или так: «Дайте мне общее количество за период, начинающийся за два месяца до текущего периода и оканчивающийся текущим периодом». Первый запрос ориентирован на ROWS, а второй — представлен в стиле RANGE. (Вскоре мы поговорим об этом примере подробнее.)

В SQL Server 2012 поддержка RANGE реализована не полностью. Сейчас поддерживаются только определители границ кадра UNBOUNDED и CURRENT ROW. Отсутствует поддержка временного типа INTERVAL, который в связке с параметром RANGE мог бы существенно повысить гибкость определения кадра. Вот пример: следующий запрос определяет кадр с началом за два месяца до текущего и текущим месяцем в качестве конца кадра (этот запрос невозможно выполнить в SQL Server 2012):

-- Не поддерживается в SQL Server
SELECT empid, ordermonth, qty,
  SUM(qty) OVER(PARTITION BY empid
                ORDER BY ordermonth
                RANGE BETWEEN INTERVAL '2' MONTH PRECEDING
                          AND CURRENT ROW) AS sum3month
FROM Sales.EmpOrders;

Это отличается от использования ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, даже если месяц заказа является уникальным для каждого сотрудника. Представьте себе, что сотрудник может не заключать никаких сделок в определенные месяцы. При использовании ROWS кадр просто начинается за две строки до текущей, что может охватывать период больший, чем два месяца до текущего. При наличии RANGE кадр более динамичен и может начинаться за два месяца до текущего, независимо от того, сколько строк попадает в этот период. Следующий рисунок иллюстрирует кадры, соответствующие некоторым строкам в базовом запросе:

Заметьте, что число строк в различных кадрах бывает 1, 2 и 3. Так происходит потому, что некоторые сотрудники в некоторые месяцы могут не заключать сделок.

Как и ROWS, параметр RANGE также поддерживает лаконичную форму выражения желаемого. Если не указать верхнюю границу, предполагается, что это CURRENT ROW (то есть текущая строка). Поэтому в нашем примере вместо RANGE BETWEEN INTERVAL '2' MONTH PRECEDING AND CURRENT ROW мы можем использовать только RANGE INTERVAL '2' MONTH PRECEDING. Но, как я уже сказал, этот запрос не будет работать в SQL Server 2012 из-за неполной поддержки параметра RANGE и отсутствия поддержки типа INTERVAL. На данный момент приходится пользоваться альтернативными методами. Решить задачу можно с использованием существующих оконных функций, но решения эти непростые. Другой вариант — воспользоваться традиционными конструкциями, например вложенными запросами, как в этом примере:

SELECT empid, ordermonth, qty,
  (SELECT SUM(qty)
   FROM Sales.EmpOrders AS O2
   WHERE O2.empid = O1.empid
     AND O2.ordermonth BETWEEN DATEADD(month, -2, O1.ordermonth)
                           AND O1.ordermonth) AS sum3month
FROM Sales.EmpOrders AS O1;

Как говорилось, SQL Server 2012 не поддерживает параметр RANGE с UNBOUNDED и CURRENT ROW в качестве границ кадра. Например, оконная функция в следующем запросе вычисляет нарастающий итог с начала работы сотрудника до текущего месяца:

SELECT empid, ordermonth, qty,
  SUM(qty) OVER(PARTITION BY empid
                ORDER BY ordermonth
                RANGE BETWEEN UNBOUNDED PRECEDING
                          AND CURRENT ROW) AS runqty
FROM Sales.EmpOrders;

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

Как вы помните, если не указать верхнюю границу, по умолчанию подразумевается текущая строка (CURRENT ROW). Поэтому вместо использования RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, можно применить более короткую форму RANGE UNBOUNDED PRECEDING:

SELECT empid, ordermonth, qty,
  SUM(qty) OVER(PARTITION BY empid
                ORDER BY ordermonth
                RANGE UNBOUNDED PRECEDING) AS runqty
FROM Sales.EmpOrders;

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

SELECT empid, ordermonth, qty,
  SUM(qty) OVER(PARTITION BY empid
                ORDER BY ordermonth) AS runqty
FROM Sales.EmpOrders;

Получается существенное сокращение объема кода. Если вы внимательно следили за примерами использования ROWS и RANGE, то сейчас вправе спросить, есть ли какая-либо разница между ними при использовании в качестве границ кадра только UNBOUNDED и CURRENT ROW. Для демонстрации разницы я воспользуюсь таблицей T1, которую мы создали ранее. Напомню, что при использовании параметра ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (или ROWS UNBOUNDED PRECEDING) кадр заканчивается текущей строкой и не содержит последующих строк:

SELECT keycol, col1,
  COUNT(*) OVER(ORDER BY col1
                ROWS BETWEEN UNBOUNDED PRECEDING
                         AND CURRENT ROW) AS cnt
FROM dbo.T1;

Вот похожий запрос, но здесь вместо ROWS используется RANGE:

SELECT keycol, col1,
  COUNT(*) OVER(ORDER BY col1
                RANGE BETWEEN UNBOUNDED PRECEDING
                          AND CURRENT ROW) AS cnt
FROM dbo.T1;

При использовании RANGE и указании в качестве верхней границы CURRENT ROW по умолчанию включаются сопутствующие строки. Хотя параметр CURRENT ROW и означает «текущая строка», это в реальности означает текущее значение упорядочения. На понятийном уровне, выраженное в виде предиката это означает <строка_окна>месяц_заказа <= <текущая строка >месяц_заказа.

Исключения оконных кадров

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

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

EXCLUDE CURRENT ROW

Исключает текущую строку.

EXCLUDE GROUP

Исключает текущую строку и сопутствующие ей строки.

EXCLUDE TIES

Оставить текущую, но исключить сопутствующие строки.

EXCLUDE NO OTHERS

(по умолчанию) Не исключать никаких других строк.

Для демонстрации исключения оконных кадров я воспользуюсь таблицей T1. Далее приводятся четыре запроса с разными вариантами исключения оконных кадров, за которыми следует ожидаемый результат (в соответствии с моей интерпретацией стандарта, потому что этот код не поддерживается SQL Server 2012 или любой другой известной мне СУБД):

/*
-- EXCLUDE не поддерживается в SQL Server 2012
-- EXCLUDE NO OTHERS (не исключаем строки)
SELECT keycol, col1,
  COUNT(*) OVER(ORDER BY col1
                ROWS BETWEEN UNBOUNDED PRECEDING
                         AND CURRENT ROW
                EXCLUDE NO OTHERS) AS cnt
FROM dbo.T1;

keycol      col1       cnt
----------- ---------- -----------
2           A          1
3           A          2
5           B          3
7           B          4
11          B          5
13          C          6
17          C          7
19          C          8
23          C          9

-- EXCLUDE CURRENT ROW (исключить текущую строку)
SELECT keycol, col1,
  COUNT(*) OVER(ORDER BY col1
                ROWS BETWEEN UNBOUNDED PRECEDING
                         AND CURRENT ROW
                EXCLUDE CURRENT ROW) AS cnt
FROM dbo.T1;

keycol      col1       cnt
----------- ---------- -----------
2           A          0
3           A          1
5           B          2
7           B          3
11          B          4
13          C          5
17          C          6
19          C          7
23          C          8

-- EXCLUDE GROUP (исключаем текущую строку и строки,
-- расположенные с ней на одном уровне)
SELECT keycol, col1,
  COUNT(*) OVER(ORDER BY col1
                ROWS BETWEEN UNBOUNDED PRECEDING
                         AND CURRENT ROW
                EXCLUDE EXCLUDE GROUP) AS cnt
FROM dbo.T1;

keycol      col1       cnt
----------- ---------- -----------
2           A          0
3           A          0
5           B          2
7           B          2
11          B          2
13          C          5
17          C          5
19          C          5
23          C          5

-- EXCLUDE TIES (оставляем текущую строку и удаляем строки,
-- расположенные с ней на одном уровне)
SELECT keycol, col1,
  COUNT(*) OVER(ORDER BY col1
                ROWS BETWEEN UNBOUNDED PRECEDING
                         AND CURRENT ROW
                EXCLUDE TIES) AS cnt
FROM dbo.T1;

keycol      col1       cnt
----------- ---------- -----------
2           A          1
3           A          1
5           B          3
7           B          3
11          B          3
13          C          6
17          C          6
19          C          6
23          C          6
*/