SQL 计算累积销售额，百分比

有一张销售记录表 t_sales，它记录了公司在某个年份的销售记录。由于一些原因，目前只能看到两个字段：month 和 quantity，它们分别对应的中文描述是月份和售额。

t_sales 的数据如下：

 month  quantity  
------  ----------
     1         100
     1         300
     2         200
     3         300
     4         400
     5         300
     5         500
     6         600
     7         600
     8         600
     9         600
    10         700
    11         800
    12         900

注意，在一个月里面是可以产生多条销售记录。

现在，我们希望从这份数据中统计出**「总销售额」、「月销售额」、「当前累积销售额」**，统计的结果如下：

 月份    销售额     总销售额   月销售额   累积销售额   
------  --------  --------  ---------  --------
     1       100    6900       400       400     
     1       300    6900       400       400     
     2       200  6900       200       600     
     3       300  6900       300       900     
     4       400  6900       400       1300    
     5       300  6900       800       2100    
     5       500  6900       800       2100    
     6       600  6900       600       2700    
     7       600  6900       600       3300    
     8       600  6900       600       3900    
     9       600  6900       600       4500    
    10       700  6900       700       5200    
    11       800  6900       800       6000    
    12       900  6900       900       6900                   

解决方案

统计总销售额可以使用 SELECT SUM(quantity) FROM t_sales；

统计月销售额可以使用 SELECT month,SUM(quantity) FROM t_sales GROUP BY month；

统计当前累积销售额使用：

SELECT
  (SELECT
    SUM(quantity)
  FROM
    t_sales
  WHERE MONTH <= a.month) AS '累积销售额'
FROM
  t_sales a

再使用原表分别去关联这些统计结果，完整的 SQL 如下：

SELECT
  a.month AS '月份',
  a.quantity AS '销售额',
  (SELECT
    SUM(quantity)
  FROM
    t_sales) AS '总销售额',
  c.amount AS '月销售额',
  (SELECT
    SUM(quantity)
  FROM
    t_sales
  WHERE MONTH <= a.month) AS '累积销售额'
FROM
  t_sales a
  LEFT JOIN
    (SELECT
      MONTH,
      SUM(quantity) AS amount
    FROM
      t_sales
    GROUP BY MONTH) c
    ON c.month = a.month

如果使用窗口函数，整个 SQL 的实现会简洁很多。

用 sum(quantity)over() 统计出总销售额；

用 sum(quantity) over (PARTITION by month) 统计月销售额；

用 sum(quantity) over (order by month) 统计当前累积销售额。

具体的 S QL 如下：

SELECT
  MONTH AS '月份',
  quantity AS '销售额',
  SUM(quantity) over () AS '总销售额',
  SUM(quantity) over (PARTITION BY MONTH) AS '月销售额',
  SUM(quantity) over (
ORDER BY MONTH) AS '累积销售额'
FROM
  t_sales

---

SELECT P.PersonID, SUM(PA.Total),
       SUM(PA.Total) * 100.0 / SUM(SUM(PA.Total)) OVER () AS Percentage
FROM Person P JOIN
     Package PA
     ON P.PersonID = PA.PackageFK
GROUP BY P.PersonID;

---

LINQ-TO-SQL GROUPBY：如何计算百分比列-LINQ，C#

var results = from events in datacontext.events
              let date = new DateTime(
                     events.datetime.Year, events.datetime.Month, events.datetime.Day,
                     events.datetime.Hour, 0, 0)
              let total = datacontext.events.Where(x => x.datetime.Date == date.Date && x.datetime.Hour == date.Hour).Count()
              group events by new
              {  // group by category and hour
                  category = events.category,
                  datetime = date,
                  total = total
              } into e
              select new
              {
                  category = e.Key.category,
                  day = e.Key.datetime.Day,
                  numberOfEvents = e.Count(),
                  percentage = (float)e.Count() / (float)e.Key.total
              };

两个重点：

1. 使用浮点数表示百分比
2. 使用 let 计算Date的总数

您必须先按天分组，然后从那里选择总数……例如：

var resultsPerDay = datacontext.events.groupBy(e=>new {
    datetime = new DateTime (
        e.datetime.Year, e.datetime.Month, e.datetime.Day, 
        e.datetime.Hour, 0, 0 )
    }
}).select(group=>new {
    datetime = group.Key.datetime,
    totalForADay = group.Key.count()
});



var results = datacontext.events.
  groupBy(e=>new   
  {  // group by category and hour
     category = e.category,
     datetime = new DateTime (
       e.datetime.Year, e.datetime.Month, e.datetime.Day, 
       e.datetime.Hour, 0, 0 ) 
     // use date+hour for grouping, ignore minutes/seconds
  }).
  select(group => new 
  {
     category = group.Key.category,
     datetime = group.Key.datetime,
     numberOfEvents = group.count(),
     percentage = 100 * group.count() / resultsPerDay.where(p=>p.datetime == group.Key.datetime).select(p=>p.totalForADay).SingleOrDefault()
  }

不过，不确定这是否是最有效的方法。 另外，由于我没有通过编译器将其放入，因此不确定语法是否良好，但您明白了。

---

LINQ 分组获取整体 LINQ 的百分比

var orderPercent = orders
    .GroupBy(x => x.OrderDate)
    .Join(
        orders.GroupBy(y => new { y.OrderDate, y.Type }),
        x => x.Key,
        y => y.Key.OrderDate,
        (a, b) => new { Date = a.Key, Type = b.Key.Type, Percent = (double)b.Sum(x => x.Value) / (double)a.Sum(x => x.Value) }
    ).ToList();