函数从MySQL数据计算加权分数？

Question

我有两个表（主题和术语），第三个表用于处理两个实体之间的多对多关系。你知道吗

每个关系称为bagging，都有一个源（text）和一个权重（int介于0和100之间）。同一对（主题词）可以有几个装袋（不同的来源），每个有不同的重量。你知道吗

现在，当我查询一个主题以找出它的最佳术语（更多权重）时，理想情况下，我需要具有计算权重的唯一值：

• 100的重量意味着这件物品的最大重量
• 同一对（不同来源）的若干重量比单个对重
• 没有“负”重量

以下是数据库架构：

| TOPIC
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| label | varchar(255)     | NO   | UNI | NULL    |                |
| wtext | varchar(40)      | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+

| TERM
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| label | varchar(255)        | NO   | UNI | NULL    |                |
| slug  | varchar(255)        | NO   |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+

| BAGGING
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| topic_id | int(11) unsigned    | NO   | MUL | NULL    |                |
| term_id  | bigint(11) unsigned | NO   | MUL | NULL    |                |
| weight   | tinyint(1) unsigned | NO   |     | NULL    |                |
| source   | varchar(8)          | YES  |     | GEN     |                |
+----------+---------------------+------+-----+---------+----------------+

我的问题很简单：

SELECT 
    bagging.topic_id as topic_id,
    topic.label as topic_label,
    bagging.term_id as term_id,
    term.label as term_label,
    bagging.weight as weight,
    bagging.source as source
FROM
    bagging
JOIN term   ON term.id  = bagging.term_id
JOIN topic  ON topic.id = bagging.topic_id
WHERE
    bagging.topic_id = ( SELECT id FROM topic WHERE label = 'Altruism' )
ORDER BY
    bagging.weight DESC

结果如下：

+----------+-------------+---------+-----------------------+--------+--------+
| topic_id | topic_label | term_id | term_label            | weight | source |
+----------+-------------+---------+-----------------------+--------+--------+
|        8 | Altruism    |      83 | Altruism              |    100 | TOPIC  |
+----------+-------------+---------+-----------------------+--------+--------+
|        8 | Altruism    |     100 | Altruism (philosophy) |     95 | WPRD   |
|        8 | Altruism    |     100 | Altruism (philosophy) |     95 | MAN    |
|        8 | Altruism    |      84 | Truist                |     95 | MAN    |
|        8 | Altruism    |      84 | Truist                |     15 | WPRD   |
+----------+-------------+---------+-----------------------+--------+--------+
|        8 | Altruism    |      94 | Selfless action       |     95 | WPRD   |
|        8 | Altruism    |      95 | Alturism              |     95 | WPRD   |
|        8 | Altruism    |      96 | Digital altruism      |     95 | WPRD   |
|        8 | Altruism    |      97 | Selflessly            |     95 | WPRD   |
|        8 | Altruism    |      98 | Altruistical          |     95 | WPRD   |
|        8 | Altruism    |      99 | Law of mutual aid     |     95 | WPRD   |
|        8 | Altruism    |     101 | Altruistically        |     95 | WPRD   |
|        8 | Altruism    |      85 | Altruistic            |     95 | WPRD   |
|        8 | Altruism    |      86 | Altruist              |     95 | WPRD   |
|        8 | Altruism    |      87 | Otherism              |     95 | WPRD   |
|        8 | Altruism    |      88 | Unselfishness         |     95 | WPRD   |
|        8 | Altruism    |      89 | Altruistic behavior   |     95 | WPRD   |
|        8 | Altruism    |      90 | Altutrists            |     95 | WPRD   |
|        8 | Altruism    |      91 | Altruists             |     95 | WPRD   |
|        8 | Altruism    |     102 | Pathological altruism |     95 | WPRD   |
+----------+-------------+---------+-----------------------+--------+--------+

现在，如何创建一个评分函数，在这个特定的例子中考虑以下因素：

• Altruism是无敌的，只能等于（=100）
• Truist显然应该受到15/100权重的惩罚，但是事实上，有两个权重也应该考虑在内，特别是因为第二个权重是95
• Altruist (Philosophy)应该比其他所有的重量都要重（除了Altruism之外，它们的重量只能相等），即使95看起来比100大两倍。你知道吗

最终结果不必从1缩放到100，它可以是考虑到这些约束的相对或抽象评级。你知道吗

我试图计算每一行的( term_sum_weight * 100 / topic_weight_sum_of_all_terms )，但是看到下面的结果，它们的权重不够。你知道吗

