آوند — داده: SQL، مدل‌سازی و تحلیل برای تصمیم

فصل ۲ از ۹

پیشرفت ترم
۰٪

در این فصل چه یاد می‌گیری#

مدیرِ کتابخانه یک جمله پرسیده: «چند عضوِ فعال داریم؟»

دو نفر جواب می‌دهند. اولی می‌گوید کسی فعال است که در سه ماهِ گذشته دستِ‌کم یک کتاب امانت گرفته باشد و عددش می‌شود ۸۵۱. دومی می‌گوید فعال یعنی کسی که در سه ماهِ گذشته دستِ‌کم یک بار به کتابخانه سر زده باشد و عددش می‌شود ۹۵۵. همان پایگاه داده، همان روز، همان پنجرهٔ زمانی.

هیچ‌کدام اشتباه نکرده‌اند. پرسش هنوز جواب ندارد، چون هنوز پرسش نیست: کلمهٔ «فعال» تا وقتی به شرطی روی سطرها ترجمه نشود، عدد ندارد.

یک درگاه که دو شمارندهٔ مکانیکی کنارش نصب شده و هرکدام مهره‌ها را در لوله‌ای جدا انباشته‌اند، با دو ارتفاعِ کاملاً متفاوت

آخر این فصل می‌توانی:

  • یک کلمهٔ مبهم را به یک تعریفِ عملیاتی تبدیل کنی که هر کسی بتواند اجرایش کند
  • نشان بدهی دو تعریفِ هردو-درست چند عدد فاصله دارند
  • بگویی پنجرهٔ زمانی و مخرج و واحدِ تحلیل، هر سه بخشی از خودِ سنجه‌اند
  • تعریف را جایی بنویسی که پرسش واقعاً از همان‌جا خوانده شود

قبل از شروع#

از فصلِ ۱: بازسازیِ دانه‌بندی، شمارشِ کنترلی، و اینکه نامِ یک ستون هیچ تضمینی نیست. از ترمِ ۲: COUNT(DISTINCT ...). از ترمِ ۱: IS NULL و پارامترِ ? و توابعِ تاریخ.

تاریخِ برشِ داده در این ترم یک ثابت است به نامِ AS_OF. پایگاه داده هیچ سطری بعد از آن روز ندارد، و هر پنجرهٔ زمانی از همان‌جا عقب شمرده می‌شود. فصلِ ۸ نشان می‌دهد چرا این باید یک پارامتر باشد و نه چیزی که به کد چسبیده.

📓 نوت‌بوک: نوت‌بوک این فصل را در Colab باز کن — همهٔ کدهای این فصل آماده و به‌ترتیب داخلش هست.

جدول تعدادِ سطر زمانِ ساخت
members ۱٬۴۰۰ آنی
loans ۱۶٬۸۳۴ کمتر از دو ثانیه
visits ۲۵٬۰۱۴ کمتر از دو ثانیه
tiny_loans و tiny_visits (همین فصل می‌سازدشان) ۳ و ۵ آنی

۱. پنج عضو، دو تعریف، دو عدد#

قبل از هر ادعایی روی ۱٬۴۰۰ عضو، جدولی که کلش جلوی چشمت جا می‌شود. پنج عضو داریم و دو دفتر: دفترِ امانت‌ها و دفترِ بازدیدها.

con.execute("DROP TABLE IF EXISTS tiny_loans")
con.execute("DROP TABLE IF EXISTS tiny_visits")
con.execute("CREATE TABLE tiny_loans (member_id INTEGER, borrowed_at TEXT)")
con.execute("CREATE TABLE tiny_visits (member_id INTEGER, visited_at TEXT)")
con.executemany("INSERT INTO tiny_loans VALUES (?, ?)", [
    (1, "2025-05-20"), (3, "2025-04-02"), (5, "2024-11-30"),
])
con.executemany("INSERT INTO tiny_visits VALUES (?, ?)", [
    (1, "2025-05-20"), (2, "2025-06-01"), (5, "2025-05-05"),
    (None, "2025-06-03"), (None, "2025-04-14"),
])
con.commit()

print(q("SELECT * FROM tiny_loans ORDER BY member_id"))
print()
print(q("SELECT * FROM tiny_visits ORDER BY visited_at"))
   member_id borrowed_at
0          1  2025-05-20
1          3  2025-04-02
2          5  2024-11-30

   member_id  visited_at
0        NaN  2025-04-14
1        5.0  2025-05-05
2        1.0  2025-05-20
3        2.0  2025-06-01
4        NaN  2025-06-03

