针对红移数据的sql查询

Question

我在红移中有下表：

ID, Category
flinch, cat
flinch, cat
mara, dog
mara, cat

这里的目的是为每个ID获取每个类别的发生次数，因此预期结果为：

ID,Category,size
flinch, cat, 2
mara, dog, 1
mara, cat, 1

我尝试了几个查询，但得到了异常：ERROR: could not identify an ordering operator for type record Hint: Use an explicit ordering operator or modify the query.

Answer 1

您似乎想要group by:

select id, category, count(*) as size
from t
group by id, category;

Answer 2

你试过这个吗？

select id, category, count(*)
from mytable
group by id, category