database - 架构设计 : How do you incorporate "total"?

Question

我目前正在为我的元素建立一个评分表。

每个项目都有一个分数，因此数据库 (postgres) 可以按分数对项目进行排序并将其返回给用户。

目前产品的总分由以下公式决定:

• 新鲜度得分(由过程 A 计算)
• 人气得分(由流程 B 计算)
• 相关性分数(由过程 C 计算)

Total = 0.5 * freshness + 0.25 * popularity + 0.25 * relevance

流程 A、B、C 将运行几个小时并生成 (item_id, score, type)，其中类型可以是“新鲜”、“流行”或“相关”。

请注意，我必须保留这些值，因为它们是由不同的过程产生的。

我需要做什么才能执行 SELECT * FROM items JOIN scores ON items.id == scores.item_id ORDER BY <total_score ??> DESC LIMIT 10 OFFSET 0;

编辑

一个明显的答案是让另一个进程生成 type = total对于所有项目。这行得通，但是很麻烦，因为这些分数中的任何一个的每次更改都需要更新 total 。此外，它几乎可以将我的数据存储量从 25% 增加到 100%。我不认为这是一个最佳解决方案，因为合并它需要相当多的维护工作。

更新

这是我的分数表:

    Column     |            Type             |                         Modifiers                         | Storage  | Description
---------------+-----------------------------+-----------------------------------------------------------+----------+-------------
 created_at    | timestamp without time zone |                                                           | plain    |
 updated_at    | timestamp without time zone |                                                           | plain    |
 id            | integer                     | not null default                             | plain    |
 score         | double precision            | not null                                                  | plain    |
 type          | character varying           | not null                                                  | extended |

Answer 1

这是使用虚拟列执行此操作的另一种很酷的方法，如所述here :

首先，创建一个 View 来聚合每个项目的分数:

CREATE OR REPLACE VIEW vw_scores_rollup AS
SELECT id,
  SUM(CASE WHEN type = 'freshness' THEN score ELSE 0 END) AS freshness,
  SUM(CASE WHEN type = 'popularity' THEN score ELSE 0 END) AS popularity,
  SUM(CASE WHEN type = 'relevance' THEN score ELSE 0 END) AS relevance
FROM scores
GROUP BY id;

接下来，此函数将源表/ View 作为参数。

CREATE OR REPLACE FUNCTION total(vw_scores_rollup) RETURNS numeric AS
$BODY$
  SELECT 0.5 * COALESCE($1.freshness, 0) + 0.25 * COALESCE($1.popularity, 0) + 0.25 * COALESCE($1.relevance, 0);
$BODY$
  LANGUAGE sql;

访问:

SELECT *, s.total
FROM items i
JOIN vw_scores_rollup s USING (id)
ORDER BY s.total DESC
LIMIT 10 OFFSET 0;

这是一个巧妙的技巧，提供了一种访问总数的直接方法。