SQL每月加入和每月总百分比

Question

(愚蠢的)尝试使用 JOIN、WITH 和 GROUP BY 来为我漂亮的人想出一个解决方案，我的脑袋冒烟了常见情况 - 我无法理解它。让我马上给你举个例子:

我有两个表(ColorCount 和 Colorname):

ColorCount:
ColorID Count Date
1       42    2010-09-07
1       1     2010-09-08
2       22    2010-09-14
1       20    2010-10-10
3       4     2010-10-14

---

ColorName:
ColorID  Name
1        Purple
2        Green
3        Yellow
4        Red

---

现在我只想将 ColorName 表连接到 ColorCount 表，汇总每个月的所有颜色计数，并计算每个计数占每月总数的百分比。表胜于言:

Output:
Month Color   Count Percentage
09    Purple  43    66%
09    Green   22    33%
09    Yellow  0     0%
09    Red     0     0%
10    Purple  20    83%
10    Green   0     0%
10    Yellow  4     16%
10    Red     0     0%

(请注意 09 月的总计数是 65，因此 Purple 的 66% code> 以及 0 表示不存在的颜色):

我希望有人在 SQL 中梦想，这是一个简单的任务...

Answer 1

这是可行的，但有以下注意事项:

• 日期时间值只能是日期
• 它只列出那些有任何数据的月份
• 我按每月的第一天列出，以防您有跨年的数据(我假设您不想汇总 2009 年 1 月的数据和 2010 年 1 月的数据)
• 我留给你的精确百分比列格式化细节，我得回去工作了

代码:

;with cte (ColorId, Mth, TotalCount)
 as (select
        ColorId
       ,dateadd(dd, -datepart(dd, Date) + 1, Date) Mth
       ,sum(Count) TotalCount
      from ColorCount
      group by ColorId, dateadd(dd, -datepart(dd, Date) + 1, Date))
 select
    AllMonths.Mth [Month]
   ,cn.Name
   ,isnull(AggData.TotalCount, 0) [Count]
   ,isnull(100 * AggData.TotalCount / sum(AggData.TotalCount * 1.00) over (partition by AllMonths.Mth), 0) Percentage
  from (select distinct Mth from cte) AllMonths
   cross join ColorName cn
   left outer join cte AggData
    on AggData.ColorId = cn.ColorId
     and AggData.Mth = AllMonths.Mth
  order by AllMonths.Mth, cn.ColorId