我现在被一个简单的请求阻止了。。。:
我有这个数据库:
order | couple | first | second
-------------------------------
1 | A-B | A | B
2 | A-D | A | D
3 | A-C | A | C
4 | A-B | A | B
5 | Y-Z | Y | Z
我想在一条线上得到:
order | count | total | percentage
-------------------------------
A-B | 2 | 4 | 50%
A-D | 1 | 4 | 25%
A-C | 1 | 4 | 25%
Y-Z | 1 | 1 | 100%
我可以找到第一部分:订单计数。。。但是不可能用total和percentage来连接它(而不是使用子查询…):当我尝试进行连接时,有很多不同的错误。
我对第一部分的要求:
SELECT couple,count(couple)
FROM DATA d1
WHERE exists (
SELECT *
FROM DATA d2
WHERE d1.first = d2.first)
GROUP BY d1.couple;
我对第二部分的要求:
SELECT first, count(first)
FROM DATA
GROUP BY first;
有人知道秘诀了吗?
最佳答案
我想你想要:
select couple, count(*),
count(*) over (partition by first) as total,
(count(*) * 1.0 / sum(count(*)) over (partition by first) ) as ratio
from t
group by couple, first;