التعامل مع القيم الفارغة مع COALESCE

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

في هذا البرنامج التعليمي، سنستكشف استخدام دالة COALESCE في استعلامات SQL الخاصة بـ Data Explorer.

تسمح لك دالة COALESCE بالتعامل مع القيم NULL في نتائج استعلامك. إذا كانت لديك قيم NULL في بياناتك، يمكنك استخدام COALESCE لتوفير قيمة افتراضية (مثل 0) لهذه القيم NULL.

تُعد COALESCE مفيدة بشكل خاص عند إجراء حسابات أو تحليلات بيانات لاحقة على نتائج استعلامك، حيث قد تتسبب قيم NULL في حدوث مشاكل أو سوء تفسير.

الصيغة

تأخذ دالة COALESCE وسيطتين أو أكثر وتُرجع أول قيمة غير NULL تصادفها من اليسار إلى اليمين في القائمة. إذا كانت جميع الوسائط NULL، فإن COALESCE تُرجع NULL.

الصيغة الأساسية لـ COALESCE هي كما يلي:

COALESCE(value1, value2, ..., valueN)

على سبيل المثال، سيعيد COALESCE(NULL, 1, 2) القيمة 1 لأن 1 هي أول وسيطة غير NULL.

استعلامات مثال

لنلقِ نظرة على بعض استعلامات المثال لفهم كيفية استخدام COALESCE في استعلامات Data Explorer.

المنشورات المنشأة، الإعجابات المستلمة، والإشارات المرجعية المستلمة

مستوى التعقيد: مبتدئ

هذا الاستعلام سيحصل على العدد الإجمالي للمنشورات المنشأة، والإعجابات المستلمة، والإشارات المرجعية المستلمة على المنشورات لكل مستخدم في موقع ما. إذا لم يكن لدى المستخدم أي منشورات أو إعجابات أو إشارات مرجعية مستلمة، فإن دالة COALESCE ستُرجع 0 بدلاً من NULL.

SELECT 
    users.id AS user_id,
    users.username,
    COALESCE(COUNT(posts.id), 0) AS post_count,
    COALESCE(SUM(posts.like_count), 0) AS likes_received,
    COALESCE(SUM(posts.bookmark_count), 0) AS bookmarks_received
FROM 
    users
LEFT JOIN 
    posts ON users.id = posts.user_id
GROUP BY 
    users.id, users.username
ORDER BY 
    post_count DESC, likes_received DESC, bookmarks_received DESC

نتائج مثال:

user username post_count likes_received bookmarks_received
1 alice 345 6 9
2 bella 278 5 6
3 charlie 37 3 3
4 dave 0 0 0

في هذا الاستعلام، نقوم بدمج جدول users مع جدول posts بناءً على حقل user_id. ثم نستخدم دالة COALESCE للتأكد من أنه إذا لم يكن لدى المستخدم أي منشورات أو إعجابات مستلمة أو إشارات مرجعية مستلمة، فإننا نرجع 0 بدلاً من NULL. يتم تجميع النتائج حسب معرف المستخدم واسم المستخدم، وترتيبها حسب عدد المنشورات والإعجابات والإشارات المرجعية بترتيب تنازلي.

المواضيع والردود لكل مستخدم

مستوى التعقيد: متوسط

يحصل هذا الاستعلام على عدد المواضيع والردود التي أنشأها كل مستخدم بين تاريخين. إذا لم يكن لدى المستخدم أي مواضيع أو ردود، فإن COALESCE ستُرجع 0 بدلاً من NULL.

-- [params]
-- date :start_date 
-- date :end_date
-- string NULL:username

WITH qtt_topics AS (
    SELECT 
        t.user_id,
        COUNT(*) AS topics
    FROM topics t
    WHERE    
        t.user_id > 0 
        AND t.deleted_at ISNULL
        AND t.archetype = 'regular'
        AND t.created_at::date BETWEEN :start_date AND :end_date
    GROUP BY t.user_id
    ),
    
