Counting unique rows as an aggregate.

Поиск
Список
Период
Сортировка
От r_musta
Тема Counting unique rows as an aggregate.
Дата
Msg-id 41008430-014c-48ee-b7b6-10fd05b50b88@8g2000hse.googlegroups.com
обсуждение исходный текст
Ответы Re: Counting unique rows as an aggregate.  ("Scott Marlowe" <scott.marlowe@gmail.com>)
Re: Counting unique rows as an aggregate.  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
My current solution is to issue a bunch of queries:

> SELECT make, count(*) FROM table WHERE >criteria< GROUP BY make ORDER BY count(*) DESC LIMIT 3;
make     count
----------+---------
 audi   | 50
 bmw  | 40
 vw     | 30

SELECT color, count(*) FROM table WHERE >criteria< GROUP BY color
ORDER BY count(*) DESC LIMIT 3;

color       count
-----------+------
 red      | 400
 blue    | 200
 green  | 100

Which will give me the top 3 counts of each column im interested in
for the >criteria< specified.

However, this is starting to become too slow (as there are about 10 of
these queries), and therefore I need to write an aggregate function
which lets me do:

>SELECT count_unique(make), count_unique(color) from table WHERE >criteria<;

After reading about aggregate functions, this should be possible, as
long as I can use a dictionary/hashmap type for the state<STYPE>
argument.

Is there such a type in postgresql that can be used in an aggregate
function, and if there isn't, how would it be possible to make one?

Also, do you think I'm going about this the wrong way, and there is a
much better solution that's I've neglected?

В списке pgsql-general по дате отправления:

Предыдущее
От: Mike Diehl
Дата:
Сообщение: Can't cast from char to integer...
Следующее
От: john.crawford@sirsidynix.com
Дата:
Сообщение: database question