rownum和分析函数 over

select rownum, t.* from qyuser.tr_apply_info t where rownum < 10;

--rownum 对于满足 查询条件的结果，从1 开始，所以大于1不行
select z.* from (select t.*, rownum from qyuser.tr_apply_info t where rownum > 10 and rownum < 20) z;

select * from (select rownum rn,t.*from qyuser.tr_apply_info t where rownum <20) where rn >10;

SELECT * FROM (select rownum rn, t.* from qyuser.tr_apply_info t where rownum < 20) where rn > 10;

--rownum sql执行顺序：
--where group by having rownum order by
--默认rownum在order by之前，所以排序放到rownum之前执行
--内部rownum 需要重命名，否则外部列重名
select * from
( select rownum rn, tt.* from
(select * from qyuser.tr_apply_info t order by t.apply_id desc) tt
where rownum <= 20) where rn >10;

 

--oracle分析函数
--rank跳跃排序 1 1 3 4 4 6
select rank() over(partition by channel_type order by channel_id desc) rank ,t.* from qyuser.tr_channel t ;
select t.channel_type, t.channel_id,dense_rank() over(partition by channel_type order by channel_id desc) rank from qyuser.tr_channel t ;
-- row_number
select t.prod_name,prod_mode , prod_price ,row_number() over(partition by prod_mode order by prod_price desc) rank from qyuser.tr_product t
--dense_rank顺序排序 1 1 2 3 3 4
select t.prod_name,prod_mode , prod_price ,dense_rank() over(partition by prod_mode order by t. desc) rank from qyuser.tr_product t

--每组每行 累计求和
select t.name, t.class, t.sroce, sum(t.sroce) over(partition by t.class order by sroce desc) mm from t2_temp t;

select t.name,t.class,t.sroce,sum(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

select t.name, t.class, t.sroce, first_value(t.sroce) over(partition by t.class order by t.sroce desc) from t2_temp t;
select t.name, t.class, t.sroce, min(t.sroce) over(partition by t.class order by t.sroce ) from t2_temp t;

select t.name, t.class, t.sroce, count(t.sroce) over(partition by t.class order by t.sroce ) from t2_temp t;
select t.name, t.class, t.sroce, count(t.name) over(partition by t.class order by t.sroce ) from t2_temp t;

select t.name, t.class, t.sroce, avg(t.sroce) over(partition by t.class order by t.sroce ) from t2_temp t;