Oracle分析函数实现报表功能(同比,环比,累计,基比)
来源:互联网 发布:写作软件ios统计 编辑:程序博客网 时间:2024/04/28 09:08
CREATE TABLE salaryByMonth
(
employeeNo varchar2(20),
yearMonth varchar2(6),
salary number
) ;
insert into SALARYBYMONTH (EMPLOYEENO, YEARMONTH, SALARY)
values (1, '200805', 500);
insert into SALARYBYMONTH (EMPLOYEENO, YEARMONTH, SALARY)
values (1, '200802', 150);
insert into SALARYBYMONTH (EMPLOYEENO, YEARMONTH, SALARY)
values (1, '200803', 200);
insert into SALARYBYMONTH (EMPLOYEENO, YEARMONTH, SALARY)
values (1, '200804', 300);
insert into SALARYBYMONTH (EMPLOYEENO, YEARMONTH, SALARY)
values (1, '200708', 100);
commit;
SELECT EMPLOYEENO
,YEARMONTH
,SALARY
,MIN(SALARY) KEEP(DENSE_RANK FIRST ORDER BY YEARMONTH) OVER(PARTITION BY EMPLOYEENO) FIRST_SALARY -- 基比分析 salary/first_salary
,LAG(SALARY, 1, 0) OVER(PARTITION BY EMPLOYEENO ORDER BY YEARMONTH) AS PREV_SAL -- 环比分析,与上个月份进行比较
,LAG(SALARY, 12, 0) OVER(PARTITION BY EMPLOYEENO ORDER BY YEARMONTH) AS PREV_12_SAL -- 同比分析,与上个年度相同月份进行比较
,SUM(SALARY) OVER(PARTITION BY EMPLOYEENO, SUBSTR(YEARMONTH, 1, 4) ORDER BY YEARMONTH RANGE UNBOUNDED PRECEDING) LJ --累计值
FROM SALARYBYMONTH
ORDER BY EMPLOYEENO
,YEARMONTH
来源:http://blog.csdn.net/hhuxyb/article/details/5190173
- Oracle分析函数实现报表功能(同比,环比,累计,基比)
- Oracle同比、环比和累计
- Oracle BIEE 中的时间维与时间轴函数,同期,同比,环比,累计,同期累计计算
- SSAS使用时间智能解决本年累计、同比、环比
- 【概念】同比、环比、定基比
- 同比、环比、定基比
- 同比环比(案例)
- 使用lag函数 统计环比 同比
- 同比环比
- 环比与同比
- 什么是同比、环比与定基比
- 浅析“同比”与“环比”
- 统计学的环比、同比
- OTHER:环比与同比
- 使用商业智能分析系统进行同比、定比、环比分析
- 集成FusionCharts图表,可实现同比环比
- 用分析服务SSAS解决占比、同比和环比问题
- 会计基础(2)同比和环比
- arm c中字节对齐
- ireport报表用法简单介绍
- 金山树形控件创建
- LINK : warning LNK4089: all references to “SHELL32.dll” discarded by /OPT:REF
- ZendStudio破解步骤
- Oracle分析函数实现报表功能(同比,环比,累计,基比)
- PhoneGap API中文帮助文档——Camera (摄像头)
- public/private/internal/protected访问修饰符
- VS2010 VC++ Directories editing in Tools>Options has been deprecated.
- android系统结构分析
- Linux Eclipse下如何打包生成.apk文件
- 开源!!一个简单的代理吸附工具
- linux下与windows下的换行符
- C#中的Invoke