---
layout: he_tutorial
title:  טענות לחפות מפשע
dbFile: data/tx_deathrow_small.db
---

<a name="possible_innocence"></a>
## חפות מפשע אפשרית
המתנגדים לעונש המוות טענו שהסכנה שבהוצאה להורג בטעות של אדם חף מפשע היא מחיר בלתי נסבל. בפרק זה ננסה להעריך כמה אנשים חפים מפשע, אולי, הוצאו להורג.

הבעיה העיקרית שטענה לחפות משפע, אפילו אם היא מושמעת על סף המוות, אינה מהווה ראייה לכך. יתר על כן, גם אם הנדונ/ה למוות דוברי אמת, יש פרשנויות רבות לחפות משפע: יכול להיות שהנדונ/ה למוות הואשמו ברצח שני אנשים אבל חפים מפשע רק ביחס לאחד ממקרי הרצח; ייתכן שהרג את אחד המשקיפים ולא את השוטר. אלו אינן סתם התפלפלויות: בטקסס רצח לכשעצמו אינו מוביל לעונש מוות. הנדון למוות צריך לבצע מה שמכונה [capital crime](https://en.wikipedia.org/wiki/Capital_punishment_in_Texas#Capital_crimes), כמו הרג של קצין בטיחות הציבור או כמה אנשים. לכן, הנדונ/ה למוות יכולים להיות חפים משפע במובן המשפטי הצר, למרות שלוא דווקא על פי הסטנדרטים המוסריים המקובלים.

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

<br>
<a name="aggregations"></a>
## פונקציות אגירה (aggregation functions)
יש שני מספרים שדרושם לנו כדי לחשב את היחס:


&nbsp;&nbsp;**המונה**: מספר ההוצאות להורג בהן הושמעה טענה לחפות מפשע.

&nbsp;&nbsp;**המכנה**:מספר ההוצאות להורג הכולל.

עד עכשיו, כל שורה של הפלט הגיעה משורה בודדת של הקלט. עם זאת, כאן יש לנו שבר שבו גם המונה (numerator) וגם המכנה (denominator) מתבססים על מידע מכמה שורות של הקלט. זה אומר שאנחנו צריכים להשתמש בפונקצוית אגירה (aggregation functions) מפני שהן <i>לוקחות מספר שורות של מידע ומחברות אותן לתוך מספר אחד.</i>

<br>
<a name="count"></a>
## הפונקציה COUNT
`COUNT` היא כנראה פונקציית האגירה הכי נפוצה בשימושה. כפי שהשם שלה מרמז, היא סופרת דברים! לדוגמה, <code class='codeblock' dir="ltr">COUNT(&lt;שם_הטור&gt;)</code> תחזיר את מספר השורות ללא null בטור.

<sql-exercise
  data-question="ערכו את השאילתה כדי למצוא כמה נדונים למוות השמיעו הצהרה אחרונה לפנ ההוצאה להורג."
  data-comment="אנחנו יכולים להשתמש כאן ב-<code>COUNT</code> מפני ש-<code>NULL</code> יופיע בשורות עבורן אין הצהרה שכזו."
  data-default-text="SELECT COUNT(first_name) FROM executions"
  data-solution="SELECT COUNT(last_statement) FROM executions"></sql-exercise>

כמו שאתם יכולים לראות, פונקציית ה-`COUNT` קשורה בצורה מהותית לרעיון של מהו `NULL`. בואו נסטה לרגע אחד כדי ללמוד על `NULL` (בעברית, ריק).


<a name="nulls"></a>
<div class="sideNote">
  <h3>ריק (Nulls)</h3>

  <p> ב-<code>NULL</code> SQL  הוא הערך שניתן לרשומה ריקה. הוא שונה מהערך של מחרוזת תווים ריקה <code>''</code> ומהמספר השלם <code>0</code>. שני אלו <i>לא</i> נחשבים <code>NULL</code>. כדי לבדוק אם ערך הוא <code>NULL</code>, השתמשו ב-<code>IS</code> וב-<code>IS NOT</code> במקום <code>=</code> או <code>!=</code>.</p>

    <sql-exercise
      data-question="ודאו ש-0 והמחרוזת הריקה אינם נחשבים ל-NULL."
      data-comment="זכרו שזוהי פסקה מורכבת. שתי הפסקאות שכוללות <code>IS NOT NULL</code> צריכות להחזיר ערך אמת על מנת שהשאילתה כולה תחזיר <code>true</code>."
      data-default-text="SELECT (0 IS NOT NULL) AND ('' IS NOT NULL) "
      ></sql-exercise>
  </div>

  עם זה, אנחנו יכולים לגלות את המכנה עבור היחס שאנחנו מחשבים:
  <sql-exercise
  data-question="מצאו את מספר ההוצאות להורג שבסט הנתונים."
  data-comment="הרעיון כאן הוא לבחור את אחד הטורים שבהם אתם מבטוחים שאין <code>NULL</code> ולספור בו."
  data-default-text=""
  data-solution="SELECT COUNT(ex_number) FROM executions"></sql-exercise>


<br>
<a name="count_var">
## וריאציות על COUNT
בינתיים הכל בסדר. אבל מה אם אנחנו לא יודעים אילו טורים מופיעים ללא `NULL`? או יותר גרוע, אם אף אחד מהטורים לא נקי ממופעים של `NULL`? חייבת להיות דרך למצוא את האורך של הטבלה גם במצבים האלה!

הפתרון הוא <span dir="ltr">`COUNT(*)`</span>. הוא קצת דומה ל-<span dir="ltr">`COUNT(*)`</span> בכך שה-`*` מיייצגת את כל הטורים. בפועל `COUNT(*)` מונה שורות כל עוד *כל אחד מהטורים בשורה* הוא לא ריק (null). זה עוזר לנו למצוא את האורכים בגלל שבטבלה לא אמורות להיות שורות שכל הטורים שלהם הן ריק (null).


<sql-exercise
data-question="ודאו ש-<code>(*)COUNT</code> נותן תוצאה זהה לתוצאה הקודמת
."
data-default-text="SELECT COUNT(*) FROM executions"></sql-exercise>

וריאציה נוספת היא לספור את חלק מהטבלה. לדוגמה, לספור את ההוצאות להורג במחוז האריס (Harris). היינו יכולים להריץ `SELECT COUNT(*) FROM executions WHERE county=’Harris’`, ובכך לסנן את סט הנתונים לאוסף קצר יותר יכיל את ההוצאות להוריג במחוז האריס ואז לספור את השורות. אבל מה אם אנחנו רוצים באותה השאילתה למצוא את מספר ההוצאות להורג במחוז בקסר (Bexar)?

הפתרון הוא להשתמש בבלוק מסוג `CASE WHEN`, שמתנהג כמו הצהרת "אם-אחרת" (if-else) גדולה. מתחילים עם `CASE` ומסיימים עם `ELSE <תוצאה> END`. ביניהן אפשר למקם כמה התפצלויות עם `WHEN <פסקה>` שרק נרצה. החלק של `ELSE <תוצאה>` משמש כברירת מחדל, למקרה שאף אחת מהאפשרויות שמופיעות בפסקאות ה-`WHEN` לא מורכות כאמת (true). תזכרו שבפרק הקודם כבר הזכרנו שפסקאות הן ביטויים יכולות להיות מוערכות (evaluated) כאמת (true) או כשקר (false). אם פסקת ה-`WHEN` היא `TRUE`, הבלוק יחזיר כתוצאה את ה-`THEN` התואם.



<sql-exercise

data-question="השאילתה הזו סופרת את מספר ההוצאות להורג במחוזות האריס (Harris) ובקסר (Bexar). החליפו את ה-<code>SUM</code> ב-<code>COUNT</code> וערכו את הבלוק של <code>CASE WHEN</code> כדי שהשאילתה עדיין תעבוד."
data-comment="החלפה של <code>SUM</code> ב-<code>COUNT</code> לבדה אינה מספיקה, משום ש-<code>COUNT</code> סופר גם את האפסים, שאינם נחשבים כערך ריק(null)."

data-default-text="SELECT
    SUM(CASE WHEN county='Harris' THEN 1
        ELSE 0 END),
    SUM(CASE WHEN county='Bexar' THEN 1
        ELSE 0 END)
FROM executions"
data-solution="SELECT
    COUNT(CASE WHEN county='Harris' THEN 1
        ELSE NULL END),
    COUNT(CASE WHEN county='Bexar' THEN 1
        ELSE NULL END)
FROM executions"></sql-exercise>

<br>

## אימון
<sql-exercise
  data-question=
  "מצאו כמה נדונים למוות היו מבוגרים מגיל 50 בעת ההוצאה להורג."
  data-comment="זה מראה ש-<code>WHERE</code> מסנן את הנתונים לפני שפונקציית האגירה (aggergative) מתרחשת."

  data-default-text=""
  data-solution='SELECT COUNT(*) FROM executions WHERE ex_age > 50'>
</sql-exercise>


<a name="documentation"></a>
<div class="sideNote">
  <h3>עיון במסמכי תיעוד והנחיות קוד</h3>
  <p>
הספר הזה לא נועד מעולם להוות מקור מידע שלם עבור שפת SQL. לשם כך יהיה עליכם לחפש  מקורות מקוונים אחרים. זוהי מיומנות לכשעצמה, מיומנות שמומלץ לשלוט בה משום שאתם עתידים לחפש במסמכי תיעוד והנחיוית קוד שנים רבות אחרי ההיכרות עם השפה.</p>

  <p>החדות הטובות הן שעם צורות החשיבה שתלמדו בספר הזה, החיפושים אחר מידע אמורים להיות מהירים ונטולי מאמץ משום שתבדקו רק פרטים כמו האם פונקציה נקראת `LENGTH` או `LEN` במקום לנסות לברר איזו גישה עליכם לנקוט.</p>
  <p>בחיפושים אני נעזר לעתים קרובות ב-<a href="https://www.w3schools.com/sql/default.asp">W3 Schools</a> וב-Stack Overflow.</p>
</div>


<sql-exercise
  data-question="מצאו את מספר הנדונים למוות שסירבו להשמיע הצהרה האחרונה לפני ביצוע גזר הדין."
  data-comment="לניקוד נוסף, נסו לעשות זאת בשלוש דרכים: <br>
  1) עם בלוק <code>WHERE</code>,<br>
  2) עם בלוק <code>COUNT</code> ו- <code>CASE WHEN</code><br>
  3) עם שתי פונקציות <code>COUNT</code>"
  data-default-text=""
  data-solution='SELECT COUNT(*) FROM executions WHERE last_statement IS NULL
  SELECT COUNT(CASE WHEN last_statement IS NULL THEN 1 ELSE NULL END) FROM executions
  SELECT COUNT(*) - COUNT(last_statement) FROM executions'>
