MySQL 8 窗口函数 + 全文搜索

Question

我使用 mysql Ver 8.0.3-rc for Linux on x86_64 (MySQL Community Server (GPL))

在 name 列上创建表和全文索引

CREATE TABLE `title` (
  `id` smallint(4) unsigned NOT NULL PRIMARY KEY,
  `name` text COLLATE utf8_unicode_ci,
  FULLTEXT idx (name) WITH PARSER ngram
) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

插入一些数据:

insert into `title` values(14,"I'm flying in for the game (one night in Niagara Falls, NY and one night in Buffalo then back home).");
insert into `title` values(23,"I've never been to the area.");
insert into `title` values(43,"Where and what must I eat (Canadian side of Niagara, American side and Buffalo)?");
insert into `title` values(125,"Don't really have much planned other than the Falls and the game.");

执行时:

select
    id,
    round(MATCH (name) AGAINST ('other than the'),2) scope
from title;

结果(一切正常):

id  | scope
----------
14  | 0.43
23  | 0.23
43  | 0.12
125 | 1.15

当使用经典的 GROUP BY - 一切正常

select
    max(scope),
    min(scope),
    sum(scope)
from
(
    select id, round(MATCH (name) AGAINST ('other than the'),2) scope
    from title
) a;

结果正常:

max  |  min | sum
----------------
1.15 | 0.12 | 1.96

但是当我尝试使用窗口函数结束时，我不明白结果:

select
    id,
    max(scope) over(),
    min(scope) over(),
    sum(scope) over()
from
(
    select id, round(MATCH (name) AGAINST ('other than the'),2) scope
    from title
) a;

我得到了一个奇怪的结果(为什么？):

id | max  |  min | sum
------------------------
14 | 1.15 | 1.15 |  4.60
23 | 1.15 | 1.15 |  4.60
43 | 1.15 | 1.15 |  4.60
125| 1.15 | 1.15 |  4.60

我希望得到类似于经典 group by 的结果，比如:

id | max  |  min | sum
------------------------
14 | 1.15 | 0.12 |  1.96
23 | 1.15 | 0.12 |  1.96
43 | 1.15 | 0.12 |  1.96
125| 1.15 | 0.12 |  1.96

这是 mysql Ver 8.0.3-rc 中的错误还是我的查询不正确？谢谢!

Answer 1

关于 wchiquito 的回答:你是对的，有一个错误。自发布以来已修复。修复后，MySQL 将此答案返回给窗口查询:

mysql> SELECT
    ->        `id`,
    ->        MAX(`scope`) OVER() `max`,
    ->        MIN(`scope`) OVER() `min`,
    ->        SUM(`scope`) OVER() `sum`
    ->      FROM
    ->      (
    ->        SELECT
    ->          `id`,
    ->          ROUND(MATCH (`name`) AGAINST ('other than the'), 2) `scope`
    ->        FROM `title`
    ->      ) `a`;
+-----+------+------+------+
| id  | max  | min  | sum  |
+-----+------+------+------+
|  14 | 0.72 | 0.00 | 0.72 |
|  23 | 0.72 | 0.00 | 0.72 |
|  43 | 0.72 | 0.00 | 0.72 |
| 125 | 0.72 | 0.00 | 0.72 |
+-----+------+------+------+
4 rows in set (0,01 sec)

这仍然与您从 Maria 那里引用的不同；但我相信上面的 MySQL 答案是正确的:因为窗口规范是空的，窗口函数应该对每一行的结果集中的所有行起作用，即对于每个结果集行的窗口函数调用应该产生相同的值。

如果您按照与 GROUP BY 查询类似的方式对结果集进行分区(请参阅下面的 PARTITION BY a.id)，您将看到以下结果:

mysql> SELECT
    ->        `id`,
    ->        MAX(`scope`) OVER(PARTITION BY a.id) `max`,
    ->        MIN(`scope`) OVER(PARTITION BY a.id) `min`,
    ->        SUM(`scope`) OVER(PARTITION BY a.id) `sum`
    ->      FROM
    ->      (
    ->        SELECT
    ->          `id`,
    ->          ROUND(MATCH (`name`) AGAINST ('other than the'), 2) `scope`
    ->        FROM `title`
    ->      ) `a`;
+-----+------+------+------+
| id  | max  | min  | sum  |
+-----+------+------+------+
|  14 | 0.00 | 0.00 | 0.00 |
|  23 | 0.00 | 0.00 | 0.00 |
|  43 | 0.00 | 0.00 | 0.00 |
| 125 | 0.72 | 0.72 | 0.72 |
+-----+------+------+------+
4 rows in set (0,00 sec)

因为每一行在这里都是它自己的分区。这与您为没有 PARTITION BY 的 Maria 引用的 相同。