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
分享到:
相关推荐
# Usage Instructions # ~~~~~~~~~~~~~~~~~~ # Usage: [n]awk -f ass.awk fname.trc (But read the Portability Section !!) # # Configuring Ass: # # By default, 'ass' attempts to dump as much information as...
A Spirallikeness Condition for Analytic Functions (1991年)
Oracle OLAP Customizing Analytic Workspace Manager 11g Release 2 (11.2)-76
Analytic Functions The Model Clause Subquery Factoring Semi-joins and Anti-joins Indexes Beyond the SELECT Transaction Processing Testing and Quality Assurance Plan Stability and Control SQL and the ...
Oracle Hyperion Essbase - Storing Analytic Data - 培训资料 Describe Essbase data storage options Explain the concepts of dense and sparse dimensions Describe data blocks and index structure View the ...
Written by renowned experts on both Oracle Essbase and Oracle OLAP, this exclusive Oracle Press guide explains the unique features and benefits of these powerful online analytic processing products....
1、AVG([DISTINCT|ALL] expr) OVER(analytic_clause) 计算平均值。 例如: --聚合函数 SELECT col, AVG(value) FROM tmp1 GROUP BY col ORDER BY col; --分析函数 SELECT col, AVG(value) OVER(PARTITION BY col ...
Analytic Functions CASE Expressions: Simple CASE Expressions: Searched CAST Function CONNECT BY Queries Data Type Conversion Data Types: Binary Integer Data Types: Character String Data Types: ...
Method of analytic tableauxMethod of analytic tableauxMethod of analytic tableaux
Take advantage of advanced Oracle SQL features such as support for hierarchical queries, analytic and window functions, flashback to a given point in time, and more Get a handle on Oracle Database’s...
Analytic Option 12cCharlie BergerSr. Director Product Management, DataMining and Advanced Analytics charlie.berger@oracle.comwww.twitter.com/CharlieDataMineCopyright :copyright: 2012, Oracle and/or ...
Calculus with Analytic Geometry. 2nd ed. New York, NY: McGraw-Hill, October 1, 1995. ISBN: 0070576424. Readings in the textbook are listed by section numbers (e.g., § 2.1-2.4 means read sections ...
从官方文档了解到其的定义为:阿里云分析型数据库AnalyticDB(简称ADB),是云端托管的PB级高并发实时数据仓库,是专注于服务OLAP领域的数据仓库。在数据存储模型上,采用关系模型进行数据存储,可以使用SQL进行自由...
2019云栖大会-AnalyticDB-快数据时代的实时数据仓库技术内幕,一起来学习阿里巴巴PB级数据处理机制
详解Oracle分析函数,主用于OLAP,以实例讲解分析函数. 如: 排序用Rank, Dense_rank, row_number 1.带空值的排列 2.Top/Bottom N查询 3.First/Last排名查询 4.按层次查询 1.窗口函数简介 2.窗口函数示例-全统计 3....
Data Science Automation Using Oracle Data Miner and Oracle R Enterprise starts with an introduction to business analytics, covering why automation is necessary and the level of complexity in ...
Complex Functions Examples C-2 Analytic Functions – FreePdf-Books.com
AnalyticDB for MySQL基础版技术解密.pdf
Introduction to matrix analytic methods in stochastic modeling