המדריך נכתב על ידי יובל מרנין.
לחברות המעוניינות לשפר את משימות ניתוח הנתונים על ידי מנטור דאטה אנליסט מנוסה, ניתן לפנות אליי [email protected]
שאילתות CTE
בשפת SQL ישנה אפשרות לכתוב את תתי השאילתות מחוץ לשאילתה הראשית בעזרת פעולה שנקראת – CTE (ראשי התיבות של Common Table Expressions).
בכדי לכתוב שאילתות כאלה יש להיעזר בהוראת With כמו בדוגמה הבאה:
—start CTE order_for_customer —
with order_for_customer as
(
select
customer_id
,count(order_id) as orders
from
e_commerce.orders
group by
1
)
—end CTE order_for_customer —
— start query —
select
*
from
order_for_customer
— end query —
הסבר:
בשאילתה הנ"ל הגדרתי תת שאילתה מסוג CTE בשם order_for_customer.
בתחילת השאילתה הכרזתי על התת שאילתה באמצעות הוראת with ולאחר מכן יצרתי שאילתה ראשית הקוראת ל- CTE בהוראת ה- from.
את השאילתה הנ"ל היינו יכולים ליצור גם ללא CTE באופן הבא:
select
*
from
(
select
customer_id
,count(order_id) as orders
from
e_commerce.orders
group by
1
) as order_for_customer
שאילתה רקורסיביות בשפת SQL
שאילתות CTE מאפשרות ליצור שאילתות רקורסיות.
מהי רקורסיה?
רקורסיה היא תהליך אשר במהלכו יש קריאה לתהליך עצמו. תופעה זאת יכולה להתרחש במציאות למשל, כאשר מצלמת וידאו משדרת את הצילום למסך אבל מצלמת את המסך שאליו היא משדרת. לחצו כאן להדגמה.
מהי שאילתה רקורסיבית?
שאילתה רקורסיבית היא שאילתה הקוראת לשאילתה עצמה מתוך השאילתה.
מתי משתמשים בשאילתה רקורסיבית?
בשאילתה רקורסיבית אפשר להשתמש כאשר רוצים להציג מבנה היררכי.
לדוגמה: בטבלה הבאה מופיעים מוכרים כאשר לכל מוכר יש מפקח (Supervisor_id) שהוא בעצמו אחד המוכרים האחרים בטבלה.
לדוגמה – בשורה 7 של הטבלה מופיע מוכר מס' 7 (Mcauley Reid) כאשר המפקח שלו הוא מוכר מס' 2 (Brandan Avery).
אם נרצה לשלוף מהטבלה הזאת את כל המוכרים והתתי מוכרים שמוכר מס' 2 (Brandan Avery) מפקח עליהם נצטרך לשלוף את המוכרים שמספרם 3,6,7 אבל גם את המוכרים שהם תחתם כלומר, גם את המוכרים שמספרם 8 ו 9 כיוון שהם מפוקחים ע"י מוכר מס' 3 (Cain Rodrigues) שגם הוא מפקח ע"י מוכר מספר 2.
שליפה מסוג זה היא רקורסיבית כיוון שהיא מתייחסת לכל תתי המפקחים בהיררכיה של מוכר מס' 2.
את השליפה נבצע באופן הבא:
—start CTE sellers_hierarchy —
with recursive sellers_hierarchy as
(
— Anchor Clause —
select
seller_id,
seller_name,
supervisor_id
from
sellers
where
seller_id = 2
union all
— Recursive Clause —
select
sellers.seller_id,
sellers.seller_name,
sellers.supervisor_id
from
sellers
inner join
sellers_hierarchy
on sellers_hierarchy.seller_id=sellers.supervisor_id
)
—end CTE sellers_hierarchy —
— start query —
select
*
from
sellers_hierarchy
— end query —
פלט:
הסבר לשליפה:
בשליפת CTE מסוג רקורסיה חייבים להגדיר לדאטה-בייס שמדובר ברקורסיה – with recursive.
לאחר מכן נגדיר את בסיס הרקורסיה (ancor) שבו נרשום את העמודות שנרצה לקבל בפלט ומהיכן להתחיל את ההיררכיה – במקרה שלנו מוכר מס' 2.
בעזרת union ניצור את החלק הרקורסיבי בשאילתה: נשלוף את הטבלה המקורית ואז נחבר אותה בחיבור מסוג inner join עם ה- CTE שהגדרנו (החיבור הזה הוא למעשה הרקורסיה).
התנאי בחיבור יהיה שמספר המוכר בקריאה ל- CTE יהיה זהה למספר המפקח של אותו מוכר בטבלת הבסיס. כיוון שהתהליך הוא רקורסיבי, מספר המוכר ישתנה בכל קריאה חדשה לטבלה הרקורסיבית.
הערה – חשוב לשים לב שחיבור בתוך רקורסיה יבוצע רק באמצעות inner join כיוון שאנחנו רוצים שהרקורסיה תעצור כאשר נגמרת ההיררכיה.
תרגול שפת SQL
באתר strata scratch תמצאו תרגילים רבים עם שאלות עסקיות מורכבות, וממשק המאפשר כתיבה והרצה של שאילתות כך שתוכלו לבדוק האם הפתרון שלכם נכון. באתר יש גם אפשרות לתרגל תרגילים ב- Python.
התרגול באתר הוא ללא עלות, אך אפשר להירשם כדי לקבל גישה לתרגילים חדשים ופתרונות לכל התרגילים (גילוי נאות – רישום לאתר דרך הקישור מתגמל אותי בעמלה קטנה, וגולשים המגיעים דרך הקישור יכולים להשתמש בקוד קופון yuval20 על מנת לקבל 20% הנחה).
נכתב על ידי יובל מרנין,
לשרותי ניתוח נתונים ויצירת דוחות BI ניתן לפנות אליי ב- לינקדאין, פייסבוק או במייל: [email protected]
*** לפודקאסט של הבלוג לחצו כאן ***
