Hilfe zur Optimierung einer SQL-Abfrage für ein benutzerdefiniertes Badge zum Extrahieren der Anzahl von Wörtern und Bildern sowie zum Zählen der Tage, die in einem Thema gepostet wurden

Gemeinschaft Daten & Berichterstattung
1

Fortsetzung der Diskussion aus Wie kann man alle Abzeichen über die Befehlszeile erteilen:

Liebe Community, kann jemand bei der Optimierung dieser SQL-Abfrage für ein benutzerdefiniertes Abzeichen helfen?

Sie zählt die Anzahl der Wörter und Bilder, die in Themen einer bestimmten Kategorie gepostet wurden, und erteilt darauf basierend ein Abzeichen.

Ein frischer Blick von ein paar zusätzlichen Augen wäre sehr hilfreich :slight_smile: Vielen Dank.

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

Selbst gelöst:

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