找到2个最接近的对应行，以达到乘以乘以的平均值，当给出输入标题时由匹配标题分组

Question

我想找到由匹配标题分组的乘以值的总和的平均值，以便给出相应的评分，然后找到最接近输入值的2。

SELECT titleValueAVG / 3 AS average,
       title
  FROM (
           SELECT Sum(a) AS titleValueAVG,
                  title
             FROM (
                      SELECT value * 1 AS a,
                             title
                        FROM Table1
                       WHERE type = 'A' AND 
                             contesting = 'yes'
                      UNION ALL
                      SELECT value * 2,
                             title
                        FROM Table1
                       WHERE type = 'A' AND 
                             contesting = 'no'
                      UNION ALL
                      SELECT value * 3,
                             title
                        FROM Table1
                       WHERE type = 'A' AND 
                             contesting = 'undecided'
                  )
            GROUP BY title
            ORDER BY title = 'Australia' DESC,
                     ABS(titleValueAVG) - (
                                        SELECT value * 1 AS a,
                                               title
                                          FROM Table1
                                         WHERE type = 'A' AND 
                                               contesting = 'yes' AND title = 'Australia'
                                        UNION ALL
                                        SELECT value * 2,
                                               title
                                          FROM Table1
                                         WHERE type = 'A' AND 
                                               contesting = 'no' AND title = 'Australia'
                                        UNION ALL
                                        SELECT value * 3,
                                               title
                                          FROM Table1
                                         WHERE type = 'A' AND 
                                               contesting = 'undecided' AND title = 'Australia'
                                    )
       ) limit 2;

从示例表中：

| Title    | Type         | Competing      | Value          |

| -------- | -------------| -------------- | -------------- |

| Australia| A            | yes            | 26             |

| Australia| A            | no             | 162            |

| Australia| A            | undecided      | 37             |

| Spain    | A            | yes            | 14             |

| Spain    | A            | no             | 101            |

| Spain    | A            | undecided      | 11             |

| Ireland  | A            | yes            | 124            |

| Ireland  | A            | no             | 62             |

| Ireland  | A            | undecided      | 9              |

| Nigeria  | C            | yes            | 4              |

| Nigeria  | C            | no             | 11             |

| Nigeria  | C            | undecided      | 7              |

| Colombia | A            | yes            | 26             |

| Colombia | A            | no             | 12             |

| Colombia | A            | undecided      | 19             |

| Turkey   | A            | yes            | 29             |

| Turkey   | A            | no             | 145            |

| Turkey   | A            | undecided      | 24             |

| Malta    | B            | yes            | 1              |

| Malta    | B            | no             | 11             |

| Malta    | B            | undecided      | 4              |

| Mexico   | A            | yes            | 74             |

| Mexico   | A            | no             | 19             |

| Mexico   | A            | undecided      | 12             |

| Slovenia | B            | yes            | 16             |

| Slovenia | B            | no             | 22             |

| Slovenia | B            | undecided      | 11             |

| Canada   | A            | yes            | 29             |

| Canada   | A            | no             | 164            |

| Canada   | A            | undecided      | 40             |

| Kenya    | C            | yes            | 8              |

| Kenya    | C            | no             | 12             |

| Kenya    | C            | undecided      | 0              |

因此，在此示例中，我想从输入标题的“澳大利亚：

| Title    | average        |

| -------- | -------------- |

| Australia| 154            |

| Canada   | 159            |

| Turkey   | 130            |

我的尝试解决方案”中返回，我尝试了多种方法来组织订单，这就是我认为的问题，但是我根本无法工作。如果有人可以帮助我解决这个问题，我将非常感谢。

Answer 1

使用条件聚合：

WITH cte AS (
  SELECT title,
         ROUND(SUM(value *
             CASE competing
               WHEN 'yes' THEN 1
               WHEN 'no' THEN 2
               WHEN 'undecided' THEN 3
             END
         ) / 3.0) average
  FROM tablename
  WHERE type = 'A'
  GROUP BY title
)
SELECT *
FROM cte
ORDER BY title = 'Australia' DESC,
         ABS(average - (SELECT average FROM cte WHERE title = 'Australia'))
LIMIT 3; 

请参阅