`
xshq
  • 浏览: 43991 次
  • 性别: Icon_minigender_1
  • 来自: 广州
社区版块
存档分类
最新评论

Oracle Analytic Functions

阅读更多

analytic_function::=


analytic_clause::= 

 

query_partition_clause::=

 

order_by_clause::=

 

windowing_clause ::=

 

1、创建表

create table LIB
(
  BILL_MONTH VARCHAR2(10),
  AREA_CODE  VARCHAR2(8),
  NET_TYPE   CHAR(1),
  LOCAL_FARE NUMBER(10,2)
)

2、插入数据

insert into LIB (BILL_MONTH, AREA_CODE, NET_TYPE, LOCAL_FARE)
values ('200405', '5761', 'G', 7393344.04);
insert into LIB (BILL_MONTH, AREA_CODE, NET_TYPE, LOCAL_FARE)
values ('200405', '5761', 'J', 5667089.85);
insert into LIB (BILL_MONTH, AREA_CODE, NET_TYPE, LOCAL_FARE)
values ('200405', '5762', 'G', 6315075.96);
insert into LIB (BILL_MONTH, AREA_CODE, NET_TYPE, LOCAL_FARE)
values ('200405', '5762', 'J', 6328716.15);
insert into LIB (BILL_MONTH, AREA_CODE, NET_TYPE, LOCAL_FARE)
values ('200405', '5763', 'G', 8861742.59);
insert into LIB (BILL_MONTH, AREA_CODE, NET_TYPE, LOCAL_FARE)
values ('200405', '5763', 'J', 7788036.32);
insert into LIB (BILL_MONTH, AREA_CODE, NET_TYPE, LOCAL_FARE)
values ('200405', '5764', 'G', 6028670.45);
insert into LIB (BILL_MONTH, AREA_CODE, NET_TYPE, LOCAL_FARE)
values ('200405', '5764', 'J', 6459121.49);
insert into LIB (BILL_MONTH, AREA_CODE, NET_TYPE, LOCAL_FARE)
values ('200405', '5765', 'G', 13156065.77);
insert into LIB (BILL_MONTH, AREA_CODE, NET_TYPE, LOCAL_FARE)
values ('200405', '5765', 'J', 11901671.7);
insert into LIB (BILL_MONTH, AREA_CODE, NET_TYPE, LOCAL_FARE)
values ('200406', '5761', 'G', 7614587.96);
insert into LIB (BILL_MONTH, AREA_CODE, NET_TYPE, LOCAL_FARE)
values ('200406', '5761', 'J', 5704343.05);
insert into LIB (BILL_MONTH, AREA_CODE, NET_TYPE, LOCAL_FARE)
values ('200406', '5762', 'G', 6556992.6);
insert into LIB (BILL_MONTH, AREA_CODE, NET_TYPE, LOCAL_FARE)
values ('200406', '5762', 'J', 6238068.05);
insert into LIB (BILL_MONTH, AREA_CODE, NET_TYPE, LOCAL_FARE)
values ('200406', '5763', 'G', 9130055.46);
insert into LIB (BILL_MONTH, AREA_CODE, NET_TYPE, LOCAL_FARE)
values ('200406', '5763', 'J', 7990460.25);
insert into LIB (BILL_MONTH, AREA_CODE, NET_TYPE, LOCAL_FARE)
values ('200406', '5764', 'G', 6387706.01);
insert into LIB (BILL_MONTH, AREA_CODE, NET_TYPE, LOCAL_FARE)
values ('200406', '5764', 'J', 6907481.66);
insert into LIB (BILL_MONTH, AREA_CODE, NET_TYPE, LOCAL_FARE)
values ('200406', '5765', 'G', 13562968.81);
insert into LIB (BILL_MONTH, AREA_CODE, NET_TYPE, LOCAL_FARE)
values ('200406', '5765', 'J', 12495492.5);
insert into LIB (BILL_MONTH, AREA_CODE, NET_TYPE, LOCAL_FARE)
values ('200407', '5761', 'G', 7987050.65);
insert into LIB (BILL_MONTH, AREA_CODE, NET_TYPE, LOCAL_FARE)
values ('200407', '5761', 'J', 5723215.28);
insert into LIB (BILL_MONTH, AREA_CODE, NET_TYPE, LOCAL_FARE)
values ('200407', '5762', 'G', 6833096.68);
insert into LIB (BILL_MONTH, AREA_CODE, NET_TYPE, LOCAL_FARE)
values ('200407', '5762', 'J', 6391201.44);
insert into LIB (BILL_MONTH, AREA_CODE, NET_TYPE, LOCAL_FARE)
values ('200407', '5763', 'G', 9410815.91);
insert into LIB (BILL_MONTH, AREA_CODE, NET_TYPE, LOCAL_FARE)
values ('200407', '5763', 'J', 8076677.41);
insert into LIB (BILL_MONTH, AREA_CODE, NET_TYPE, LOCAL_FARE)
values ('200407', '5764', 'G', 6456433.23);
insert into LIB (BILL_MONTH, AREA_CODE, NET_TYPE, LOCAL_FARE)
values ('200407', '5764', 'J', 6987660.53);
insert into LIB (BILL_MONTH, AREA_CODE, NET_TYPE, LOCAL_FARE)
values ('200407', '5765', 'G', 14000101.2);
insert into LIB (BILL_MONTH, AREA_CODE, NET_TYPE, LOCAL_FARE)
values ('200407', '5765', 'J', 12301780.2);
insert into LIB (BILL_MONTH, AREA_CODE, NET_TYPE, LOCAL_FARE)
values ('200408', '5761', 'G', 8085170.84);
insert into LIB (BILL_MONTH, AREA_CODE, NET_TYPE, LOCAL_FARE)
values ('200408', '5761', 'J', 6050611.37);
insert into LIB (BILL_MONTH, AREA_CODE, NET_TYPE, LOCAL_FARE)
values ('200408', '5762', 'G', 6854584.22);
insert into LIB (BILL_MONTH, AREA_CODE, NET_TYPE, LOCAL_FARE)
values ('200408', '5762', 'J', 6521884.5);
insert into LIB (BILL_MONTH, AREA_CODE, NET_TYPE, LOCAL_FARE)
values ('200408', '5763', 'G', 9468707.65);
commit;

 

3、测试

select lib.area_code,
       sum(lib.local_fare) sum_fare,
       rank() over(order by sum(lib.local_fare) desc) fare_rank
  from lib
 group by lib.area_code
------------
select lib.area_code,
       sum(lib.local_fare) sum_fare,
       dense_rank() over(order by sum(lib.local_fare) desc) fare_rank
  from lib
 group by lib.area_code
--------
select lib.area_code,
       sum(lib.local_fare) sum_fare,
       row_number() over(order by sum(lib.local_fare) desc) fare_rank
  from lib
 group by lib.area_code
--------
select lib.area_code,
       lib.bill_month,
       lib.local_fare,
       lag(lib.local_fare, 2, 0) over(partition by lib.area_code order by lib.bill_month) pre_local_fare,
       lag(lib.local_fare, 1, 0) over(partition by lib.area_code order by lib.bill_month) last_local_fare,
       lead(lib.local_fare, 1, 0) over(partition by lib.area_code order by lib.bill_month) next_local_fare,
       lead(lib.local_fare, 2, 0) over(partition by lib.area_code order by lib.bill_month) post_local_fare
  from (select lib.area_code, lib.bill_month, sum(lib.local_fare) local_fare
          from lib
         group by lib.area_code, lib.bill_month) lib
-----------
select lib.area_code,
       lib.bill_month,
       lib.local_fare,
       sum(lib.local_fare) over(partition by area_code order by to_number(bill_month) range between 1 preceding and 1 following) "3month_sum",
       avg(lib.local_fare) over(partition by area_code order by to_number(bill_month) range between 1 preceding and 1 following) "3month_avg",
       max(lib.local_fare) over(partition by area_code order by to_number(bill_month) range between 1 preceding and 1 following) "3month_max",
       min(lib.local_fare) over(partition by area_code order by to_number(bill_month) range between 1 preceding and 1 following) "3month_min"
  from (select lib.area_code, lib.bill_month, sum(lib.local_fare) local_fare
          from lib
         group by lib.area_code, lib.bill_month) lib
------------
select lib.bill_month,
       lib.area_code,
       sum(lib.local_fare) local_fare,
       ratio_to_report(sum(lib.local_fare)) over(partition by lib.bill_month) area_pct
  from lib
 group by lib.bill_month, lib.area_code
-------
select lib.bill_month,
       lib.area_code,
       sum(lib.local_fare) local_fare,
       first_value(lib.area_code) over(order by sum(lib.local_fare) desc rows unbounded preceding) firstval,
       first_value(lib.area_code) over(order by sum(lib.local_fare) asc rows unbounded preceding) lastval
  from lib
 group by lib.bill_month, lib.area_code
 order by lib.bill_month

 

  • 大小: 3 KB
  • 大小: 2.6 KB
  • 大小: 3.1 KB
  • 大小: 4.9 KB
  • 大小: 8 KB
分享到:
评论

相关推荐

    Pro Oracle SQL

    You’ll learn analytic functions, the MODEL clause, and advanced grouping syntax—techniques that will help in creating good queries for reporting and business intelligence applications. Pro Oracle ...

    Oracle10g 数据仓库

    12. **高级分析函数(Analytic Functions)**:提供了窗口函数和集合函数,用于在单个查询中进行复杂的分析运算。 13. **数据仓库构建工具(Data Warehouse Builder)**:Oracle10g提供了直观的图形界面,帮助用户...

    oracle 10.2 chm文档

    2. **SQL语言增强**:10.2版本增加了SQL语法的改进,如Analytic Functions(分析函数)和窗口函数,使复杂的数据分析和报表生成更便捷。 3. **PL/SQL优化**:PL/SQL是Oracle的嵌入式过程语言,10.2版提供了更好的...

    Oracle中的分析函数详解

    分析函数(Analytic Functions)是Oracle SQL中的高级特性,它们在数据集上执行计算,并返回基于分组或排序的数据结果。与聚合函数(如SUM, AVG, COUNT等)不同,分析函数可以在每个行级别上返回结果,而不只是返回...

    Oracle10g(10.2)数据库及其文档的下载

    新的SQL功能如Analytic Functions和Materialized Views增强了数据分析能力。Oracle的查询优化器通过CBO(Cost-Based Optimizer)自动选择执行计划,以实现最佳性能。此外,10g还引入了SQL Performance Analyzer,...

    oracle数据库期末考试试题及答案.docx

    **解析**: 分析函数(Analytic functions)在Oracle数据库中被用于进行复杂的数据分析,如累计排名、移动平均数、百分比计算等。这些函数可以基于一组行执行计算,并返回多个行的结果,适用于报表聚合等场景。因此,正确...

    Oracle10g_学习笔记.zip

    此外,还有Oracle特有的SQL扩展,如Analytic Functions和Nested Queries。 五、PL/SQL编程 PL/SQL是Oracle的内置过程式语言,用于编写存储过程、函数、触发器等。它结合了SQL的查询功能和传统编程语言的控制结构,...

    Oracle试题及答案

    - **分析函数(Analytic Functions)**:如ROW_NUMBER()、RANK()等,用于处理分组数据或执行复杂的数据排序和窗口运算。 - **分组函数(Grouping Functions)**:如GROUP_CONCAT,用于组合多行数据成一个字符串。 - *...

    oracle 9i 10g编程艺术

    在9i和10g中,可以使用Analytic Functions进行复杂的分析操作,利用Materialized Views加速复杂查询。 四、PL/SQL编程 Oracle的PL/SQL是一种过程化语言,结合了SQL的功能,用于编写存储过程、函数和触发器。9i和10g...

    Oracle10gR2分析函数(中英对照版).pdf

    分析函数(Analytic Functions)是Oracle 10g R2数据库引入的一项重要特性,用于解决传统SQL聚合函数无法满足的复杂数据分析需求。与标准的聚合函数(如SUM, AVG, COUNT等)不同,分析函数可以在分组后的数据集上...

    oracle sql program

    7. 分析函数(Analytic Functions):类似于窗口函数,可以在分组数据上进行滑动计算,如RANK、ROW_NUMBER、LEAD和LAG等。 8. 存储过程(Stored Procedures)和函数:预编译的SQL代码块,可封装复杂逻辑并重复使用。...

    Oracle Database

    - **Analytic Functions**:增加了更多的分析函数,如 LAG() 和 LEAD() 函数,这些函数可以帮助用户轻松地进行复杂的统计计算。 - **Windowing**:支持窗口函数,如 RANK()、ROW_NUMBER() 等,这些函数可以在结果...

    oracle高级sql讲座

    4. **窗口函数(Analytic Functions)**:Oracle的窗口函数允许你在结果集的特定“窗口”内执行计算,如RANK()、ROW_NUMBER()、LAG()、LEAD()等,它们在数据分析和排名问题中非常有用。 5. **集合操作(UNION、...

    oracle分析函数over_及开窗函数.txt

    在Oracle数据库中,分析函数(Analytic Functions)是一种非常强大的工具,用于处理复杂的查询需求。这些函数可以在一组相关的行上执行计算,并且每行返回一个结果。与传统的聚合函数(如SUM, AVG等)不同的是,分析...

    Oracle表的管理

    3. **Analytic Functions方式**:利用分析函数如`ROW_NUMBER()`等来实现分页。 这些分页方式各有优缺点,可以根据实际需求选择最合适的一种。例如,使用ROWNUM的方式较为简单,但可能无法很好地支持复杂的查询逻辑...

    oracle分析函数

    在IT领域,尤其是在数据库管理与查询技术中,Oracle分析函数(Analytic Functions)是一套强大的工具,用于执行复杂的业务智能计算,如移动平均、排名以及领先/滞后比较等。这一功能集首次在Oracle 8i版本中引入,极...

    oracle管理员学习笔记

    #### 七、高级分析函数(Advanced Analytic Functions) 这些函数,如`LAG`, `LEAD`, `RANK`, `DENSE_RANK`, `ROW_NUMBER`等,提供了强大的工具,用于处理窗口函数,进行复杂的数据分析和报表制作。 #### 八、SQL...

Global site tag (gtag.js) - Google Analytics