Агрегатные вычисления с DISTINCT

В версии SQL Server 2017 CTP2 не предусмотрены оконные агрегатные вычисления с ключевым словом DISTINCT в T-SQL. В данной статье рассматривается четыре способа обойти это ограничение.

T-SQL поддерживает сгруппированные агрегатные вычисления с ключевым словом DISTINCT, например COUNT (DISTINCT <выражение>), но в версии SQL Server 2017 CTP2 не поддерживаются оконные агрегатные вычисления, учитывающие только различные значения аргумента. На сайте Microsoft Connect есть открытый раздел (https://connect.microsoft.com/SQLServer/feedback/details/254393/over-clause-enhancement-request-distinct-clause-for-aggregate-functions), где обсуждается возможность ввода этой функции. Далее в статье я расскажу о том, что такое агрегатные вычисления с ключевым словом DISTINCT, приведу пример задачи, для которой требуются такие вычисления, покажу желательный, но отсутствующий синтаксис, который решил бы эту проблему, и представлю четыре поддерживаемых разработчиком обходных приема. Кроме того, мы рассмотрим тесты производительности для сравнения четырех решений, как с планами, использующими обработку только в построчном режиме, так и с планами с пакетным режимом обработки. В моих примерах используется тестовая база данных с именем TSQLV4. Сценарий для установки тестовой базы данных можно загрузить по адресу: tsql.solidq.com/SampleDatabases/TSQLV4.zip, а найти ER-диаграмму по адресу tsql.solidq.com/SampleDatabases/Diagrams/TSQLV4.jpg.

Задача и отсутствующий синтаксис

Как и в случае со сгруппированными агрегатными вычислениями с ключевым словом DISTINCT, оконные агрегатные вычисления с ключевым словом DISTINCT учитывают только уникальные вхождения аргумента. Но в отличие от сгруппированных вычислений, оконные вычисления не скрывают подробности. Например, предположим, что у нас есть таблица T1 с данными, показанными на экране 1.

Экран 1. Данные таблицы T1

Требуется вычислить для каждой строки как число различных значений (столбец val) в текущей группе (столбец grp), так и общее число различных значений. На экране 2 показан желаемый результат для приведенных тестовых данных.

Экран 2. Желаемый результат для тестовых данных

Если бы оконные агрегатные вычисления с параметром DISTINCT поддерживались в T-SQL, эту задачу можно было бы решить так, как показано в листинге 1.

Примером практического применения таких вычислений может быть задача с таблицами Sales.Orders и Sales.OrderDetails в тестовой базе данных TSQLV4. В первой содержатся данные заголовка заказа, а в последней — данные строки заказа. Для нашей задачи важны столбцы orderid, orderdate и custid из таблицы Sales.Orders и столбцы orderid, productid, qty и unitprice из таблицы Sales.OrderDetails. Предположим, что клиенты получают скидки в зависимости от заказов, сделанных за предыдущий месяц, и нам нужно составить запрос для расчета этих скидок. Правила для начисления скидок приведены ниже.

1. Процент скидки по номенклатуре рассчитывается так: 35% умножается на количество разных продуктов, заказанных клиентом в течение рассматриваемого месяца, и делится на количество продуктов, заказанных всеми клиентами в течение рассматриваемого месяца. Например, предположим, что в январе 2016 года клиент 1 заказал 5 разных продуктов, клиент 2 заказал 10 разных продуктов, и все клиенты заказали 50 разных продуктов. Размер скидки по номенклатуре для клиента 1 в январе 2016 года составит 35% × 5/50 = 3,5%, а для клиента 2 — 35% × 10/50 = 7%.
2. Процент скидки за количество рассчитывается для каждой строки заказа следующим образом: если количество составляет не менее 10, то скидка будет 6%, в противном случае скидка не начисляется. Например, если количество заказанного продукта в строке заказа 15, то скидка за количество составит 6%, но для строки заказа с количеством 9 она не действует.
3. Окончательный процент скидки для строки заказа рассчитывается следующим образом: если сумма скидки по номенклатуре и скидки за количество не превышает 10%, то применяется сумма обеих скидок, в противном случае — 10%. Например, вспомните, что в приведенном выше примере в январе 2016 года клиент 1 получает скидку по номенклатуре 3,5%, а клиент 2 — скидку по номенклатуре 7%. В строке заказа для клиента 1, где применяется скидка за количество 6%, сумма скидки по номенклатуре (3,5%) и скидки за количество (6%) составляет 9,5%. Поскольку скидка не превышает максимального значения 10% для строки заказа, скидка 9,5% считается окончательной для данной строки заказа. Но в строке заказа для клиента 2, где применяется скидка за количество, сумма скидки по номенклатуре (7%) и скидки за количество (6%) превышает максимальное значение 10%. Поэтому окончательная скидка для этой строки заказа составляет 10%.

Примените окончательную скидку строки заказа к значению строки заказа (pct × qty × unitprice). Сумма всех скидок строки заказа для клиента должна быть возвращена клиенту как скидка за месяц.

Например, ваше решение для января 2016 года должно принести такой результат, как показано на экране 3.

Экран 3. Результат расчета скидок для января 2016

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

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

Решение 1. Использование сгруппированных запросов

Первое представленное решение (назовем его Решением 1) покажется многим самым очевидным. В листинге 3 приводится полный программный код решения.

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

Обобщенное табличное выражение CustCounts вычисляет количество сгруппированных отдельных продуктов для каждого клиента из Base, а обобщенное табличное выражение AllCount вычисляет общее количество отдельных продуктов из Base.

Внешний запрос объединяет Base, CustCounts и AllCount, чтобы сопоставить каждой строке заказа количество отдельных продуктов соответствующего клиента и общее количество отдельных продуктов. Затем внешний запрос применяет логику скидок на основе перечисленных выше правил и, наконец, группирует данные по custid и агрегирует скидки по строкам, чтобы вычислить общую скидку клиента.

План выполнения для Решения 1 показан на рисунке 1. Этот план был применен на моем компьютере к большим таблицам с именами OrdersBig (приблизительно 1 млн строк) и OrderDetailsBig (приблизительно 3 млн строк). Тестовые данные для более объемных таблиц и пересмотренный код решения приведены далее в разделе о тестировании производительности.

Рисунок 1. План для Решения 1 в построчном режиме

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

Естественно, это решение пригодно и для других агрегатных вычислений с ключевым словом DISTINCT, таких как SUM (DISTINCT …).

Решение 2. Использование ROW_NUMBER

Во втором решении (Решение 2) применяется довольно простой подход. Здесь нумеруются строки в каждой отдельной группе, обнуляются все, кроме одного вхождения каждого уникального значения, а затем применяется агрегирование к значениям, отличным от NULL, как регулярная оконная агрегатная функция. В листинге 4 приводится полный программный код решения.

Запрос в обобщенном табличном выражении C1 назначает номера строк, чтобы пронумеровать строки в каждом уникальном клиенте и продукте (rownumcust) и номера строк, чтобы пронумеровать строки в каждом уникальном продукте (rownumall). Обратите внимание на использование произвольного оконного упорядочения (ORDER BY (SELECT NULL)), поскольку порядок назначения номеров строк в действительности не имеет значения. Единственное условие — уникальные номера строк назначаются начиная с 1.

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

Затем внешний запрос вычисляет окончательную скидку строки и группирует и агрегирует результат для вычисления общей скидки клиента. План для этого запроса (при использовании больших таблиц) показан на рисунке 2.

Рисунок 2. План для Решения 2 в построчном режиме

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

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

Это решение также может работать с другими функциями с ключевым словом DISTINCT, такими как SUM (DISTINCT …).

Решение 3. Использование MAX (DENSE_RANK)

В третьем решении (Решение 3) используется общая форма, аналогичная Решению 2, но вместо оконных функций ROW_NUMBER и COUNT применяются оконные функции DENSE_RANK и MAX. Учтите, что максимальное значение плотности диапазона представляет собой просто количество различных объектов. В предыдущем решении в C1 вычисление ROW_NUMBER заменяется вычислением DENSE_RANK, основанным на тех же оконных характеристиках. В C2 вычисление COUNT заменяется на MAX, применяемое к результатам плотности диапазона, полученным в C1. Остальное без изменений. В листинге 5 приводится программный код полного решения.

План для Решения 3 показан на рисунке 3.

Рисунок 3. План для Решения 3 в построчном режиме

Очевидно, этот план очень похож на план для Решения 2. Функция DENSE_RANK рассчитывается очень похоже на ROW_NUMBER, а MAX рассчитывается похоже на COUNT. Поэтому неудивительно, что это решение также довольно медленное. На моем компьютере оно выполнялось 24 секунды.

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

Решение 4. Использование DENSE_RANK asc + DENSE_RANK desc — 1

Наиболее творческий подход применен в четвертом рассматриваемом решении (Решение 4). Оно появилось как ответ на вопрос на форуме stackoverflow.com по адресу: http://stackoverflow.com/questions/11202878/partition-function-count-over-possible-using-distinct.

Его идея заключается в том, что вычисляются два значения плотности диапазона — одно нарастающее (drkasc), другое убывающее (drkdesc). Сумма двух величин всегда на единицу больше количества уникальных объектов, поэтому, чтобы получить количество уникальных объектов, нужно сложить две величины и вычесть единицу. Для простоты метод будет продемонстрирован на примере упомянутой выше таблицы T1. Используйте программный код листинга 6, чтобы создать и заполнить таблицу.

Программный код листинга 7 вычисляет общее количество различных величин (в столбце val) наряду с подробными строками.

Этот программный код формирует выходные данные, показанные на экране 4.

Экран 4. Результаты работы листинга 7

Очевидно, что нарастающая плотность диапазона начинается с 1 и увеличивается на единицу на каждом шаге, а убывающая плотность диапазона начинается с количества уникальных объектов и уменьшается на единицу на каждом шаге. Поэтому их сумма во всех строках всегда на единицу больше количества уникальных объектов. Таким образом, чтобы получить количество уникальных объектов, сложите две величины и вычтите 1.

Как применяется эта остроумная идея к поставленной в статье задаче в Решении 4, показано в листинге 8. План для данного решения приведен на рисунке 4.

Рисунок 4. План для Решения 4 в построчном режиме

Большое достоинство плана состоит в том, что базовые данные считываются и объединяются только один раз и буферизации не происходит. К сожалению, чтобы обеспечить вычисление плотности диапазона, приходится дважды сортировать большой объем данных. При использовании только операторов построчного режима для выполнения плана на моем компьютере потребовалось 15 секунд. Это лучше, чем в случае Решения 2 и Решения 3, но медленнее, чем в Решении 1.

Как мы видим, Решение 4 ограничено подсчетом уникальных объектов и не может использоваться для других типов агрегатных вычислений с ключевым словом DISTINCT.

Тест производительности

В этом разделе приведены тестовые данные и переработанные запросы, использованные мною для тестирования производительности решений с более объемными таблицами, нежели таблицы в тестовой базе данных.

Используйте программный код листинга 9 для создания и заполнения таблиц Sales.OrdersBig (примерно 1 млн строк) и Sales.OrderDetailsBig (приблизительно 3 млн строк).

Используйте переработанные запросы, приведенные в листинге 10, для тестирования четырех описанных решений с более объемными таблицами.

Пакетная обработка

Во всех четырех решениях используются расчеты, которые можно выполнить гораздо эффективнее с помощью пакетных операций, таких как группирование, статистическая обработка и оконные функции, особенно начиная с версии SQL Server 2016, в которой появились операторы агрегированного значения окна и сортировки в пакетном режиме. Если вы создаете индексы columnstore для таблиц, это естественным образом позволяет оптимизатору SQL Server задействовать пакетную обработку. Или, как описано в статье «Агрегатный оконный оператор пакетного режима в SQL Server 2016» (три части статьи опубликованы в журнале Windows IT Pro/RE № 9, 10 и 11 соответственно), вы могли определить фиктивный пустой фильтруемый индекс columnstore. Это также позволит оптимизатору задействовать пакетный режим, даже если данные организованы с использованием формата rowstore, — достаточно, чтобы создать такой индекс для одной из участвующих таблиц. Например, примените следующий программный код, чтобы создать такой фиктивный индекс для Sales.OrdersBig (предполагается, что используется версия SQL Server 2016 или более новая):

CREATE NONCLUSTERED COLUMNSTORE
   INDEX idx_cs_dummy
ON Sales.OrdersBig (orderid)
WHERE orderid = -1 AND orderid = -2;

Теперь выполним все четыре решения повторно.

План для Решения 1 показан на рисунке 5.

Рисунок 5. План для Решения 1 в пакетном режиме

Основные действия, в том числе объединение, группирование и статистические операции, выполняются операторами хеширования пакетного режима. На моем компьютере это решение было выполнено за 1,825 секунды — треть времени, необходимого для обработки только в построчном режиме. Естественно, производительность этого решения повышается благодаря использованию настоящих, а не фиктивных индексов columnstore. Удивительно, что если необходимо задействовать представление rowstore по другим причинам и нельзя обосновать дублированное представление, то этот прием не связан ни с какими затратами, но дает огромные преимущества.

В трех других решениях (Решение 2, 3 и 4) улучшения при использовании пакетной обработки еще более заметны по сравнению с первоначальными планами построчного режима. Это связано с использованием новых операторов агрегированного значения окна и сортировки в пакетном режиме, эффективность которых намного выше, чем у аналогов в построчном режиме.

Новый план для Решения 2 с пакетной обработкой показан на рисунке 6.

Рисунок 6. План для Решения 2 в пакетном режиме

Это решение было выполнено за 2,748 секунды на моем компьютере, по сравнению с 24 секундами для первоначального плана в построчном режиме.

План для Решения 3 с пакетной обработкой показан на рисунке 7.

Рисунок 7. План для Решения 3 в пакетном режиме

Это решение было выполнено за 2,934 секунды на моем компьютере, по сравнению с 24 секундами для первоначального плана в построчном режиме.

План для Решения 4 с пакетной обработкой показан на рисунке 8.

Рисунок 8. План для Решения 4 в пакетном режиме

Это решение было выполнено за 2,332 секунды на моем компьютере, по сравнению с 15 секундами для первоначального плана в построчном режиме.

Итак, в версии SQL Server 2017 CTP2 отсутствует поддержка оконных агрегатных вычислений с ключевым словом DISTINCT со стороны T-SQL. В этой статье мы рассмотрели четыре обходных приема: использование объединения и группирования, номеров строк, плотности диапазона и максимума и применение возрастающих и убывающих «плотных» рангов. Первый метод оказался более эффективным даже при использовании пакетной обработки, но все решения в пакетном режиме выполнялись довольно быстро, в течение 2-3 секунд. Поэтому в случае пакетной обработки вы можете по-прежнему пользоваться привычным решением, не беспокоясь о производительности.

Если вы считаете, что встроенная поддержка оконных агрегатных вычислений с ключевым словом DISTINCT более эффективна, нежели применение обходных приемов, проголосуйте за эту функцию по адресу: connect.microsoft.com/SQLServer/feedback/details/254393/over-clause-enhancement-request-distinct-clause-for-aggregate-functions.

SELECT id, grp, val,
  COUNT(DISTINCT val) OVER(PARTITION BY grp) AS countgrp,
  COUNT(DISTINCT val) OVER() AS countall
FROM dbo.T1;

WITH C AS
(
  SELECT O.custid, O.orderid, OD.productid, OD.qty, OD.unitprice,
    -- varietydiscount = 35% * #distinct products per customer
    --                       / #distinct products
    0.35 * COUNT(DISTINCT OD.productid) OVER(PARTITION BY O.custid)
      / COUNT(DISTINCT OD.productid) OVER() AS varietydiscount,
    -- qtydiscount = if qty >= 10 then 6% else none
    CASE WHEN qty >= 10 THEN 0.06 ELSE 0 END AS qtydiscount
  FROM Sales.Orders AS O
    INNER JOIN Sales.OrderDetails AS OD
      ON O.orderid = OD.orderid
  WHERE O.orderdate >= '20160101'
    AND O.orderdate < '20160201'
)
SELECT custid,
  CAST(SUM( qty * unitprice * linediscount ) AS NUMERIC(12, 2)) AS rebate
FROM C
  -- line discount = min(0.10, varietydiscount + qtydiscount)
  CROSS APPLY ( VALUES( CASE
                          WHEN varietydiscount + qtydiscount > 0.10 THEN 0.10
                          ELSE varietydiscount + qtydiscount
                        END ) ) AS A(linediscount)
GROUP BY custid;

WITH Base AS
(
  SELECT O.custid, O.orderid, OD.productid, OD.qty, OD.unitprice
  FROM Sales.Orders AS O
    INNER JOIN Sales.OrderDetails AS OD
      ON O.orderid = OD.orderid
  WHERE O.orderdate >= '20160101'
    AND O.orderdate < '20160201'
),
CustCounts AS
(
  SELECT custid, COUNT(DISTINCT productid) AS custproductcnt
  FROM Base
  GROUP BY custid
),
AllCount AS
(
  SELECT COUNT(DISTINCT productid) AS allproductcnt
  FROM Base
)
SELECT B.custid,
  CAST(SUM( B.qty * B.unitprice * A2.linediscount ) AS NUMERIC(12, 2)) AS rebate
FROM Base AS B
  INNER JOIN CustCounts AS CC
    ON B.custid = CC.custid
  CROSS JOIN AllCount AS AC
  CROSS APPLY ( VALUES( 0.35 * CC.custproductcnt / AC.allproductcnt,
                        CASE WHEN B.qty >= 10 THEN 0.06 ELSE 0 END ) )
              AS A1(varietydiscount, qtydiscount)
  CROSS APPLY ( VALUES( CASE
                          WHEN A1.varietydiscount + A1.qtydiscount > 0.10
                            THEN 0.10
                          ELSE A1.varietydiscount + A1.qtydiscount
                        END ) ) AS A2(linediscount)
GROUP BY B.custid;

WITH C1 AS
(
  SELECT O.custid, O.orderid, OD.productid, OD.qty, OD.unitprice,
    ROW_NUMBER() OVER(PARTITION BY O.custid, OD.productid
                      ORDER BY (SELECT NULL)) AS rownumcust,
    ROW_NUMBER() OVER(PARTITION BY OD.productid
                      ORDER BY (SELECT NULL)) AS rownumall
  FROM Sales.Orders AS O
    INNER JOIN Sales.OrderDetails AS OD
      ON O.orderid = OD.orderid
  WHERE O.orderdate >= '20160101'
    AND O.orderdate < '20160201'
),
C2 AS
(
  SELECT custid, orderid, productid, qty, unitprice,
    0.35 * COUNT(CASE WHEN rownumcust = 1 THEN productid END)
                 OVER(PARTITION BY custid)
      / COUNT(CASE WHEN rownumall = 1 THEN productid END)
              OVER() AS varietydiscount,
  CASE WHEN qty >= 10 THEN 0.06 ELSE 0 END AS qtydiscount
  FROM C1
)
SELECT custid,
  CAST(SUM( qty * unitprice * linediscount ) AS NUMERIC(12, 2)) AS rebate
FROM C2
  CROSS APPLY ( VALUES( CASE
                          WHEN varietydiscount + qtydiscount > 0.10 THEN 0.10
                          ELSE varietydiscount + qtydiscount
                        END ) ) AS A(linediscount)
GROUP BY custid;

WITH C1 AS
(
  SELECT O.custid, O.orderid, OD.productid, OD.qty, OD.unitprice,
    DENSE_RANK() OVER(PARTITION BY O.custid ORDER BY OD.productid) AS drkcust,
    DENSE_RANK() OVER(ORDER BY OD.productid) AS drkall
  FROM Sales.Orders AS O
    INNER JOIN Sales.OrderDetails AS OD
      ON O.orderid = OD.orderid
  WHERE O.orderdate >= '20160101'
    AND O.orderdate < '20160201'
),
C2 AS
(
  SELECT custid, orderid, productid, qty, unitprice,
    0.35 * MAX(drkcust) OVER(PARTITION BY custid)
      / MAX(drkall) OVER() AS varietydiscount,
  CASE WHEN qty >= 10 THEN 0.06 ELSE 0 END AS qtydiscount
  FROM C1
)
SELECT custid,
  CAST(SUM( qty * unitprice * linediscount ) AS NUMERIC(12, 2)) AS rebate
FROM C2
  CROSS APPLY ( VALUES( CASE
                          WHEN varietydiscount + qtydiscount > 0.10 THEN 0.10
                          ELSE varietydiscount + qtydiscount
                        END ) ) AS A(linediscount)
GROUP BY custid;

DROP TABLE IF EXISTS dbo.T1;

SELECT id, grp, val
INTO dbo.T1
FROM ( VALUES ( 1, 'GRP1', 'A'),
              ( 2, 'GRP1', 'A'),
              ( 3, 'GRP1', 'B'),
              ( 4, 'GRP1', 'C'),
              ( 5, 'GRP1', 'C'),
              ( 6, 'GRP2', 'A'),
              ( 7, 'GRP2', 'A'),
              ( 8, 'GRP2', 'D'),
              ( 9, 'GRP2', 'D'),
              (10, 'GRP2', 'D') ) AS T1(id, grp, val);

ALTER TABLE dbo.T1 ADD CONSTRAINT PK_T1 PRIMARY KEY(id);

WITH C AS
(
  SELECT id, grp, val,
    DENSE_RANK() OVER(ORDER BY val) AS drkasc,
    DENSE_RANK() OVER(ORDER BY val DESC) AS drkdesc
  FROM dbo.T1
)
SELECT id, grp, val, drkasc, drkdesc,
  drkasc + drkdesc - 1 AS distinctcount
FROM C
ORDER BY val;

WITH C AS
(
  SELECT O.custid, O.orderid, OD.productid, OD.qty, OD.unitprice,
    0.35 * ( DENSE_RANK() OVER(PARTITION BY O.custid
                               ORDER BY OD.productid)
               + DENSE_RANK() OVER(PARTITION BY O.custid
                                   ORDER BY OD.productid DESC) - 1 )
         / ( DENSE_RANK() OVER(ORDER BY OD.productid)
               + DENSE_RANK() OVER(ORDER BY OD.productid DESC) - 1 )
      AS varietydiscount,
  CASE WHEN qty >= 10 THEN 0.06 ELSE 0 END AS qtydiscount
  FROM Sales.Orders AS O
    INNER JOIN Sales.OrderDetails AS OD
      ON O.orderid = OD.orderid
  WHERE O.orderdate >= '20160101'
    AND O.orderdate < '20160201'
)
SELECT custid,
  CAST(SUM( qty * unitprice * linediscount ) AS NUMERIC(12, 2)) AS rebate
FROM C
  CROSS APPLY ( VALUES( CASE
                          WHEN varietydiscount + qtydiscount > 0.10 THEN 0.10
                          ELSE varietydiscount + qtydiscount
                        END ) ) AS A(linediscount)
GROUP BY custid;

USE TSQLV4;
DROP TABLE IF EXISTS Sales.OrderDetailsBig, Sales.OrdersBig;
SELECT ISNULL(N.n * 100000 + O.orderid, 0) AS orderid,
  O.custid, O.empid, O.orderdate, O.requireddate, O.shippeddate,
  O.shipperid, O.freight, O.shipname, O.shipaddress, O.shipcity,
  O.shipregion, O.shippostalcode, O.shipcountry
INTO Sales.OrdersBig
FROM Sales.Orders AS O
  CROSS JOIN dbo.Nums AS N
WHERE N.n <= 20000
  AND O.orderdate >= '20160101'
  AND O.orderdate < '20160201';
CREATE UNIQUE CLUSTERED INDEX idx_od_oid ON Sales.OrdersBig(orderdate, orderid);
ALTER TABLE Sales.OrdersBig ADD CONSTRAINT PK_OrdersBig
  PRIMARY KEY NONCLUSTERED(orderid);
CREATE INDEX idx_cid_od ON Sales.OrdersBig(custid, orderdate);
SELECT ISNULL(N.n * 100000 + OD.orderid, 0) AS orderid,
  OD.productid, OD.unitprice, OD.qty, OD.discount
INTO Sales.OrderDetailsBig
FROM Sales.OrderDetails AS OD
  CROSS JOIN dbo.Nums AS N
WHERE OD.orderid IN
  (SELECT O.orderid
   FROM Sales.Orders AS O
   WHERE O.orderdate >= '20160101'
     AND O.orderdate < '20160201')
  AND N.n <= 20000;
ALTER TABLE Sales.OrderDetailsBig ADD CONSTRAINT PK_OrderDetailsBig
  PRIMARY KEY(orderid, productid);
CREATE NONCLUSTERED INDEX idx_oid_i_pid_qty_price
  ON Sales.OrderDetailsBig(orderid)
  INCLUDE(productid, qty, unitprice);

 -- Решение 1
WITH Base AS
(
  SELECT O.custid, O.orderid, OD.productid, OD.qty, OD.unitprice
  FROM Sales.OrdersBig AS O
    INNER JOIN Sales.OrderDetailsBig AS OD
      ON O.orderid = OD.orderid
  WHERE O.orderdate >= '20160101'
    AND O.orderdate < '20160201'
),
CustCounts AS
(
  SELECT custid, COUNT(DISTINCT productid) AS custproductcnt
  FROM Base
  GROUP BY custid
),
AllCount AS
(
  SELECT COUNT(DISTINCT productid) AS allproductcnt
  FROM Base
)

SELECT B.custid,
  CAST(SUM( B.qty * B.unitprice * A2.linediscount ) AS NUMERIC(12, 2)) AS rebate
FROM Base AS B
  INNER JOIN CustCounts AS CC
    ON B.custid = CC.custid
  CROSS JOIN AllCount AS AC
  CROSS APPLY ( VALUES( 0.35 * CC.custproductcnt / AC.allproductcnt,
                        CASE WHEN B.qty >= 10 THEN 0.06 ELSE 0 END ) )
              AS A1(varietydiscount, qtydiscount)
  CROSS APPLY ( VALUES( CASE
                          WHEN A1.varietydiscount + A1.qtydiscount > 0.10
                            THEN 0.10
                          ELSE A1.varietydiscount + A1.qtydiscount
                        END ) ) AS A2(linediscount)
GROUP BY B.custid;

-- Решение 2
WITH C1 AS
(
  SELECT O.custid, O.orderid, OD.productid, OD.qty, OD.unitprice,
    ROW_NUMBER() OVER(PARTITION BY O.custid, OD.productid
                      ORDER BY (SELECT NULL)) AS rownumcust,
    ROW_NUMBER() OVER(PARTITION BY OD.productid
                      ORDER BY (SELECT NULL)) AS rownumall
  FROM Sales.OrdersBig AS O
    INNER JOIN Sales.OrderDetailsBig AS OD
      ON O.orderid = OD.orderid
  WHERE O.orderdate >= '20160101'
    AND O.orderdate < '20160201'
),

C2 AS
(
  SELECT custid, orderid, productid, qty, unitprice,
    0.35 * COUNT(CASE WHEN rownumcust = 1 THEN productid END)
             OVER(PARTITION BY custid)
      / COUNT(CASE WHEN rownumall = 1 THEN productid END)
          OVER() AS varietydiscount,
  CASE WHEN qty >= 10 THEN 0.06 ELSE 0 END AS qtydiscount
  FROM C1
)
SELECT custid,
  CAST(SUM( qty * unitprice * linediscount ) AS NUMERIC(12, 2)) AS rebate
FROM C2
  CROSS APPLY ( VALUES( CASE
                          WHEN varietydiscount + qtydiscount > 0.10 THEN 0.10
                          ELSE varietydiscount + qtydiscount
                        END ) ) AS A(linediscount)
GROUP BY custid;

-- Решение 3
WITH C1 AS
(
  SELECT O.custid, O.orderid, OD.productid, OD.qty, OD.unitprice,
    DENSE_RANK() OVER(PARTITION BY O.custid ORDER BY OD.productid) AS drkcust,
    DENSE_RANK() OVER(ORDER BY OD.productid) AS drkall
  FROM Sales.OrdersBig AS O
    INNER JOIN Sales.OrderDetailsBig AS OD
      ON O.orderid = OD.orderid
  WHERE O.orderdate >= '20160101'
    AND O.orderdate < '20160201'
),
C2 AS
(
  SELECT custid, orderid, productid, qty, unitprice,
    0.35 * MAX(drkcust) OVER(PARTITION BY custid)
      / MAX(drkall) OVER() AS varietydiscount,
  CASE WHEN qty >= 10 THEN 0.06 ELSE 0 END AS qtydiscount
  FROM C1
)
SELECT custid,
  CAST(SUM( qty * unitprice * linediscount ) AS NUMERIC(12, 2)) AS rebate
FROM C2
  CROSS APPLY ( VALUES( CASE
                          WHEN varietydiscount + qtydiscount > 0.10 THEN 0.10
                          ELSE varietydiscount + qtydiscount
                        END ) ) AS A(linediscount)
GROUP BY custid;

-- Решение 4
WITH C AS
(
  SELECT O.custid, O.orderid, OD.productid, OD.qty, OD.unitprice,
    0.35 * ( DENSE_RANK() OVER(PARTITION BY O.custid
                               ORDER BY OD.productid)
               + DENSE_RANK() OVER(PARTITION BY O.custid
                                   ORDER BY OD.productid DESC) - 1 )
         / ( DENSE_RANK() OVER(ORDER BY OD.productid)
               + DENSE_RANK() OVER(ORDER BY OD.productid DESC) - 1 )
      AS varietydiscount,
  CASE WHEN qty >= 10 THEN 0.06 ELSE 0 END AS qtydiscount
  FROM Sales.OrdersBig AS O
    INNER JOIN Sales.OrderDetailsBig AS OD
      ON O.orderid = OD.orderid
  WHERE O.orderdate >= '20160101'
    AND O.orderdate < '20160201'
)
SELECT custid,
  CAST(SUM( qty * unitprice * linediscount ) AS NUMERIC(12, 2)) AS rebate
FROM C
  CROSS APPLY ( VALUES( CASE
                          WHEN varietydiscount + qtydiscount > 0.10 THEN 0.10
                          ELSE varietydiscount + qtydiscount
                        END ) ) AS A(linediscount)
GROUP BY custid;