oracle 常用函数

1. 多行函数
   
2. 分析函数
   

多行函数:

MIN   求最小值

MAX   求最大的

SUM  求和

AVG  求平均值

COUNT 计数

VARIANCE

STDDEV

分析函数:

KEEP( DENSE_RANK FIRST  |  DENSE_RANK LAST    ORDER BY col )

获取一组数据中,排名第一或者排名最后的记录。必须有order by 子句用来排序。

后面也可以接over（）分析函数部分。

实例: 

           Min（col2）keep（dense_rank first order by col1）  保留按col1排序第一的col2的最小值。

           Min（col2）keep（dense_rank first order by col1）over (partition by col3)  按col3分组保留按col1排序各组第一的col2的最小值。

问题描述：
每天收集各个批发市场各种水果的价格。市场有多个，但规模(MARKET_LEVEL)不同。如果同一种水果，有多个市场的价格，那么以规模最大的市场为准。最后查出的结果是每种水果一条记录。

表结构与样例数据：
create table fruit_quotation(
fruit_name varchar2(10),  --水果种类名称
quotation_date date not null,       --行情日期
price number(4,2) not null,          -- 价格
market varchar2(20) not null,       --市场名称
market_level number not null,       --市场规模
constraint PK_FRUIT_QUOT PRIMARY KEY (fruit_name,market)
);

insert into fruit_quotation values('红富士苹果',   to_date('2009/11/9', 'yyyy/mm/dd'), 3.5, '福州和平市场', 1);
insert into fruit_quotation values('红富士苹果',   to_date('2009/11/9', 'yyyy/mm/dd'), 4.0, '西营里市场', 2);
insert into fruit_quotation values('红富士苹果',   to_date('2009/11/9', 'yyyy/mm/dd'), 3.6, '塔头市场', 3);
insert into fruit_quotation values('江西脐橙',  to_date('2009/11/9', 'yyyy/mm/dd'), 2.0, '福州和平市场', 1);
insert into fruit_quotation values('江西脐橙',  to_date('2009/11/9', 'yyyy/mm/dd'), 2.5, '西营里市场', 2);
insert into fruit_quotation values('天宝香蕉',  to_date('2009/11/9', 'yyyy/mm/dd'), 3.2, '福州和平市场', 1);
insert into fruit_quotation values('天宝香蕉',  to_date('2009/11/9', 'yyyy/mm/dd'), 3.5, '西营里市场', 2);
insert into fruit_quotation values('普通梨',    to_date('2009/11/9', 'yyyy/mm/dd'), 2.9, '福州和平市场', 1);

commit;


 


SQL> select * from fruit_quotation order by fruit_name;
 
FRUIT_NAME QUOTATION_DATE  PRICE MARKET               MARKET_LEVEL
---------- -------------- ------ -------------------- ------------
红富士苹果 2009/11/9        4.00 西营里市场                      2
红富士苹果 2009/11/9        3.60 塔头市场                        3
红富士苹果 2009/11/9        3.50 福州和平市场                    1
江西脐橙   2009/11/9        2.00 福州和平市场                    1
江西脐橙   2009/11/9        2.50 西营里市场                      2
普通梨     2009/11/9        2.90 福州和平市场                    1
天宝香蕉   2009/11/9        3.50 西营里市场                      2
天宝香蕉   2009/11/9        3.20 福州和平市场                    1


 


期望的查询结果：


FRUIT_NAME QUOTATION_DATE      PRICE MARKET               MARKET_LEVEL
---------- -------------- ---------- -------------------- ------------
红富士苹果 2009/11/9             3.6 塔头市场                        3
江西脐橙   2009/11/9             2.5 西营里市场                      2
普通梨     2009/11/9             2.9 福州和平市场                    1
天宝香蕉   2009/11/9             3.5 西营里市场                      2

更多0 分享到：



方法一:

select fruit_name,
max(quotation_date)keep(dense_rank last order by market_level)quotation_date,
max(price)keep(dense_rank last order by market_level)price,
max(market)keep(dense_rank last order by market_level)market,
max(market_level)market_level
from fruit_quotation t
group by fruit_name

方法二:

select t2.* from
(select fruit_name,max(market_level) market_level
from fruit_quotation
group by fruit_name) t1,fruit_quotation t2
where t1.fruit_name= t2.fruit_name
and t1.market_level= t2.market_level

方法三:

select *
  from (select row_number() over(partition by fruit_name order by market_level desc) rn,
                a.*
           from fruit_quotation a)
 where rn = 1