Получите DISTINCT COUNT за один проход в SQL Server

Тег к вашему вопросу;

window-functions

предполагает, что у вас есть неплохая идея.

Для РАЗЛИЧНОЕ КОЛИЧЕСТВО брендов, сгруппированных по региону, стране, производителю и периоду: вы можете написать:

Select   Region 
        ,Country
        ,Manufacturer
        ,Brand
        ,Period
        ,Spend
        ,DENSE_RANK() Over (Partition By Region, Country, Manufacturer, Period Order By Brand asc) 
         + DENSE_RANK() Over (Partition By Region, Country, Manufacturer, Period Order By Brand desc) 
         -1 UniqBrandCount
From myTable T1
Order By 1,2,3,4

Сильно заимствовано из этого вопроса: https://dba.stackexchange.com/questions/89031/using-distinct-in-window-function-with-over

Счетчик Distinct не работает, поэтому требуется density_rank. Ранжирование брендов в прямом и обратном порядке с последующим вычитанием 1 дает различный счет.

Функцию суммы также можно переписать с использованием логики PARTITION BY. Таким образом, вы можете использовать разные уровни группировки для каждой агрегации:

SELECT 
[Region]
,[Country]
,[Manufacturer]
,[Brand]
,[Period]
,dense_rank() OVER 
    (PARTITION BY 
     [Region] 
    ,[Country]
    ,[Manufacturer]
    ,[Period] Order by Brand) 
+ dense_rank() OVER 
    (PARTITION BY 
     [Region] 
    ,[Country]
    ,[Manufacturer]
    ,[Period] Order by Brand Desc) 
- 1  
AS [BrandCount]
,SUM([Spend]) OVER
    (PARTITION BY
     [Region] 
    ,[Country]
    ,[Manufacturer]
    ,[Brand]
    ,[Period]) as [Spend]
from
myTable
ORDER BY 1,2,3,4

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

R1  C1  M1  B1  2016    2   5
R1  C1  M1  B1  2017    2   30 --dup1
R1  C1  M1  B1  2017    2   30 --dup1
R1  C1  M1  B2  2016    2   15
R1  C1  M1  B3  2017    2   20
R1  C2  M1  B1  2017    1   5
R1  C2  M2  B4  2017    2   25
R1  C2  M2  B5  2017    2   30
R2  C3  M1  B1  2017    1   35
R2  C3  M2  B4  2017    2   40
R2  C3  M2  B5  2017    2   45

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

Как работает трюк "density_rank"

Рассмотрим эти данные:

Col1    Col2
B       1
B       1
B       3
B       5
B       7
B       9

density_rank () ранжирует данные по количеству отдельных элементов перед текущим плюс 1. Итак:

1->1, 3->2, 5->3, 7->4, 9->5.

В обратном порядке (с использованием desc) получается обратная картина:

1->5, 3->4, 5->3, 7->2, 9->1:

Сложение этих рангов дает одно и то же значение:

1+5 = 2+4 = 3+3 = 4+2 = 5+1 = 6

Формулировка здесь полезна,

(number of distinct items before + 1) + (number of distinct items after + 1) 
= number of distinct OTHER items before AND after + 2 
= Total number of distinct items + 1

Итак, чтобы получить общее количество различных элементов, сложите ascending и descending density_ranks вместе и вычтите 1.

Идея двойного dense_rank означает, что вам нужно две сортировки (при условии, что не существует индекса, обеспечивающего порядок сортировки). Предполагая, что нет брендов NULL (как эта идея), вы можете использовать один dense_rank и оконный MAX, как показано ниже (демонстрация)

WITH T1
     AS (SELECT *,
                DENSE_RANK() OVER (PARTITION BY [Region], [Country], [Manufacturer], [Period] ORDER BY Brand) AS [dr]
         FROM   myTable),
     T2
     AS (SELECT *,
                MAX([dr]) OVER (PARTITION BY [Region], [Country], [Manufacturer], [Period]) AS UniqBrandCount
         FROM   T1)
SELECT [Region],
       [Country],
       [Manufacturer],
       [Brand],
       Period,
       SUM([Spend])        AS [Spend],
       MAX(UniqBrandCount) AS UniqBrandCount
FROM   T2
GROUP  BY [Region],
          [Country],
          [Manufacturer],
          [Brand],
          [Period]
ORDER  BY [Region],
          [Country],
          [Manufacturer],
          [Period],
          Brand 

Вышеупомянутое имеет некоторую неизбежную буферизацию (невозможно сделать это 100% потоковым способом), но только один вид.

Как ни странно, последнее предложение order by необходимо, чтобы количество сортировок было меньше одного (или нуля, если существует подходящий индекс).