הפוגות

הגרף הבא מציג את מספר ההוצאות להורג על פני ציר הזמן. שימו לב שהיו כמה פרקי זמן בהם לא בוצעו הוצאות להורג. המטרה שלנו היא להבין מתיי בדיוק היו ההפסקות הללו ומה גרם להן.

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

מחשבות על חיבורים (JOINs)

אף אחת מהטכניקות שלמדנו עד עכשיו לא מתאימות כאן. הטבלה הרצויה שלנו תהיה באותו אורך של טבלת ה-executions המקוריתי, ואנחנו יכולים ליצור אגירות (aggregations) שיוצרים טבלאות קטנות יותר. הפרק על בזלי רק לימד אותנו לבצע פעולות ברמת השורה, שמגבילות אותנו לעבוד רק עם מידע שכבר נמצא בשורות. התאריך של ההוצאה הקודמת להורג נמצא מחוץ לשורה, ולכן עלנו להשתמש ב-JOIN כדי לצרף את המידע הנוסף.

בואו נניח שהמידע הנוסף שאנחנו רוצים נמצא בטבלה שנקראת previous, ובה שני טורים (ex_number, lastex_date). נהיה מסוגלים להריץ את השאילתה הבא כדי להשלים את המשימה שלנו:

SELECT
  last_ex_date AS start,
  ex_date AS end,
   ex_date - last_ex_date AS day_difference
FROM executions
JOIN previous
  ON executions.ex_number = previous.ex_number
ORDER BY day_difference DESC
LIMIT 10

בלוק ה-JOIN הוא המיקוד של הפרק הזה. במקום לראות בו שורה נפרדת זה יותר יעיל לפעמים לראות בה משהו כזה: זה מדגיש איך JOIN יוצר טבלה מאוחדת גדולה שאז מוזנת לתוך בלוק ה-FROM כמו כל טבלה אחרת.

סוגים של חיבורים (Joins)

בלוק ה-JOIN מתממשים בצורת <table1> JOIN <table2> ON <clause>. הפסקה עובדת בדומה לWHERE <clause>. כלומר, זוהי הצהרה שמוערכת כאמת (true) או שקר (false), ובכל פעם ששורה מהטבלה הראשונה ושורה אחרת מהטבלה השניה עומדות עם הפסקה ומוערכות כביטוי אמת, שתי השורות מתחברות.

אבל מה קורה לשורות שלא מתחברות? במקרה הזה לטבלת ה-previous לא התייה שורה להוצאה להורג מספר 1, משום שלא היו הוצאות להורג קודמות

ברירת המחדל של הפקודה JOIN היא היא לבצע מה שנקרא “חיבור פנימי” (“inner join”), במסגרתו שורות ללא התאמות מוסרות.

כדי לשמור את כל השורות שבטבלה השמאלית, נשתמש ב-LEFT JOIN במקום ה-JOIN הרגיל. החלקם הריקים של השורה נשארים ללא שינוי, מה שורה שהם יוערכו כ-to NULL.

החיבור RIGHT JOIN יכול לשמש כדי לשמר שורות לא תואמות בטבלה הימנית ו-OUTER JOIN יכול לשמש כדי לשמר שורות ללא התאמה משתי הטבלאות.

האבחנה האחרונה היא התמודדות עם כמה התאמות. נניח שיש לנו טבלאת duplicated_previous שמכילה שני עותקים של כל שורה בטבלת ה-previous. כל שורה של executions עכשיו תואמת שתי שורות ב-duplicated_previous.

החיבור יוצר מספיק שורות של executions כך שכל שורה מתאיימה של duplicated_previous מקבלת שותף משלה. בדרך זו חיבורים יכולים ליצור טבלאות ארוכות מהרכיבים שמרכיבים אותן

תאריכים

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

   ex_date - last_ex_date AS day_difference