qtt_replies AS (
    SELECT 
        p.user_id,
        COUNT(*) AS replies
    FROM posts p
    WHERE    
        p.user_id > 0 
        AND p.deleted_at ISNULL
        AND p.post_number != 1
        AND p.created_at::date BETWEEN :start_date AND :end_date
    GROUP BY p.user_id
    ),

total AS (
    SELECT
        COALESCE(qr.user_id, qt.user_id) user_id,
        COALESCE(topics,0) qtt_topics,
        COALESCE(replies,0) qtt_replies
    FROM qtt_topics qt
    FULL JOIN qtt_replies qr ON qt.user_id = qr.user_id
    ORDER BY user_id)

SELECT 
    username,
    qtt_topics,
    qtt_replies
FROM total
INNER JOIN users u ON u.id = user_id
WHERE :username ISNULL OR LOWER(username) LIKE '%'||LOWER(:username)||'%'

نتائج مثال:

username qtt_topics qtt_replies
Alice 10 50
Bella 15 45
Charlie 12 30

في هذا الاستعلام، تُستخدم COALESCE في التعبير الجدولي المشترك (CTE) المسمى total. يضمن ذلك أنه إذا كان user_id NULL في أي من qtt_topics أو qtt_replies، يتم استخدام القيمة الأخرى. هذا مهم لأنه يتم استخدام FULL JOIN لدمج qtt_topics و qtt_replies، وإذا كان لدى المستخدم مواضيع فقط دون ردود (أو العكس)، فسيكون user_id الخاص به NULL في إحدى الجداول. تمنع COALESCE حدوث ذلك.

شرح تفصيلي مع تعليقات مضمنة 
-- [params]
-- date :start_date 
-- date :end_date
-- string NULL:username

-- تعريف CTE (تعبير جدول مشترك) لحساب عدد المواضيع لكل مستخدم
WITH qtt_topics AS (
    SELECT 
        t.user_id,  -- معرف المستخدم
        COUNT(*) AS topics  -- عدد المواضيع
    FROM topics t  -- من جدول المواضيع
    WHERE    
        t.user_id > 0  -- النظر فقط في معرفات المستخدمين غير الصفرية
        AND t.deleted_at ISNULL  -- النظر فقط في المواضيع التي لم يتم حذفها
        AND t.archetype = 'regular'  -- النظر فقط في المواضيع العادية
        AND t.created_at::date BETWEEN :start_date AND :end_date  -- النظر فقط في المواضيع التي تم إنشاؤها بين تاريخ البدء وتاريخ الانتهاء
    GROUP BY t.user_id  -- التجميع حسب معرف المستخدم للحصول على عدد المواضيع لكل مستخدم
),
    
-- تعريف CTE لحساب عدد الردود لكل مستخدم
qtt_replies AS (
    SELECT 
        p.user_id,  -- معرف المستخدم
        COUNT(*) AS replies  -- عدد الردود
    FROM posts p  -- من جدول المنشورات
    WHERE    
        p.user_id > 0  -- النظر فقط في معرفات المستخدمين غير الصفرية
        AND p.deleted_at ISNULL  -- النظر فقط في المنشورات التي لم يتم حذفها
        AND p.post_number != 1  -- النظر فقط في المنشورات التي ليست المنشور الأول في موضوع (أي الردود)
        AND p.created_at::date BETWEEN :start_date AND :end_date  -- النظر فقط في المنشورات التي تم إنشاؤها بين تاريخ البدء وتاريخ الانتهاء
    GROUP BY p.user_id  -- التجميع حسب معرف المستخدم للحصول على عدد الردود لكل مستخدم
),

-- تعريف CTE لدمج عدد المواضيع والردود لكل مستخدم
total AS (
    SELECT
        COALESCE(qr.user_id, qt.user_id) user_id,  -- معرف المستخدم (من qtt_replies أو qtt_topics)
        COALESCE(topics,0) qtt_topics,  -- عدد المواضيع (إذا كان NULL، ارجع 0)
        COALESCE(replies,0) qtt_replies  -- عدد الردود (إذا كان NULL، ارجع 0)
    FROM qtt_topics qt  -- من CTE qtt_topics
    FULL JOIN qtt_replies qr ON qt.user_id = qr.user_id  -- الدمج مع CTE qtt_replies بناءً على معرف المستخدم
    ORDER BY user_id  -- الترتيب حسب معرف المستخدم
)