دو بازدید member_id ندارند: کسی آمده و کارتش را نکشیده. pandas ستونی را که NULL دارد اعشاری نشان می‌دهد، و همان NaNها همان NULLهای SQLite هستند.

حالا پنجره را روی نود روزِ منتهی به تاریخِ برش می‌گذاریم و هر دو تعریف را می‌شماریم:

WINDOW_START = "2025-03-14"
print("تعریف الف — دست‌کم یک امانت:",
      int(q("""
          SELECT COUNT(DISTINCT member_id) AS n FROM tiny_loans
          WHERE borrowed_at > ?
      """, (WINDOW_START,)).iloc[0, 0]))
print("تعریف ب — دست‌کم یک بازدیدِ شناسایی‌شده:",
      int(q("""
          SELECT COUNT(DISTINCT member_id) AS n FROM tiny_visits
          WHERE visited_at > ? AND member_id IS NOT NULL
      """, (WINDOW_START,)).iloc[0, 0]))
تعریف الف — دست‌کم یک امانت: 2
تعریف ب — دست‌کم یک بازدیدِ شناسایی‌شده: 3

دو در برابرِ سه، و با انگشت شمردنی: تعریف الف اعضای ۱ و ۳ را می‌گیرد (امانتِ عضوِ ۵ خارج از پنجره است). تعریف ب اعضای ۱ و ۲ و ۵ را می‌گیرد. عضوِ ۴ در هیچ‌کدام نیست و اصلاً در هیچ دفتری سطر ندارد.

و توجه کن که دو بازدید از چشمِ تعریف ب افتادند — همان دو سطری که member_id ندارند. آن دو بازدید واقعاً اتفاق افتاده‌اند؛ فقط نمی‌دانیم مالِ چه کسی بودند.

۲. همان دو تعریف، روی دادهٔ واقعی#

متنِ هر دو پرسش را در متغیر می‌گذاریم، چون در بخشِ ۶ دوباره لازمشان داریم:

ACTIVE_BY_LOAN = """
    SELECT COUNT(DISTINCT member_id) AS active
    FROM loans
    WHERE borrowed_at > date(?, '-90 days')
"""
ACTIVE_BY_VISIT = """
    SELECT COUNT(DISTINCT member_id) AS active
    FROM visits
    WHERE visited_at > date(?, '-90 days') AND member_id IS NOT NULL
"""
by_loan = int(q(ACTIVE_BY_LOAN, (AS_OF,)).iloc[0, 0])
by_visit = int(q(ACTIVE_BY_VISIT, (AS_OF,)).iloc[0, 0])
print("تعریف الف — امانت گرفته  :", by_loan)
print("تعریف ب  — سر زده        :", by_visit)
print("اختلاف                   :", by_visit - by_loan)
تعریف الف — امانت گرفته  : 851
تعریف ب  — سر زده        : 955
اختلاف                   : 104

۸۵۱ و ۹۵۵ — صد و چهار نفر اختلاف، و هر دو پرسش درست‌اند.

انتخاب بینِ این دو یک تصمیم است، نه یک کشف. اگر می‌خواهی بدانی چند نفر از سرویسِ اصلیِ کتابخانه استفاده می‌کنند، تعریف الف. اگر می‌خواهی بدانی چند نفر هنوز پایشان به اینجا باز است — برای درسِ زبان، برای سالنِ مطالعه، برای هر چیزِ دیگری — تعریف ب.

چیزی که مجاز نیست این است که عدد را بدونِ تعریفش تحویل بدهی. «۹۵۵ عضوِ فعال» یک ادعاست؛ «۹۵۵ عضو در ۹۰ روزِ منتهی به تاریخِ برش دستِ‌کم یک بازدیدِ شناسایی‌شده داشته‌اند» یک عدد است.

۳. کدام تعریف چه کسی را نمی‌بیند#

دو عددِ نزدیک همیشه یعنی دو مجموعهٔ نزدیک نیست. بشماریم:

print(q("""
    WITH borrowed AS (
        SELECT DISTINCT member_id FROM loans
        WHERE borrowed_at > date(?, '-90 days')
    ),
    visited AS (
        SELECT DISTINCT member_id FROM visits
        WHERE visited_at > date(?, '-90 days') AND member_id IS NOT NULL
    )
    SELECT (SELECT COUNT(*) FROM borrowed WHERE member_id IN (SELECT member_id FROM visited)) AS both_ways,
           (SELECT COUNT(*) FROM borrowed WHERE member_id NOT IN (SELECT member_id FROM visited)) AS loan_only,
           (SELECT COUNT(*) FROM visited WHERE member_id NOT IN (SELECT member_id FROM borrowed)) AS visit_only
""", (AS_OF, AS_OF)))
   both_ways  loan_only  visit_only
