ruby-on-rails - PostgreSQL:按多个列权重排序

Question

我正在使用 PostgreSQL 9.4。我有一个包含以下列的 resources 表:

id
name
provider
description
category

假设这些列都不是必需的(id 除外)。我希望资源具有完成级别，这意味着每列具有 NULL 值的资源将处于 0% 完成级别。

现在，每一列都有一个百分比权重。比方说:

name: 40%
provider: 30%
description: 20%
category: 10%

因此，如果资源具有提供者和类别，则其完成级别为 60%。

这些权重百分比可能会随时更改，因此拥有一个始终具有完成级别值的 completion_level 列是行不通的(可能有数百万个资源)。例如，在任何时候，description 的百分比权重可能会从 20% 降低到 10%，类别的权重从 10% 到 20%。甚至可以创建其他列并拥有它们自己的权重。

最终目标是能够按完成级别对资源进行排序。

我不确定如何处理这个问题。我目前正在使用 Rails，因此几乎所有与数据库的交互都是通过 ORM 进行的，我认为这在这种情况下不会有太大帮助。

我发现唯一有点类似于解决方案(但不是真正)的查询是执行如下操作:

SELECT * from resources
ORDER BY CASE name IS NOT NULL AND
              provider IS NOT NULL AND
              description is NOT NULL AND
              category IS NOT NULL THEN 100
WHEN name is NULL AND provider IS NOT NULL...

但是，我必须对每个可能的组合进行每次变异，这很糟糕。

Answer 1

添加一个权重表，如此 SQL Fiddle :

PostgreSQL 9.6 架构设置:

CREATE TABLE resource_weights
    (  id int primary key check(id = 1)
     , name numeric
     , provider numeric
     , description numeric
     , category numeric);

INSERT INTO resource_weights
    (id, name, provider, description, category)
VALUES
    (1, .4, .3, .2, .1);

CREATE TABLE resources
    (  id int
     , name varchar(50)
     , provider varchar(50)
     , description varchar(50)
     , category varchar(50));

INSERT INTO resources
    (id, name, provider, description, category)
VALUES
    (1, 'abc', 'abc', 'abc', 'abc'),
    (2, NULL, 'abc', 'abc', 'abc'),
    (3, NULL, NULL, 'abc', 'abc'),
    (4, NULL, 'abc', NULL, NULL);

然后像这样在运行时计算你的权重

查询 1:

select r.*
     , case when r.name is null then 0 else w.name end
     + case when r.provider is null then 0 else w.provider end
     + case when r.description is null then 0 else w.description end
     + case when r.category is null then 0 else w.category end weight
  from resources r
 cross join resource_weights w
 order by weight desc

Results :

| id |   name | provider | description | category | weight |
|----|--------|----------|-------------|----------|--------|
|  1 |    abc |      abc |         abc |      abc |      1 |
|  2 | (null) |      abc |         abc |      abc |    0.6 |
|  3 | (null) |   (null) |         abc |      abc |    0.3 |
|  4 | (null) |      abc |      (null) |   (null) |    0.3 |