按字段中相同的值对行进行分组，同时仅对部分值进行匹配

Question

我有一个表，它有很多行(在1000到几百万之间(。我需要我的查询来完成以下操作：

• 组结果由字段中相同部分的值决定
• 先按最大的组排序

该表的大多数值只有部分相似(即后缀不同(。由于相似值的数量巨大，我无法预测所有值。

这就是我的桌子：

+--------+-----------+------+
|   Id   | Uri       | Run  |
+--------+-----------+------+
|  15145 | select_123|    Y |
|  15146 | select_345|    Y |
|  15148 | delete_123|    N |
|  15150 | select_234|    Y |
|  15314 | delete_334|    N |
|  15315 | copy_all  |    N |
|  15316 | merge_all |    Y |
|  15317 | select_565|    Y |
|  15318 | copy_all  |    Y |
|  15319 | delete_345|    Y |
+--------+-----------+------+

我想看到的是这样的东西(计数部分是可取的，但不是必需的(：

+-----------+------+
| Uri       | Count|
+-----------+------+
|  select   |  4   |
|  delete   |  3   |
|  copy_all |  2   |
|  merge_all|  1   |
+-----------+------+

Answer 1

如果您使用的是MySQL 5.x，您可以使用以下表达式从Uri值中去除尾随的_和数字：

LEFT(Uri, LENGTH(Uri) - LOCATE('_', REVERSE(Uri)))

使用REGEXP测试来查看Uri是否以_和一些数字结尾，然后我们可以根据该值处理Uri，然后处理GROUP BY以获得计数：

SELECT CASE WHEN Uri REGEXP '_[0-9]+$' THEN LEFT(Uri, LENGTH(Uri) - LOCATE('_', REVERSE(Uri)))
ELSE Uri
END AS Uri2,
COUNT(*) AS Count
FROM data
GROUP BY Uri2

输出：

Uri2        Count
copy_all    2
delete      3
merge_all   1
select      4

SQLFiddle 演示

Answer 2

字符串的格式使用字符串函数解析它变得不容易。

如果您运行的是MySQL 8.0，则可以使用regexp_replace()截断字符串，然后使用group by和order by:

select regexp_replace(uri, '_\d+$', '') new_uri, count(*) cnt
from mytable
group by new_uri
order by cnt desc

Answer 3

如果使用MySQL 8.x，可以使用REGEXP_REPLACE()从select_XXX和delete_XXX中删除数字后缀，然后根据结果分组。

SELECT REGEXP_REPLACE(uri, '_[0-9]+$', '') AS new_uri, COUNT(*) as count
FROM yourTable
GROUP BY new_uri

Answer 4

您可以执行以下操作，创建一个视图，并使用大小写表达式+子字符串查找，即"select"one_answers"delete"。

根据视图，您可以使用count/group_by对其进行查询。

WITH view_1 AS (
SELECT
CASE
WHEN substr(uri, 1, 6) = 'select'       THEN
substr(uri, 1, 6)
WHEN substr(uri, 1, 6) = 'delete'       THEN
substr(uri, 1, 6)
ELSE uri
END AS uri
FROM
your_table
)
SELECT
uri,
COUNT(uri) as "Count"
FROM
view_1
GROUP BY
uri
ORDER BY count(uri) DESC;

输出将为

delete      5
merge_all   4
select      3
copy_all    3