DB2中用分析函数查询上月、去年同期数据
本帖最后由 xdw8933 于 2011-01-13 13:31:11 编辑 问题：DB2中在一条语句中用分析函数查询出本月数据、上月数据与上年同期数据的结果集。
另：能否推荐有关窗口函数详细说明（如：windowing clause 三种开窗方式: range、row、specifying的详细和面）的电子书下载地址或纸质书，或者是相关APT。
环境及SQL说明：
1、DB2版本为9
2、建表语句
create table a_test(stat_dt date,amt integer);
insert into a_test values('2010-12-31',350697);
insert into a_test values('2010-10-31',350666);
insert into a_test values('2010-09-30',350677);
insert into a_test values('2009-12-31',350655);
insert into a_test values('2009-08-31',350644);
commit;
3、查询结果类似如下：
账期本月金额上月金额去年同期金额
2010-12-31350697 0350655
2010-10-31350666350677 0
2010-09-30350677 0 0
2009-12-31350655 0 0
2009-08-31350644 0 0
4、参考网上查找的ORACLE语句，自己编写SQL如下：
SELECT stat_dt,
AMT,
sum(AMT) OVER (ORDER BY STAT_DT range BETWEEN INTERVAL 1 month preceding AND current row) AS prev_sal --上月数据
sum(AMT) OVER (ORDER BY STAT_DT range BETWEEN INTERVAL 1 year preceding AND current row) AS prev_sal --去年同期数据
FROM a_xutest
order by stat_dt
;
但是编译不通过，报SQL中有非法字符错误
[解决办法]
不好意思，上面的sql写错了，应该是：
SELECT stat_dt
,AMT
,sum(AMT) OVER (ORDER BY STAT_DT range BETWEEN 100 preceding AND 100 preceding) AS prev_sal --上月数据
,sum(AMT) OVER (ORDER BY STAT_DT range BETWEEN 10000 preceding AND 10000 preceding) AS prev_sal --去年同期数据
FROM a_test
order by stat_dt ;
[解决办法]
这下好像更接近了。
with a (DT, AMT) as (
select max(STAT_DT) + 1 day as DT, 0 from a_test -- 算出最大日期
union all
select DT - 1 month, 0 from a
where DT > (select min(STAT_DT) from a_test) + 1 day -- 算出最小日期
)
, b as (
select STAT_DT, sum(AMT) as AMT
from (
select STAT_DT, AMT
from a_test
union all
select DT - 1 day, AMT
froma
) x
group by STAT_DT
)
SELECT stat_dt, AMT
, sum(AMT) OVER (ORDER BY STAT_DT rows between 2 preceding AND 1 preceding) AS 上月数据
, sum(AMT) OVER (ORDER BY STAT_DT rows BETWEEN 24 preceding AND 12 preceding) AS 去年同期数据
FROM b
order by stat_dt
;
