Maxcompute中COUNT和RANK联合Partition BY的案例

--按比例分为3级, 3级15%-2级30%-1级55%

INSERT OVERWRITE TABLE tableresult

SELECT v.post_id AS post_id, v.create_time AS create_time, v.cid AS cid, v.now_time AS now_time, v.score AS score

    , v.rownum AS rownum

    , IF(v.rownum < ${gradetopnum3}*cidnum, 3, IF(v.rownum < ${gradetopnum2}*cidnum, 2, 1)) AS grade

FROM (

    SELECT u.post_id AS post_id, u.create_time AS create_time, u.cid AS cid, u.now_time AS now_time, u.score AS score

        , RANK() OVER (PARTITION BY u.cid ORDER BY score DESC) AS rownum

        , COUNT(*) over (partition by u.cid) as cidnum

    FROM tableoriginal u

) v;

背景：

cid字段有多个取值，要计算postid在所属cid类别里的score的排名rownum，以及所属cid类别所有的条数