</sql-exercise>

כדאי לקחת צעד אחורנית ולחשוב על הדרכים השונות בהן המחשב מתמודד עם שלוש השאילתות הללו. הגרסה עם ה-`WHERE` קודם סיננה את זה לטבלה קטנה לפני ביצוע האגירה. בשתי הגרסאות האחרות צריך היה לעבור דרך הטבלה בשלמותה. בגרסה עם `COUNT` + `CASE WHEN`, צריך היה לעבור על כל הטבלה רק פעם אחת, ואילו בגרסה עם שני ה-`COUNT` צריך היה לעבור על כל הטבלה פעמיים. אז למרות שהפלט הוא זהה בשלוש הגרסאות, הביצוע הטוב ביויתר היה כנראה של הגרסה הראשונה, והביצוע הגרוע בייותר הוא בגרסה השלישית.

<sql-exercise
  data-question="מצאו את גילאי המינימום, המקסימום והגיל הממוצע של הנדונים למוות בעת ההוצאה להורג."
  data-comment="השתמשו בפונקציות האוגרות <code>MAX</code>, <code>MIN</code> וב-<code>AVG</code>."  
  data-default-text="SELECT ex_age FROM executions"
  data-solution='SELECT MIN(ex_age), MAX(ex_age), AVG(ex_age) FROM executions'></sql-exercise>

  <sql-exercise
    data-question="מצאו את האורך הממוצע של ההצהרות האחרונות (בהתבסס על ספירת התווים בהצהרה) המופיעת בסט הנתונים."
    data-comment='התרגיל הזה מדגים שאתם יכולםי לחבר פונקציות. ראו את <a href="http://sqlite.org/lang_corefunc.html">הדוקומנטציה</a> כדי להבין איזו פונקציה מחזירה את מספר התויים במחרוזת טקסט.'
    data-default-text=""
    data-solution='SELECT AVG(LENGTH(last_statement)) FROM executions'></sql-exercise>

  <sql-exercise
    data-question="רשמו את כל המחוזות שמופיעים בסט הנתונים ללא כפילויות."
    data-comment="אנחנו יכולים לקבל ערכים יחודיים בעזרת <code>SELECT DISTINCT</code>. ראו את המידע שמופיע ב<a href='https://www.w3schools.com/sql/sql_distinct.asp'>תיעוד.</a>"
    data-default-text=""
    data-solution='SELECT DISTINCT county FROM executions'></sql-exercise>

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

