SQL متوسط تا انتقال از توسعه دهنده ETL به مهندس داده
چیزی به نام Self join در SQL وجود ندارد. به من گوش کن
اغلب، تحلیلگران داده و مهندسان داده نیاز به یافتن الگوهایی در داده ها دارند که واضح نیستند. با این حال، بینش ها را می توان با استفاده از شیوه های رایج SQL مانند Self Join ایجاد کرد و الگوها را شناسایی کرد.
بسیاری از مبتدیان اغلب برای درک Self Join تلاش می کنند. آنها Self Join را در SQL به عنوان دستور دیگری مانند Inner یا Left Join اشتباه می گیرند. اما Self Join یک کلمه کلیدی در SQL نیست. این یک دستور در SQL نیست.
یک اتصال خود درست مانند یک اتصال معمولی (داخلی/چپ/راست/خارجی) بین دو جدول است. با این حال، در یک self-join، دو جدول یکسان هستند، اما از طریق نام مستعار خود به عنوان جداول متفاوت عمل می کنند.
خود پیوستن اغلب یک عمل بد در مهندسی داده در نظر گرفته می شود. آنها می گویند استفاده از آن خطرناک است. اما سناریوهایی وجود دارد که در آنها استفاده از self-join عملی است و بهترین راه برای رسیدگی به مشکل است.
بیایید چند نمونه را ببینیم:
داده های سلسله مراتبی:
Self-join برای کار با داده های سلسله مراتبی مفید است. در نمودار سازمانی، می‌توانیم جدولی را بر اساس روابط مدیر و کارمند به خود بپیوندیم تا گزارش‌های کارمندان، روسای بخش و غیره را بیابیم.
بیایید چند داده رایگان برای آزمایش آن تولید کنیم.
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');
اینجا جایی است که مشخصات کارکنان یک سازمان به همراه شناسه مدیر آنها ذخیره می شود. ما می توانیم از Self join برای شناسایی مدیر همه کارمندان استفاده کنیم.
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 = staff_id .
هشدار: حتما از نام مستعار برای استفاده کنید کارمند جدول برای تمایز بین دو بخش از خود پیوستن. همچنین ستون join باید به درستی استفاده شود.
به طور مشابه، ما حتی می‌توانیم سطوح مختلف سلسله مراتب را با پیوستن بازگشتی به 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')
رویکرد به راه حل:
در جدول مثالی که ایجاد کردیم، یک شناسه کاربری، یک شناسه تراکنش و یک ستون create_date داریم که با چالش مرتبط هستند. از آنجایی که از ما خواسته شد کاربرانی را که حداقل 2 خرید در یک دوره 7 روزه انجام داده اند شناسایی کنیم، می توانیم رویکرد زیر را در نظر بگیریم:
- بررسی کنید که کاربران چند تراکنش مختلف انجام داده اند.
- هر تراکنش را با خودش ترکیب کنید تا تمام جفت تراکنش های ممکن از یک کاربر شناسایی شود.
- تفاوت تاریخ بین دو ترکیب را محاسبه کنید.
- تفاوت تاریخ باید > 0 و < 7 باشد. این اطمینان را ایجاد می کند که فقط سوابق در جایی که تراکنش ها در عرض 7 روز انجام شده است بازگردانده می شوند.
- ممکن است شناسه‌های کاربر فردی را جمع‌آوری کنیم تا کاربرانی را که تراکنش‌های خود را در عرض 7 روز برگردانده‌اند شناسایی کنیم.
این یک مورد استفاده کلاسیک برای در نظر گرفتن Self-Join همراه با non-equi join است.
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 به خودش، با استفاده از نام مستعار، در ستون userid به این مهم دست یافتیم. این اتصال داخلی تضمین می کند که فقط تراکنش های همان کاربر برگردانده می شوند.
با این حال، اتصالات غیر معادل گنجانده شده است a.tranid <> b.tranid این تضمین می کند که معاملات مشابه تکرار نمی شوند.
ما همچنین تفاوت تاریخ بین دو معامله را محاسبه کردیم.
حال، اگر ما به سادگی آنهایی را که تفاوت تاریخ بین create_dates > 0 و < 7 است را فیلتر کنیم، تمام تراکنش‌های انجام شده در مدت 7 روز توسط همان کاربر به ما نشان می‌دهد. می‌توانیم مقدار جداگانه‌ای از ستون userid بگیریم تا فقط کاربرانی را شناسایی کنیم که در عرض 7 روز خریدهای برگشتی انجام داده‌اند.
نتیجه:
امیدوارم درک کلی و شهودی از نحوه انجام self-join ها در SQL داشته باشید. اگرچه خود پیوستن ها برای درک و استفاده چندان شهودی نیستند، موارد استفاده خاصی وجود دارد که در آنها ضروری است.
من فقط چند مورد از سناریوهای ممکن را پوشش داده ام. با این حال، این کافی است تا هنگام مواجهه با هر سؤال مصاحبه SQL به شما اطمینان دهد. حتی اگر سوال ممکن است نیاز به درک شهودی برای حل چالش‌ها داشته باشد، این مفاهیم به تعیین رویکردی برای استفاده کمک می‌کنند.