-- الاستعلام الرئيسي للحصول على مجموعة النتائج النهائية
SELECT 
    username,  -- اسم المستخدم
    qtt_topics,  -- عدد المواضيع
    qtt_replies  -- عدد الردود
FROM total  -- من CTE total
INNER JOIN users u ON u.id = user_id  -- الدمج مع جدول المستخدمين بناءً على معرف المستخدم
WHERE :username ISNULL OR LOWER(username) LIKE '%'||LOWER(:username)||'%'  -- التصفية حسب اسم المستخدم (إذا تم توفيره)

بيانات الأسئلة المحلولة

مستوى التعقيد: متقدم / يتطلب إضافة Discourse Solved

يُستخدم هذا الاستعلام للحصول على تفاصيل حول المواضيع، بما في ذلك ما إذا كانت محلولة أم لا، والوقت المستغرق للحصول على أول رد، والوقت المستغرق للحصول على الحل، وإحصائيات أخرى ذات صلة.

يفترض هذا الاستعلام أن جميع المواضيع في الموقع يمكن حلها.

--[params]
-- date :start_date = 2022-01-01
-- date :end_date = 2024-01-01
-- text :tag_name = all


WITH valid_topics AS (
    SELECT 
        t.id,
        t.user_id,
        t.title,
        t.views,
        posts_count-1 AS "posts_count", 
        t.created_at,
        (CURRENT_DATE::date-t.created_at::date) AS "total_days",
        string_agg(tags.name, ', ') AS tag_names
    FROM topics t
    LEFT JOIN topic_tags tt ON tt.topic_id = t.id
    LEFT JOIN tags ON tags.id = tt.tag_id
    LEFT JOIN tag_group_memberships tgm ON tgm.tag_id = tt.tag_id
    LEFT JOIN (SELECT topic_id, MIN(created_at) AS "created_at"
                FROM posts
                WHERE deleted_at ISNULL
                    AND post_type = 1
                    AND post_number > 1
                GROUP BY topic_id) date_first_reply ON date_first_reply.topic_id = t.id
    WHERE t.deleted_at ISNULL
        AND t.created_at::date BETWEEN :start_date AND :end_date
        AND t.archetype = 'regular'
    GROUP BY t.id, t.user_id, t.title, t.views, posts_count, t.created_at
),

solved_topics AS (
    SELECT 
        vt.id,
        tcf.created_at
    FROM topic_custom_fields tcf
    INNER JOIN valid_topics vt ON vt.id = tcf.topic_id
    WHERE tcf.name = 'accepted_answer_post_id'
),

last_reply AS (
    SELECT p.topic_id, p.user_id FROM posts p
    INNER JOIN (SELECT topic_id, MAX(id) AS post FROM posts p
                WHERE deleted_at ISNULL
                AND post_type = 1
                GROUP BY topic_id) x ON x.post = p.id
),

first_reply AS (
    SELECT p.topic_id, p.user_id, p.created_at FROM posts p
    INNER JOIN (SELECT topic_id, MIN(id) AS post FROM posts p
                WHERE deleted_at ISNULL
                AND post_type = 1
                AND post_number > 1
                GROUP BY topic_id) x ON x.post = p.id
)