<br>
<a name="strange"></a>
## שאילתה מוזרה
  לפני שנסכם, תנו מבט בשאלתה הזו:<br> `SELECT first_name, COUNT(*) FROM executions`.

היא נראית מוזרה, לא? זה אומר להיראות לכם מוזר אם יש לכם כבר בראש דגם מחשבתי טוב של פונקציות אגירה (aggregations). <span dir="ltr">`COUNT(*)`</span> מנסה להחזיר ערך אחד שכולל את האורך של כל הטבלה עליה השאילתה רצה. `first_name` מנסה להחזיר ערך אחד עבור כל שורה בטבלה. האם המחשב אמור להחזיר כתגובה לשאילתה הזו שורה בודדת או אוסף של שורות? אם הוא יחזיר שורה אחת, איזו שורה עליו לבחור? ואם יחזיר כמה שורות, האם בכולן ישתכפל אותו הערך של <span dir="ltr">`COUNT(*)`</span>? הצורות של חלקי הפלט פשוט לא מסתדרות זו עם זו.


<sql-exercise
  data-question="בואו ננסה בכל זאת ונראה מה קורה."
  data-default-text="SELECT first_name, COUNT(*) FROM executions"></sql-exercise>

בפועל, בסיסי נתונים מנסםי להחזיר משהו הגיוני גם אם תעבירו אליהם שאילתה לא הגיונית. במקרה הזה, בסיס הנתונםי שלנו אוסף את השם הפרטי מהרשומה האחרונה בטבלה שלנו. מכיוון שהטבלה שלנו מסודרת בסדר כרונולוגי הפוך, הרשומה האחרונה הייא של צ’רלי ברוק ג’וניור <span dir="ltr">(Charile Brook Jr.)</span>, האדם הראשון שהוצא להורג לאחר שבית המשפט העליון ביטל את האיסור על עונש המוות. בסיסי נתונים שונם יתמודדו בצורות שונות עם מקרה כזה, ולכן מוטב לא לסמוך על התנהגות ברירת המחדל שלהם. ם אתם יודעיםי שאתם רוצים את הרשומה האחרונה, כדאי שתחפשו אותה באופן מפורש. בדיאלקטים רבים של SQL יש פנוקציית אגירה בשם `LAST` שהופכת שאילתה כזו לפשוטה. למרבה הצער, ב-SQLite  היא לא קיימת ולכן נדרש מעקף.


