按子类别查看

社区 数据与报告
1

按子级分类查看浏览量

对主题浏览量进行更细粒度的分析，但不进行汇总。子分类为“（无）”的条目表示直接放置在顶级分类中的主题。

SELECT 
    topcat AS "Category", 
    subcat AS "Subcategory", 
    SUM(views) AS "Subcategory views"

FROM (

    /* 子级分类中的主题浏览量。 */
    SELECT
        topcat.name AS topcat, subcat.name AS subcat, SUM(topics.views) AS views
    FROM topics
        INNER JOIN categories subcat ON topics.category_id = subcat.id
        INNER JOIN categories topcat ON subcat.parent_category_id = topcat.id
    GROUP BY topcat.name, subcat.name
    
    UNION

    /* 顶级分类中的主题浏览量（排除子级分类）。 */
    SELECT
        topcat.name AS topcat, '(none)', SUM(topics.views) AS views
    FROM topics 
        INNER JOIN categories topcat ON topics.category_id = topcat.id
    WHERE topcat.parent_category_id IS NULL
    GROUP BY topcat.name

) AS views_by_cat

GROUP BY topcat, subcat

/* 按分类或浏览量对输出进行排序。启用以下其中一项： */
/* ORDER BY topcat, subcat */
ORDER BY "Subcategory views" DESC
7 个赞