SQL intermedio per la transizione dallo sviluppo ETL al Data Engineer
Non esiste nulla chiamato Self join in SQL. Ascoltami.
Spesso gli analisti e gli ingegneri dei dati hanno bisogno di trovare modelli nei dati che non sono ovvi. Tuttavia, è possibile generare informazioni approfondite e identificare modelli utilizzando pratiche SQL comuni, come il Self Join.
Molti principianti spesso hanno difficoltà a comprendere il Self Join. Confondono il Self Join in SQL con un altro comando come Inner o Left Join. Ma Self Join non è una parola chiave in SQL. Né è un comando in SQL.
L'auto-unione è proprio come una normale unione (Interna/Sinistra/Destra/Esterna), tra due tabelle. Tuttavia, in un self-join, le due tabelle sono identiche ma agiscono come tabelle diverse tramite i rispettivi alias.
Il Self Join è spesso considerato una cattiva pratica nell'ingegneria dei dati. Dicono che sia rischioso da usare. Tuttavia, ci sono scenari in cui l'utilizzo di un self-join è pratico e rappresenta il modo migliore per affrontare il problema.
Vediamo alcuni esempi:
Dati gerarchici:
I self-join sono utili per lavorare con dati gerarchici. In un organigramma possiamo unire una tabella a se stessa in base alle relazioni manager-dipendente per trovare report dei dipendenti, capi dipartimento, ecc.
Generiamo alcuni dati gratuiti per testarlo.
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');
Qui i dettagli dei dipendenti di un'organizzazione vengono archiviati insieme al loro ID manager. Possiamo utilizzare Self join per identificare il manager di tutti i dipendenti distinti.
select emp.*,isnull(mgr.employee_name,'Boss') as managerName from employee emp
left join employee mgr on emp.manager_id = mgr.employee_id
Qui la query restituisce il nome del manager corrispondente a ciascun dipendente che si unisce allo stesso dipendente tabella con se stessa su manager_id = Employee_id.
ATTENZIONE: non dimenticare di utilizzare l'alias per il file dipendente tabella, per distinguere tra due parti del self join. Inoltre, la colonna join deve essere utilizzata correttamente.
Allo stesso modo, possiamo anche trovare i diversi livelli di gerarchia unendo ricorsivamente la CTE come un auto-unione con se stessa.
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;
Prodotti e Categorie:
Questo può essere correlato solo ai dati gerarchici, ma si tratta di un sottoinsieme specifico. I Self Join possono essere estremamente utili per identificare tutte le combinazioni di prodotti, categorie e sottocategorie. Nell'industria manifatturiera può fornire componenti e sottocomponenti, nell'e-commerce può essere utilizzato per ottenere prodotti o categorie simili.
Impariamolo attraverso un esempio:
Crea una tabella e inserisci dati fittizi:
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);
Abbiamo creato una tabella con colonne item_id, parent_id, descrizione e quantità. Abbiamo anche inserito dati di esempio da una linea di produzione, dove “Widget (assemblaggio principale)” è il prodotto principale e ingranaggio, vite, dado ecc. sono sottoprodotti.
Possiamo utilizzare l'autounione per identificare la relazione genitore-figlio e l'autounione ricorsiva può identificare l'intera sequenza del prodotto.
Esaminiamolo con la query e i risultati:
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;
Dati di esempio e output
Segmentazione degli utenti:
In Business and Data Analytics, un aspetto importante è la segmentazione degli utenti. Gli utenti vengono spesso classificati in base al loro comportamento di acquisto, alla frequenza di interazione con l'azienda, ecc. Un'autoiscrizione può essere un ottimo modo per identificare questi modelli nei dati delle transazioni.
Consideriamo l'esempio seguente:
Dobbiamo identificare i clienti ricorrenti entro un periodo di tempo specificato (7 giorni) per un'attività di e-commerce. Sfide simili possono essere trovate su Internet, ad esempio Qui.
Creiamo una tabella di test e inseriamo alcuni record di esempio nella tabella.
Trucco: puoi chiedere a ChatGpt di generare i dati di test secondo necessità.
Crea una tabella e inserisci dati fittizi:
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')
Approccio risolutivo:
Nella tabella di esempio creata, abbiamo la colonna userid, TransactionID e Created_date che sono rilevanti per la sfida. Poiché ci è stato chiesto di identificare gli utenti che hanno effettuato almeno 2 acquisti in un periodo di 7 giorni, possiamo considerare il seguente approccio:
- Controlla quante transazioni diverse hanno effettuato gli utenti.
- Combina ogni transazione con se stessa per identificare tutte le possibili coppie di transazioni dello stesso utente.
- Calcola la differenza di data tra le due combinazioni.
- La differenza di data deve essere > 0 e < 7. Ciò garantirà che vengano restituiti solo i record in cui le transazioni sono state effettuate entro 7 giorni.
- Possiamo raccogliere gli ID utente distinti per identificare gli utenti che hanno effettuato transazioni di ritorno entro 7 giorni.
Questo è un caso d'uso classico da considerare sia il self-join che il 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
La query precedente genera tutte le combinazioni di transazioni effettuate dagli stessi utenti. Abbiamo ottenuto questo risultato unendo ecom_tran a se stesso, con l'aiuto di alias, sulla colonna userid. Questo inner join garantisce che vengano restituite solo le transazioni dello stesso utente.
Tuttavia, il join non equi è attivo a.tranid <> b.tranid ciò garantisce che le stesse transazioni non vengano ripetute.
Abbiamo anche calcolato la differenza di data tra le due transazioni.
Ora, se filtriamo solo quelli in cui la differenza di data tra create_dates è > 0 e < 7, otterremo tutte le transazioni avvenute entro 7 giorni dallo stesso utente. Possiamo prendere un valore distinto della colonna userid per identificare semplicemente gli utenti che hanno effettuato acquisti di ritorno entro 7 giorni.
Conclusione:
Spero che tu abbia una comprensione e un'intuizione generali su come funziona l'auto-join in SQL. Sebbene i self-join non siano molto intuitivi da comprendere e utilizzare, esistono casi d'uso specifici in cui sono indispensabili.
Ho coperto solo alcuni dei possibili scenari. Tuttavia, questo è sufficiente per darti sicurezza mentre affronti qualsiasi domanda di intervista SQL. Anche se la domanda potrebbe richiedere una comprensione intuitiva per risolvere le sfide, questi concetti aiuteranno a identificare quale approccio utilizzare.
Fonte: towardsdatascience.com