<a name="dialects"></a>
<div class="sideNote">
  <h3>דיאלקטים ובסיסי נתונים של SQL</h3>
    <p> למרות שקראנו לספר הזה כאילו הוא על SQL, אם חשוב לנו להיות קפדנים האמת היא שמדובר בספר על <i>SQLite</i>. זאת משום ש-SQL הוא מושג מדומיין, אידאל מופשט. במציאות ישרק דיאלקטים שמנסים לעקוב אחרי הרעיונות המפורטים של SQL. </p>
    <p>SQL גם כן אינו מפורט מספיק, כלומר, חלק מהשימושיות שלו אינה כלולה בהגדרות הסטנדרטיות. לדוגמה, ההגדרות הסטנדרטיות אינן אומרות האם פונקציה לאיתור אורך של מחרוזת תווים צריכה להיקרא <code>LEN</code> (SQL Server) או <code>LENGTH</code> (SQLite); או <code>LENGTH</code> (SQLite); או כיצד שמות מזהים (identifier) כמו שמות טבלא או שמות טורים צריכים להופיע בשאילתה: מוקפים ב-<code>”</code> כמו ב-SQLite, או ב-<code>’</code> כמו ב-MySQL.</p>
    <p> כדי להפוך את הדברים לגרועים יותר, אפילו שאילתה יחידה בדיאלקט מסויים עשויה להיות מעובדת בצורה שונה משום שלבסיסי הנתונים הדרושים יש מבנים שונים. לדוגמה, הדיאלקט של PostgreSQL יכול לשמש על בסיסי נתונים שמפוצלים בין מספר מחשבים/יחידות פיסיות ועל בסיסי נתונים שמכילים קובץ יחיד. המשמעות היא שהדגמים המחשבתיים (מודלים מנטליים) שאנחנו מפתחים כאן הם רק אמצעי עזר, הם עשויים לא לשקף בדיוק מה שבסיסי הנתונים עושים בדיוק בפועל </p>.
    <p> בחרנו ב-SQLite, שהוא גם דיאלקט וגם יישום, מפני שזהו אחד מבסיסי הנתונים הכי נפוצים. ניסינו גם להתמקד בשימושיות הבסיסי ובדגם המחשבתי שמאחורי SQL, במקום בחלקים שמבדילים את SQLite. עם דגם מחשבתי מבוסס יהיה לכם די קל להחליף בין דיאלקטים ובסיסי נתונים שונים של SQL.</p>
