摘要：下面以論壇用戶貢獻(xiàn)值為例子來(lái)演示一個(gè)熱度分的計(jì)算過(guò)程。一個(gè)改進(jìn)的方案是，排名之后按區(qū)間分段，比如打分，打分，以此類推。

Web開(kāi)發(fā)會(huì)經(jīng)常遇到給實(shí)體打分的需求，比如論壇用戶的聲望分、電商系統(tǒng)的類目和產(chǎn)品熱度分，新聞的熱度分等。有些分?jǐn)?shù)只需要排序使用，有些分?jǐn)?shù)需要顯示給用戶，讓用戶看到分?jǐn)?shù)之后能直觀的感受到這個(gè)分?jǐn)?shù)所處的位置。往往熱度分的計(jì)算并不只是參考單一維度，會(huì)有很多維度的參考。比如，如果我們想計(jì)算一個(gè)論壇的用戶的綜合貢獻(xiàn)值，需要參考回帖數(shù)量、發(fā)帖數(shù)量、被點(diǎn)贊數(shù)量等指標(biāo)。下面以論壇用戶貢獻(xiàn)值為例子來(lái)演示一個(gè)熱度分的計(jì)算過(guò)程。

首先，初始化一個(gè)用戶表，200條記錄，字段為id,post_count, reply_count, faver_count,值為隨機(jī)整數(shù)。

    SELECT row_number() over() as id, 
           (generate_series * random())::integer as post_count, 
           (generate_series * random())::integer as reply_count, 
           (generate_series * random())::integer as faver_count
INTO TABLE users
      FROM (SELECT * FROM generate_series(1, 200)) AS r;

users表數(shù)據(jù)如下：

select * from users limit 10;
 id | post_count | reply_count | faver_count
----+------------+-------------+-------------
  1 |          0 |           0 |           0
  2 |          2 |           1 |           1
  3 |          2 |           2 |           1
  4 |          3 |           0 |           1
  5 |          3 |           3 |           4
  6 |          4 |           3 |           3
  7 |          4 |           0 |           3
  8 |          5 |           4 |           3
  9 |          7 |           2 |           5
 10 |          6 |           3 |           5

最簡(jiǎn)單的想法是單指標(biāo)的排名相加，比如，按post_count從大到小排序，算出post_num，reply和faver同理：

SELECT *,
       row_number() over(order by post_count desc) as post_num, 
       row_number() over(order by reply_count desc) as reply_num, 
       row_number() over(order by faver_count desc) as faver_num  
  FROM users;

計(jì)算結(jié)果如下，score = a * post_num + b * reply_num + c * faver_num，其中abc為加權(quán)系數(shù)。這樣的計(jì)算方法存在一個(gè)問(wèn)題，score的范圍不確定，一個(gè)用戶打了99分的話，我們無(wú)法從99這個(gè)數(shù)值看出他處于什么位置。

id  | post_count | reply_count | faver_count | post_num | reply_num | faver_num
-----+------------+-------------+-------------+----------+-----------+-----------
 187 |         62 |          25 |         173 |       70 |       122 |         1
 169 |          6 |          57 |         167 |      176 |        69 |         2
 171 |          2 |          46 |         162 |      193 |        84 |         3
 172 |         41 |         152 |         162 |      101 |         6 |         4
 200 |         76 |         193 |         149 |       51 |         1 |         5
 156 |         62 |         116 |         144 |       69 |        28 |         6
 166 |        114 |          31 |         144 |       20 |       109 |         7
 153 |        127 |          97 |         138 |       10 |        39 |         8
 135 |         25 |         131 |         135 |      135 |        16 |         9
 186 |        112 |         147 |         134 |       23 |         7 |        10
 163 |        106 |         134 |         133 |       28 |        13 |        11
 155 |        124 |           3 |         132 |       14 |       188 |        12
 173 |         74 |          92 |         132 |       53 |        42 |        13
 133 |        119 |          78 |         132 |       17 |        53 |        14

一個(gè)改進(jìn)的方案是，排名之后按區(qū)間分段，比如1-10打1分，11-20打2分，以此類推。這樣可以把每個(gè)指標(biāo)的范圍確定，再加權(quán)之后范圍也是可以計(jì)算的。

SELECT *,
       ntile(10) over(order by post_count desc) as post_num, 
       ntile(10) over(order by reply_count desc) as reply_num, 
       ntile(10) over(order by faver_count desc) as faver_num  
  FROM users;

按區(qū)間分段存在的問(wèn)題是，結(jié)果不夠平滑，也不能反映不同用戶之間的差別，比如第一名和第二名分別為10000和100，分段之后他們得到相同的分?jǐn)?shù)，體現(xiàn)不出差異。

