【IT168 技术文章】
1. partition
select c1,c2,c3, rank() over(partition by c2 order by c3 desc)
as r1, rank() over (order by c3 desc) as r2
from t1
where c2 in (15,20)
order by c2,c1
2. Rank ,Over
select c1,c2,c3 ,rank() over(order by c3 asc null last) c4
from t1
where c1 in(15,20)
3. reporting function
select c1,c2,c3 ,sum(c3) over (partition by c2) as s1
from t1
where c2 in(15,20)
--------------------------------------------------------------------------------------------------
db2 => select * from test
NO NAME KM CJ
----------- ----- ----- -----------
1 zsan yuwen 80
1 zsan shuxe 86
1 zsan eng 78
2 lisi yuwen 73
2 lisi shuxe 90
2 lisi eng 67
6 条记录已选择。
db2 => select name,km,cj,rank() over(order by cj desc) from test
NAME KM CJ 4
----- ----- ----------- --------------------
lisi shuxe 90 1
zsan shuxe 86 2
zsan yuwen 80 3
zsan eng 78 4
lisi yuwen 73 5
lisi eng 67 6
6 条记录已选择。
db2 => select name,km,cj,rank() over(partition by km order by cj desc) from test
NAME KM CJ 4
----- ----- ----------- --------------------
zsan eng 78 1
lisi eng 67 2
lisi shuxe 90 1
zsan shuxe 86 2
zsan yuwen 80 1
lisi yuwen 73 2
6 条记录已选择。
db2 => select name,km,cj,rank() over(partition by name order by cj desc) from te
st
NAME KM CJ 4
----- ----- ----------- --------------------
lisi shuxe 90 1
lisi yuwen 73 2
lisi eng 67 3
zsan shuxe 86 1
zsan yuwen 80 2
zsan eng 78 3
6 条记录已选择。
-------------------------------------------------------------------------------------------------
db2 => select name,km,cj,sum(cj) over(order by cj desc) from test
NAME KM CJ 4
----- ----- ----------- -----------
lisi shuxe 90 90
zsan shuxe 86 176
zsan yuwen 80 256
zsan eng 78 334
lisi yuwen 73 407
lisi eng 67 474
6 条记录已选择。
db2 => select name,km,cj,sum(cj) over(partition by name order by cj desc) from t
est
NAME KM CJ 4
----- ----- ----------- -----------
lisi shuxe 90 90
lisi yuwen 73 163
lisi eng 67 230
zsan shuxe 86 86
zsan yuwen 80 166
zsan eng 78 244
6 条记录已选择。
db2 => select name,km,cj,sum(cj) over(partition by name) from test
NAME KM CJ 4
----- ----- ----------- -----------
lisi yuwen 73 230
lisi shuxe 90 230
lisi eng 67 230
zsan yuwen 80 244
zsan shuxe 86 244
zsan eng 78 244
6 条记录已选择。
----------------------------------------------------------------------------------------------
Percent change
select year(sales_id) as y,quarter(sales_d) as q,
sum(sales) as tot_s,
sum(sales)*100/sum(sales) over(order by year(sales_d),quarter(sales_d)
rows between 1 preceding and 1 preceding)-100 as pct_change
from t1
group by year(sales_id) ,quarter(sales_d)
季度销售总额 sum(sales)
本季度的销售总额相对上一个季度的销售总额的增持的百分比
sum(sales)*100/sum(sales) over(order by year(sales_d),quarter(sales_d)
rows between 1 preceding and 1 preceding)-100
db2 => select name,km,cj,sum(cj)*100/sum(cj) over(order by name,km,cj rows between 1 pre
ceding and 1 preceding)-100 as pct_change from test group by name,km,cj
NAME KM CJ PCT_CHANGE
----- ----- ----------- -----------
lisi eng 67 -
lisi yuwen 73 8
zsan eng 78 6
zsan yuwen 80 2
zsan shuxe 86 7
lisi shuxe 90 4
6 条记录已选择。
db2 => select name,km,cj,sum(cj) as tot,sum(cj)*100/sum(cj) over(order by cj row
s between 1 preceding and 1 preceding)-100 as pct_change from test group by name
,km,cj
NAME KM CJ TOT PCT_CHANGE
----- ----- ----------- ----------- -----------
lisi eng 67 67 -
lisi yuwen 73 73 8
zsan eng 78 78 6
zsan yuwen 80 80 2
zsan shuxe 86 86 7
lisi shuxe 90 90 4
6 条记录已选择。