比如说，我怎么能给95到96的重量比1到20的重量多得多呢？你知道吗

公式比语言更重要。。。MySQL或Python/PHP在我的程序中。你知道吗

预期结果（沿着这些路线……）

+----------+-------------+---------+-----------------------+-------+--------+
| topic_id | topic_label | term_id | term_label            | score | source |
+----------+-------------+---------+-----------------------+-------+--------+
|        8 | Altruism    |      83 | Altruism              |     1 | TOPIC  |
+----------+-------------+---------+-----------------------+-------+--------+
|        8 | Altruism    |     100 | Altruism (philosophy) |  0.98 | WPRD   |
|        8 | Altruism    |      84 | Truist                |  0.96 | MAN    |
+----------+-------------+---------+-----------------------+--------+-------+
|        8 | Altruism    |      94 | Selfless action       |  0.95 | MAN    |
|        8 | Altruism    |      95 | Alturism              |  0.95 | MAN    |
|        8 | Altruism    |      96 | Digital altruism      |  0.95 | MAN    |
                                ...........
|        8 | Altruism    |      97 | Selflessly            |  0.95 | MAN    |
|        8 | Altruism    |      90 | Altutrists            |  0.95 | MAN    |
|        8 | Altruism    |      91 | Altruists             |  0.95 | MAN    |
|        8 | Altruism    |     102 | Pathological altruism |  0.95 | MAN    |
+----------+-------------+---------+-----------------------+--------+-------+

Answer 1

您应该从计算出所需公式应该具有的一些属性开始。一些可能的假设如下：

1. 重量为100的袋装总是得到最高分（不管是1分还是100分）
2. 添加重量为零的袋装不会改变任何东西
3. 添加一个非零重量的袋子会增加一对的分数，除非分数已经达到最大值
4. 如果两对都有一个袋子，那么重量越大得分越高

条件2和条件3表明类似于加法。但条件1告诉你，你不能只是加，因为这样做会超过分数。一种方法是使用物理模拟。把你的体重想象成速度。在日常生活中，你可以简单地增加速度。但是在非常高的速度下，狭义相对论告诉我们，我们永远不能超过光速的极限。加上两个低于光速的速度确实会产生一个高于光速但仍然低于光速的速度。在你的设置中，“光速”是100的最大宽度。你知道吗

因此，查找the formula for the addition of speeds，并根据您的用例调整它。如果你有两袋重量v和w那么总重量是

(v + w)/(1 + v*w/10000)

现在，您需要计算出一个公式，该公式对单个公式中任意数量的求和数执行上述操作，或者编写一些应用程序代码，以增量方式计算累加器与数据库中下一项之间的和。或者你在维基百科上读下去，发现以下等式成立：

c-s   c-v   c-w
 - =  - *  -
c+s   c+v   c+w

这里s是速度的总和，或者说是你所在世界的分数。但是s越大，这个分数就越小。因此，您可以按升序（c-s）/（c+s）排序，而不是按降序排序。不幸的是MySQLdoesn't have a ^{} aggregate function。但是你可以用对数把乘积变成和：

SUM(LOG((100-weight)/(100+weight))) AS score

不幸的是，MySQL不像IEEE浮点算法那样处理无穷大（即log（0）=-∞），而是将100的权重转换成NULL的分数。因此，您可以使用SORT BY (score IS NULL) DESC或类似方法将NULL分数排序在非NULL分数之前。或者将上述值转换回值s，以正确处理零。你知道吗

SELECT …,
  IF(MAX(weight) = 100, 1,
    (1-EXP(SUM(LOG((100-weight)/(100+weight)))))/
    (1+EXP(SUM(LOG((100-weight)/(100+weight)))))) AS score

在这里你可以把EXP(SUM(LOG(…)))想象成PRODUCT(…)。要为s解(c-s)/(c+s)=p（我刚才提到的乘积是p），您需要计算s=c*(1-p)/(1+p)。所以去掉c*，你得到的分数是0到1，而不是0到100，这与你目前的计算结果相似。MAX(weight) = 100情况会导致LOG(0)进行整个计算NULL，因此必须单独处理。你知道吗

有关使用数据的示例，请参见http://sqlfiddle.com/#!9/1cd56/1。它返回1表示利他主义，0.9987表示利他主义（哲学），0.9628表示真理，0.95表示其他。第二个问题的分数比你在问题中预期的要高得多，但我没有一个好主意来调整公式，使之更像你预期的那样。你知道吗