如何在多个列上计数不同

I have the following table.

我有下面这张桌子。

group _id   p_id    version value
1              1       1    10
1              1       2    11
1              1       2    12
1              2       3    13
2              1       2    14
2              1       3    15
2              1       2    16

I would like to count on how many records for each group_id and how many distinct p_id + version for each group_id. I have following query

我希望计算每个group_id有多少条记录，每个group_id有多少个不同的p_id +版本。我有跟踪查询

SELECT "group_id",count(*) , count(distinct "p_id","version")
FROM tbl
group by "group_id"

Aapparently, it' not going to work, as Oracle will give me error on COUNT

显然，它不会起作用，因为Oracle会给我错误的计数。

ORA-00909: invalid number of arguments 

I know this can be done by subquery. However, is there any simple way to get same result? Considing the performance is important to me, as we have more than 500 million records in the table.

我知道这可以通过子查询来实现。然而，有什么简单的方法可以得到相同的结果吗?考虑到表演对我来说很重要，因为我们有超过5亿的记录。

SQL Fiddle

SQL小提琴

1 个解决方案

#1

I don't know if it's the best way, but I normally concatenate the two values, using a delimiter to enforce "distinctness", so they become one expression, which Oracle can handle with COUNT DISTINCT:

我不知道这是不是最好的方法，但我通常将这两个值串联起来，使用分隔符强制执行“特殊性”，因此它们成为一个表达式，Oracle可以用COUNT DISTINCT处理:

SELECT "group_id",count(*) , count(distinct "p_id" || '-' || "version")
FROM tbl
group by "group_id"