</div>

<br>
<a name="recap"></a>
## מסקנה וסיכום
חשבו מהו החלק היחסי של של הנדונים למוות שכללו טענה לחפות מפשע בהצהרתם האחרונה.
<sql-exercise
  data-question="חשבו מהו החלק היחסי של של הנדונים למוות שכללו טענה לחפות מפשע בהצהרתם האחרונה."
  data-comment="כדי לבצע חילוק עשרוני, ודאו שאחד מהגורמים הוא מספר עשרוני, באמצעות הכפלתו ב-1.0. השתמשו ב-<code dir='ltr'>LIKE '%innocent%'</code> כדי לאתר טענות לחפות מפשע."
  data-solution="SELECT
1.0 * COUNT(CASE WHEN last_statement LIKE '%innocent%'
    THEN 1 ELSE NULL END) / COUNT(*)
FROM executions"
></sql-exercise>

אם להיות כנים, השיטה הזו של איתור טענות לחפות מפשע היא לא ממש מדוייקת, מפני שטענה לחפות מפשע יכולה להיות מובעת בעזרת מונחים אחרים, כמו "לא אשם/אשמה" (“not guilty”).  לכן, אני חושד שהתוצאה נמוכה מהמספר האמיתי, ושכנראה מדובר בסדר גודל נכון. השאלה היא איתה נשארנו היא האם אנחנו מוכנים לקבל את האפשרות שעד 5% מהאנשים שאנו מוציאים להורג הם למעשה חפים מפשע. [פול גראהם (Paul Graham)](http://paulgraham.com/prop62.html) לא מוכן לקבל את זה.

לסיכום, עברנו מפעולות ברמת השורה הבודדת בפרק הקודם, לשימוש בפונקציות מסכמות (aggregate functions) על אוסף שורות בסט הנתונים. צעד זה פתח לנו דרך ללימוד ברמת ההתנהגות המערכתית. בפרק הבא נלמד להפעייל פונקציות מסכמות על אוסף של תתי-קבוצות של סט הנתונים, באמצעות שימוש בבלוק ה-`GROUP BY`.