הנחנו כאן הנחה משמעותית לפייה ניתן לבצע פעולת חיסור בין שני תאריכים. אבל דמייינו שאתם מחשב שמקבל שורה שכזו. האם תחזירו את מספר הימים בין התארכםי? מדוע לא את מספר השעות, או מספר השניות? כדי לסבך את העניינים, ל-SQLite אין ממש סוגי נתונים של תאריך או שעה (להבדיל מכמה צורות ודיאלקטים אחרים של SQL), כך שהטורים ex_date ו-last_ex_date יראו לכם כמו מחרוזות טקסט רגילות. זה כאילו אתם מתבקשים לבצע את הפעולה hello-world. מה המשמעות של זה בכלל?

למרבה המזל, SQLite כוללת כמה פונקציות שאומרות למחשב: “היי, מחרוזות הטקסט הללו שאני מעביר לך מכילות תאריכים ושעות. תתנהג איתן כמו שהיית מתנהג עם תאריך”.

חיבורים עצמיים (Self Joins)

עם מה שלמדנו על תאריכים, אנחנו ייכולים לתקן את תבנית השאילתה שלנו:

SELECT
  last_ex_date AS start,
  ex_date AS end,
  JULIANDAY(ex_date) - JULIANDAY(last_ex_date)
    AS day_difference
FROM executions
JOIN previous
  ON executions.ex_number = previous.ex_number
ORDER BY day_difference DESC
LIMIT 5

הצעד הבא הוא לבנות את טבלת ה-previous.

כעת אנחנו יכולים להניח (nest) את השאילתה הזו בתוך התבנית שלמעלה:

previous נובע מתוך executions, כך שאנחנו למעשה מחברים את טבלת ה-executions לעצמה. זה נקרא “חיבור עצמי” (“self join”), טכניקה רבת עוצמה שמאפשרת לשורות לגשת לנתונים מחלקים אחרים של אותה הטבלה.

יצרנו את טבלת ה-previous כדי להבהיר את המטרה שהטכניקה הזו משרתת. אבל אנחנו יכולים למעשה לכתוב את השאילתה הזו בצורה אלגנטית יותר, בכך שנחבר את טבלת ה-executions ישירות לעצמה.

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

הפוגה 1 התרחשה בלש האתגורים החוקייים ל-Antiterrorism and Effective Death Penalty Act of 1996 שנוצרו כתגובה למתקפה על מרכז הסחר העולמי ב-1993 והפיגועים באוקלהומה סיטי ב-1995. החקיקה הגבילה את הליכי התביעה כדי להפוך את עונש המוות לאפקטיבי יותר במיוחד למקרי טרור(מקור).

הפוגה 2 נגרמה בשל השהייה שנקבעה על ידי בית המשפט העליון בזמן הדיונים בתיק Baze v. Rees, בו נבחנה השאלה האם זריקה קטלנית מפירה את התיקון השמיני לחוקה, שאוסר “ענישה אכזרית ולא רגילה”. זה השפעי על הוצאות להורג בכל ארצות הברית, מפני שרוב המדינות השתמשו בתערובת של חומרים זהה לזו שבקנטאקי (Kentucky). בית המשפט העליון אשרר בסופו של דבר את החלטת בית המשפט של קנטאקי וההוצאות להורג בטקסס נמשכו כמה חודש לאחר מכן.

סיכום

הרעיון הכלל מאחורי ה-JOINs היה ליצור טבלה משופרת משום שהטבלה המקורית לא הכילה את המידע שנדרש. זוהי גישה יעילה בגלל שהיא משחררת אותנו מהמגבלות של הטבלה היחידה ומאפשר לנו לאחד מספר טבלאות בדרכים מורכבות. ראינו גם שעם המורככבות הזו יש חשיבות לניהול קפדני. מתן שמות חלופיים (aliasing) לטבלאות, שינוי שמות טורם והגדרות נכונות של פסקאות JOIN ON הן טכניקות שיעזרו לנו לשמר את הסדר.