Oracle 窗口函數等高級運用

 

代码

-- Create table
create table CUS_SECTION
(
SECTION_ID NUMBER ,
SECTION_NAME VARCHAR2 ( 10 ),
WORK_TIME NUMBER ,
UPDATE_USERID VARCHAR2 ( 10 ),
UPDATE_DATE DATE default sysdate,
SECTION_DESC VARCHAR2 ( 20 ),
SECTION_DEMO NUMBER ,
SEQ NUMBER ,
SHIFT_ID NUMBER ,
SECTION_SEQ NUMBER
)



select to_char(sysdate, ' yyyy-mm-dd hh:mi:ss ' )now_date,
to_char(sysdate + ( 30 / 24 / 60 ), ' yyyy-mm-dd hh:mi:ss ' ) new_date from dual;

select to_char(sysdate, ' yyyy-mm-dd hh:mi:ss ' )now_date,
to_char(sysdate - ( 30 / 24 / 60 ), ' yyyy-mm-dd hh:mi:ss ' ) new_date from dual;

select add_months(sysdate, - 4 ) from dual;

select months_between(sysdate,to_date( ' 2008-01-01 02:00:00 ' , ' yyyy-mm-dd hh:mi:ss ' )) result from dual;

select add_months(sysdate, 2 * 12 ) two_years_later from dual;

select last_day(sysdate) last_day,last_day(sysdate) + 1 fisrt_day from dual;

select next_day(sysdate, 4 ) from dual;

select to_char(sysdate, ' yyyy-mm-dd hh:mi:ss ' ) now_date,
to_char( round (sysdate), ' yyyy-mm-dd hh:mi:ss ' ) round_date,
to_char(trunc(sysdate), ' yyyy-mm-dd hh:mi:ss ' ) trunc_date
from dual;

select to_char(sysdate, ' yyyy-mm-dd hh:mi:ss ' ) now_date,
to_char( round (sysdate, ' hh24 ' ), ' yyyy-mm-dd hh:mi:ss ' ) round_date,
to_char(trunc(sysdate, ' hh24 ' ), ' yyyy-mm-dd hh:mi:ss ' ) trunc_date
from dual;

select section_id,section_name,shift_id,
sum (work_time) section_times,
sum ( sum (work_time)) over (partition by shift_id) shift_times
from cus_section
group by section_id,section_name,shift_id

select section_id,section_name,shift_id,
sum (work_time) section_times,
sum ( sum (work_time)) over (partition by shift_id) shift_times,
sum ( sum (work_time)) over () all_times
from cus_section
group by section_id,section_name,shift_id

select section_id,section_name,
sum (work_time) section_times,
rank() over ( order by sum (work_time) desc ) rank,
dense_rank() over ( order by sum (work_time) desc ) dense_rank,
row_number() over ( order by sum (work_time) desc ) row_number
from cus_section
group by section_id,section_name

select section_id,section_name,
sum (work_time) section_times,
rank() over (partition by shift_id order by sum (work_time) desc nulls last) rank,
dense_rank() over (partition by shift_id order by sum (work_time) desc ) dense_rank,
row_number() over (partition by shift_id order by sum (work_time) desc ) row_number
from cus_section
group by section_id,section_name,shift_id

select min (section_id) keep(dense_rank first order by sum (work_time) desc ) first,
min (section_id) keep(dense_rank last order by sum (work_time) desc ) last
from cus_section
group by section_id

select section_id,section_name,shift_id,
ntile( 5 ) over ( order by shift_id) shift_times
from cus_section
group by section_id,section_name,shift_id


select section_id,section_name,
sum (work_time) section_times,
sum ( sum (work_time)) over ( order by section_id rows between unbounded preceding and unbounded following) sum_shift_times
from cus_section
group by section_id,section_name,shift_id


select section_id,section_name,
sum (work_time) section_times,
sum ( sum (work_time)) over ( order by section_id rows between 1 preceding and unbounded following) sum_shift_times
from cus_section
group by section_id,section_name,shift_id

select section_id,section_name,
sum (work_time) section_times,
sum ( sum (work_time)) over ( order by section_id rows between unbounded preceding and current row) sum_shift_times,
avg ( sum (work_time)) over ( order by section_id rows between unbounded preceding and current row) avg_shift_times
from cus_section
group by section_id,section_name,shift_id


select trunc(update_date) day ,
sum (work_time) section_times,
avg ( sum (work_time)) over ( order by trunc(update_date) range between interval ' 2 ' day preceding
and interval ' 2 ' day following) five_shift_times
from cus_section
group by update_date


select section_id,section_name,shift_id,
sum (work_time) section_times,
sum ( sum (work_time)) over (partition by shift_id) shift_times ,
round (ratio_to_report( sum (work_time)) over (partition by shift_id), 2 ) shift_present
from cus_section
group by section_id,section_name,shift_id


select * from sys_bom
where bom_id = 1000000135 ;

select * from sys_bom
where item_part_id = 1000000135

select distinct level ,bom_id,item_part_id from sys_bom
start with bom_id = 1000000135 connect by prior bom_id = item_part_id order by bom_id;
select distinct level ,bom_id,item_part_id from sys_bom
start with bom_id = 1000000135 connect by bom_id = prior item_part_id order by level

select count ( distinct level ) from sys_bom
start with bom_id = 1000000135 connect by bom_id = prior item_part_id

select level , count ( level ) from sys_bom
start with bom_id = 1000000135 connect by bom_id = prior item_part_id
group by level

select level ,bom_id,item_part_id from sys_bom where level = 2
start with bom_id = 1000000135 connect by bom_id = prior item_part_id

select level ,bom_id,item_part_id from sys_bom
start with bom_id = 1000000135 connect by bom_id = prior item_part_id

 

转载于:https://www.cnblogs.com/Doitman/archive/2010/07/09/1774357.html