sql server语言 - 如何在sql上计算我的总销售额的每日快照

Question

我有一个表(我们称它为DiodeSales)，它告诉我按日期、二极管颜色和国家分组的二极管销售总数。下面是这个模式的一个示例:

<>之前国家销售2016年6月20日00:00:00绿色美国2016年6月20日00:00:002016年6月20日00:00:00红色美国2016年6月21日00:00:00红色美国2016年6月22日00:00:00绿色美国12016年6月22日00:00:00红色美国2016年6月23日00:00:00绿色美国12016年6月23日00:00:002016年6月23日00:00:00红色美国2016年6月24日00:00:00绿色美国12016年6月24日00:00:00之前

我希望能够有一个额外的列，告诉我到目前为止我们已经售出了多少个二极管。因此，例如，使用上述数据，{June 23, Red, 1, US}行的总销售额为4，因为此时我们在美国销售了4个红色二极管。

我一开始以为一个累加的总和就可以了。所以我写了:(sqlfiddle here)

SELECT
  t1.Date,
  t1.Color,
  t1.Country,
  t1.Sales,
  SUM(t2.Sales) AS CumulativeSales
FROM DiodeSales AS t1
INNER JOIN DiodeSales AS t2
ON t1.Date >= t2.Date
  AND t1.Color = t2.Color
  AND t1.Country = t2.Country
GROUP BY
  t1.Date,
  t1.Color,
  t1.Country

如预期的那样，这给了我累积的总和，但它没有给我给定国家给定颜色在给定日期的总销售额。特别是，因为某些特定的日子在某些国家的销售额可能为0，因此它们将没有与之相关的累积值。例如，考虑上一个表的结果:

<>之前日期颜色国家销售累计销售2016年6月20日00:00:00绿色美国112016年6月20日00:00:002016年6月20日00:00:00红色美国112016年6月21日00:00:00红色美国122016年6月22日00:00:00绿色美国122016年6月22日00:00:00红美12016年6月23日00:00:00绿美12016年6月23日00:00:002016年6月23日00:00:00红色美国142016年6月24日00:00:00绿色美国142016年6月24日00:00:00红色美国15之前

如果我要查找6月24日日本对应的列，我将找不到任何内容(因为当天没有日本销售，所以当天没有日本行)。我不认为有一种方法在SQL中做到这一点，但是否有可能填充这个结果表的值在一些国家没有销售的日子?对于某些国家，起始表每天至少有一列。

我知道我可以只写一个简单的 <>之前SELECT SUM(Sales) FROM DiodeSalesWHERE Date < @someDate AND Color = @ someecolor AND Country = @ someecountry之前

来获取这些信息，但这是针对一个必须以这种方式格式化的表，以便它被另一个已制作的软件使用。

编辑:有人提到这是在SQL Server中计算运行总数的潜在副本，但那篇文章只解决了计算运行总和的效率问题。我已经有了计算这个总和的各种方法，但我正在寻找一种方法来解决在那个国家没有销售的日子里缺少日/国家组合的问题。对于上面的示例，固定查询将返回如下内容:

<>之前日期颜色国家销售累计销售2016年6月20日00:00:00绿色美国112016年6月20日00:00:002016年6月20日00:00:00红色美国112016年6月21日00:00:00绿色美国012016年6月21日00:00:00红色日本012016年6月21日00:00:00红色美国122016年6月22日00:00:00绿色美国122016年6月22日00:00:00红色日本02016年6月22日00:00:00红美12016年6月23日00:00:00绿美12016年6月23日00:00:002016年6月23日00:00:00红色美国142016年6月24日00:00:00绿色美国142016年6月24日00:00:002016年6月24日00:00:00红色美国15

Answer 1

试试这个:

SELECT [Date], Color, Country, Sales,
   SUM(Sales) OVER(PARTITION BY Color, Country ORDER BY [Date] rows unbounded preceding) as RunningTotal
FROM YourTable
ORDER BY [Date], Color

它产生预期的输出。

[编辑]

如果您正在寻找缺少日期，国家和颜色的解决方案，请尝试以下操作(将@tmp替换为您的表名):

SELECT A.[Date], A.Color, A.Country, COALESCE(B.Sales, 0) AS Sales
    ,   SUM(COALESCE(B.Sales, 0)) OVER(PARTITION BY A.Color, A.Country ORDER BY A.[Date] rows unbounded preceding) as RunningTotal
FROM (
    SELECT [Date], Color, Country
    FROM (SELECT DISTINCT [Date] FROM @tmp) AS q1 CROSS JOIN
        (SELECT DISTINCT Color FROM @tmp) AS q2 CROSS JOIN
        (SELECT DISTINCT Country FROM @tmp) AS q3 
    ) AS A
LEFT JOIN @tmp AS B ON A.[Date] = B.[Date] AND A.Color= B.Color AND A.Country = B.Country 
ORDER BY A.[Date], A.Color 

以上查询产生:

Date    Color       Country Sales   RunningTotal
2016-06-20  Green   Japan   0       0
2016-06-20  Green   US      1       1
2016-06-20  Red     Japan   1       1
2016-06-20  Red     US      1       1
2016-06-21  Green   US      0       1
2016-06-21  Green   Japan   0       0
2016-06-21  Red     US      1       2
2016-06-21  Red     Japan   0       1
2016-06-22  Green   Japan   0       0
2016-06-22  Green   US      1       2
2016-06-22  Red     Japan   0       1
2016-06-22  Red     US      1       3
2016-06-23  Green   US      1       3
2016-06-23  Green   Japan   0       0
2016-06-23  Red     US      1       4
2016-06-23  Red     Japan   1       2
2016-06-24  Green   Japan   0       0
2016-06-24  Green   US      1       4
2016-06-24  Red     Japan   0       2
2016-06-24  Red     US      1       5

Answer 2

我认为你应该使用左连接而不是内连接

  SELECT
 t.Date,
 t.Color,
 t.Country,
 t.CumulativeSales
 from DiodeSales t
 left join
 (SELECT
 t1.Date,
 t1.Color,
 t1.Country,
 t1.Sales,
 SUM(t2.Sales) AS CumulativeSales
 FROM DiodeSales AS t1
  GROUP BY
  t1.Date,
  t1.Color,
 t1.Country) t2
  on
 t.Date=t2.date
 and t.Color=t2.color
 and t.Country=t2.country

Answer 3

试试这个

 Select distinct Date into SalesDate From DiodeSales
   SELECT S.Date,t.Color,t.Country,t.CumulativeSales
    from DiodeSales t left join
     (SELECt t1.Date,t1.Color,t1.Country,t1.Sales,
     SUM(t2.Sales) AS CumulativeSales FROM DiodeSales AS t1
      GROUP BY
       t1.Date,
       t1.Color,
     t1.Country) t2 on
       S.Date=t2.date
     and t.Color=t2.color
      and t.Country=t2.country
       join
      SalesDate S
      on t.date=S.date