זנבות ארוכים
הכוונה בזנבות ארוכים היא למספר קטן של דגימות שמתרחשות מספר רב של פעמים. כאשר אנחנו מציגים אותם בגרף, הם נפרסים לרצועה דקה מימין למרכז, בצורה שמזכירה זנב. זנבות ארוכים מסמנים את קיומם של קצוות עם התנהגות לא רגילה שעשויה לעניין אותנו. 
בהקשר של הוצאות להורג בטקסס, משמעות הזנב הארוך היא מספר קטן של מחוזות שמוכרים ככאלה שביצעו מספר גדול של הוצאות להורג.
בואו נמצא את אחוז ההוצאות להורג בכל מחוז כך שנוכל לאסוף את אלו שבזנב.
כפי שילך ויתברר, הצורה של הטבלאות מספרת לנו הרבה על הפעולות שאנחנו נדרשים לבצע (בדומה לניתוח מימדיי בפיסיקה). במקרה הזה, אנחנו יכולים להבחין שהשיטחות שכיסינו עד כה לא מתאימות: הפרק על ביזלי עסק בשורות בודדות של נתונים, אבל זה ברור שאנחנו צריכים סוג של אגירה כדי למצוא מידע ברמת המחוז. הפרק שעסק בטענות לחפות מפשע לימד אותנו פונקציות אגירה, אבל הפונקציות הללו מסכמם סט נתוניםי שלהם לתוך שורה, בזמן שמה שאנחנו באמת צריכים הוא שורה אחת מסכמת לכל מחוז.
בלוק ה-GROUP BY
כאן נכנס לתמונה בלוק ה-GROUP BY. הוא מאפשר לנו לפצל את סט הנתונים ולהפעיל פונקציות אגירה (aggregate functions) על כל קבוצה, ולתוצאה עם שורה עבור כל קבוצה. המבנה הבסיסי ביותר של הבלוק הוא GROUP BY <column>, <column>, ... והוא מופיע אחרי בלוק ה-WHERE.
אם אתם זוכרים את השאילתה המוזרה, פעמוני אזהרה אמורים לצלצל בראשכם. האם לא למדנו בדיוק עכשיו לא לערב בין טורים עם אגירה וטורים ללא אגירה? ההבדל כאן הוא שטורים של קבוצות הם הטורים היחידים שמותר להם להיות ללא אגירה (non-aggregate). אחרי הכל, לכל השורות באותה הקבוצה חיייבם להיות אותם הערכים בטורים אלו, כך שלא יכול להיות שיוחזרו ערכים שונים עבורן.
יתכן שגם שמתם לב לשימוש שלנו ב-AS. זה מה שאנחנו מכנים “הענקת כינוי” (aliasing). בבלוק ה-SELECT, <expression> AS <alias> מספק כינוי, בו ניתן להשתמש מאוחר יותר בשאילתה, מה שחוסך מאיתנו את הצורך לכתוב את אותו ביטוי ארוך פעם נוספת, ויכול להבהיר את הכוונה של הביטוי.
בלוק ה-Having
התרגיל הבא מדגים שסינון באמצעות בלוק WHERE מתרחש לפני הסידור בקבוצות והאגירה. זה מתבטא בסדר של הפקודה. אחרי הכל, בלוק ה-WHERE תמיד מקדים את בלוק ה-GROUP BY.
זה בסדר גמור, אבל מה קורה אם אנחנו רוצים לסנן את התוצאה של החיבור לקבוצות והאגירה? אנחנו הרי לא יכולםי לקפוץ קדימה לתוך העתיד ולמשוך משם את המידע. כדי לפתור בעיות שכאלה, אנחנו משתמשים ב-HAVING.
county) בבלוק ה-SELECT.”
data-hint="הטורים שמקובצים בקבוצות לא חייבים להיות בבלוק ה-SELECT."
data-value="missing_gp_col"
data-correct="true"></sql-quiz-option>
שאילתות מקוננות (nested queries)
עכשיו, יתכן ואתם שואלים, זה לא היינו כבר מסיימים את זה לו יכולנו פשוט להריץ משהו כזה?
SELECT
county,
PERCENT_COUNT(*)
FROM executions
GROUP BY county
אחוזים הם מדד כל כך מקובל —יכול להיות שיש כבר פונקציה שמחשבת אותם? למרבה הצער, לא, ואולי מסיבה טובה: פונקציה שכזו היתה צריכה לאגור את בתוך כל אחת מהקבוצות וגם לאורך סט הנתונים כולו כדי להגיע למונה (numerator) ולמכנה (demoniator) על מנת לחשב את האחוזים. אבל לכל אחת מהשאילתות יש או אין בלוק GROUP BY. אז מה שאנחנו באמת צריכים זה שתי שאילתות נפרדות, אחת שתאגור ותחשב בעזרת GROUP BY ואחרת שתאתגור ותמנה ללא בלוק ה-GROUP BY. אז נוכל לאחד ביניהן בעזרת טכניקה שנקראת “קינון”, או “יצירת קינים” (nesting).
הנה דוגמה לאיך עובדת שיטת הקינים. הסוגריים חשובות כדי לסמן את הגבול בין השאילתה הפנימית והשאילתה החיצונית:
כדאי לחזור ולהדגיש, הכנסה של שאילתה לתוך קן הכרחית במקרה הזה מפני שבפסקת ה-WHERE, בזמן שהמחשב בודק כל שורה כדי להחליט אם עמודת ההצהרה האחרונה שלה היא באורך הנכון, הוא אינו יכול להסתכל החוצה ולברר מה האורך המקסימלי עבור כל סט הנתונים אנחנו צריכים למצוא דרך למצוא את האורך המקסימלי בנפרד ולהזין אותו לתוך הפסקה. עכשיו, בואו ניישם את אותה שיטה כדי למצוא את אחוז ההוצאות להורג בכל אחד מהמחוזות.
בשקט הגנבתי פנימה בלוק ‘ORDER BY’. המבנה שלו הוא ORDER BY <column>, <column>, ... וניתן לשנות אותו באמצעות הוספת DESC אם רוצים שהתוצאות יהיו מסודרות בסדר יורד ולא בסדר עולה, שהוא ברירת המחדל.
מחוז האריס (Harris)
האם זה מפתיע שמחוז האריס (Harris, Houston) דאלס (Dallas), בקסר (Bexar) וטרנט (Tarrant) מונים כ-50% מסך ההוצאות להורג בטקסס? אולי זה מפתיע, במיוחד אם נקודת ההנחה שלנו היא שהוצאו להורג אמורות להתחלק בצורה שווה בין מחוזות. אבל הערכה ראשונית מוצלחת יותר תהיה שההוצאות להורג מתחלקות בין המחוזות על פי יחסיות פזור האוכלוסיה. מפקד האוכלוסין של טקסס בשנת 2010 מראה שבארבעת המחוזות היו יותר מ-19 מליון תושבים, היוו 40% מהאוכלוסיה של טקסס (25.1 מליון איש). הנתון הזה הופך את הממצא לקצת פחות מפתיע.
אבל אם נתמקד בזנב הזה נבין שמחוז האריס (Harris) אחראי למרבית ההפרש. במחוז מתגוררת רק 16.4% מן האוכלוסיה, אבל מתבצעות בו 23.1% מההוצאות להורג. זה כמעט 50% יותר מאשר השיעור שניתן היה לצפות לו.
מחקרים רבים בחנו מדוע מחוז האריס (Harris) כה רווי בהוצאות להורג, והצביעו על הגורמים הבאים:
-
התביעות נהנו מארגון ומימון, בעוד ההגנות המשפטיות מומנו על ידי בית המשפטי עם בתמריצים נמוכים. (מקור, ראו עמ’ 49).
-
התובע המחוזי, שכיהן תקופה ארוכה, היה נחוש ונלהב בנוגע לעונש המוות.
-
שופטים בטקסס עומדים לבחירות והאוכלוסיה תמכה בעונש מוות. (מקור).
-
מערכת האיזונים והבלמים במערכת המשפטית במחוז האריס (Harris) לא עבדה. (מקור, ראו עמ’ 929).
סיכום
בפרק זה למדנו כיצד לבצע אגירה בתוך קבוצות ואיך להיעזר בקינון (nesting) כדי להשתמש בפלט של שאילתה פנימית במסגרת שאילתה חיצונית. לטכניקות האלו מאפשרות לנו לחשב אחוזים.
MapReduce
תוספת מעניינת היא שלמעשה כרגע למדנו לבצע MapReduce ב-SQL.
MapReduce היא פרדיגמה תכנותית מפורסמת שרואה את פעולות החישוב כמתרחשות בצעדים של "מיפוי" (map) ו"צמצום" (reduce). תוכלו ללמוד כאן על MapReduce.
הפרק על ביזלי עסק כולו במיפוי (mappping) מפני שהוא הראה לנו כיצד למפות פעולות שונות על כל השורות. למשל, SELECT LENGTH(last_statement) FROM executions ממפה את פונקצית האורך (LENGTH) על פני כל השורות. הפרק הזה הראה לנו כיצד לצמצם (Reduce) חלק מקבוצות הנתונים בעזרת פונקציות אוגרות (aggregation functions); והפרק הקודם טענות לחפות מפשע היה מקרה ייחודיי שבו כל הטבלה שימשה כקבוצה אחת.
בפרק הבא נלמד על JOIN אשר יאפשר לנו לעבוד עם כמה טבלאות.