sql - 在 SQL Server 中一次性获取 DISTINCT COUNT

Question

我有一个如下表:

Region    Country    Manufacturer    Brand    Period    Spend
R1        C1         M1              B1       2016      5
R1        C1         M1              B1       2017      10
R1        C1         M1              B1       2017      20
R1        C1         M1              B2       2016      15
R1        C1         M1              B3       2017      20
R1        C2         M1              B1       2017      5
R1        C2         M2              B4       2017      25
R1        C2         M2              B5       2017      30
R2        C3         M1              B1       2017      35
R2        C3         M2              B4       2017      40
R2        C3         M2              B5       2017      45
...

我编写了下面的查询来聚合它们:

SELECT [Region]
    ,[Country]
    ,[Manufacturer]
    ,[Brand]
    ,Period
    ,SUM([Spend]) AS [Spend]
FROM myTable
GROUP BY [Region]
    ,[Country]
    ,[Manufacturer]
    ,[Brand]
    ,[Period]
ORDER BY 1,2,3,4

产生如下所示的结果:

Region    Country    Manufacturer    Brand    Period    Spend
R1        C1         M1              B1       2016      5
R1        C1         M1              B1       2017      30 -- this row is an aggregate from raw table above
R1        C1         M1              B2       2016      15
R1        C1         M1              B3       2017      20
R1        C2         M1              B1       2017      4  -- aggregated result
R1        C2         M2              B4       2017      25
R1        C2         M2              B5       2017      30
R2        C3         M2              B4       2017      40
R2        C3         M2              B5       2017      45

我想在上表中添加另一列，以显示按地区分组的Brand的DISTINCT COUNT，国家/地区、制造商 和时期。所以决赛 table 将变成如下:

Region    Country    Manufacturer    Brand    Period    Spend    UniqBrandCount
R1        C1         M1              B1       2016      5        2 -- two brands by R1, C1, M1 in 2016
R1        C1         M1              B1       2017      30       1
R1        C1         M1              B2       2016      15       2 -- same as first row's result
R1        C1         M1              B3       2017      20       1
R1        C2         M1              B1       2017      4        1
R1        C2         M2              B4       2017      25       2
R1        C2         M2              B5       2017      30       2
R2        C3         M2              B4       2017      40       2
R2        C3         M2              B5       2017      45       2

我知道如何通过三个步骤获得最终结果。

1. 运行此查询(查询 #1):

   选择[区域] ，[国家] ，[制造商] ，[时期] ,COUNT(DISTINCT [品牌]) AS [品牌计数]INTO临时1来 self 的 table 按[地区]分组 ，[国家] ，[制造商] ,[句号]

2. 运行此查询(查询 #2)

   选择[区域] ，[国家] ，[制造商] ，[品牌] ,YEAR([期间]) AS 期间 ,SUM([花费]) AS [花费]进入温度2来 self 的表按[地区]分组 ，[国家] ，[制造商] ，[品牌] ,[句号]

3. 然后LEFT JOIN Temp2和Temp1从后者引入[BrandCount]，例如如下:

   选择一个。* ,b.*从 Temp2 AS aLEFT JOIN Temp1 AS b ON a.[区域] = b.[区域] AND a.[国家/地区] = b.[国家/地区] AND a.[广告商] = b.[广告商] AND a.[句点] = b.[句点]

我很确定有一种更有效的方法可以做到这一点，是吗？预先感谢您的建议/回答!

Answer 1

大量借用这个问题:https://dba.stackexchange.com/questions/89031/using-distinct-in-window-function-with-over

Count Distinct不起作用，因此需要dense_rank。按正序和倒序对品牌进行排名，然后减去 1 即可得出不同计数。

您的 sum 函数也可以使用 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

从此输出中选择不同的行即可满足您的需要。

dense_rank 技巧如何工作

考虑以下数据:

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

dense_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

因此，要获得不同项目的总数，请将升序和降序dense_rank相加并减去1。