0        805         46         150

۸۰۵ نفر در هر دو تعریف‌اند، ۴۶ نفر فقط در الف، و ۱۵۰ نفر فقط در ب.

شمارشِ کنترلی همین‌جاست: ⁦۸۰۵ + ۴۶ = ۸۵۱⁩ و ⁦۸۰۵ + ۱۵۰ = ۹۵۵⁩. هر دو خواندند.

آن ۴۶ نفر جالب‌ترین گروه‌اند: کتاب برده‌اند ولی هیچ بازدیدِ شناسایی‌شده‌ای ندارند. یعنی داده‌ای که تعریف ب رویش سوار است ناقص است — و از فصلِ ۱ می‌دانی که این‌طور چیزها باید کنارِ عدد نوشته شوند، نه بعداً کشف شوند.

۴. پنجرهٔ زمانی هم بخشی از تعریف است#

«سه ماهِ گذشته» را از کجا آوردیم؟ از هیچ‌جا. یک انتخاب بود. سه انتخابِ متفاوت را کنارِ هم بگذاریم:

rows = []
for days in (30, 90, 180):
    a = int(q("""SELECT COUNT(DISTINCT member_id) AS n FROM loans
                 WHERE borrowed_at > date(?, ?)""", (AS_OF, "-%d days" % days)).iloc[0, 0])
    b = int(q("""SELECT COUNT(DISTINCT member_id) AS n FROM visits
                 WHERE visited_at > date(?, ?) AND member_id IS NOT NULL""",
              (AS_OF, "-%d days" % days)).iloc[0, 0])
    rows.append({"پنجره": "%d روز" % days, "الف: امانت": a, "ب: بازدید": b})
print(pd.DataFrame(rows).to_string(index=False))
  پنجره  الف: امانت  ب: بازدید
 30 روز         561        651
 90 روز         851        955
180 روز         970       1020

شش عدد، همه درست، از ۵۶۱ تا ۱٬۰۲۰ — تقریباً دو برابر.

و توجه کن که رشتهٔ '-90 days' هم با پارامترِ ? رفته، نه با چسباندن به متنِ پرسش. این یک مقدار است، حتی وقتی شبیهِ بخشی از دستور به‌نظر می‌رسد.

پنجرهٔ کوتاه‌تر عددِ کوچک‌تر و حساس‌تر می‌دهد؛ پنجرهٔ بلندتر عددی که آرام‌تر تکان می‌خورد ولی کسی را هم که چهار ماه است نیامده «فعال» می‌شمارد. هیچ‌کدام درست‌تر نیست، ولی گزارشی که پنجره‌اش را ننویسد، عددش قابلِ استفاده نیست.

💡 نکته: پنجره را از روی داده انتخاب نکن. اگر سه پنجره را امتحان کنی و آن یکی را برداری که عددِ قشنگ‌تری می‌دهد، دیگر داری سنجه را از روی جوابِ دلخواه می‌سازی. اول پنجره را به دلیلِ کسب‌وکار انتخاب کن، بعد بشمار.

🔧 اگر کار نکرد: کسی که از گویشِ دیگری آمده، تابعِ تاریخِ همان‌جا را می‌نویسد و SQLite نمی‌شناسدش:

try:
    q("""
        SELECT COUNT(DISTINCT member_id) AS active
        FROM loans
        WHERE borrowed_at > DATE_SUB(?, INTERVAL 90 DAY)
    """, (AS_OF,))
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
pandas.errors.DatabaseError
Execution failed on sql '
        SELECT COUNT(DISTINCT member_id) AS active
        FROM loans
        WHERE borrowed_at > DATE_SUB(?, INTERVAL 90 DAY)
    ': near "90": syntax error

پیام روی کلمهٔ 90 گیر کرده، ولی مشکل از آن نیست: SQLite اصلاً چنین نحوی ندارد و date(?, '-90 days') معادلِ درستش است. خطای نحوی معمولاً جایی را نشان می‌دهد که تحلیلگر گیر کرده، نه جایی را که تو اشتباه کرده‌ای.

۵. مخرج، و واحدِ تحلیل#

