פרק 9: קיבוץ נתונים Group By ב- שפת SQL

המדריך נכתב על ידי יובל מרנין.
למידע על שרותי מנטורינג של דאטה אנליסט לחצו כאן.

בפרק זה נלמד על הוראת Group by והוראת Having בשפת SQL.

ב- שפת SQL פונקציות אגרגטיביות הופכות את כל השאילתה לאגרגטיבית. אם נרצה לקבץ את הטבלה האגרגטיבית בעזרת אחד השדות נשתמש בהוראה group by.

לדוגמה:

select

       seller_id,

       count(order_id)  as order_items, —  count of ordered items

       count(distinct order_id)  as orders, —  count of orders

       count(order_id)::float / count(distinct order_id)::float  as avg_items_for_orders, — average number of orders

       count(distinct seller_id ) as selers, — number of unique orders

       sum(price) as sum_of_orders, —  sum of orders

       avg(price) as avg_item_price, — average item price

       sum(case when shipping_limit_date::date='2017-09-19' then 1  else 0 end ) as sum_of_orders_09_19,  — number of items ordered in 19 sep 2019   max(price) as max_amt -–- maximum price item price

from

       order_items

Group by

       seller_id

בדוגמה הנ"ל לקחנו את את השאילתה מהפרק הקודם וחילקנו אותה לפי מוכרים בעזרת שדה seller_id. שדה זה גם מופיע לאתר הוראת ה – group by.

הערות לכתיבת שאילתה אגרגטיבית:

• בראש השאילתה רצוי שיופיע השדה שלפיו מקבצים את הטבלה (seller_id) כדי שבפלט נדע למה הערכים האגרגרטיבים מתייחסים.
• בעת אגרגציה ניתן להשתמש באזור ה- select  רק בשדה שעושים לפיו אגרגציה או בפונקציות אגרגטיביות
• ניתן גם לבצע אגרגציה גם לפי מספר שדות. למשל:

select

       seller_id,

       shipping_limit_date::date as shipping_limit_date,

       count(order_id)  as order_items –- number of items ordered

from

       order_items

Group by

       seller_id,

       shipping_limit_date::date

שיטת כתיבה נוספת לרשום את בשדות שלפיהם מבצעים את ה-group by  היא לפי מספר העמודה של שהשדה מופיע באזור ה- select  (שימו לב שעלולים להיות דאטה ביסים שלא תומכים באופן כתיבה זה):

select

       seller_id,

       shipping_limit_date::date as shipping_limit_date,

       count(order_id)  as order_items — number of items ordered

from

       order_items

Group by

       1,2

order by

1,2

סינון על נתונים אגרגטיבים בשאילתה בעזרת Having

בשפת SQL ישנה אפשרות לבצע סינון על שדות לאחר התוצאה של האגרגציה.

סינון כזה מבוצע בעזרת הוראת having.

select

       seller_id,

       count(order_id)  as order_items — number of items ordered

from

       order_items

Group by

       seller_id

having

       count(order_id)>100

בשאילתה הזאת קיבלנו את המוכרים שמכרו יותר מ 100 פריטים.

שימו לב שיש לכתוב את הפונקציה שלפיה אנחנו מסננים בהוראת having.

ההבדל בין Where ל- Having

בהוראת where  נשתמש לפני ביצוע האגרגציה – אם למשל נרצה לסנן את לקוחות מסוימים שאנחנו לא רוצים להכליל בשאילתה, ואילו בהוראת having נשתמש כאשר נרצה לסנן שורות מהתוצאה של  המשתנים האגרגטיבים,

ניתן בשאילתה אחת להשתמש גם בהוראת having  וגם ב- where.

כאשר משתמשים בשני ההוראות, הוראת ה –  whereתופיע לפני הוראות ה– group by  וה –  having. זאת כיוון שהדטה-בייס מסנן קודם השורות בטבלה בעזרת where ורק אח"כ מבצע את אגרגציה.

select

       seller_id,

       count(order_id)  as order_items — number of items ordered

from

       order_items

where

       shipping_limit_date::date='2017-09-19'

Group by

       seller_id

having

       count(order_id)>3