SQL متوسط لـ ETL dev لانتقال مهندس البيانات
لا يوجد شيء يسمى الانضمام الذاتي في SQL. أستمع لي.
في كثير من الأحيان يحتاج محللو البيانات ومهندسو البيانات إلى العثور على أنماط غير واضحة في البيانات. ومع ذلك، يمكن إنشاء الرؤى وتحديد الأنماط باستخدام ممارسات SQL الشائعة، مثل الانضمام الذاتي.
غالبًا ما يكافح العديد من المبتدئين لفهم برنامج Self Join. إنهم يخلطون بين Self Join في SQL وأمر آخر مثل Inner أو Left Join. لكن الانضمام الذاتي ليس كلمة أساسية في SQL. ولا هو أمر في SQL.
الانضمام الذاتي يشبه تمامًا الانضمام العادي (داخلي/يسار/يمين/خارجي)، بين جدولين. ومع ذلك، في الصلة الذاتية، يكون الجدولان متماثلين ولكنهما يعملان كجداول مختلفة عبر الأسماء المستعارة الخاصة بهما.
غالبًا ما يُعتبر الانضمام الذاتي ممارسة سيئة في هندسة البيانات. يقولون أنه من الخطورة استخدامها. ولكن، هناك سيناريوهات عندما يكون استخدام الانضمام الذاتي عمليًا وأفضل طريقة لمعالجة المشكلة.
دعونا نرى بعض الأمثلة:
البيانات الهرمية:
تعتبر عمليات الانضمام الذاتي مفيدة في التعامل مع البيانات الهرمية. في المخطط التنظيمي، يمكننا ضم جدول إلى نفسه بناءً على العلاقات بين المدير والموظف للعثور على تقارير الموظفين ورؤساء الأقسام وما إلى ذلك.
لنقم بإنشاء بعض البيانات المجانية لاختبار ذلك.
create table employee
(
employee_id int,
employee_name varchar(10),
EmpSSN varchar(11),
manager_id int null,
city varchar(20)
);--Correct data
insert into employee values(1, 'Jack', '555-55-5555','','Kolkata');
insert into employee values (2, 'Joe', '555-56-5555',1,'Kolkata');
insert into employee values (3, 'Fred', '555-57-5555',2,'Dehli');
insert into employee values (4, 'Mike', '555-58-5555',2,'Kolkata');
insert into employee values (5, 'Cathy', '555-59-5555',2,'Dehli');
insert into employee values (6, 'Lisa', '555-70-5555',3,'Bangalore');
هنا يتم تخزين تفاصيل الموظف الخاص بالمؤسسة مع معرف المدير الخاص بهم. يمكننا استخدام الانضمام الذاتي لتحديد مدير جميع الموظفين المتميزين.
select emp.*,isnull(mgr.employee_name,'Boss') as managerName from employee emp
left join employee mgr on emp.manager_id = mgr.employee_id
هنا يقوم الاستعلام بإرجاع اسم المدير المقابل لكل موظف من خلال الانضمام إليه موظف الجدول مع نفسه على manager_id = member_id.
تحذير: لا تنس استخدام الاسم المستعار لـ موظف الجدول، للتمييز بين جزأين من الانضمام الذاتي. كما يجب استخدام عمود الصلة بشكل صحيح.
وبالمثل، يمكننا العثور على مستويات مختلفة من التسلسل الهرمي من خلال الانضمام بشكل متكرر إلى CTE باعتباره انضمامًا ذاتيًا إلى نفسه.
WITH
EmployeeHierarchy AS (
SELECT
employee_id, employee_name, manager_id, 0
AS
level
FROM
employee
WHERE
manager_id = 0
UNION ALL
SELECT
emp.employee_id, emp.employee_name, emp.manager_id, eh.level + 1
FROM
employee emp
JOIN
EmployeeHierarchy eh
ON
emp.manager_id = eh.employee_id
)
SELECT
employee_id, employee_name, level
FROM
EmployeeHierarchy;
المنتجات والفئات:
يمكن أن يرتبط هذا بالبيانات الهرمية فقط، ولكن هذه مجموعة فرعية محددة. يمكن أن تكون عمليات الانضمام الذاتي مفيدة للغاية لتحديد جميع مجموعات المنتجات والفئات والفئات الفرعية. في الصناعة التحويلية، يمكن أن يوفر ذلك مكونات ومكونات فرعية، وفي التجارة الإلكترونية يمكن استخدامه للحصول على منتجات أو فئات مماثلة.
فلنتعرف عليه من خلال مثال:
إنشاء جدول وإدراج بيانات وهمية:
create table bom (item_id int, parent_id int null,description varchar(50), quantity int)INSERT INTO bom (item_id, parent_id, description, quantity)
VALUES (1, NULL, 'Widget (Main Assembly)', 1),
(2, 1, 'Gear A', 2),
(3, 1, 'Spring B', 4),
(4, 2, 'Screw C (Small)', 10),
(5, 2, 'Screw C (Large)', 5),
(6, 3, 'Nut D', 1);
لقد أنشأنا جدولًا يحتوي على أعمدة item_id وparent_id والوصف والكمية. لقد قمنا أيضًا بإدراج بيانات نموذجية من خط تصنيع، حيث تكون “القطعة (التجميع الرئيسي)” هي المنتج الأصلي ويكون الترس والمسمار والجوز وما إلى ذلك عبارة عن منتجات فرعية.
يمكننا استخدام الانضمام الذاتي لتحديد العلاقة بين الوالدين والطفل، ويمكن للانضمام الذاتي العودي تحديد تسلسل المنتج الكامل.
دعنا نراجع هذا مع الاستعلام والنتائج:
WITH recursive_bom AS (
SELECT item_id, parent_id, description, quantity, cast(description as nvarchar(255)) AS full_path
FROM bom
WHERE parent_id IS NULL -- Starting point: Top-level items
UNION ALL
SELECT
b.item_id,
b.parent_id,
b.description,
b.quantity,
cast(CONCAT(rb.full_path, '.', b.description) as nvarchar(255)) AS full_path
FROM bom b
INNER JOIN recursive_bom rb ON b.parent_id = rb.item_id
)
SELECT item_id, description, quantity, full_path
FROM recursive_bom
ORDER BY full_path;
بيانات العينة والمخرجات
تجزئة المستخدم:
في تحليلات الأعمال والبيانات، هناك جانب مهم وهو تقسيم المستخدمين. غالبًا ما يتم تصنيف المستخدمين بناءً على سلوك الشراء الخاص بهم، وتكرار تفاعلهم مع الشركة وما إلى ذلك. ويمكن أن يكون الانضمام الذاتي طريقة رائعة لتحديد هذه الأنماط في بيانات المعاملة.
دعونا نفكر في المثال أدناه:
نحن بحاجة إلى تحديد العملاء العائدين خلال فترة زمنية محددة (7 أيام) لأعمال التجارة الإلكترونية. يمكن العثور على تحديات مماثلة على شبكة الإنترنت، مثال هنا.
لنقم بإنشاء جدول اختبار وإدراج بعض نماذج السجلات في الجدول.
الخدعة: يمكنك أن تطلب من ChatGpt إنشاء بيانات الاختبار حسب الحاجة.
إنشاء جدول وإدراج بيانات وهمية:
create table ecom_tran (
tranid int,
userid int,
created_date datetime,
itemname varchar(50)
)INSERT INTO ecom_tran (tranid, userid, created_date, itemname, price_paid)
VALUES
(1, 201, '2024-02-23 11:45:00', 'Running Shoes'),
(2, 202, '2024-02-24 10:00:00', 'Yoga Mat'),
(3, 203, '2024-02-26 14:10:00', 'Water Bottle'),
(4, 204, '2024-02-27 09:30:00', 'Gym Bag'),
(5, 205, '2024-02-28 12:00:00', 'Protein Powder'),
(6, 201, '2024-02-29 15:15:00', 'Phone Case'),
(7, 206, '2024-03-01 10:45:00', 'Webcam'),
(8, 202, '2024-03-02 16:30:00', 'Pen Drive'),
(9, 207, '2024-03-04 12:00:00', 'Powerbank'),
(10, 203, '2024-03-05 09:00:00', 'Monitor'),
(11, 101, '2024-03-06 11:00:00', 'Mouse'),
(12, 102, '2024-03-07 14:45:00', 'Speaker'),
(13, 103, '2024-03-08 10:10:00', 'Tablet'),
(14, 101, '2024-03-09 13:30:00', 'Headphones'),
(15, 104, '2024-03-10 17:00:00', 'Book'),
(16, 102, '2024-03-11 08:20:00', 'Coffee Maker'),
(17, 105, '2024-03-12 11:15:00', 'Smartwatch'),
(18, 101, '2024-03-13 15:45:00', 'Shirt'),
(19, 103, '2024-03-14 12:30:00', 'Laptop')
نهج الحل:
في نموذج الجدول الذي تم إنشاؤه، لدينا معرف المستخدم ومعرف المعاملة وعمود تاريخ الإنشاء ذات الصلة بالتحدي. نظرًا لأنه طُلب منا تحديد المستخدمين الذين أجروا عمليتي شراء على الأقل خلال فترة 7 أيام، يمكننا التفكير في النهج التالي:
- تحقق من عدد المعاملات المختلفة التي أجراها المستخدمون.
- قم بدمج كل معاملة مع نفسها لتحديد جميع أزواج المعاملات الممكنة بواسطة نفس المستخدم.
- احسب فرق التاريخ بين المجموعتين.
- يجب أن يكون فرق التاريخ > 0 و< 7. وسيضمن ذلك إرجاع السجلات التي تمت فيها المعاملات خلال 7 أيام فقط.
- يمكننا جمع معرفات المستخدمين المميزة لتحديد المستخدمين الذين لديهم معاملات عائدة في غضون 7 أيام.
هذه حالة استخدام كلاسيكية للنظر في الانضمام الذاتي إلى جانب الانضمام غير المتساوي.
SELECT a.userid,
a.tranid AS id1,
a.created_date AS created_at1,
b.tranid AS id2,
b.created_date AS created_at2,
mod(DATEDIFF(dd,a.created_date,b.created_date))
FROM ecom_tran a
JOIN ecom_tran b
ON a.userid=b.userid
AND a.tranid <> b.tranid
ORDER BY a.userid
يقوم الاستعلام أعلاه بإنشاء جميع مجموعات المعاملات التي أجراها نفس المستخدمين. لقد حققنا ذلك من خلال ضم ecom_tran إلى نفسه، بمساعدة الأسماء المستعارة، في عمود معرف المستخدم. تضمن هذه الصلة الداخلية إرجاع المعاملات الخاصة بالمستخدم نفسه فقط.
لكن الانضمام غير المتساوي قيد التشغيل أ.ترانيد <> ب.ترانيد وهذا يضمن عدم تكرار نفس المعاملات.
وقمنا أيضًا بحساب فرق التاريخ بين المعاملتين.
الآن، إذا قمنا فقط بتصفية تلك التي يكون فيها فرق التاريخ بين التواريخ التي تم إنشاؤها > 0 و< 7، فسيعطينا ذلك جميع المعاملات التي حدثت خلال 7 أيام بواسطة نفس المستخدم. يمكننا أخذ قيمة مميزة لعمود معرف المستخدم لتحديد المستخدمين الذين أجروا عمليات شراء مرتجعة خلال 7 أيام.
خاتمة:
أتمنى أن تكون قد حصلت على فهم عام وحدس حول كيفية أداء الانضمام الذاتي في SQL. على الرغم من أن الصلات الذاتية ليست سهلة الفهم والاستخدام، إلا أن هناك حالات استخدام محددة حيث لا غنى عنها.
لقد قمت بتغطية عدد قليل فقط من السيناريوهات المحتملة. ومع ذلك، فهذا يكفي ليمنحك الثقة أثناء مواجهة أي أسئلة في مقابلة SQL. حتى لو كان السؤال يحتاج إلى فهم بديهي لحل التحديات، فإن هذه المفاهيم ستساعد في تحديد النهج الذي يجب استخدامه.