تا اینجا فقط صورتِ کسر را عوض کردیم. حالا مخرج:

all_members = int(q("SELECT COUNT(*) AS n FROM members").iloc[0, 0])
current = int(q("SELECT COUNT(*) AS n FROM members WHERE left_at IS NULL").iloc[0, 0])
households = int(q("""
    SELECT COUNT(DISTINCT phone) AS n FROM members
    WHERE id IN (SELECT member_id FROM loans WHERE borrowed_at > date(?, '-90 days'))
""", (AS_OF,)).iloc[0, 0])

print("صورت — اعضای فعال (تعریف الف، ۹۰ روز):", by_loan)
print()
print("مخرج ۱ — همه اعضای ثبت‌شده           :", all_members,
      "→", round(100.0 * by_loan / all_members, 1))
print("مخرج ۲ — فقط اعضای فعلی              :", current,
      "→", round(100.0 * by_loan / current, 1))
print()
print("همان صورت، واحد تحلیل «خانوار»       :", households)
صورت — اعضای فعال (تعریف الف، ۹۰ روز): 851

مخرج ۱ — همه اعضای ثبت‌شده           : 1400 → 60.8
مخرج ۲ — فقط اعضای فعلی              : 1000 → 85.1

همان صورت، واحد تحلیل «خانوار»       : 837

یک صورت، دو مخرج، دو نرخِ کاملاً متفاوت — و هر دو از همان ۸۵۱ نفر ساخته شده‌اند.

مخرجِ اول شاملِ ۴۰۰ نفری است که عضویتشان تمام شده. اگر می‌پرسی «عضویت چقدر به کار می‌آید»، همان درست است. مخرجِ دوم فقط اعضای فعلی را می‌بیند و قرار است جوابِ «کسی که الان عضو است چقدر استفاده می‌کند» را بدهد. ولی صورتش هنوز همان ۸۵۱ نفر است و ۱۸ نفرشان در همین نود روز امانت گرفته و بعد رفته‌اند، پس این ۱۸ نفر در صورت هستند و در مخرج نیستند. عددِ سازگار ۸۳۳ از ۱٬۰۰۰ است، یعنی ۸۳٫۳ درصد. این همان درسِ ترمِ ۴ فصلِ ۱ است، این بار روی یک سنجه: مخرج را عوض کن و عدد عوض می‌شود؛ و هر کسی که در صورت است باید در مخرج هم باشد.

و خطِ آخر چیزِ سومی را عوض می‌کند: واحدِ تحلیل. ۸۵۱ عضوِ فعال فقط ۸۳۷ شمارهٔ تلفنِ متمایز دارند، چون چند خانواده یک شماره داده‌اند. اگر سنجه‌ات قرار است «چند خانوار از کتابخانه استفاده می‌کنند» را بگوید، عددِ درست ۸۳۷ است.

پس یک سنجهٔ کامل چهار تکه دارد و هر چهار باید نوشته شوند: صورت، مخرج، پنجرهٔ زمانی، و واحدِ تحلیل.

۶. فرهنگِ سنجه‌ها را جایی بنویس که پرسش از آن خوانده شود#

تعریفی که در یک پیام نوشته شده گم می‌شود. تعریفی که در پایگاه داده نشسته و خودِ پرسش هم از همان‌جا خوانده می‌شود گم نمی‌شود:

con.execute("DROP TABLE IF EXISTS metric_book")
con.execute("""
    CREATE TABLE metric_book (
        name        TEXT PRIMARY KEY,
        question    TEXT    NOT NULL,
        grain       TEXT    NOT NULL,
        window_days INTEGER NOT NULL,
        denominator TEXT    NOT NULL,
        sql_text    TEXT    NOT NULL
    )
""")
con.executemany("INSERT INTO metric_book VALUES (?, ?, ?, ?, ?, ?)", [
    ("active_borrower",
     "چند عضو در پنجره دست‌کم یک کتاب امانت گرفته؟",
     "یک سطر = یک عضو", 90, "members", ACTIVE_BY_LOAN),
    ("active_visitor",
     "چند عضو در پنجره دست‌کم یک بار شناسایی‌شده سر زده؟",
     "یک سطر = یک عضو", 90, "members", ACTIVE_BY_VISIT),
])
con.commit()

print(q("SELECT name, window_days, denominator FROM metric_book ORDER BY name"))
print()
for name, sql_text in con.execute(
        "SELECT name, sql_text FROM metric_book ORDER BY name").fetchall():
    print(name, "→", int(q(sql_text, (AS_OF,)).iloc[0, 0]))
              name  window_days denominator
