# カスタムバッジの作成に役立つSQLクエリの最適化：単語数と画像数の抽出、およびトピックへの投稿日数のカウント

**URL:** <https://meta.discourse.org/t/help-optimize-sql-query-for-a-custom-badge-extracting-number-of-words-and-images-and-counting-days-posted-in-a-topic/137969>\
**Category:** Data & reporting\
**Tags:** sql-triggered-badge\
**Created:** [2020 年 1 月 7 日午後 1:43 UTC](https://meta.discourse.org/t/help-optimize-sql-query-for-a-custom-badge-extracting-number-of-words-and-images-and-counting-days-posted-in-a-topic/137969 "2020-01-07T13:43:43Z")\
**Posts on this page:** 1\
**Showing post:** 2

<div class="post-metadata">

**Author:** ![meglio](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/meglio/32/71444_2.png) [@meglio](https://meta.discourse.org/u/meglio)\
**Post date:** [2020 年 1 月 9 日午後 12:49 UTC](https://meta.discourse.org/t/help-optimize-sql-query-for-a-custom-badge-extracting-number-of-words-and-images-and-counting-days-posted-in-a-topic/137969/2 "2020-01-09T12:49:07Z")

</div>

自分で修正しました：

```plaintext
WITH summary as (

SELECT
  t.id as topic_id,
  t.created_at as created_at,
  t.user_id as user_id,
  COUNT (DISTINCT p.created_at::date) as num_days,
  SUM (p.word_count) as num_words,
  SUM (
    length(raw) * 2
      - length(replace(raw, '![IMG', '1234'))
      - length(replace(raw, '[img]', '1234'))
  ) as num_images
FROM topics t
  LEFT JOIN posts p ON p.topic_id = t.id
WHERE
    t.category_id = 17
    AND t.archived = false
    AND t.closed = false
    AND t.visible = true
    AND p.deleted_at IS NULL
    AND p.user_id = t.user_id
GROUP BY t.id

)

SELECT s.*,
  CURRENT_DATE as granted_at
FROM summary s
WHERE
    num_days >= 10
    AND num_words >= 1000
    AND num_images >= 10
ORDER BY created_at DESC

```

---

_[View the full topic](https://meta.discourse.org/t/help-optimize-sql-query-for-a-custom-badge-extracting-number-of-words-and-images-and-counting-days-posted-in-a-topic/137969)._