sql - 如何在 SQL 汇总中获得总计百分比？

Question

我在 SQL Server 2014 中有一组数据，我需要将其汇总到小计中，并为每一行得出一个总计百分比。这是我需要做的一个简化示例:

示例数据:SELECT * FROM myTable

Week    Category    Collection  Sales
1       Red         Good        4
1       Red         Bad         5
1       Blue        Good        2
1       Blue        Bad         2
2       Red         Good        3
2       Red         Bad         1
2       Blue        Good        4
2       Blue        Bad         1

汇总部分很简单:
SELECT Week, Category, Collection, Sum(Sales) as Sales
FROM myTable
GROUP BY Week, Category, Collection WITH ROLLUP

Week    Category    Collection  Sales
1       Red         Good        4
1       Red         Bad         5
1       Red         NULL        9
1       Blue        Good        2
1       Blue        Bad         2
1       Blue        NULL        4
1       NULL        NULL        13
2       Red         Good        3
2       Red         Bad         1
2       Red         NULL        4
2       Blue        Good        4
2       Blue        Bad         1
2       Blue        NULL        5
2       NULL        NULL        9
NULL    NULL        NULL        22

现在，我想要得到的是汇总的每个部分的汇总百分比。像这样:

Week    Category    Collection  Sales    % Total
1       Red         Good        4        .4444 (4/9)
1       Red         Bad         5        .5556 (5/9)
1       Red         NULL        9        .6923 (9/13)
1       Blue        Good        2        .5 (2/4)
1       Blue        Bad         2        .5 (2/4)
1       Blue        NULL        4        .3077 (4/13)
1       NULL        NULL        13       .5909 (13/22)
2       Red         Good        3        .75 (3/4)
2       Red         Bad         1        .25 (1/4)
2       Red         NULL        4        .4444 (4/9)
2       Blue        Good        4        .8 (4/5)
2       Blue        Bad         1        .2 (1/5)
2       Blue        NULL        5        .5556 (5/9)
2       NULL        NULL        9        .4091 (9/22)
NULL    NULL        NULL        22

*注意，我不需要显示括号中显示的除数。那只是为了更容易看到计算。

我不太确定如何使用 SQL 来获得这种结果。如果有人能让我指出正确的方向，我将不胜感激。谢谢!

Answer 1

这是一种方法，它使用公用表表达式 (CTE) 使用分析函数预先计算可能的分母，然后执行聚合汇总。在聚合查询中，百分比是通过在分母中使用合并运算符以及汇总中的分组集来确定的，这样在汇总的每个阶段，合并函数都会获得下一个连续的分母值。您可以查看下面的查询和结果或查看SQL Fiddle查看实际的设置和查询。

with t1 as (
select week
     , category
     , collection
     , sales
     , sum(sales) over (partition by week, category) cat
     , sum(sales) over (partition by week) w
     , sum(sales) over () o
  from mytable
)
 select week
     , category
     , collection
     , sum(sales) sales
     , sum(sales)/coalesce(cat, w, o, sum(sales)) [% sales]
   from t1
  group by rollup(
        (week, o)
      , (category, w)
      , (collection , cat))
order by 1, 2, 3

Results :

|   week | category | collection | sales |  % sales |
|--------|----------|------------|-------|----------|
| (null) |   (null) |     (null) |    22 |        1 |
|      1 |   (null) |     (null) |    13 | 0.590909 |
|      1 |     Blue |     (null) |     4 | 0.307692 |
|      1 |     Blue |        Bad |     2 |      0.5 |
|      1 |     Blue |       Good |     2 |      0.5 |
|      1 |      Red |     (null) |     9 | 0.692307 |
|      1 |      Red |        Bad |     5 | 0.555555 |
|      1 |      Red |       Good |     4 | 0.444444 |
|      2 |   (null) |     (null) |     9 |  0.40909 |
|      2 |     Blue |     (null) |     5 | 0.555555 |
|      2 |     Blue |        Bad |     1 |      0.2 |
|      2 |     Blue |       Good |     4 |      0.8 |
|      2 |      Red |     (null) |     4 | 0.444444 |
|      2 |      Red |        Bad |     1 |     0.25 |
|      2 |      Red |       Good |     3 |     0.75 |

采用 Yogesh 的解决方案并在下面修复它的缺陷会产生与我上面的解决方案相同的结果。看到这个 SQL Fiddle看到它在行动:

SELECT Week
     , Category
     , Collection
     , Sum(Sales) as Sales
     , Sum(Sales)
     / (select sum(sales)
          from myTable s
         where (m.category is null or Week = m.Week)
           and (m.collection is null or Category = m.Category)
       ) as [% Total]
FROM myTable m
GROUP BY Week, Category, Collection WITH ROLLUP
order by 1, 2, 3;