2006-02-23

Taggerati for www.astronet.ru

I want to show keywords from www.astronet.ru using fonts depending on their popularity (logarithm, 12 groups).

Relevant tables:

discovery=# \d keywords
            Table "public.keywords"
  Column   |         Type          | Modifiers 
-----------+-----------------------+-----------
 key_id    | integer               | not null
 name      | character varying(64) | not null
 status_id | integer               | default 3
Indexes:
    "keywords_pkey" PRIMARY KEY, btree (key_id)
    "keywords_first_name_char" btree (first_symbol_uppered(name::text))

discovery=# \d message_keyword_map
Table "public.message_keyword_map"
 Column |  Type   | Modifiers 
--------+---------+-----------
 msg_id | integer | not null
 key_id | integer | not null
Indexes:
    "message_keyword_map_pkey" PRIMARY KEY, btree (msg_id, key_id)
    "message_keyword_map_key_id" btree (key_id)
    "message_keyword_map_msg_id" btree (msg_id)

Queries:

• top-20 most popular (and published) keywords

discovery=# select kw.name, log(1+count(*)) as cnt from keywords kw,  message_keyword_
map mk where kw.key_id = mk.key_id and kw.status_id in (5,8) group by kw.name order by cnt desc limit 20;

• top-20 most popular keywords beginning from letter 'a'

discovery=# select kw.name, log(1+count(*)) as cnt from keywords kw,  message_keyword_
map mk where kw.name ilike 'а%' and kw.key_id = mk.key_id and kw.status_id in (5,8)  group by kw.name order by cnt desc limit 10;