Oracle对分区进行了多次计数

Question

我有以下查询，它输出以下结果：

SELECT
a,
b,
COUNT(1) count,
round(RATIO_TO_REPORT(COUNT(1)) OVER() * 100, 2) perc
FROM
t1
WHERE
condition1
GROUP BY
a,
b
ORDER BY
COUNT(1) DESC

>PERC89.374.120.99662

A	B	计数
纠正		968272
不匹配	正确	52620	4.86
正确	无值	44630
正确	不匹配	10722
无值	无值	6217	0.57
不匹配		0.06
无值	不匹配	294	0.03

这回答了问题的原始版本。

如果我理解正确的话，你想要b的值与总计数的比值。如果是，只需直接计算比率：

SELECT a, b, COUNT(1) as count,
SUM(SUM(CASE WHEN b = 'Correct' THEN 1 ELSE ) END)) OVER () * 100.0 / COUNT(*) as perc
FROM t1
WHERE condition1
GROUP BY a, b
ORDER BY COUNT(1) DESC;

试试这个：

WITH t AS
(SELECT a, b, COUNT(1) AS cnt FROM T1 WHERE condition1 GROUP BY a, b)
SELECT DISTINCT
a,
b,
SUM(cnt) OVER (PARTITION BY A, B) COUNT,
ROUND(RATIO_TO_REPORT(cnt) OVER () * 100, 2) perc,
SUM(cnt) OVER (PARTITION BY A),
ROUND(RATIO_TO_REPORT(cnt) OVER(PARTITION BY A) * 100, 2) 
FROM t
ORDER BY 3 DESC

如果您对如何在不同级别上将聚合与分析函数组合有疑问，请将其拆分为单独的CTE

第一个CTE计算总计数和百分比，第二个CTE用于A列。

然后将这两个结果合并以获得完整的报告。

with cnt as (
select a,b, count(*) count,
round(RATIO_TO_REPORT(COUNT(1)) OVER() * 100, 2) perc
from  tab t1
where 1=1 /* some condition 1 */
group by a,b),
cnt_a as (
select a, count(*) count,
round(RATIO_TO_REPORT(COUNT(1)) OVER() * 100, 2) perc
from  tab t1
where 1=1 /* some condition 1 */
group by a )
select 
cnt.a, cnt.b, cnt.count, cnt.perc,
cnt_a.count as count_by_a,
cnt_a.perc as perc_by_a
from cnt 
join cnt_a on cnt.a = cnt_a.a
order by 3 desc;
A         B              COUNT       PERC COUNT_BY_A  PERC_BY_A
--------- --------- ---------- ---------- ---------- ----------
Correct   Correct       968272      89,37    1023624      94,48
Mismatch  Correct        52620       4,86      53282       4,92
Correct   NO VALUES      44630       4,12    1023624      94,48
Correct   Mismatch       10722 ,99           1023624      94,48
NO VALUES NO VALUES       6217 ,57              6511        ,6        
Mismatch  Mismatch         662 ,06             53282       4,92
NO VALUES Mismatch         294 ,03              6511        ,6 

我不是声称有一个简单的一次性解决方案，但这个解决方案有效。

有趣的是，为什么您在样本数据中期望例如Correct的PERC_PARTITION_BY_A的三个不同值