0  active_borrower           90     members
1   active_visitor           90     members

active_borrower → 851
active_visitor → 955

دو سنجه، دو تعریفِ نوشته‌شده، و دو عددی که از همان تعریف‌ها بیرون آمدند — نه از حافظهٔ کسی.

اینجا یک مرزِ مهم هست که باید صریح بگوییمش: چیزی که از جدول درآمد و به q رفت متنِ خودِ پرسش بود، نه یک مقدار. تاریخِ برش همچنان با ? رفت. این همان مرزی است که تابعِ plan هم رویش می‌ایستد: متنِ پرسش می‌تواند از جایی بیاید، مقدار هرگز. اگر آن متن از دستِ کاربر می‌آمد، این کد امن نبود.

✅ چک کن: دو عددِ خطِ آخر باید دقیقاً همان ۸۵۱ و ۹۵۵ باشند که در بخشِ ۲ گرفتی. اگر فرق کردند، یعنی متنی که در جدول ذخیره کرده‌ای همان متنی نیست که اجرا کرده بودی — و این دقیقاً همان واگراییِ تعریف است که این جدول برای جلوگیری از آن ساخته شده.

📏 اندازه بگیر: یک سطر یعنی چه؟ در هر دو تعریف یک سطرِ نتیجه یک عدد است که روی «یک عضو» شمرده شده؛ در خطِ آخرِ بخشِ ۵ واحد عوض می‌شود و یک سطر یعنی یک خانوار. با کدام شمارشِ دوم سنجیدی؟ با دوتا: تفکیکِ بخشِ ۳ که ⁦۸۰۵ + ۴۶ = ۸۵۱⁩ و ⁦۸۰۵ + ۱۵۰ = ۹۵۵⁩ داد، و جدولِ پنج‌سطریِ بخشِ ۱ که هر دو تعریف رویش با انگشت شمردنی بود. چه کسی از قلم افتاد؟ بازدیدهای بی‌کارت که member_id ندارند و تعریف ب هیچ‌وقت نمی‌بیندشان؛ و در مخرجِ دوم، ۴۰۰ عضوی که عضویتشان تمام شده.

🤖 از دستیارت بپرس: «تعریفِ عملیاتی یک سنجه یعنی چه و چرا بدونِ آن نمی‌شود عدد گزارش کرد؟» بعد این را بپرس: «دو تعریفِ متفاوت از «کاربرِ فعال» بنویس که هر دو قابلِ دفاع باشند و بگو هرکدام چه کسی را نمی‌بیند.» — جوابِ درست برای هر تعریف می‌گوید کدام گروه از قلم می‌افتد. اگر جواب فقط یک تعریف داد و گفت «استاندارد همین است»، ردش کن: استانداردی وجود ندارد.

واژه‌های تازهٔ این فصل#

کلمه تلفظ به حروف فارسی یعنی چه
metric متریک سنجه؛ عددی که برای پیگیریِ یک چیز مرتب اندازه گرفته می‌شود
operational definition آپریشنال دفینیشن تعریفِ عملیاتی؛ ترجمهٔ یک کلمه به شرطی که روی سطرها اجرا می‌شود
lookback window لوک‌بک ویندو پنجرهٔ زمانی؛ اینکه چند روز به عقب نگاه می‌کنیم
unit of analysis یونیت آو انالیسیس واحدِ تحلیل؛ اینکه یک «چیز» در گزارش عضو است یا خانوار یا سفارش
metric dictionary متریک دیکشنری فرهنگِ سنجه‌ها؛ جایی که تعریفِ هر سنجه یک بار نوشته می‌شود

تمرین‌ها

اول خودت فکر کن یا امتحان کن — بعد اینجا را باز کن.

در فصل بعد#

تا اینجا هر عددی که دیدیم قابلِ دفاع بود؛ فقط باید تعریفش را می‌نوشتی. فصلِ بعد سراغِ عددهایی می‌رود که قابلِ دفاع نیستند و باز هم هیچ خطایی نمی‌دهند.

و با پنج پرسشِ کاملاً معمولی شروع می‌کند که هر پنج‌تایشان دروغ می‌گویند — از جمله یکی که میانگینِ مدتِ امانت را ۲۲٫۴۲ روز اعلام می‌کند، در حالی که عددِ صادقانه ۳۰٫۰۴ روز است.

به آخر این فصل رسیدی!

اگر ساختی و جواب داد، این دکمه مال توست.