هذا هو إصدار SQL من تقرير لوحة المعلومات للمواضيع التي لم يتم الرد عليها.
تم تصميم تقرير لوحة المعلومات لحساب عدد المواضيع التي تم إنشاؤها ضمن نطاق تاريخ محدد ولم تتلق أي ردود من مستخدمين آخرين. يمكن تصفية هذا التقرير حسب فئة معينة ويمكن أن يشمل اختياريًا الفئات الفرعية.
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-02-01
-- null int :category_id
-- boolean :include_subcategories = false
WITH no_response_total AS (
SELECT *
FROM (
SELECT t.id, t.created_at, MIN(p.post_number) first_reply
FROM topics t
LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
WHERE t.archetype <> 'private_message'
AND t.deleted_at ISNULL
AND (
:category_id IS NULL
OR t.category_id = :category_id
OR (
:include_subcategories
AND t.category_id IN (
SELECT id FROM categories WHERE parent_category_id = :category_id
)
)
)
GROUP BY t.id
) tt
WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)
SELECT
DATE(nrt.created_at) AS date,
COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN :start_date AND :end_date
GROUP BY date
ORDER BY date ASC
المعلمات
معلمات التاريخ:
تقبل الاستعلام معلمتين، :start_date و :end_date، اللتين تحددان نطاق التاريخ للتقرير. تقبل كلتا معلمتي التاريخ تنسيق التاريخ YYYY-MM-DD.
معلمات الفئة:
:category_id: معلمة عدد صحيح يمكن تعيينها إلى معرف فئة معينة لتضييق نطاق التحليل إلى المشاركات ضمن تلك الفئة. إذا تم تعيينها إلى فارغة أو لم يتم توفيرها، يتم النظر في المواضيع من جميع الفئات.
:include_subcategories: معلمة منطقية تتحكم فيما إذا كان سيتم تضمين المشاركات من الفئات الفرعية للفئة المحددة :category_id. إذا تم تعيينها إلى true، فسيشمل التقرير روابط للمشاركات في كل من الفئة المحددة وفئاتها الفرعية؛ إذا كانت false، فسيتم النظر فقط في الفئة المحددة.
شرح استعلام SQL
يبدأ الاستعلام بتعبير جدول مشترك (CTE) يسمى no_response_total. يقوم هذا CTE بتنفيذ الخطوات التالية:
اختيار المواضيع: يختار جميع المواضيع (t.id) وتواريخ إنشائها (t.created_at) من جدول topics.
الربط الأيسر مع المشاركات: يقوم بربط أيسر مع جدول posts للعثور على أول رد لكل موضوع. تضمن شروط الربط أن المشاركة ليست من منشئ الموضوع (p.user_id != t.user_id)، وأن المشاركة لم يتم حذفها (p.deleted_at IS NULL)، وأن المشاركة من النوع 1، والذي يمثل عادةً ردًا قياسيًا.
تصفية المواضيع: يقوم الاستعلام بتصفية المواضيع التي هي رسائل خاصة (t.archetype <> 'private_message') والمواضيع التي تم حذفها (t.deleted_at ISNULL).
تصفية الفئة: إذا تم توفير :category_id، فسيقوم الاستعلام بتصفية المواضيع لتضمين تلك الموجودة في الفئة المحددة فقط. إذا كانت :include_subcategories صحيحة، فستشمل أيضًا المواضيع من الفئات الفرعية للفئة المحددة.
التجميع والحد الأدنى لرقم المشاركة: يتم تجميع المواضيع حسب معرفها، ويتم حساب الحد الأدنى لرقم المشاركة (MIN(p.post_number)) للعثور على أول رد.
التصفية لعدم وجود رد: يقوم الاستعلام الفرعي tt بتصفية المواضيع التي لها رد أول برقم مشاركة أكبر من أو يساوي 2، تاركًا فقط المواضيع التي لا تحتوي على ردود (tt.first_reply IS NULL) أو المشاركة الأصلية فقط (tt.first_reply < 2).
بعد تعريف CTE no_response_total، يقوم الاستعلام الرئيسي بما يلي:
التصفية حسب نطاق التاريخ: يقوم بتصفية المواضيع من CTE حسب تواريخ البدء والانتهاء المقدمة (:start_date و :end_date).
عد المواضيع التي لم يتم الرد عليها: يقوم بعدد المواضيع التي لم يتم الرد عليها لكل تاريخ ضمن النطاق المحدد.
التجميع حسب التاريخ: يتم تجميع النتائج حسب تاريخ إنشاء الموضوع (DATE(nrt.created_at)).
الترتيب: يتم ترتيب النتائج حسب التاريخ بترتيب تصاعدي.
هل يمكنك إنشاء إصدار لا يحتوي على معلمات؟ أريد إنشاء إصدار ينظر إلى الوراء 7 أيام ليتم إرساله عبر البريد الإلكتروني إلى الأشخاص وأواجه صعوبة في استخدام هذا الرمز لأنه يحتوي على معلمات محددة فيه
نعم، إليك إصدار محدث من الاستعلام الذي يعود 7 أيام من التاريخ الحالي دون استخدام معلمات.
لا يتضمن هذا الإصدار أي تصفية حسب الفئات أو الفئات الفرعية.
WITH no_response_total AS (
SELECT *
FROM (
SELECT
t.id,
t.created_at,
MIN(p.post_number) AS first_reply
FROM
topics t
LEFT JOIN
posts p
ON
p.topic_id = t.id
AND p.user_id != t.user_id
AND p.deleted_at IS NULL
AND p.post_type = 1
WHERE
t.archetype <> 'private_message'
AND t.deleted_at IS NULL
AND (
t.category_id = :category_id
OR t.category_id IN (
SELECT id FROM categories WHERE parent_category_id = :category_id
)
)
GROUP BY
t.id
) tt
WHERE
tt.first_reply IS NULL
OR tt.first_reply < 2
)
SELECT
DATE(nrt.created_at) AS date,
COUNT(nrt.id) AS topics_without_response
FROM
no_response_total nrt
WHERE
nrt.created_at::date BETWEEN CURRENT_DATE - INTERVAL '7 days' AND CURRENT_DATE
GROUP BY
date
ORDER BY
date ASC
إذا كنت ترغب في تعديل المدة التي يبحث فيها الاستعلام، فما عليك سوى تغيير هذا السطر في الاستعلام:
nrt.created_at::date BETWEEN CURRENT_DATE - INTERVAL '7 days' AND
أواجه صعوبة كبيرة في تحويل هذا من يعتمد على التاريخ إلى شهر وسنة.
لقد قمت بأشياء متعددة لمحاولة جعل هذا يعمل، ولكنه يستمر في إخباري بأن العمود غير موجود (على الرغم من أنه موجود حيث أنني أنشأته للتو ضمن عبارة with، ثم أشير إليه لاحقًا)
كيف يمكن لشخص ما تعديل هذا الرمز بحيث بدلاً من النظر إلى الموضوعات التي لا تحتوي على استجابة يومًا بيوم، يمكننا رؤيتها سنة بسنة، شهر بشهر، إلخ.
لتعديل الاستعلام بحيث يمكنه تجميع المواضيع التي لا تحتوي على ردود حسب السنة أو الشهر أو فترات زمنية أخرى، يمكنك إضافة معلمة لتحديد الفترة المطلوبة في دالة date_trunc لتحقيق ذلك.
على سبيل المثال:
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
-- null int :category_id
-- boolean :include_subcategories = false
-- text :interval = day -- Options: day, week, month, year
WITH no_response_total AS (
SELECT *
FROM (
SELECT t.id, t.created_at, MIN(p.post_number) first_reply
FROM topics t
LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
WHERE t.archetype <> 'private_message'
AND t.deleted_at ISNULL
AND (
:category_id IS NULL
OR t.category_id = :category_id
OR (
:include_subcategories
AND t.category_id IN (
SELECT id FROM categories WHERE parent_category_id = :category_id
)
)
)
GROUP BY t.id
) tt
WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)
SELECT
date_trunc(:interval, nrt.created_at)::date AS period,
COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN :start_date AND :end_date
GROUP BY period
ORDER BY period ASC
إذا كنت ترغب في إزالة المعلمات، يمكنك بدلاً من ذلك استخدام استعلام مثل:
WITH no_response_total AS (
SELECT *
FROM (
SELECT t.id, t.created_at, MIN(p.post_number) first_reply
FROM topics t
LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
WHERE t.archetype <> 'private_message'
AND t.deleted_at ISNULL
GROUP BY t.id
) tt
WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)
SELECT
date_trunc('year', nrt.created_at)::date AS period,
COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN '2024-01-01' AND '2025-01-01'
GROUP BY period
ORDER BY period ASC
لتغيير تنسيق التاريخ من 10-22 إلى Oct-22 في PostgreSQL، يمكنك استخدام الدالة TO_CHAR. تسمح لك هذه الدالة بتنسيق التواريخ بطرق مختلفة، على سبيل المثال:
SELECT
TO_CHAR(TO_DATE('10-22', 'MM-YY'), 'Mon-YY') AS formatted_date
في بيان SQL هذا:
TO_DATE('10-22', 'MM-YY') يحول السلسلة النصية 10-22 إلى نوع تاريخ باستخدام التنسيق MM-YY.
TO_CHAR(..., 'Mon-YY') يقوم بعد ذلك بتنسيق هذا التاريخ لعرض اسم الشهر المختصر متبوعًا بالسنة، مما ينتج عنه Oct-22.
إليك مثال آخر لهذا بناءً على إصدار استعلام Topics with No Response مع المعلمة interval التي تمت مشاركتها أعلاه:
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
-- null int :category_id
-- boolean :include_subcategories = false
-- text :interval = month
WITH no_response_total AS (
SELECT *
FROM (
SELECT t.id, t.created_at, MIN(p.post_number) first_reply
FROM topics t
LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
WHERE t.archetype <> 'private_message'
AND t.deleted_at ISNULL
AND (
:category_id IS NULL
OR t.category_id = :category_id
OR (
:include_subcategories
AND t.category_id IN (
SELECT id FROM categories WHERE parent_category_id = :category_id
)
)
)
GROUP BY t.id
) tt
WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)
SELECT
TO_CHAR(date_trunc(:interval, nrt.created_at)::date, 'Mon-YY') AS period,
COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN :start_date AND :end_date
GROUP BY period, date_trunc(:interval, nrt.created_at)::date
ORDER BY date_trunc(:interval, nrt.created_at)::date ASC
