mysql - 复杂查询中组函数的无效使用

Question

我有一个相当复杂的查询，其中包括一个子查询，然后我想按计算值对子查询的结果进行排序。我尝试在保持其结构的同时尽可能减少查询。

SELECT id,
       name,
       country,
       population,
       SUM(
           rescale(partial_relevance, (SELECT MIN(partial_relevance)), (SELECT MAX(partial_relevance))) +
           rescale(distance, (SELECT MIN(distance)), (SELECT MAX(distance)))
       ) AS relevance
FROM (
    SELECT p.id,
           pn.name AS name,
           cn.name AS country,
           p.population AS population,
           MIN(
               ST_DISTANCE_SPHERE(position, POINT(7.4653, 51.5136))
           ) AS distance,
           SUM(
               rescale(
                       MATCH(pn.name) AGAINST('+berl*' IN BOOLEAN MODE),
                       0,
                       1000
               ) +
               rescale(
                       MATCH(cn.name) AGAINST('berl*' IN BOOLEAN MODE),
                       0,
                       1000
               ) * 0.5
           ) AS partial_relevance
    FROM places p
    JOIN place_names pn ON p.id = pn.place_id
    JOIN country_names cn ON p.country_id = cn.country_id
    WHERE MATCH(pn.name) AGAINST('+berl*' IN BOOLEAN MODE)
    GROUP BY p.id, pn.name, cn.name, population
) temp
ORDER BY relevance DESC, population DESC;

该rescale()函数将一个值重新调整为 [0, 1] 的范围，并将(value, min, max)其作为参数。

这是一个等效于我正在使用的表的创建脚本（减去约束、索引等）：

CREATE TABLE places
(
    id          bigint   AUTO_INCREMENT PRIMARY KEY,
    position    point    NOT NULL,
    country_id  bigint   NULL,
    population  bigint   NULL
);

CREATE TABLE place_names
(
    id          bigint    AUTO_INCREMENT PRIMARY KEY,
    place_id    bigint    NOT NULL,
    name        char(255) NOT NULL
);

CREATE TABLE country_names
(
    id          bigint    AUTO_INCREMENT PRIMARY KEY,
    country_id  bigint    NOT NULL,
    name        char(255) NOT NULL
);


-- Some test data
INSERT INTO country_names (id, country_id, name) VALUES (
   (1, 1, 'Germany'),
   (2, 2, 'Italy'),
   (3, 3, 'France')
);

INSERT INTO places (id, position, country_id, population) VALUES (
    (1, POINT(13.4050, 52.5200), 1, 3645000),
    (2, POINT(12.4964, 41.9028), 2, 2873000),
    (3, POINT(2.3522, 48.8566), 3, 2161000),
    (4, POINT(14.2681, 40.8518), 2, 3085000)
);

INSERT INTO place_names (id, place_id, name) VALUES (
    (1, 1, 'Berlin'),
    (2, 2, 'Rome'),
    (3, 3, 'Paris'),
    (4, 4, 'Naples')
);

目的是根据它们是否与全文搜索匹配来获取所有位置，然后我组合并重新调整匹配相关性分数（由MATCH AGAINST;返回，* 0.5即使第二个分数小于第一个分数）。但是，我还希望考虑到给定点的距离，所以我想partial_relevance通过重新缩放distance并将两者相加，然后按结果排序来将它与它结合起来。我不必这样做，但我想不出更好的方法来组合我想要组合的值。

当我尝试运行查询时，我得到Invalid use of group function. 向主查询添加 agroup by无济于事。如果我尝试自己运行内部查询，它不会出错，所以问题一定出在外部查询中。

我没有在WHERE子句中使用任何聚合值，也没有使用HAVING子句，这排除了我看到的错误的最常见原因；与往常一样，sql 错误消息尽可能通用且无用，因此以我有限的经验，我不知道自己做错了什么。

我该怎么做才能使查询运行？我怎么能将任意数量的指标组合在一起，以便我可以按它们排序，其中一些是聚合的？

标签： mysqlgroup-bysql-order-byaggregatemysql-5.7