时间戳

首页 > 解决方案 > MySQL 中的异国 GROUP BY

mysql - MySQL 中的异国 GROUP BY

问题描述

考虑 SQL 中典型的 GROUP BY 语句：您有一个类似的表

+------+-------+
| Name | Value |
+------+-------+
| A    |     1 |
| B    |     2 |
| A    |     3 |
| B    |     4 |
+------+-------+

你要求

SELECT Name, SUM(Value) as Value
FROM table
GROUP BY Name

你会收到

+------+-------+
| Name | Value |
+------+-------+
| A    |     4 |
| B    |     6 |
+------+-------+

在您的脑海中，您可以想象 SQL 生成一个中间排序表，例如

+------+-------+
| Name | Value |
+------+-------+
| A    |     1 |
| A    |     3 |
| B    |     2 |
| B    |     4 |
+------+-------+

然后将连续的行聚合在一起：“值”列已被赋予一个聚合器（在本例中为 SUM），因此很容易聚合。“名称”列没有被赋予聚合器，因此使用您可能称之为“琐碎的部分聚合器”：给定两个相同的事物（例如 A 和 A），它将它们聚合到其中一个的单个副本中输入（在本例中为 A）。给定任何其他输入，它不知道该做什么，并被迫重新开始聚合（这次“名称”列等于 B）。

我想做一种更奇特的聚合。我的桌子看起来像

+------+-------+
| Name | Value |
+------+-------+
| A    |     1 |
| BC   |     2 |
| AY   |     3 |
| AZ   |     4 |
| B    |     5 |
| BCR  |     6 |
+------+-------+

预期的输出是

+------+-------+
| Name | Value |
+------+-------+
| A    |     8 |
| B    |    13 |
+------+-------+

这是从哪里来的？A 和 B 是这组名称的“最小前缀”：它们出现在数据集中，每个名称都有其中一个作为前缀。当它们的名称具有相同的最小前缀时，我想通过将行分组在一起来聚合数据（当然还要添加值）。

在之前的玩具分组模型中，中间排序表将是

+------+-------+
| Name | Value |
+------+-------+
| A    |     1 |
| AY   |     3 |
| AZ   |     4 |
| B    |     5 |
| BC   |     2 |
| BCR  |     6 |
+------+-------+

如果 X 是 Y 的前缀，我们将使用可以将 X 和 Y 聚合在一起的名称，而不是使用“简单的部分聚合器”；在这种情况下，它返回 X。因此，前三行将被聚合成一行，其中 (Name, Value) = (A, 8)，然后聚合器将看到 A 和 B 无法聚合并继续到要聚合的新“块”行。

棘手的是我们分组的值是“非本地的”：如果 A 不是数据集中的名称，那么 AY 和 AZ 都将是一个最小前缀。事实证明，AY 和 AZ 行在最终输出中聚合到同一行中，但仅通过单独查看它们是无法知道的。

神奇的是，在我的用例中，可以在不参考数据集中其他任何内容的情况下确定字符串的最小前缀。（假设我的每个名字都是字符串“hello”、“world”和“bar”之一，后跟任意数量的 z。我想将所有具有相同“base”单词的 Names 组合在一起。）

正如我所看到的，我有两个选择：

1）简单的选项：计算每一行的前缀并直接按该值分组。不幸的是，我在名称上有一个索引，并且计算最小前缀（其长度取决于名称本身）阻止我使用该索引。这会强制进行全表扫描，这非常慢。

2) 复杂的选项：以某种方式说服 MySQL 使用“部分前缀聚合器”作为名称。这遇到了上面的“非局部性”问题，但是只要我们根据我的 Name 索引扫描表就可以了，因为这样每个最小前缀都会在它作为前缀的任何其他字符串之前遇到；如果 A 在数据集中，我们永远不会尝试将 AY 和 AZ 聚合在一起。

