嘗試模擬 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

您的留言將使用 WordPress.com 帳號。 登出 / 變更 )

Twitter picture

您的留言將使用 Twitter 帳號。 登出 / 變更 )

Facebook照片

您的留言將使用 Facebook 帳號。 登出 / 變更 )

Google+ photo

您的留言將使用 Google+ 帳號。 登出 / 變更 )

連結到 %s

%d 位部落客按了讚: