# 获取特定类别每月的主题数量

**URL:** <https://meta.discourse.org/t/get-topic-count-per-month-for-a-certain-category/222100>\
**Category:** Data & reporting\
**Tags:** sql-query\
**Created:** [2022年三月27日 17:10 UTC](https://meta.discourse.org/t/get-topic-count-per-month-for-a-certain-category/222100 "2022-03-27T17:10:24Z")\
**Posts on this page:** 1\
**Showing post:** 2

<div class="post-metadata">

**Author:** ![JammyDodger](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/jammydodger/32/254611_2.png) [@JammyDodger](https://meta.discourse.org/u/JammyDodger)\
**Post date:** [2022年三月27日 17:16 UTC](https://meta.discourse.org/t/get-topic-count-per-month-for-a-certain-category/222100/2 "2022-03-27T17:16:46Z")

</div>

我认为您应该能够利用 [(Superseded) What cool data explorer queries have you come up with?](https://meta.discourse.org/t/what-cool-data-explorer-queries-have-you-come-up-with/43516) 中的一些示例来接近目标（并且 #sql-query 标签下还有更多主题）

如果您查看这些内容并发现有什么与您想要的内容相符，我们可以帮助您进行完善。👍

* * *

我确信有更巧妙的方法可以做到这一点，但也许是这样的？

```plaintext
-- [params]
-- int :cat_id = 6
-- date :date_from = 01/03/2022
-- date :date_to = 01/04/2022

SELECT t.category_id, count(t.category_id)
FROM topics t
WHERE t.category_id = :cat_id
AND t.created_at::date BETWEEN :date_from::date AND :date_to::date
AND t.deleted_at is null
GROUP BY t.category_id

```

等等。那不是按月计算的。我再试一次……

* * *

也许这次我会做得更好。🙂 怎么样？

```plaintext
-- [params]
-- int :cat_id = 5
-- int :months = 12

    SELECT
        date_part('year', created_at) AS year,
        date_part('month', created_at) AS month,
        COUNT(category_id) AS "new_topics_month"
    FROM topics t
    WHERE t.category_id = :cat_id
    AND t.deleted_at is NULL
    GROUP BY date_part('year', created_at), date_part('month', created_at)
    ORDER BY date_part('year', created_at) DESC, date_part('month', created_at) DESC
    LIMIT :months

```

---

_[View the full topic](https://meta.discourse.org/t/get-topic-count-per-month-for-a-certain-category/222100)._