从一个选择中除以两个计数 [英] Divide two counts from one select

问题描述

我有一张这样的桌子:

date(timestamp) Error(integer)   someOtherColumns

我有一个查询来选择特定日期的所有行:

I have a query to select all the rows for specific date:

SELECT * from table
WHERE date::date = '2010-01-17'  

现在我需要计算 Error 等于 0(从那天起)的所有行，并将其除以所有行的计数(从那天起).

Now I need to count all rows which Error is equal to 0(from that day) and divide it by count of all rows(from that day).

所以结果应该是这样的

Date(timestamp)      Percentage of failure
2010-01-17           0.30

数据库相当大，数百万行..

Database is pretty big, millions of rows..

如果有人知道如何在更多天里这样做会很棒 - 从一天到另一天.

And it would be great if someone know how to do this for more days - interval from one day to another.

Date(timestamp)      Percentage of failure
2010-01-17           0.30
2010-01-18           0.71
and so on

推荐答案

这个怎么办(如果 error 只能是 1 和 0):

what about this (if error could be only 1 and 0):

select
   date,
   sum(Error)::numeric / count(Error) as "Percentage of failure"
from Table1
group by date

或者，如果 error 可以是任何整数:

or, if error could be any integer:

select
   date,
   sum(case when Error > 0 then 1 end)::numeric / count(Error) as "Percentage of failure"
from Table1
group by date

<小时>

只是发现我已经数了not 0(假设错误是当 Error != 0 时)，并且没有考虑空值(不知道你想如何对待它).所以这是另一个查询，它将空值视为 0 并以两种相反的方式计算失败的百分比:

---

Just fount that I've counted not 0 (assumed that error is when Error != 0), and didn't take nulls into accounts (don't know how do you want to treat it). So here's another query which treats nulls as 0 and counts percentage of failure in two opposite ways:

select
    date,
    round(count(nullif(Error, 0)) / count(*) ::numeric , 2) as "Percentage of failure",
    1- round(count(nullif(Error, 0)) / count(*) ::numeric , 2) as "Percentage of failure2"
from Table1
group by date
order by date;

sql 小提琴演示

这篇关于从一个选择中除以两个计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！