sql - 获得每日错误的百分比

Question

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

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

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

我想得到每天的错误百分比

我试过:当 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;