竞赛附分是怎么算出来的？

背景

附分是怎么回事？假设性能大赛评判标准为: 用耗时代表性能, 耗时越低性能越好(成绩越好).

已知每个考生的性能成绩(考察值)及排序方法(耗时越低性能越好).

设计一种算法: 求每个考察值对应的逻辑分.

输入如下:

• 每位考生的考察值(例如耗时)
• 总考生(有成绩的)m名
• 第1名, 对应 最高逻辑分x
• 第n名, 对应 逻辑分y (表示入围)   -- (N的算法举例: 1、按百分比, 例如100个考生, 40%入围; 2、按绝对值, 例如无论多少考生, 都只有前10名入围.)
• 最后1名, 对应 最低逻辑分z

附分举例

• 已知 每位考生的考察值(例如耗时) 及 排序方法(耗时越低性能越好)
• 已知 m
• 可根据需求提前设置 n
• 可根据需求提前设置 x,y,z

使用bucket函数即可完成附分需求

width_bucket函数介绍:

width_bucket(value, low_bound_include, up_bound_exclude, buckets)    -- 返回value对应的bucket  
  1-10 均匀分为5个bucket, 0-1-2-3-4-5-6   1包含在第1个bucket内, 10不包含在第5个bucket内.    
width_bucket(0.99,1,10,5) = 0   
width_bucket(1,1,10,5) = 1   
width_bucket(9.9,1,10,5) = 5   
width_bucket(10,1,10,5) = 6   

1、考察值val越大越好的情况:

min_score + ( width_bucket(val, min_val, max_val, trunc((max_score-min_score)*10000)::int ) - 1 )  / 10000.0   

2、考察值val越小越好的情况:

max_score - ( width_bucket(val, min_val, max_val, trunc((max_score-min_score)*10000)::int ) - 1 )  / 10000.0    

创建测试表, 生成一些测试数据

postgres=# create table t (id int, val numeric);  
CREATE TABLE  
postgres=# insert into t select generate_series(1,1000), 600+random()*1900;  
INSERT 0 1000  
postgres=# select * from t limit 10;  
 id |       val          
----+------------------  
  1 | 2111.59215049175  
  2 |  1440.7595493891  
  3 | 1046.29591096683  
  4 | 1578.52637612988  
  5 | 2365.20836877369  
  6 | 813.016443763998  
  7 | 1250.24334239677  
  8 | 1503.40285271799  
  9 | 2227.88037116028  
 10 | 1876.73487686622  
(10 rows)  

获取边界值 min_val, max_val

postgres=# select min(val),max(val) from t;  
       min        |       max          
------------------+------------------  
 600.104637171789 | 2498.64386498567  
(1 row)  

获取边界值n_val, 这里假设取第100名的val.

postgres=# select * from t order by val offset 99 limit 1;  
 id  |       val          
-----+------------------  
 657 | 814.348698858268  
(1 row)  

得到如下映射关系

min_val 600.104637171789   
max_val 2498.64386498567   
n_val 814.348698858268   
  假设附分对应 10,80,100 :   
min_score 10 分  
max_score 80 分  
n_score 100 分  
  对应关系如下 :   
600.104637171789 -- 100  分  
814.348698858268 -- 80  分  
2498.64386498567 -- 10  分  

塞入一个80分的并列值

postgres=# insert into t values (1999, 814.348698858268);  
INSERT 0 1  

如果这些分是性能耗时, 越小成绩越好, 采用val越小越好的算法

max_score - ( width_bucket(val, min_val, max_val, trunc((max_score-min_score)*10000)::int ) - 1 )  / 10000.0    

得到附分80及以上的记录

select row_number() over (order by score desc,id) as rn, id, val, score from   
  (select id, val, 100 - ( width_bucket(val, 600.104637171789, 814.348698858268, trunc((100-80)*10000)::int ) - 1 ) / 10000.0 as score from t where val <= 814.348698858268 order by val ) t order by rn;  
   rn  |  id  |       val        |            score               
-----+------+------------------+------------------------------  
   1 |  365 | 600.104637171789 | 100.000000000000000000000000  
   2 |  764 | 600.651156842011 |      99.94900000000000000000  
   3 |  386 | 608.282973468951 |      99.23660000000000000000  
   4 |  411 | 608.952058109832 |      99.17410000000000000000  
   5 |  315 | 609.687920240662 |      99.10540000000000000000  
