如何加入两个供应商的结果

Question

我选择并使用sum() over PARTITION来获取每个月按分公司划分的销售数据。我试了试group by，但是没有得到分公司的总销售额。

我想具体地看到两个供应商的结果及其价值占总销售额的百分比。

SELECT 
distinct sv.Branch 'Branch',
dim.ExclusiveGroup_KEY 'Exclusive Tag',
sum(sv.Revenue) over (PARTITION by sv.branch, dim.ExclusiveGroup_KEY) 'Supplier 1',
sum(sv.Revenue) over (PARTITION by sv.branch, dim.ExclusiveGroup_KEY) 'Supplier 2',
sum(sv.Revenue) over (PARTITION by sv.Branch) 'Branch Overall',
sum(sv.Revenue) over (PARTITION by sv.branch, dim.ExclusiveGroup_KEY)/sum(sv.Revenue) over (PARTITION by sv.Branch) 'Amount %'
FROM dbo.SalesView as sv
WHERE sv.Time between ? and ?

但是输出将结果放在单独的行上。

|Branch|Exclusive Tag|Supplier 1|Supplier 2|Branch Overall|Amount %|
|000001|EXCLSUPPLIER1|700       |700       |25000         |2.8%    |
|000001|EXCLSUPPLIER2|1400      |1400      |25000         |5.6%    |
|000002|EXCLSUPPLIER1|1300      |1300      |60000         |2.2%    |
|000002|EXCLSUPPLIER2|800       |800       |60000         |1.3%    |

我希望结果是这样的

|Branch|Supplier 1|Supplier 2|Branch Overall|Supp1 %|Supp2 %|
|000001|700       |1400      |25000         |2.8%   |5.6%   |
|000002|1300      |800       |60000         |2.2%   |1.3%   |

请让我知道我可以对代码进行哪些更改以显示所需的结果。任何帮助或建议将非常感激!

Answer 1

这不是最好的解决方案，它不是最后一个，但我没有足够的信息-例如，我不知道你如何加入到dim表。我想你应该用PIVOT。下面是一些更脏的解决方案:

SELECT sv.Branch [Branch]
--,dim.ExclusiveGroup_KEY [Exclusive Tag]
,(SELECT SUM(svint.Revenue) FROM dbo.SalesView svint WHERE svint.Branch=sv.Branch AND dim.ExclusiveGroup_KEY='EXCLSUPPLIER1') [Supplier 1]
,(SELECT SUM(svint.Revenue) FROM dbo.SalesView svint WHERE svint.Branch=sv.Branch AND dim.ExclusiveGroup_KEY='EXCLSUPPLIER2') [Supplier 2]
,SUM(sv.Revenue) [Branch Overall]
,(SELECT SUM(svint.Revenue) FROM dbo.SalesView svint WHERE svint.Branch=sv.Branch AND dim.ExclusiveGroup_KEY='EXCLSUPPLIER1')/SUM(sv.Revenue) [Supp1]
,(SELECT SUM(svint.Revenue) FROM dbo.SalesView svint WHERE svint.Branch=sv.Branch AND dim.ExclusiveGroup_KEY='EXCLSUPPLIER2')/SUM(sv.Revenue) [Supp2]
--sum(sv.Revenue) over (PARTITION by sv.branch, dim.ExclusiveGroup_KEY) 'Supplier 1',
--sum(sv.Revenue) over (PARTITION by sv.branch, dim.ExclusiveGroup_KEY) 'Supplier 2',
--sum(sv.Revenue) over (PARTITION by sv.Branch) 'Branch Overall',
--sum(sv.Revenue) over (PARTITION by sv.branch, dim.ExclusiveGroup_KEY)/sum(sv.Revenue) over (PARTITION by sv.Branch) 'Amount %'
FROM dbo.SalesView as sv
WHERE sv.Time between ? and ?
GROUP BY sv.Branch

Answer 2

使用group by:

SELECT sv.Branch,
       sum(case when dim.ExclusiveGroup_KEY = 'EXCLSUPPLIER1' then sv.Revenue else 0 end) as Supplier1,
       sum(case when dim.ExclusiveGroup_KEY = 'EXCLSUPPLIER2' then sv.Revenue else 0 end) as Supplier2,
       sum(sv.Revenue) as Branch,
       sum(case when dim.ExclusiveGroup_KEY = 'EXCLSUPPLIER1' then sv.Revenue else 0 end) / sum(sv.Revenue) as Supplier1_ratio,
       sum(case when dim.ExclusiveGroup_KEY = 'EXCLSUPPLIER2' then sv.Revenue else 0 end) / sum(sv.Revenue) as Supplier2_ratio
FROM dbo.SalesView as sv
WHERE sv.Time between ? and ?
GROUP BY sv.Branch;

你的问题是一个旋转或条件聚合的问题。我不知道你为什么要用select distinct和窗口函数来处理这个问题。