id  | post_count | reply_count | faver_count | post_num | reply_num | faver_num
-----+------------+-------------+-------------+----------+-----------+-----------
 187 |         62 |          25 |         173 |        4 |         7 |         1
 169 |          6 |          57 |         167 |        9 |         4 |         1
 171 |          2 |          46 |         162 |       10 |         5 |         1
 172 |         41 |         152 |         162 |        6 |         1 |         1
 200 |         76 |         193 |         149 |        3 |         1 |         1
 156 |         62 |         116 |         144 |        4 |         2 |         1
 166 |        114 |          31 |         144 |        1 |         6 |         1
 153 |        127 |          97 |         138 |        1 |         2 |         1
 135 |         25 |         131 |         135 |        7 |         1 |         1
 186 |        112 |         147 |         134 |        2 |         1 |         1
 163 |        106 |         134 |         133 |        2 |         1 |         1
 155 |        124 |           3 |         132 |        1 |        10 |         1
 173 |         74 |          92 |         132 |        3 |         3 |         1
 133 |        119 |          78 |         132 |        1 |         3 |         1
 174 |         42 |          14 |         131 |        5 |         8 |         1
 181 |        120 |          10 |         130 |        1 |         8 |         1
 148 |         38 |         120 |         129 |        6 |         2 |         1
 176 |         84 |         105 |         128 |        3 |         2 |         1
 128 |        113 |         103 |         128 |        2 |         2 |         1
 179 |        114 |         118 |         127 |        1 |         2 |         1
 157 |         66 |         120 |         127 |        4 |         2 |         2
 198 |        122 |          35 |         126 |        1 |         6 |         2
 195 |        166 |         112 |         118 |        1 |         2 |         2
 192 |        175 |         124 |         117 |        1 |         1 |         2

標(biāo)準(zhǔn)分?jǐn)?shù)（Standard Score，又稱z-score，中文稱為Z-分?jǐn)?shù)或標(biāo)準(zhǔn)化值）在統(tǒng)計(jì)學(xué)中是一種無(wú)因次值，就是一種純數(shù)字標(biāo)記，是借由從單一（原始）分?jǐn)?shù)中減去母體的平均值，再依照母體（母集合）的標(biāo)準(zhǔn)差分割成不同的差距，按照z值公式，各個(gè)樣本在經(jīng)過(guò)轉(zhuǎn)換后，通常在正、負(fù)五到六之間不等。

WITH post AS (SELECT avg(post_count) as mean, stddev(post_count) as sd from users),
     reply AS (SELECT avg(reply_count) as mean, stddev(reply_count) as sd from users),
     faver AS (SELECT avg(faver_count) as mean, stddev(faver_count) as sd from users)
SELECT users.*,
       ((post_count - post.mean) / post.sd)::numeric(6,3) AS z_score_post,
       ((reply_count - reply.mean) / reply.sd)::numeric(6,3) AS z_score_reply,
       ((faver_count - faver.mean) / faver.sd)::numeric(6,3) AS z_score_faver
  FROM users,
       post,
       faver,
       reply
ORDER BY 4 DESC

結(jié)果如下：

id  | post_count | reply_count | faver_count | z_score_post | z_score_reply | z_score_faver
-----+------------+-------------+-------------+--------------+---------------+---------------
 187 |         62 |          25 |         173 |        0.251 |        -0.543 |         2.835
 169 |          6 |          57 |         167 |       -1.076 |         0.150 |         2.697
 172 |         41 |         152 |         162 |       -0.247 |         2.208 |         2.582
 171 |          2 |          46 |         162 |       -1.171 |        -0.088 |         2.582
 200 |         76 |         193 |         149 |        0.582 |         3.096 |         2.283
 156 |         62 |         116 |         144 |        0.251 |         1.428 |         2.168
 166 |        114 |          31 |         144 |        1.483 |        -0.413 |         2.168
 153 |        127 |          97 |         138 |        1.791 |         1.016 |         2.031
 135 |         25 |         131 |         135 |       -0.626 |         1.753 |         1.962
 186 |        112 |         147 |         134 |        1.435 |         2.099 |         1.939
 163 |        106 |         134 |         133 |        1.293 |         1.818 |         1.916
 133 |        119 |          78 |         132 |        1.601 |         0.605 |         1.893
 173 |         74 |          92 |         132 |        0.535 |         0.908 |         1.893
 155 |        124 |           3 |         132 |        1.720 |        -1.019 |         1.893
 174 |         42 |          14 |         131 |       -0.223 |        -0.781 |         1.870
 181 |        120 |          10 |         130 |        1.625 |        -0.868 |         1.847
 148 |         38 |         120 |         129 |       -0.318 |         1.515 |         1.824
 128 |        113 |         103 |         128 |        1.459 |         1.146 |         1.801
 176 |         84 |         105 |         128 |        0.772 |         1.190 |         1.801
 179 |        114 |         118 |         127 |        1.483 |         1.471 |         1.778

Z-Score的意義是樣本值到均值之間有多少個(gè)標(biāo)準(zhǔn)差，它的取值理論上也是沒(méi)有范圍的，但如果樣本數(shù)值服從正態(tài)分布，會(huì)有99%以上的值落在[-3, 3]這個(gè)區(qū)間。如圖：

其實(shí)從上面我們計(jì)算的結(jié)果也可以觀察出這個(gè)結(jié)論。對(duì)于落在[-3, 3]區(qū)間外的數(shù)據(jù)，我們可以調(diào)整為3或-3，這個(gè)影響完全可以忽略不計(jì)。使用Z-Score之后，我們可以保證了分?jǐn)?shù)值既平滑又有范圍區(qū)間。

https://zh.wikipedia.org/wiki...

https://docs.snowflake.net/ma...