مساعدة في تحسين استعلام SQL لشعار مخصص يستخرج عدد الكلمات والصور، ويحسب عدد أيام النشر في موضوع

مجتمع البيانات والتقارير
1

متابعة للنقاش من كيفية منح جميع الشارات قسريًا عبر سطر الأوامر:

أيها المجتمع العزيز، هل يمكن لأحد المساعدة في تحسين استعلام 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