Log Parser Studio:每月统计500个错误和非错误

Question

在分析w3c日志时，我想得到500个错误的计数，而不是每个月。这给出了每月500次的计数：

SELECT TO_STRING(date, 'yyyy-MM') AS yearMonth, COUNT(*) AS HowMany
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '%pageIcareabout%'
AND sc-status = 500
GROUP BY yearMonth

将sc-status = 500更改为sc-status <> 500可得出非500的计数。然而，我不知道如何将500和非500显示为每个月的独立列。

Answer 1

我最终得到了这个：

SELECT 
TO_STRING(date, 'yyyy-MM') AS yearMonth 
, SUM(500) AS 500s
, SUM(Not500) AS Not500s
USING 
CASE sc-status WHEN 500 THEN 1 ELSE 0 END AS 500
, CASE sc-status WHEN 500 THEN 0 ELSE 1 END AS Not500
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '%pageIcareabout%'
GROUP BY yearMonth

结果正是我想要的——3列，yearMonth、500s和Not500s，最后2列是它们各自的月份值的计数。

Answer 2

看起来您想要使用CASE语句：

SELECT TO_STRING(date, 'yyyy-MM') AS yearMonth, MyStatus, COUNT(*) AS HowMany
USING CASE sc-status WHEN 500 THEN '500' ELSE 'Not500' END AS MyStatus
FROM ...
WHERE cs-uri-stem LIKE '%pageIcareabout%'
GROUP BY yearMonth, MyStatus