Fix bi-20.sql
-with recursive
- subroots(s_tagclassid) as (
- select distinct tc_tagclassid from tagclass tc where tc_name in ($tagClasses)
- ),
- hierarchy(h_subtagclassid, h_supertagclassid) as (
- select s_subtagclassid, s_supertagclassid from subclass s
- where s.s_supertagclassid in (select s_tagclassid from subroots)
- union
- select s.s_subtagclassid, h.h_supertagclassid
- from subclass s, hierarchy h
- where s.s_supertagclassid = h.h_subtagclassid
+/* Q20. High-level topics
+\set tagClasses '\'{"Album","Person","OfficeHolder","MusicalArtist","Single","Country"}\''::varchar[]
+ */
+WITH RECURSIVE tagclass_tree(root_tagclassid, root_tagclassname, tagclassid) AS (
+ SELECT tc.tc_tagclassid AS root_tagclassid
+ , tc.tc_name AS root_tagclassname
+ , tc.tc_tagclassid AS tagclassid
+ FROM tagclass tc
+ WHERE 1=1
+ AND tc.tc_name = ANY (:tagClasses)
+ UNION ALL
+ SELECT tt.root_tagclassid
+ , tt.root_tagclassname
+ , tc.tc_tagclassid AS tagclassid
+ FROM tagclass tc
+ , tagclass_tree tt
+ WHERE 1=1
+ -- join
+ AND tt.tagclassid = tc.tc_subclassoftagclassid
)
-select
- tc_name,
- count(*) as cnt
-from tagclass, tag_tagclass, tag, post_tag, hierarchy
-where t_tagid = ttc_tagid
- and ttc_tagclassid = h_subtagclassid
- and h_supertagclassid = tc_tagclassid
- and pst_tagid = ttc_tagclassid
-group by tc_name
-order by
- cnt desc,
- tc_name asc
-limit 100;
+SELECT tt.root_tagclassname AS "tagClass.name"
+ , count(mt.pst_postid) AS messageCount
+ FROM tagclass_tree tt INNER JOIN tag_tagclass ttc ON (tt.tagclassid = ttc.ttc_tagclassid)
+ LEFT JOIN post_tag mt ON (ttc.ttc_tagid = mt.pst_tagid)
+ WHERE 1=1
+ GROUP BY tt.root_tagclassname
+ ORDER BY messageCount DESC, tt.root_tagclassname
+ LIMIT 100
+;
