用于计算列等于特定值的组中的行的SQL

I have a table with columns CMDNAME, TIME, and FAILED. There are a number of rows with the same CMDNAME value. The TIME column is just a timestamp for the transaction. The FAILED column is either "Y" or "N".

我有一个表格列CMMLME，TIME和FAILED。有许多行具有相同的CMDNAME值。 TIME列只是事务的时间戳。 FAILED列是“Y”或“N”。

I want my result to be, for each distinct value of CMDNAME, the ratio of rows with FAILED='Y' to the total number of rows (for the same CMDNAME value).

我希望我的结果是，对于CMDNAME的每个不同值，FAILED ='Y'的行与总行数的比率（对于相同的CMDNAME值）。

I can get sort of close with the following:

我可以接受以下内容：

select CMDNAME, FAILED, count(*) groupCount from TABLE group by CMDNAME, FAILED

That gives me two rows for each unique CMDNAME value, one with the "N" count and one with the "Y" count. I could use this, with some intermediate computation, if I can't figure out the more direct way to compute it.

这为每个唯一的CMDNAME值提供了两行，一行具有“N”计数，一行具有“Y”计数。如果我无法找出更直接的计算方法，我可以使用这个，进行一些中间计算。

2 个解决方案

#1

One way to do it is using the count window function.

一种方法是使用计数窗口功能。

select distinct CMDNAME, 
1.0*count(case when failed = 'Y' then 1 end) over(partition by CMDNAME)
/count(*) over(partition by CMDNAME) as groupCount 
from TABLE

or using conditional aggregation.

或使用条件聚合。

select CMDNAME, 
1.0*count(case when failed = 'Y' then 1 end)/count(*) as groupCount 
from TABLE 
group by CMDNAME

#2

count, like many aggregate functions, skips nulls. You could use this property and count a case expression that reports only the failed='Y' rows:

与许多聚合函数一样，count会跳过空值。您可以使用此属性并计算仅报告失败='Y'行的案例表达式：

SELECT   cmdname,
         COUNT(*) AS all_rows
         COUNT(CASE failed WHEN 'Y' THEN 1 END) AS only_failures,
         COUNT(CASE failed WHEN 'Y' THEN 1 END) / COUNT(*) AS failure_ratio
FROM     mytbale
GROUP BY cmdname

#1