......  
  98 |  448 |  811.41019083952 |          80.2744000000000000  
  99 |    6 | 813.016443763998 |          80.1244000000000000  
 100 |  657 | 814.348698858268 |          80.0000000000000000  
 101 | 1999 | 814.348698858268 |          80.0000000000000000  
(101 rows)  

得到附分80及以上的人数

select count(*) from t where val <= 814.348698858268; -- 得到附分80及以上的人数  
  101  

得到附分80以下的记录

select 101 + row_number() over (order by score desc,id) as rn, id, val, score from   
  (select id, val, 80 - ( width_bucket(val, 814.348698858268, 2498.64386498567, trunc((80-10)*10000)::int ) - 1 ) / 10000.0 as score from t where val > 814.348698858268 order by val ) t order by rn;  
    rn  |  id  |       val        |          score            
------+------+------------------+-------------------------  
  102 |  924 | 814.992859010394 | 79.97330000000000000000  
  103 |  792 | 815.560099101809 | 79.94970000000000000000  
  104 |  932 | 817.127449339619 | 79.88460000000000000000  
  105 |  409 | 818.964021185144 | 79.80820000000000000000  
  106 |  405 | 819.072533536111 | 79.80370000000000000000  
......  
  997 |  230 | 2488.59569201934 |     10.4177000000000000  
  998 |  105 | 2489.86879421614 |     10.3647000000000000  
  999 |  389 | 2494.56081667216 |     10.1697000000000000  
 1000 |  651 | 2497.11539444513 |     10.0636000000000000  
 1001 |  171 | 2498.64386498567 |     10.0000000000000000  
(900 rows)  

关于相同成绩的处理?

以上算法适用于相同成绩的附分, 只影响最后的名次.

先提交的名次优先?

并列名次?

用好周边工具, 数据库管理水平战胜90%老司机

1、管控软件

云猿生开源的kubeblocks, 如果你要管理很多套并且种类很多的数据库产品, 推荐选择.

• https://github.com/apecloud/kubeblocks

乘数开源的clup, 专门用来管理PostgreSQL和PolarDB的集群管理软件, 如果你要管理很多套数据库, 推荐选择. 并且clup还提供了企业版、自研的连接池、分布式存储、一体机、备份平台等, 企业可以关注一下.

• https://www.csudata.com/

若航开源的pigsty, 集成了300多个PG插件的PG集群和PolarDB集群管理软件, 如果你要管理很多套数据库, 并且对插件有特别多的需求, 推荐选择.

• https://pigsty.cc/zh/

2、审计监控诊断优化

海信聚好看的 DBdoctor, 采用ebpf技术, 在对数据库几乎没有影响的情况下实时监控数据库和服务器的各项指标, 发现和诊断问题根因非常方便.

• https://www.dbdoctor.cn/

Bytebase 的目标非常远大, 是位于您和数据库之间的中间件。它是数据库 DevOps 的 GitLab/GitHub，专为开发人员、DBA 和平台工程师打造。

• https://bytebase.cc/docs/introduction/what-is-bytebase/

D-Smart, Oracle老前辈白老大他们搞的, 专注企业级市场, 将业界顶级DBA经验的产品化作品, 产品功能包括数据库监控、诊断、优化等.

• https://www.modb.pro/db/567140

3、国产数据库IDE

这个关注的人比较少但确是开发者的必备工具，请查看老程序员前辈达刚老师的DeskUI: https://www.deskui.com

4、数据同步&迁移&备份恢复

NineData, 老领导出去创业做的产品, 产品涵盖了数据同步、迁移、备份、比对、devops、chatDBA等.

• https://www.ninedata.cloud/home

DSG, 非常老牌的数据库同步迁移企业级产品, 支持各种数据库的异构和同构迁移, 用他们的话说, 没有dsg搞不定的迁移, 比goldengate还牛.

• https://www.dsgdata.com/

除了PolarDB还非常值得关注的几款PG栈国产数据库:

HaloDB(基于PG兼容PostgreSQL、Oracle、MySQL. http://www.halodbtech.com/ )、IvorySQL(基于开源PG兼容PG、Oracle. https://www.ivorysql.org/zh-cn/ )、ProtonBase(云原生分布式数仓. https://protonbase.com/ )、成都文武数据库（https://ww-it.cn）