커스텀 배지에서 단어 수와 이미지 수를 추출하고 주제 내 게시 일수를 계산하는 SQL 쿼리 최적화求助

커뮤니티 구축 데이터 및 보고
1

How to force grant all badges from command line에서 이어서 논의합니다:

커뮤니티 여러분, 커스텀 배지를 위한 이 SQL 쿼리를 최적화하는 데 도움을 주실 수 있나요?

이 쿼리는 특정 카테고리의 토픽에 게시된 단어 수와 이미지 수를 집계하고, 그 기준에 따라 배지를 부여합니다.

몇 분의 새로운 시각이 큰 도움이 될 것 같습니다 :slight_smile: 감사합니다.

WITH

personal_pages AS (
  SELECT *
  FROM topics
  WHERE category_id = 17
    AND archived = false
    AND closed = false
    AND visible = true
),

word_counts AS (
  SELECT p.id as post_id,
    p.word_count as num
  FROM posts p
),

image_count AS (
  SELECT p.id as post_id,
    length(raw) * 2
      - length(replace(raw, '<img', '123'))
      - length(replace(raw, '[img]', '1234')) as num
  FROM posts p
),

summary AS (
  SELECT 
    pp.id as topic_id,
    pp.user_id as user_id,
    COUNT (DISTINCT p.created_at::date) as num_days,
    SUM (wc.num) as num_words,
    SUM (ic.num) as num_images
  FROM personal_pages pp
  LEFT JOIN users u on u.id = pp.user_id
  LEFT JOIN posts p ON p.topic_id = pp.id
                   AND p.user_id = pp.user_id
  LEFT JOIN word_counts wc ON wc.post_id = p.id
  LEFT JOIN image_count ic ON ic.post_id = p.id
  WHERE u.staged = false
    AND p.deleted_at IS NULL
  GROUP BY pp.id, pp.user_id
)

SELECT summary.*,
  CURRENT_DATE as granted_at
FROM summary
WHERE num_days >= 10
  AND num_words >= 1000
  AND num_images >= 10
2

직접 수정했습니다:

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