按年、月以及累计金额排序 [英] Order by year, month along with cumulative sum

问题描述

我的表DATA123"如下

My table "DATA123" is as follows

NoticeDate------Amount
2016-02-14------60000
2015-11-06------10000
2015-12-02------10000
2016-01-13------50000
2016-01-24------10000
2015-12-29------30000
2016-01-12------20000
2015-11-18------50000

我希望输出为

Year----month----Amount----Cumulative
2015----NOV------60000------60000
2015----DEC------40000------100000
2016----JAN------80000------180000
2016----FEB------60000------240000

简而言之，我想要有累积总和的有序月度报告 sql 查询(不是过程).但是，我没有代表每条记录的唯一 ID.

In short i want orderly monthly report sql query (not procedure) with cumulative sum. However, I dont have a unique id representing each record.

推荐答案

我建议这个查询:

select datepart(year, NoticeDate1) Year,
       format(NoticeDate1,'MMM') Month,
       Amount,
       sum(Amount) over (order by NoticeDate1
                         rows unbounded preceding) Cumulative
from (
    select   dateadd(month, datediff(month, 0, NoticeDate), 0) NoticeDate1,
             sum(amount) Amount
    from     data123
    group by dateadd(month, datediff(month, 0, NoticeDate), 0)
) a

它为示例数据生成以下输出:

It produces this output for the sample data:

| Year | Month | Amount | cumulative |
|------|-------|--------|------------|
| 2015 |   Nov |  60000 |      60000 |
| 2015 |   Dec |  40000 |     100000 |
| 2016 |   Jan |  80000 |     180000 |
| 2016 |   Feb |  60000 |     240000 |

SQL 小提琴

查询使用带有行无界的windows函数，这是累积总和的关键.NoticeData1 是日期 NoticeDate 的月份的第一天.format 函数可用于获取月份名称.

The query makes use of windows functions with rows unbounding preceding, which is the key for cumulative sums. NoticeData1 is the first day of the month for date NoticeDate. The format function can be used to get month names.

您可能想添加一个 order by 子句，但您最好选择月份编号，因为月份名称的排序方式与您希望的不同:).排序顺序仍然可以像现在一样.引擎需要正确的累积总和排序顺序，因此没有理由更改它.

You might want to add an order by clause, but then you better select the month number, because month names don't sort like you would want :). Still the sort order will be OK like it is now. The engine needs the correct sort order for the cumulative sum, so it has no reason to change it.

这篇关于按年、月以及累计金额排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！