在声明性编程语言中，#2 相当简单：一次提取一行，按字母顺序，跟踪当前前缀。如果您的新行的名称具有前缀，它将进入您当前使用的存储桶。否则，以它作为前缀启动一个新存储桶。在 MySQL 中，我不知道该怎么做。请注意，最小前缀的集合事先是未知的。

标签： mysqlindexing

解决方案

编辑 2

我突然想到，如果表格是按 排序的Name，这会更容易（而且更快）。由于我不知道您的数据是否已排序，因此我在此查询中包含了排序，但如果数据已排序，您可以删除(SELECT * FROM table1 ORDER BY Name) t1并使用FROM table1

SELECT prefix, SUM(`Value`)
FROM (SELECT Name, Value, @prefix:=IF(Name NOT LIKE CONCAT(@prefix, '_%'), Name, @prefix) AS prefix
      FROM (SELECT * FROM table1 ORDER BY Name) t1
      JOIN (SELECT @prefix := '~') p
      ) t2
GROUP BY prefix

更新了 SQLFiddle

编辑

解决了这个问题，我意识到没有必要这样做，只需要在 JOINed 表上IN有一个子句就足够了：WHERE NOT EXISTS

SELECT t1.Name, SUM(t2.Value) AS `Value`
FROM table1 t1
JOIN table1 t2 ON t2.Name LIKE CONCAT(t1.Name, '%')
WHERE NOT EXISTS (SELECT * 
                  FROM table1 t3
                  WHERE t1.Name LIKE CONCAT(t3.Name, '_%')
                  )
GROUP BY t1.Name

更新了解释（Name更改为UNIQUEkey from PRIMARY）

id  select_type         table   type    possible_keys   key         key_len     ref             rows    Extra   
1   PRIMARY             t1      index   Name            Name        11          NULL            6       Using where; Using index; Using temporary; Using filesort
1   PRIMARY             t2      ALL     NULL            NULL        NULL        NULL            6       Using where; Using join buffer (Block Nested Loop)
3   DEPENDENT SUBQUERY  t3      index   NULL            Name        11          NULL            6       Using where; Using index

更新了 SQLFiddle

原始答案

这是您可以做到的一种方法。首先，您需要找到表中的所有唯一前缀。您可以通过查找Name它看起来不像另一个值的所有值来做到这一点Name，最后是其他字符。这可以通过以下查询来完成：

SELECT Name
FROM table1 t1
WHERE NOT EXISTS (SELECT * 
                  FROM table1 t2
                  WHERE t1.Name LIKE CONCAT(t2.Name, '_%')
                  )

对于您的示例数据，这将给出

Name
A
B

现在，您可以对 Name 以这些前缀之一开头的所有值求和。请注意，我们更改了LIKE此查询中的模式，使其也与前缀匹配，否则我们不会在您的示例中计算Aand的值：B

SELECT t1.Name, SUM(t2.Value) AS `Value`
FROM table1 t1
JOIN table1 t2 ON t2.Name LIKE CONCAT(t1.Name, '%')
WHERE t1.Name IN (SELECT Name
                  FROM table1 t3
                  WHERE NOT EXISTS (SELECT * 
                                    FROM table1 t4
                                    WHERE t3.Name LIKE CONCAT(t4.Name, '_%')
                                    )
                  )
GROUP BY t1.Name

输出：

Name    Value
A       8
B       13

AnEXPLAIN说这两个查询都使用 上的索引Name，因此应该相当有效。这是我的 MySQL 5.6 服务器上的解释结果：

id  select_type         table   type    possible_keys   key         key_len     ref             rows    Extra   
1   PRIMARY             t1      index   PRIMARY         PRIMARY     11          NULL            6       Using index; Using temporary; Using filesort
1   PRIMARY             t3      eq_ref  PRIMARY         PRIMARY     11          test.t1.Name    1       Using where; Using index
1   PRIMARY             t2      ALL     NULL            NULL        NULL        NULL            6       Using where; Using join buffer (Block Nested Loop)
3   DEPENDENT SUBQUERY  t4      index   NULL            PRIMARY     11          NULL            6       Using where; Using index

SQLFiddle 演示

推荐阅读