日期范围内的运行总计 - 填写缺失的日期

Question

我有下表。

DATE  | AMT
10/10 | 300
12/10 | 300
01/11 | 200
03/11 | 100

我如何获得每月总计？像这样的结果 -

DATE | TOT
1010 | 300
1110 | 300
1210 | 600 
0111 | 800
0211 | 800
0311 | 900

类似的 sql 语句

SELECT SUM(AMT) FROM TABLE1 WHERE DATE BETWEEN '1010' AND '0111'

会导致 0111 为 800，但是...

注意 没有日期限制。这是我的困境。如何填充此列而不对所有日期进行循环并同时显示缺失的月份？

Answer 1

为了满足缺失的月份，请创建一个模板表来连接。

将其视为缓存。只需在数据库中缓存一个日历，而不是循环遍历并填补空白。

您甚至可以将多个日历（月初、周初、银行假日、工作日等）全部合并到一个表中，并带有一堆搜索标志和索引。

你最终会得到类似...

SELECT
  calendar.date,
  SUM(data.amt)
FROM
  calendar
LEFT JOIN
  data
    ON  data.date >= calendar.date
    AND data.date <  calendar.date + INTERVAL 1 MONTH
WHERE
      calendar.date >= '20110101'
  AND calendar.date <  '20120101'
GROUP BY
  calendar.date

编辑

我刚刚注意到OP想要一个运行总计。

这在 SQL 中是可能的，但效率极低。原因是一个月的结果不用于计算下个月。相反，必须重新计算整个运行总计。

因此，通常强烈建议您按上述方法计算每月总计，然后使用您的应用程序进行迭代并得出运行总计值。

如果您确实必须使用 SQL 执行此操作，那么会类似于...

SELECT
  calendar.date,
  SUM(data.amt)
FROM
  calendar
LEFT JOIN
  data
    ON  data.date >= @yourFirstDate
    AND data.date <  calendar.date + INTERVAL 1 MONTH
WHERE
      calendar.date >= @yourFirstDate
  AND calendar.date <  @yourLastDate
GROUP BY
  calendar.date

Answer 2

主要问题是并且还显示缺失的月份吗？
我不知道如何在没有包含要显示的月\年组合的辅助表的情况下执行此操作：

create table table1(
date datetime,
amt int
)
insert into table1 values ('10/10/2010',100)
insert into table1 values ('12/12/2010',200)
insert into table1 values ('01/01/2011',50)
insert into table1 values ('03/03/2011',500)

truncate table #dates
create table #dates(
_month int,
_year int
)
insert into #dates values(10,2010)
insert into #dates values(11,2010) --missing month
insert into #dates values(12,2010)
insert into #dates values(01,2011)
insert into #dates values(02,2011)--missing month
insert into #dates values(03,2011)


select D._month, D._year, sum(amt)
from #dates D left join TABLE1 T on D._month=month(T.date) and D._year=year(T.date)
group by D._month, D._year

Answer 3

您还可以动态生成一个范围，将其值作为间隔传递给 DATE_ADD，并基本上投影一系列月份值。

正如@Dems所说，您需要有一个相关子查询来计算运行总计，这将非常低效，因为它将在内部运行嵌套循环。

要了解如何生成序列，请查看我的帖子：
如何在Mysql中生成一系列数字

最终查询应该如下所示：（顺便说一句，您应该有一个日期列，而不是这个 varchar 混乱）。

/*NOTE: This assumes a derived table (inline view) containing the sequence of date values and their corresponding TOT value*/
SELECT
  DATEVALUES.DateValue,
  (
  SELECT SUM(TABLE1.AMT) FROM TABLE1 WHERE TABLE1.DateValue <= DATEVALUES.DateValue)
  ) AS RunningSubTotal
FROM
  DATEVALUES

或者类似的东西。

Answer 4

select sum(AMT) from TABLE1 group by Date