oracle上浮下浮分析函数_oracle 分析函数

除了最后的Order by子句之外,分析函数是在查询中执行的最后的操作集,这样的话,就不能直接在谓词中使用分析函数,即不能在上面使用where或having子句!!!

等级(ranking)函数:

用于寻找前N种查询;

①ROW_NUMBER：      Row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。  ②DENSE_RANK：      Dense_rank函数返回一个唯一的值，除非当碰到相同数据时，此时所有相同数据的排名都是一样的。  ③RANK：   Rank函数返回一个唯一的值，除非遇到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。

SQL> select region_id, customer_id, sum(customer_sales) total,

2         rank() over(order by sum(customer_sales) desc) rank,

3         dense_rank() over(order by sum(customer_sales) desc) dense_rank,

4         row_number() over(order by sum(customer_sales) desc  NULLS LAST) row_number

5    from user_order

6   group by region_id, customer_id;

order by sum(customer_sales) desc NULLS LAST

nulls last/first  告诉oracle让空值排名最后第一

开窗(windowing)函数:

用于计算不同的累计,如SUM,COUNT,AVG,MIN,MAX等,作用于数据的一个窗口上; 制表(reporting)函数:与开窗函数同名,作用于一个分区或一组上的所有列;

last/first排名查询

select               min(customer_id)            keep (dense_rank first order by sum(customer_sales) desc) first,            min(customer_id)            keep (dense_rank last order by sum(customer_sales) desc) last        from user_order   group by region_id;

min保证返回唯一记录 keep 告诉oracle保留符合keep条件的记录 dense_rank 排序策略 last/first 最终筛选条件

3、订单总额排名前1/5的客户

ntile(5) over(order by sum(customer_sales) desc) til

ntile(n)把记录集分为5个等份

窗口函数

之前的分析函数都是在一定时间范围内进行的(统计一个明确的阶段/记录集),但是统计如果要随着记录集的每一条记录而进行时(业务数据随着时间的而变化的)就不适用了，如:

①列出每月的订单总额以及全年的订单总额

②列出每月的订单总额以及截至到当前月的订单总额

③列出上个月、当月、下一月的订单总额以及全年的订单总额

④列出每天的营业额及一周来的总营业额

⑤列出每天的营业额及一周来每天的平均营业额

指定记录进行统计

rows between ... preceding and ... following

在当前记录之前XXX和之后XXX的所有记录，参考记录是当前记录

rows between unbounded preceding and current row

unbounded：无限的

rows between unbounded preceding and current row)

current：当前

指定范围的统计 range between interval '2' day preceding and interval '2' day following

通过rang和interval 指定前2天，后2天内的记录

比较相邻记录

frist_value(sum(sal)) over(order by month rows between 1 perceding and 0 following)

lag(sum(sal),1) over(order by month)

一、统计方面：

Sum() Over ([Partition by ] [Order by ])

Sum() Over ([Partition by ] [Order by ]        Rows Between  Preceding And  Following)

Sum() Over ([Partition by ] [Order by ]      Rows Between  Preceding And Current Row)

Sum() Over ([Partition by ] [Order by ]      Range Between Interval '' 'Day' Preceding  And Interval '' 'Day' Following )

二、排列方面：

Rank() Over ([Partition by ] [Order by ] [Nulls First/Last])

Dense_rank() Over ([Patition by ] [Order by ] [Nulls First/Last])

Row_number() Over ([Partitionby ] [Order by ] [Nulls First/Last])     Ntile() Over ([Partition by ] [Order by ])

三、最大值/最小值查找方面：

Min()/Max() Keep (Dense_rank First/Last [Partition by ] [Order by ])

四、首记录/末记录查找方面：

First_value / Last_value(Sum()) Over ([Patition by ] [Order by ]        Rows Between  Preceding And  Following  )

五、相邻记录之间比较方面： Lag(Sum(), 1) Over([Patition by ] [Order by ])