SELECT
    CASE 
        WHEN st.id IS NOT NULL THEN 'solved'
        ELSE 'unsolved'
    END AS status,
    vt.tag_names,
    vt.id AS topic_id,
    vt.user_id topic_user_id,
    ue.email,
    vt.title,
    vt.views,
    lr.user_id AS last_reply_user_id,
    ue2.email AS last_reply_user_email,
    vt.created_at::date topic_create,
    COALESCE(TO_CHAR(fr.created_at,'YYYY-MM-DD'),'') AS first_reply_create,
    COALESCE(TO_CHAR(st.created_at,'YYYY-MM-DD'),'') AS solution_create,
    COALESCE(fr.created_at::date - vt.created_at::date,0) AS "time_first_reply(days)",
    COALESCE(CEIL(extract(epoch from (fr.created_at - vt.created_at))/3600.00),0) as "time_first_reply(hours)",
    COALESCE(st.created_at::date - vt.created_at::date,0) AS "time_solution(days)",
    COALESCE(CEIL(extract(epoch from (st.created_at - vt.created_at))/3600.00),0) as "time_solution(hours)",
    posts_count AS number_of_replies,
    total_days AS total_days_without_solution
FROM valid_topics vt
LEFT JOIN last_reply lr ON lr.topic_id = vt.id
LEFT JOIN first_reply fr ON fr.topic_id = vt.id
LEFT JOIN solved_topics st ON st.id = vt.id
INNER JOIN user_emails ue ON vt.user_id = ue.user_id AND ue."primary" = true
LEFT JOIN user_emails ue2 ON lr.user_id = ue2.user_id AND ue2."primary" = true
WHERE (:tag_name =  'all' OR LOWER(vt.tag_names) LIKE '%' || LOWER(:tag_name) || '%')
ORDER BY tag_names, total_days DESC

نتائج مثال:

status tag_names topic topic_user email title views last_reply_user last_reply_user_email topic_create first_reply_create solution_create time_first_reply(days) time_first_reply(hours) time_solution(days) time_solution(hours) number_of_replies total_days_without_solution
solved a, c, b عنوان موضوع (7) alice alice@example.com عنوان موضوع 58 bella bella@example.com 2023-08-25 2023-08-25 2023-08-29 0 1 1 24 9 4
unsolved tag1 مرحبًا بك في الصالة (3) system no_email مرحبًا بك في الصالة 3 system no_email 2023-05-01 0 0 0 0 2 134

في هذا الاستعلام، تُستخدم COALESCE في السطور التالية:

  • COALESCE(TO_CHAR(fr.created_at,'YYYY-MM-DD'),'') AS first_reply_create: هذا السطر يحول تاريخ created_at لأول رد إلى نص. إذا لم يكن أول رد موجودًا (أي أن fr.created_at هو null)، فسيُرجع نصًا فارغًا (‘’).
  • COALESCE(TO_CHAR(st.created_at,'YYYY-MM-DD'),'') AS solution_create: مشابه للسابق، يحول هذا السطر تاريخ created_at للحل إلى نص. إذا لم يكن الحل موجودًا (أي أن st.created_at هو null)، فسيُرجع نصًا فارغًا (‘’).
  • COALESCE(fr.created_at::date - vt.created_at::date,0) AS "time_first_reply(days)": هذا السطر يحسب فرق الوقت بالأيام بين إنشاء الموضوع وأول رد. إذا لم يكن أول رد موجودًا (أي أن fr.created_at هو null)، فسيُرجع 0.
  • COALESCE(CEIL(extract(epoch from (fr.created_at - vt.created_at))/3600.00),0) as "time_first_reply(hours)": هذا السطر يحسب فرق الوقت بالساعات بين إنشاء الموضوع وأول رد. إذا لم يكن أول رد موجودًا (أي أن fr.created_at هو null)، فسيُرجع 0.
  • COALESCE(st.created_at::date - vt.created_at::date,0) AS "time_solution(days)": هذا السطر يحسب فرق الوقت بالأيام بين إنشاء الموضوع والحل. إذا لم يكن الحل موجودًا (أي أن st.created_at هو null)، فسيُرجع 0.
  • COALESCE(CEIL(extract(epoch from (st.created_at - vt.created_at))/3600.00),0) as "time_solution(hours)": هذا السطر يحسب فرق الوقت بالساعات بين إنشاء الموضوع والحل. إذا لم يكن الحل موجودًا (أي أن st.created_at هو null)، فسيُرجع 0.

