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
分享到:
相关推荐
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 ...
12. **高级分析函数(Analytic Functions)**:提供了窗口函数和集合函数,用于在单个查询中进行复杂的分析运算。 13. **数据仓库构建工具(Data Warehouse Builder)**:Oracle10g提供了直观的图形界面,帮助用户...
2. **SQL语言增强**:10.2版本增加了SQL语法的改进,如Analytic Functions(分析函数)和窗口函数,使复杂的数据分析和报表生成更便捷。 3. **PL/SQL优化**:PL/SQL是Oracle的嵌入式过程语言,10.2版提供了更好的...
分析函数(Analytic Functions)是Oracle SQL中的高级特性,它们在数据集上执行计算,并返回基于分组或排序的数据结果。与聚合函数(如SUM, AVG, COUNT等)不同,分析函数可以在每个行级别上返回结果,而不只是返回...
新的SQL功能如Analytic Functions和Materialized Views增强了数据分析能力。Oracle的查询优化器通过CBO(Cost-Based Optimizer)自动选择执行计划,以实现最佳性能。此外,10g还引入了SQL Performance Analyzer,...
**解析**: 分析函数(Analytic functions)在Oracle数据库中被用于进行复杂的数据分析,如累计排名、移动平均数、百分比计算等。这些函数可以基于一组行执行计算,并返回多个行的结果,适用于报表聚合等场景。因此,正确...
此外,还有Oracle特有的SQL扩展,如Analytic Functions和Nested Queries。 五、PL/SQL编程 PL/SQL是Oracle的内置过程式语言,用于编写存储过程、函数、触发器等。它结合了SQL的查询功能和传统编程语言的控制结构,...
- **分析函数(Analytic Functions)**:如ROW_NUMBER()、RANK()等,用于处理分组数据或执行复杂的数据排序和窗口运算。 - **分组函数(Grouping Functions)**:如GROUP_CONCAT,用于组合多行数据成一个字符串。 - *...
在9i和10g中,可以使用Analytic Functions进行复杂的分析操作,利用Materialized Views加速复杂查询。 四、PL/SQL编程 Oracle的PL/SQL是一种过程化语言,结合了SQL的功能,用于编写存储过程、函数和触发器。9i和10g...
分析函数(Analytic Functions)是Oracle 10g R2数据库引入的一项重要特性,用于解决传统SQL聚合函数无法满足的复杂数据分析需求。与标准的聚合函数(如SUM, AVG, COUNT等)不同,分析函数可以在分组后的数据集上...
7. 分析函数(Analytic Functions):类似于窗口函数,可以在分组数据上进行滑动计算,如RANK、ROW_NUMBER、LEAD和LAG等。 8. 存储过程(Stored Procedures)和函数:预编译的SQL代码块,可封装复杂逻辑并重复使用。...
- **Analytic Functions**:增加了更多的分析函数,如 LAG() 和 LEAD() 函数,这些函数可以帮助用户轻松地进行复杂的统计计算。 - **Windowing**:支持窗口函数,如 RANK()、ROW_NUMBER() 等,这些函数可以在结果...
4. **窗口函数(Analytic Functions)**:Oracle的窗口函数允许你在结果集的特定“窗口”内执行计算,如RANK()、ROW_NUMBER()、LAG()、LEAD()等,它们在数据分析和排名问题中非常有用。 5. **集合操作(UNION、...
在Oracle数据库中,分析函数(Analytic Functions)是一种非常强大的工具,用于处理复杂的查询需求。这些函数可以在一组相关的行上执行计算,并且每行返回一个结果。与传统的聚合函数(如SUM, AVG等)不同的是,分析...
3. **Analytic Functions方式**:利用分析函数如`ROW_NUMBER()`等来实现分页。 这些分页方式各有优缺点,可以根据实际需求选择最合适的一种。例如,使用ROWNUM的方式较为简单,但可能无法很好地支持复杂的查询逻辑...
在IT领域,尤其是在数据库管理与查询技术中,Oracle分析函数(Analytic Functions)是一套强大的工具,用于执行复杂的业务智能计算,如移动平均、排名以及领先/滞后比较等。这一功能集首次在Oracle 8i版本中引入,极...
#### 七、高级分析函数(Advanced Analytic Functions) 这些函数,如`LAG`, `LEAD`, `RANK`, `DENSE_RANK`, `ROW_NUMBER`等,提供了强大的工具,用于处理窗口函数,进行复杂的数据分析和报表制作。 #### 八、SQL...