如何获得一个月范围的滚动年度总计
问题描述:
我试图从一组数据中获得一个滚动的12个月总计,该数据提供给定的月份和年份的分钟数。在很多情况下,我不会有一个月的记录。是否有一种方法可以显示丢失的月份(值为零),然后将其卷起或不显示,但让查询确认这是12个月范围的一部分。下面的查询是我试图使用的,但它只是抓住了以前的记录,并没有考虑到失踪的月份。下面的查询似乎没有与一些总数有一点关系。任何援助将不胜感激。如何获得一个月范围的滚动年度总计
Select year
, month
, SUM(NVL(minutes, 0)) OVER (order by year,month rows between 11 preceding and current row) as total_minutes
from
(
Select *
from test_table
Order by year, month
)
Order by year desc , month desc
这里是创建测试表和数据的sql。
drop table test_table;
create table test_table (
month number(2),
year number(4),
minutes number(4,2)
);
insert into test_table values (1, 2012, 3);
insert into test_table values (2, 2012, 3);
insert into test_table values (3, 2012, 3);
insert into test_table values (4, 2012, 4);
insert into test_table values (5, 2012, 5);
insert into test_table values (7, 2012, 5);
insert into test_table values (8, 2012, 5);
insert into test_table values (9, 2012, 5);
insert into test_table values (10, 2012, 4);
insert into test_table values (11, 2012, 3);
insert into test_table values (12, 2012, 3);
insert into test_table values (1, 2011, 3);
insert into test_table values (2, 2011, 3);
insert into test_table values (5, 2011, 5);
insert into test_table values (6, 2011, 5);
insert into test_table values (7, 2011, 5);
insert into test_table values (8, 2011, 5);
insert into test_table values (9, 2011, 5);
insert into test_table values (10, 2011, 4);
insert into test_table values (11, 2011, 3);
insert into test_table values (12, 2011, 3);
insert into test_table values (1, 2010, 3);
insert into test_table values (2, 2010, 3);
insert into test_table values (3, 2010, 3);
insert into test_table values (4, 2010, 4);
insert into test_table values (5, 2010, 4);
insert into test_table values (6, 2010, 5);
insert into test_table values (7, 2010, 5);
insert into test_table values (10, 2010, 4);
insert into test_table values (11, 2010, 4);
insert into test_table values (12, 2010, 3);
COMMIT;
答
我确实使用了外连接,但没有给出我需要的滚动12个月范围。我不得不使用窗口子句来让范围和数字出来。
with m_num as(
select level as mn
from dual
connect by level <= 12
)
select year
, mn
, minutes
, sum(minutes) over(order by year,mn rows between 11 preceding and current row) rt
from (select tt.Year
, mn.mn
, nvl(tt.minutes, 0) minutes
from test_table tt
partition by (tt.year)
right outer join m_num mn
on (tt.month = mn.mn)
)
order by year desc, mn desc;
答
为了填补空白个月partition outer join
(Oracle 10g的广告更高)使用下面的查询,如果你真的需要看到那些零,否则你可以保留原来的查询重写SUM
功能SUM(NVL(minutes, 0)) OVER (partition by year order by year,month) as total_minutes
。
SQL> with m_num as(
2 select level as mn
3 from dual
4 connect by level <= 12
5 )
6 select year
7 , mn
8 , minutes
9 , sum(minutes) over(partition by year order by year, mn) rt
10 from (select tt.Year
11 , mn.mn
12 , nvl(tt.minutes, 0) minutes
13 from test_table tt
14 partition by (tt.year)
15 right outer join m_num mn
16 on (tt.month = mn.mn)
17 )
18 order by year desc, mn desc
19 ;
YEAR1 MN MINUTES RT
----- ---------- ---------- ----------
2012 12 3 43
2012 11 3 40
2012 10 4 37
2012 9 5 33
2012 8 5 28
2012 7 5 23
2012 6 0 18
2012 5 5 18
2012 4 4 13
2012 3 3 9
2012 2 3 6
2012 1 3 3
2011 12 3 41
2011 11 3 38
2011 10 4 35
2011 9 5 31
2011 8 5 26
2011 7 5 21
2011 6 5 16
2011 5 5 11
2011 4 0 6
2011 3 0 6
2011 2 3 6
2011 1 3 3
谢谢你的帮助。我不确定如何回复解决方案,因为我不得不为自己的工作做出一些改变。无论如何,我真的很感激它。 –