في جميع هذه الحالات، تُستخدم COALESCE لمنع ظهور قيم NULL في النتائج النهائية، مما يحسن قابلية قراءة استعلام الناتج وقد يكون مفيدًا لمعالجة البيانات أو تحليلها لاحقًا.

شرح تفصيلي مع تعليقات مضمنة 
--[params]
-- date :start_date = 2022-01-01
-- date :end_date = 2024-01-01
-- text :tag_name = all

-- تعريف CTE للمواضيع الصالحة
WITH valid_topics AS (
    -- تحديد الحقول اللازمة
    SELECT 
        t.id,  -- معرف الموضوع
        t.user_id,  -- معرف المستخدم
        t.title,  -- عنوان الموضوع
        t.views,  -- عدد المشاهدات
        posts_count-1 AS "posts_count",  -- عدد المنشورات في الموضوع
        t.created_at,  -- تاريخ إنشاء الموضوع
        (CURRENT_DATE::date-t.created_at::date) AS "total_days",  -- إجمالي الأيام منذ إنشاء الموضوع
        string_agg(tags.name, ', ') AS tag_names  -- تجميع جميع الوسوم المرتبطة بالموضوع
    FROM topics t  -- من جدول المواضيع
    -- دمج الجداول اللازمة للحصول على أسماء الوسوم
    LEFT JOIN topic_tags tt ON tt.topic_id = t.id
    LEFT JOIN tags ON tags.id = tt.tag_id
    LEFT JOIN tag_group_memberships tgm ON tgm.tag_id = tt.tag_id
    -- استعلام فرعي للحصول على تاريخ أول رد لكل موضوع
    LEFT JOIN (SELECT topic_id, MIN(created_at) AS "created_at"
                FROM posts
                WHERE deleted_at ISNULL
                    AND post_type = 1
                    AND post_number > 1
                GROUP BY topic_id) date_first_reply ON date_first_reply.topic_id = t.id
    WHERE t.deleted_at ISNULL  -- النظر فقط في المواضيع التي لم يتم حذفها
        AND t.created_at::date BETWEEN :start_date AND :end_date  -- النظر فقط في المواضيع التي تم إنشاؤها بين تاريخ البدء وتاريخ الانتهاء
        AND t.archetype = 'regular'  -- النظر فقط في المواضيع العادية
    GROUP BY t.id, t.user_id, t.title, t.views, posts_count, t.created_at  -- التجميع حسب الحقول اللازمة للحصول على العدد الصحيح
),

-- تعريف CTE للمواضيع المحلولة
solved_topics AS (
    -- تحديد معرف الموضوع وتاريخ إنشاء الحل
    SELECT 
        vt.id,
        tcf.created_at
    FROM topic_custom_fields tcf  -- من جدول topic_custom_fields
    INNER JOIN valid_topics vt ON vt.id = tcf.topic_id  -- الدمج مع CTE valid_topics
    WHERE tcf.name = 'accepted_answer_post_id'  -- النظر فقط في المواضيع التي لها إجابة مقبولة
),

-- تعريف CTE لآخر رد لكل موضوع
last_reply AS (
    -- تحديد معرف الموضوع ومعرف المستخدم لآخر رد
    SELECT p.topic_id, p.user_id FROM posts p
    INNER JOIN (SELECT topic_id, MAX(id) AS post FROM posts p  -- استعلام فرعي للحصول على معرف آخر منشور لكل موضوع
                WHERE deleted_at ISNULL
                AND post_type = 1
                GROUP BY topic_id) x ON x.post = p.id  -- الدمج مع جدول المنشورات للحصول على معرف المستخدم لآخر رد
),

-- تعريف CTE لأول رد لكل موضوع
first_reply AS (
    -- تحديد معرف الموضوع، معرف المستخدم، وتاريخ إنشاء أول رد
    SELECT p.topic_id, p.user_id, p.created_at FROM posts p
    INNER JOIN (SELECT topic_id, MIN(id) AS post FROM posts p  -- استعلام فرعي للحصول على معرف أول رد لكل موضوع
                WHERE deleted_at ISNULL
                AND post_type = 1
                AND post_number > 1
                GROUP BY topic_id) x ON x.post = p.id  -- الدمج مع جدول المنشورات للحصول على معرف المستخدم وتاريخ إنشاء أول رد
)

