如何获取SQL中最频繁的值，其中值类似于变量

Question

我有一个SQL表，在其中存储我的应用程序日志。我有一个列错误，我在其中存储值，比如这个例子

+------+--------+----------------------------------------------+
|  id  |  name  |  error                                       |
+------+--------+----------------------------------------------+  
|  1   |  john  |  Flushing folder error on folderid 456       |
|  2   |  paul  |  Flushing folder error on folderid 440       |
|  3   |  gary  |  Error connection has timed out on source 320|
|  4   |  ade   |  Error connection has timed out on source 220|
|  5   |  fred  |  Error connection has timed out on source 821|
|  6   |  bob   |  Reading errors occured on folder 400        |
|  7   |  ade   |  Error connection has timed out on source 320|
|  8   |  fred  |  Error connection has timed out on source 320|
|  9   |  bob   |  Reading errors occured on folder 402        |
|  10  |  ade   |  Error connection has timed out on source 320|
|  11  |  fred  |  Error connection has timed out on source 320|
|  12  |  bob   |  Reading errors occured on folder 400        |
|  13  |  paul  |  Flushing folder error on folderid 100       |
+------+--------+----------------------------------------------+

我想得到的结果是：

+-------------------------------------------+------------+
|  Error Like                               |  Occurence |
+-------------------------------------------+------------+ 
| Error connection has timed out on source  | 7          |
| Flushing folder error on folderid         | 3          |
|Reading errors occured on folder           | 3          |
+-------------------------------------------+------------+

我在SQL中是否可以做到这一点？错误是可变的，我没有列出所有可能的错误。

谢谢！

Answer 1

删除最后一个数字部分并计数。

查询

SELECT LEFT(error, LEN(error) - 4) AS [Error Like], 
COUNT(LEFT(error, LEN(error) - 4)) AS [Occurence]
FROM tbl_error
GROUP BY LEFT(error, LEN(error) - 4);

或者您也可以使用子查询来执行此操作。

查询

SELECT t.[Error Like], COUNT(t.[Error Like]) AS [Occurence] FROM(
    SELECT LEFT(error, LEN(error) - 4) AS [Error Like]
    FROM tbl_error
)t
GROUP BY t.[Error Like];

如果您不知道最后一个数字部分，则

查询

SELECT t.[Error Like], COUNT(t.[Error Like]) as [Occurence] FROM(
    SELECT LEFT(error, LEN(error) - CHARINDEX(' ', REVERSE(error), 1)) AS [Error Like]
    FROM tbl_error
)t
GROUP BY t.[Error Like]
ORDER BY COUNT(t.[Error Like]) desc, t.[Error Like];

结果

+--------------------------------------------+-----------+
| Error Like                                 | Occurence | 
+--------------------------------------------+-----------+
| Error connection has timed out on source   | 7         | 
| Flushing folder error on folderid          | 3         | 
| Reading errors occured on folder           | 3         | 
+--------------------------------------------+-----------+

在此处查找演示

Answer 2

看起来您想要去掉最后四个字符并聚合：

select left(error, len(error) - 4) as ErrorLike, count(*)
from applogs
group by left(error, len(error) - 4)
order by count(*) desc;

Answer 3

下面的查询将对错误id大小进行硬编码。假设您的表名为LogDetails

SELECT E.[ErrorDetail], COUNT(E.[ErrorDetail]) [ErrorCount], 
FROM (
    SELECT REPLACE(error, Substring(error, PatIndex('%[0-9]%', error), LEN(error)), '') [ErrorDetail]
    FROM LogDetails
    WHERE error LIKE '%[0-9]%'
) E
GROUP BY E.[ErrorDetail]
ORDER BY COUNT(E.[ErrorDetail]) DESC