嘗試模擬 cume_dist 和 percent_rank 的定義

with v(Part,ID) as (select * from (values(1,null),(1,0),(1,1),(1,1),(1,3),
(2,1),(2,1),(2,3),(2,4)) t(Part,ID)),
maxRank(Part,ID,RankNO,IDCount) as (select Part,ID,RANK() OVER
(PARTITION BY Part ORDER BY ID )-1 AS [RankNo],
case when lag(id,1) over(order by Part,ID)=id then 0 else count(*) –CUME_DIST 有計算 null,所以這要 count(*) 否則是 count(id)
over(partition by Part,ID) end IDCount  from v)
SELECT Part,ID,
IDCount,
–‘累計到當下的筆數除以總紀錄數,但相同大小的紀錄,其筆數相同’,
sum(IDCount) over (PARTITION BY Part order by id rows between unbounded preceding and current row) as Cumulate,
convert(float,sum(IDCount) over (PARTITION BY Part order by id rows between unbounded preceding and current row))/count(*) over(partition by part) myCumeDist,
CUME_DIST () OVER (PARTITION BY Part ORDER BY ID) AS CumeDist,RankNo,

Max(RankNo) OVER (Partition By Part) [Max],
Convert(Float,RankNo)/Max(RankNo) OVER (Partition By Part) myPercentRank,
PERCENT_RANK() OVER (PARTITION BY Part ORDER BY ID ) AS PctRank
FROM maxRank

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: