SQL Server:如何用其余的零值填充稀疏数据? [英] SQL Server: how to populate sparse data with the rest of zero values?

问题描述

我有数据报告每个月和每个客户的销售额.当我计算值时，由于稀疏数据格式，不会报告零值.

I have data reporting sales by every month and by every customer. When I count the values, the zero-values are not reported because of the sparsa data format.

假设客户1-4.假设只有客户1-2有录音.直线表在行上有customerID，在列上有month，这样

Suppose customer 1-4. Suppose only customers 1-2 have recordings. Straight table has customerIDs on rows and months on the columns such that

|CustomerID|MonthID|Value|
-------------------------|
|     1    |201101 |  10 |
|     2    |201101 | 100 |

，然后以交叉表格式将其报告，例如

and then they are reported in Crosstab format such that

|CustomerID|201101|201102|2011103|...|201501|
---------------------------------------------
|    1     |  10  |   0  |   0   |...|  0   |  
|    2     |  100 |   0  |   0   |...|  0   |
|    3     |  0   |   0  |   0   |...|  0   |
|    4     |  0   |   0  |   0   |...|  0   |

当我将其计算在内时，由于客户3-4没有记录，所以他们什么也没得到.我想得到缺少的零行.如何填充或选择原始数据，然后将不存在的零值填充到选择中?或更简短地说:

when I count this I get nothing for the customers 3-4 because they have no recordings. I want to get the missing zero rows. How can I populate or select the original data and fill the non-existing zero values to the selection? Or more shortly:

处理稀疏数据格式并在最终报告中仍保留零客户的最优雅方法是什么?

推荐答案

在转为交叉表格式之前，您将cross join表Customers和Months，然后是left join表Sales

Prior to pivoting to your crosstab format, you would cross join tables Customers and Months, and then left join table Sales to that.

select 
    c.CustomerId
  , m.MonthId
  , Value = isnull(s.Value,0)
from customers c
  cross join months m
  left join sales s
    on s.CustomerId = c.CustomerId
   and s.MonthId = m.MonthId

rextester演示: http://rextester.com/XKU62242

rextester demo: http://rextester.com/XKU62242

返回:

+------------+---------+-------+
| CustomerId | MonthId | Value |
+------------+---------+-------+
|          1 |  201101 |    10 |
|          2 |  201101 |   100 |
|          3 |  201101 |     0 |
|          4 |  201101 |     0 |
|          1 |  201102 |     0 |
|          2 |  201102 |     0 |
|          3 |  201102 |     0 |
|          4 |  201102 |     0 |
|          1 |  201103 |     0 |
|          2 |  201103 |     0 |
|          3 |  201103 |     0 |
|          4 |  201103 |     0 |
+------------+---------+-------+

---

可以将动态pivot()添加到上面，如下所示:

---

Adding a dynamic pivot() to the above could be done like so:

declare @cols nvarchar(max);
declare @sql  nvarchar(max);

select @cols = stuff((
    select ',' + quotename(MonthId)
    from months 
    order by MonthId
    for xml path (''), type).value('.','nvarchar(max)')
  ,1,1,'');

select @sql = '
select CustomerId, ' + @cols + '
from (
    select 
        c.CustomerId
      , m.MonthId
      , Value = isnull(s.Value,0)
    from customers c
      cross join months m
      left join sales s
        on s.CustomerId = c.CustomerId
      and s.MonthId = m.MonthId
    ) as t
pivot (sum([Value]) for [MonthId] in (' + @cols + ') ) p';

select @sql as CodeGenerated;
exec sp_executesql @sql;

返回:

+-----------------------------------------------------------------------+
|                             CodeGenerated                             |
+-----------------------------------------------------------------------+
| select CustomerId, [201101],[201102],[201103]                         |
| from (                                                                |
|     select                                                            |
|         c.CustomerId                                                  |
|       , m.MonthId                                                     |
|       , Value = isnull(s.Value,0)                                     |
|     from customers c                                                  |
|       cross join months m                                             |
|       left join sales s                                               |
|         on s.CustomerId = c.CustomerId                                |
|       and s.MonthId = m.MonthId                                       |
|     ) as t                                                            |
| pivot (sum([Value]) for [MonthId] in ([201101],[201102],[201103]) ) p |
+-----------------------------------------------------------------------+

和exec返回:

+------------+--------+--------+--------+
| CustomerId | 201101 | 201102 | 201103 |
+------------+--------+--------+--------+
|          1 |     10 |      0 |      0 |
|          2 |    100 |      0 |      0 |
|          3 |      0 |      0 |      0 |
|          4 |      0 |      0 |      0 |
+------------+--------+--------+--------+

这篇关于SQL Server:如何用其余的零值填充稀疏数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！