日期范围内的累计运行-填写缺少的日期 [英] Running total over date range - fill in the missing dates

问题描述

我有下表.

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

我如何获得每月总计?结果--

How do I get the monthly total? A result like -

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'

将得出800代表0111，但是...

would result in the 800 for 0111 but...

注意没有日期限制.这是我的困境.如何填充此列而不对所有日期进行循环并且也显示缺少的月份?

NOTE There is not a date restriction. which is my dilemma. How do I populate this column without doing a loop for all dates and have the missing months displayed as well?

推荐答案

为满足缺少的月份，请创建一个模板表以进行连接.

To cater for missing months, create a template table to join against.

将其视为缓存.无需遍历和填补空白，只需在数据库中缓存一个日历即可.

Think of it as caching. Rather than looping through and filling gaps, just have a calendar cached in your database.

您甚至可以将多个日历(月初，周初，银行假期，工作​​日等)全部组合到一个表中，并带有一堆搜索标记和索引.

You can even combine multiple calendars (start of month, start of week, bank holidays, working day, etc) all into one table, with a bunch of search flags and indexes.

您最终得到类似...

You end up with something like...

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希望获得总计.

I just noticed that the OP wants a running total.

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

This -is- possible in SQL but it is extremely inefficient. The reason being that the result from one month isn't used to calculate the following month. Instead the whole running-total has to be calculated again.

因此，通常强烈建议您按上述方法计算每月总计，然后使用您的应用程序计算并得出连续的总计值.

For this reason It is normally strongly recommended that you calculate the monthly total as above, then use your application to itterate through and make the running total values.

如果您真的必须在SQL中执行此操作，那将类似于...

If you really must do it in SQL, it would be something like...

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

这篇关于日期范围内的累计运行-填写缺少的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！