异国情调groupby

0mkxixxg 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(217)

考虑sql中一个典型的groupby语句：您有一个类似

+------+-------+
| 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 |
+------+-------+

然后将连续的行聚合在一起：“value”列被赋予了一个聚合器（在本例中是sum），因此很容易进行聚合。“name”列没有给定聚合器，因此使用了您可能称之为“普通部分聚合器”的内容：给定两个相同的东西（例如a和a），它将它们聚合到一个输入（在本例中是a）的单个副本中。给定任何其他输入，它不知道该做什么，被迫重新开始聚合（这次“name”列等于b）。
我想做一种更奇异的聚合。我的table看起来像

+------+-------+
| 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。我想将所有具有相同“基本”单词的名称组合在一起。）
在我看来，我有两个选择：
1）简单的选择是：计算每一行的前缀，然后直接按该值分组。不幸的是，我在名称上有一个索引，计算最小前缀（其长度取决于名称本身）会阻止我使用该索引。这将强制进行全表扫描，速度非常慢。
2）复杂的选择：以某种方式说服mysql使用“部分前缀聚合器”作为名称。这会遇到上面的“非局部性”问题，但只要我们根据我的name索引扫描表就可以了，因为这样每个最小前缀都会出现在它作为前缀的任何其他字符串之前；如果a在数据集中，我们永远不会尝试将ay和az聚合在一起。
在声明式编程语言中#2相当简单：按字母顺序一次提取一行，跟踪当前前缀。如果新行的名称以它作为前缀，那么它将进入当前使用的bucket中。否则，用它作为前缀启动一个新的bucket。在mysql中，我不知道怎么做。请注意，最小前缀集事先是未知的。

mysql indexing

来源：https://stackoverflow.com/questions/52088005/exotic-group-by-in-mysql

2条答案

按热度按时间

bmvo0sr51#

这里有一些关于如何完成这项任务的提示。这将定位任何有用的前缀。这不是您所要求的，而是查询的流程和 @variables ，再加上需要2（实际上是3）个嵌套级别，可能会对您有所帮助。

SELECT  DISTINCT `Prev`
    FROM  
        (
        SELECT  @prev := @next AS 'Prev',
                @next := IF(LEFT(city, LENGTH(@prev)) = @prev, @next, city) AS 'Next'
            FROM ( SELECT  @next := ' ' ) AS init
            JOIN ( SELECT  DISTINCT city FROM  us ) AS dedup
            ORDER BY  city
        ) x
    WHERE  `Prev` = `Next` ;

部分输出：

+----------------+
| Prev           |
+----------------+
| Alamo          |
| Allen          |
| Altamont       |
| Ames           |
| Amherst        |
| Anderson       |
| Arlington      |
| Arroyo         |
| Auburn         |
| Austin         |
| Avon           |
| Baker          |

检查 Al% 城市：

mysql> SELECT DISTINCT city FROM us WHERE city LIKE 'Al%' ORDER BY city;
+-------------------+
| city              |
+-------------------+
| Alabaster         |
| Alameda           |
| Alamo             | <--
| Alamogordo        | <--
| Alamosa           |
| Albany            |
| Albemarle         |
...
| Alhambra          |
| Alice             |
| Aliquippa         |
| Aliso Viejo       |
| Allen             | <--
| Allen Park        | <--
| Allentown         | <--
| Alliance          |
| Allouez           |
| Alma              |
| Aloha             |
| Alondra Park      |
| Alpena            |
| Alpharetta        |
| Alpine            |
| Alsip             |
| Altadena          |
| Altamont          | <--
| Altamonte Springs | <--
| Alton             |
| Altoona           |
| Altus             |
| Alvin             |
+-------------------+
40 rows in set (0.01 sec)

赞(0）回复(0）举报 2021-06-21

mctunoxg2#

编辑2
我突然想到如果这张table是由 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
编辑
在这个问题上睡了一觉之后，我意识到没有必要做同样的事情 `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` 更改为 `UNIQUE` 密钥来自 `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

现在可以对名称以其中一个前缀开头的所有值求和。注意我们改变了 `LIKE` 模式，以便它也匹配前缀，否则我们将不计算 `A` 以及 `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

安 `EXPLAIN` 表示这两个查询都使用 `Name` ，所以应该是合理有效的。以下是MySQL5.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演示

赞(0）回复(0）举报 2021-06-20

我来回答

异国情调groupby

2条答案

相关问题

热门标签

最新问答