ليس لدي منتديات كبيرة، ومعظم ردود الفعل التي نحصل عليها تأتي من “الموظفين” (المسؤولون، المشرفون، المستوى 4). أردت أن أرى كيف تقارن الإعجابات من المستخدمين العاديين مقابل “الموظفين”، وأن أسرد عدد المشاركات/اليوم للحصول على فهم أفضل لما يحدث وأين نحتاج إلى تركيز الجهود لتحسين استخدام ردود الفعل.
أنا وصديقي ChatGPT توصلنا إلى هذا:
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-12-31
WITH date_range AS (
SELECT date_trunc('day', series) AS date
FROM generate_series(
:start_date::timestamp,
:end_date::timestamp,
'1 day'::interval
) series
),
staff_users AS (
SELECT id
FROM users
WHERE admin = true OR moderator = true OR trust_level = 4
)
SELECT
dr.date::date,
COALESCE(pa_non_staff.regular_likes_count, 0) AS regular_likes_count,
COALESCE(pa_staff.staff_likes_count, 0) AS staff_likes_count,
COALESCE(pa_non_staff.regular_likes_count, 0) + COALESCE(pa_staff.staff_likes_count, 0) AS total_likes,
COALESCE(posts_count.posts_per_day, 0) AS posts_per_day
FROM
date_range dr
LEFT JOIN (
SELECT
date_trunc('day', pa.created_at) AS action_date,
COUNT(*) AS regular_likes_count
FROM post_actions pa
WHERE pa.post_action_type_id = 2
AND pa.user_id NOT IN (SELECT id FROM staff_users)
AND pa.created_at >= :start_date
AND pa.created_at <= (:end_date::date + 1)
GROUP BY action_date
) pa_non_staff ON dr.date = pa_non_staff.action_date
LEFT JOIN (
SELECT
date_trunc('day', pa.created_at) AS action_date,
COUNT(*) AS staff_likes_count
FROM post_actions pa
WHERE pa.post_action_type_id = 2
AND pa.user_id IN (SELECT id FROM staff_users)
AND pa.created_at >= :start_date
AND pa.created_at <= (:end_date::date + 1)
GROUP BY action_date
) pa_staff ON dr.date = pa_staff.action_date
LEFT JOIN (
SELECT
date_trunc('day', p.created_at) AS post_date,
COUNT(*) AS posts_per_day
FROM posts p
WHERE p.created_at >= :start_date
AND p.created_at <= (:end_date::date + 1)
GROUP BY post_date
) posts_count ON dr.date = posts_count.post_date
ORDER BY dr.date
التغييرات على استعلام @SaraDev الأصلي (شكرًا، سارة!):
ملخص تغييرات SQL
- إنشاء مجموعة الموظفين:
تمت إضافة CTE staff_users لتحديد مستخدمي الموظفين من جدول users. يُعرَّف مستخدم الموظف بأنه أي مما يلي:
admin = truemoderator = truetrust_level = 4
- فصل إعجابات الموظفين:
تمت إضافة استعلام فرعي لحساب عدد الإعجابات من مستخدمي الموظفين (staff_likes_count) عن طريق تصفية post_actions لـ user_id في مجموعة staff_users.
- إعادة تسمية عمود إعجابات غير الموظفين:
تم تغيير تسمية الإخراج لإعجابات غير الموظفين من likes_count إلى regular_likes_count.
- إضافة إجمالي الإعجابات:
تم تقديم عمود total_likes لجمع regular_likes_count و staff_likes_count.
- إضافة المشاركات في اليوم:
تمت إضافة استعلام فرعي لحساب عدد المشاركات في اليوم (posts_per_day) وربطه بنطاق التاريخ.
(نعم، ChatGPT قام بهذه القائمة من التغييرات لي أيضًا.)
نتائج مثال:
| التاريخ |
regular_likes_count |
staff_likes_count |
posts_per_day |
| 1/1/24 |
0 |
6 |
7 |
| 1/2/24 |
0 |
5 |
3 |
| 1/3/24 |
1 |
0 |
4 |
| 1/4/24 |
1 |
2 |
5 |
| 1/5/24 |
9 |
9 |
30 |
| 1/6/24 |
0 |
1 |
11 |
| 1/7/24 |
2 |
4 |
11 |
| 1/8/24 |
0 |
5 |
18 |
| 1/9/24 |
0 |
0 |
2 |
| 1/10/24 |
0 |
0 |
7 |
| 1/11/24 |
0 |
4 |
5 |
| 1/12/24 |
4 |
0 |
4 |
| 1/13/24 |
6 |
0 |
10 |
| 1/14/24 |
1 |
7 |
18 |
| 1/15/24 |
2 |
4 |
7 |
نفس الاستعلام المبلغ عنه حسب الأسابيع للتنعيم
-- [params]
-- integer :weeks_ago = 52
WITH date_range AS (
SELECT date_trunc('week', series) AS week_start
FROM generate_series(
date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval,
date_trunc('week', now()),
'1 week'::interval
) series
),
staff_users AS (
SELECT id
FROM users
WHERE admin = true OR moderator = true OR trust_level = 4
)
SELECT
dr.week_start::date AS week_start,
COALESCE(pa_non_staff.regular_likes_count, 0) AS regular_likes_count,
COALESCE(pa_staff.staff_likes_count, 0) AS staff_likes_count,
COALESCE(pa_non_staff.regular_likes_count, 0) + COALESCE(pa_staff.staff_likes_count, 0) AS total_likes,
COALESCE(posts_count.posts_per_week, 0) AS posts_per_week
FROM
date_range dr
LEFT JOIN (
SELECT
date_trunc('week', pa.created_at) AS action_week,
COUNT(*) AS regular_likes_count
FROM post_actions pa
WHERE pa.post_action_type_id = 2
AND pa.user_id NOT IN (SELECT id FROM staff_users)
AND pa.created_at >= date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval
AND pa.created_at <= date_trunc('week', now())
GROUP BY action_week
) pa_non_staff ON dr.week_start = pa_non_staff.action_week
LEFT JOIN (
SELECT
date_trunc('week', pa.created_at) AS action_week,
COUNT(*) AS staff_likes_count
FROM post_actions pa
WHERE pa.post_action_type_id = 2
AND pa.user_id IN (SELECT id FROM staff_users)
AND pa.created_at >= date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval
AND pa.created_at <= date_trunc('week', now())
GROUP BY action_week
) pa_staff ON dr.week_start = pa_staff.action_week
LEFT JOIN (
SELECT
date_trunc('week', p.created_at) AS post_week,
COUNT(*) AS posts_per_week
FROM posts p
WHERE p.created_at >= date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval
AND p.created_at <= date_trunc('week', now())
GROUP BY post_week
) posts_count ON dr.week_start = posts_count.post_week
ORDER BY dr.week_start
في حال كان الأمر مثيرًا للاهتمام، إليك الاستعلامات النهائية التي عدلت استعلام سارة:
لدي استعلام SQL يقدم عدد الإعجابات اليومية (likes_count) بين تاريخين، ولكن أحتاج إلى إجراء التحسينات التالية لإنتاج مخرجات نهائية تجمع البيانات حسب الأسابيع وتتضمن تفاصيل إضافية:
- تحديد مجموعة الموظفين:
- إنشاء مجموعة
staff_users من جدول users. يجب اعتبار المستخدم موظفًا إذا استوفى أيًا من المعايير التالية:
admin = truemoderator = truetrust_level = 4
- فصل الإعجابات حسب الموظفين وغير الموظفين:
- إضافة عمودين منفصلين:
regular_likes_count: عدد الإعجابات من المستخدمين غير الموظفين.staff_likes_count: عدد الإعجابات من مستخدمي الموظفين.
- التأكد من أن عمود
regular_likes_count يستبعد الإعجابات التي أنشأها مستخدمو الموظفين.
- إضافة إجمالي الإعجابات:
- تضمين عمود
total_likes الذي يجمع regular_likes_count و staff_likes_count.
- إضافة المشاركات لكل فترة:
- إضافة عمود
posts_per_week الذي يحسب عدد المشاركات التي تم إنشاؤها خلال كل أسبوع.
- التجميع حسب الأسابيع:
- تعديل الاستعلام لتجميع جميع البيانات حسب فترات أسبوعية بدلاً من يومية.
- تضمين عمود
week_start الذي يمثل تاريخ بداية كل أسبوع.
- الحد حسب الأسابيع الماضية:
- تقديم معلمة
:weeks_ago لتحديد النتائج إلى آخر N أسبوع. يجب أن تكون القيمة الافتراضية 52 أسبوعًا (سنة واحدة).
- الترتيب والأعمدة النهائية:
- التأكد من أن المخرجات مرتبة حسب
week_start وتتضمن الأعمدة التالية بهذا الترتيب:
week_start: تاريخ بداية الأسبوع.regular_likes_count: عدد الإعجابات من المستخدمين غير الموظفين.staff_likes_count: عدد الإعجابات من مستخدمي الموظفين.total_likes: مجموع regular_likes_count و staff_likes_count.posts_per_week: عدد المشاركات التي تم إنشاؤها خلال الأسبوع.