-- الاستعلام الرئيسي للحصول على مجموعة النتائج النهائية
SELECT
    CASE 
        WHEN st.id IS NOT NULL THEN 'solved'  -- إذا كان معرف الموضوع موجودًا في CTE solved_topics، فإن الحالة هي 'solved'
        ELSE 'unsolved'  -- وإلا، فإن الحالة هي 'unsolved'
    END AS status,
    vt.tag_names,  -- أسماء الوسوم
    vt.id AS topic_id,  -- معرف الموضوع
    vt.user_id topic_user_id,  -- معرف المستخدم
    ue.email,  -- البريد الإلكتروني للمستخدم
    vt.title,  -- عنوان الموضوع
    vt.views,  -- عدد المشاهدات
    lr.user_id AS last_reply_user_id,  -- معرف المستخدم لآخر رد
    ue2.email AS last_reply_user_email,  -- البريد الإلكتروني للمستخدم الذي قدم آخر رد
    vt.created_at::date topic_create,  -- تاريخ إنشاء الموضوع
    COALESCE(TO_CHAR(fr.created_at,'YYYY-MM-DD'),'') AS first_reply_create,  -- تاريخ إنشاء أول رد، إذا كان موجودًا، وإلا نص فارغ
    COALESCE(TO_CHAR(st.created_at,'YYYY-MM-DD'),'') AS solution_create,  -- تاريخ إنشاء الحل، إذا كان موجودًا، وإلا نص فارغ
    COALESCE(fr.created_at::date - vt.created_at::date,0) AS "time_first_reply(days)",  -- الوقت المستغرق للحصول على أول رد بالأيام
    COALESCE(CEIL(extract(epoch from (fr.created_at - vt.created_at))/3600.00),0) as "time_first_reply(hours)",  -- الوقت المستغرق للحصول على أول رد بالساعات
    COALESCE(st.created_at::date - vt.created_at::date,0) AS "time_solution(days)",  -- الوقت المستغرق للحصول على الحل بالأيام
    COALESCE(CEIL(extract(epoch from (st.created_at - vt.created_at))/3600.00),0) as "time_solution(hours)",  -- الوقت المستغرق للحصول على الحل بالساعات
    posts_count AS number_of_replies,  -- عدد الردود
    total_days AS total_days_without_solution  -- إجمالي الأيام بدون حل
FROM valid_topics vt  -- من CTE valid_topics
LEFT JOIN last_reply lr ON lr.topic_id = vt.id  -- الدمج مع CTE last_reply
LEFT JOIN first_reply fr ON fr.topic_id = vt.id  -- الدمج مع CTE first_reply
LEFT JOIN solved_topics st ON st.id = vt.id  -- الدمج مع CTE solved_topics
INNER JOIN user_emails ue ON vt.user_id = ue.user_id AND ue."primary" = true  -- الدمج مع جدول user_emails للحصول على البريد الإلكتروني للمستخدم
LEFT JOIN user_emails ue2 ON lr.user_id = ue2.user_id AND ue2."primary" = true  -- الدمج مع جدول user_emails للحصول على البريد الإلكتروني للمستخدم الذي قدم آخر رد
WHERE (:tag_name =  'all' OR LOWER(vt.tag_names) LIKE '%' || LOWER(:tag_name) || '%')  -- التصفية حسب اسم الوسم
ORDER BY tag_names, total_days DESC  -- الترتيب حسب أسماء الوسوم وإجمالي الأيام بترتيب تنازلي

إذا كانت لديك أي أسئلة أو أمثلة حول كيفية استخدامك لـ COALESCE في استعلامات Data Explorer، فلا تتردد في مشاركتها أدناه. :slightly_smiling_face:

5 إعجابات