I Currently have written an sql query like this:
我目前已经编写了这样的SQL查询：
SELECT a, b, count(id) / ????
FROM X
group by a, b
Please pay attention to the ????? in the code above. There are two groups in group by. Namely by columns a and b.
请注意?????在上面的代码中。分组中有两组。即，列a和b。
What I intend to do is to divide the (1) count of items in the second group (b) to the (2) count of items in the first group (a).
我打算做的是将（1）第二组（b）中的项目数除以第一组（a）中项目的（2）项数。
count(id) accomplishes (1). But I don't know what to write for (2).
count（id）完成（1）。但我不知道该写些什么（2）。
P.S.: However I know It is possible to replace "????" with another complex select query, but I want to learn if there is a simpler way to aggregate items of first Group by.
P.S。：但我知道有可能取代“????”与另一个复杂的选择查询，但我想知道是否有一种更简单的方法来聚合第一个Group by的项目。
Update: Sample data:
更新：示例数据：
id a b
______________________
1 'A' 'G'
2 'A' 'H'
3 'A' 'H'
4 'B' 'G'
5 'B' 'G'
6 'B' 'K'
7 'B' 'K'
results:
结果：
a b [unnamed]
________________________________
'A' 'G' 0.33333
'A' 'H' 0.66667
'B' 'G' 0.5
'B' 'K' 0.5
The third column is the percentage of columns with values as in column b relative to values in column a.
第三列是列中b的值相对于列a中的值的列的百分比。
Thank you.
谢谢。
2 个解决方案
#1
2
Maybe this works if you are using SQL Server
如果您使用的是SQL Server，这可能有用
SELECT DISTINCT a, b, (COUNT(*)OVER(PARTITION BY a, b)* 1.0/COUNT(*)OVER(PARTITION BY a)) as unnamed
FROM Sample
#2
2
select A.a, B.b, a.num, b.num, CAST(b.num as float) / cast(a.num as float)
from
(select a, count(*) as num
from @table
group by a) as A
join
(select a, b, count(*) as num
from @table
group by a, b) as B on A.a = B.a
#1
2
Maybe this works if you are using SQL Server
如果您使用的是SQL Server，这可能有用
SELECT DISTINCT a, b, (COUNT(*)OVER(PARTITION BY a, b)* 1.0/COUNT(*)OVER(PARTITION BY a)) as unnamed
FROM Sample
#2
2
select A.a, B.b, a.num, b.num, CAST(b.num as float) / cast(a.num as float)
from
(select a, count(*) as num
from @table
group by a) as A
join
(select a, b, count(*) as num
from @table
group by a, b) as B on A.a = B.a