获取每日错误的百分比

Question

>我有 1 个表，我使用 2 列(时间、状态(我在时间列中选择带有date_trunc()的某一天，并应用条件where status = '404 NOT FOUND'

并将其除以每日计数获取每日错误的百分比

status has 2 values 404 NOT FOUND and 200 OK
--------------------------------------------

我想获取每日错误百分比

我试过了： select case when status = '404 NOT FOUND' then count(time) END / count(time) from log group by date_trunc('day',time);但是得到一个错误，我得到错误列"log.status"必须出现在 GROUP BY 子句中或在聚合函数中使用

Answer 1

你可以使用这样的东西：

SELECT days, (ERROR*1.0/TOTAL)*100.0 Percentage FROM
(select date_trunc('day',time) days,
    COUNT(case when status = '404 NOT FOUND' 
                   then 1 ELSE NULL END) ERROR,
    COUNT(1) TOTAL
from log
group by date_trunc('day',time)) A;

Answer 2

我会这样做：

select date_trunc('day', time) as dte,
       avg(case when status <> '200 OK' then 1.0 else 0 end) as daily_rate
from log l
group by dte;

在Postgres中，这可以进一步缩短为：

select date_trunc('day', time) as dte,
       avg( (status <> '200 OK')::int ) as daily_rate
from log l
group by dte;