Найти наиболее распространенные ключи, пары значений с Hstore в postgres

Question

Я собираю список предметов и номеров версий в столбце hstore в postgres. Мне интересно увидеть 100 наиболее распространенных пар ключ-значение. Например, если это был мой набор данных:

"foo"=> "22",
"foo"=> "33",
"bar"=> "55",
"baz"=> "77",
"foo"=> "22"

Я хотел бы знать, что "foo"=>"22" является наиболее распространенной парой ключ / значение в моей базе данных. Скажем для простоты разговора о проблеме, что имя таблицы widgets и имя столбца hstore items,

select ??? from widgets;

Можно ли получить список пар значений верхнего ключа, используя только SQL?

Answer 1

О, это довольно просто. Вот:

SELECT key, count(*) FROM
  (SELECT (each(h)).key FROM reports) 
    AS stat
GROUP BY key
ORDER BY count DESC, key
LIMIT 100;

Answer 2

Чтобы получить пары ключ / значение в виде набора, соответствующая функция each():

select * from each('a=>1,b=>2')

http://www.postgresql.org/docs/current/static/hstore.html

Простой подсчет с лимитом может помочь:

SELECT (item).key, (item).value, count(*) as count
FROM (SELECT each(items) as item FROM widgets) as t
GROUP BY (item).key
ORDER BY 2 DESC, (item).value
LIMIT 100

Если вас интересуют только ключи, вы можете использовать более простые skeys() вместо:

SELECT k, count(*) as count
FROM (SELECT skeys(items) as k FROM widgets) as t
GROUP BY k
ORDER BY 2 DESC, k
LIMIT 100

Answer 3

Как вы после полной пары ключ / значение, следующее должно сделать это:

select items, count(*) as cnt
from widgets
group by items
order by 2 desc
limit 100