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

فصل ۷ از ۹

پیشرفت ترم
۰٪

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

در این کتابخانه پنج کتاب دقیقاً ۱۹۰ بار امانت رفته‌اند — همان پنج‌تایی که ترمِ ۱ فصلِ ۴ پیدایشان کرد و نشان داد پرسشِ «سه کتابِ پرامانت» جوابِ یکتا ندارد.

حالا سه تابعِ رتبه‌بندی روی همان پنج سطر می‌گذاریم و سه جوابِ کاملاً متفاوت می‌گیریم: یکی به آن‌ها ۱ تا ۵ می‌دهد، یکی به هر پنج‌تا ۱ می‌دهد و سطرِ بعدی را ۶ می‌کند، و یکی به هر پنج‌تا ۱ می‌دهد و سطرِ بعدی را ۲.

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

یک ردیف میلهٔ اندازه‌گیری که پنج‌تای اولش دقیقاً هم‌طول‌اند، کنارِ سه ریلِ شیاردار با فاصلهٔ شیارهای متفاوت

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

  • داخلِ OVER ترتیب بگذاری و بگویی چه چیزی را عوض می‌کند
  • تفاوتِ ROW_NUMBER و RANK و DENSE_RANK را روی تساوی نشان بدهی
  • «N تای اولِ هر گروه» را در یک پرسش بگیری
  • با LAG و LEAD هر سطر را با سطرِ قبل و بعدش مقایسه کنی
  • تغییرِ دوره‌به‌دوره بسازی و بگویی کدام سطرش قابلِ‌اعتماد نیست

قبل از شروع#

از فصلِ ۶: OVER () و PARTITION BY، و اینکه تابعِ پنجره بعد از WHERE اجرا می‌شود و در WHERE در دسترس نیست.

از ترمِ ۱ فصلِ ۴: بدونِ یک ستونِ یکتا در ORDER BY، «سه تای اول» جوابِ یکتا ندارد. این فصل همان درس را در سطحِ رتبه تکرار می‌کند.

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

جدول تعدادِ سطر زمانِ ساخت
books ۱٬۲۰۰ کمتر از یک ثانیه
branches ۶ آنی

۱. ORDER BY داخلِ OVER#

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

ORDER BY داخلِ OVER همین را عوض می‌کند: سطرهای هر پنجره را مرتب می‌کند، و از آن به بعد می‌شود پرسید «این سطر چندمین است».

دقت کن که این ORDER BY داخلِ پرانتزِ OVER است و ربطی به ORDER BY آخرِ پرسش ندارد. اولی ترتیبِ محاسبه را می‌گوید، دومی ترتیبِ نمایش را.

print(q("""
    SELECT id,
           borrowed,
           ROW_NUMBER() OVER (ORDER BY borrowed DESC, id) AS rn,
           RANK()       OVER (ORDER BY borrowed DESC)     AS rk,
           DENSE_RANK() OVER (ORDER BY borrowed DESC)     AS dr
    FROM books
    ORDER BY borrowed DESC, id
    LIMIT 7
"""))
     id  borrowed  rn  rk  dr
0    46       190   1   1   1
1   448       190   2   1   1
2   601       190   3   1   1
3  1056       190   4   1   1
4  1169       190   5   1   1
5   138       189   6   6   2
6   392       189   7   6   2

پنج سطرِ اول همه borrowed برابر دارند، و سه ستونِ آخر سه چیزِ متفاوت می‌گویند.

  • ROW_NUMBER یک شماره‌گذاریِ ساده است: ۱، ۲، ۳، ۴، ۵. هیچ‌وقت تکراری نمی‌دهد، حتی وقتی سطرها یکسان باشند.
  • RANK به هر پنج‌تا رتبهٔ ۱ می‌دهد، بعد می‌پرد به ۶ — چون پنج نفر جلوتر از سطرِ ششم بوده‌اند.
  • DENSE_RANK هم به هر پنج‌تا ۱ می‌دهد، ولی نمی‌پرد: سطرِ بعدی ۲ می‌شود، چون دومین مقدارِ متفاوت است.

سه پرسشِ متفاوت، سه جواب: «چندمین سطر است؟»، «چند نفر جلوترند؟»، «چندمین مقدارِ متفاوت است؟»

۲. کدام را کِی، و تلهٔ ROW_NUMBER#

اگر می‌خواهی هر سطر یک شمارهٔ یکتا بگیرد — مثلاً برای صفحه‌بندی — ROW_NUMBER. اگر می‌خواهی یک رتبهٔ منصفانه بدهی که تساوی را به رسمیت بشناسد، RANK یا DENSE_RANK.

و اینجا تلهٔ اصلی است: ROW_NUMBER روی سطرهای مساوی، ترتیب را خودش انتخاب می‌کند.

without_key = q("""
    SELECT id FROM books
    ORDER BY borrowed DESC
    LIMIT 5
""")
with_key = q("""
    SELECT id, ROW_NUMBER() OVER (ORDER BY borrowed DESC, id) AS rn
    FROM books
    ORDER BY rn
    LIMIT 5
""")
print("پنج سطر اول، بدون کلید یکتا:", list(without_key["id"]))
print("پنج سطر اول، با کلید یکتا  :", list(with_key["id"]))
print("چند کتاب دقیقاً ۱۹۰ بار رفته‌اند؟",
      int(q("SELECT COUNT(*) FROM books WHERE borrowed = ?", (190,)).iloc[0, 0]))
پنج سطر اول، بدون کلید یکتا: [46, 448, 601, 1056, 1169]
پنج سطر اول، با کلید یکتا  : [46, 448, 601, 1056, 1169]
چند کتاب دقیقاً ۱۹۰ بار رفته‌اند؟ 5

در این اجرا هر دو یک جواب دادند — و این یک نتیجهٔ منفی است که باید صادقانه نوشته شود.

آنچه ثابت شد این است که SQLite در این اجرا همان ترتیب را انتخاب کرد. آنچه ثابت نشد این است که مجبور بوده. پنج کاندید برای پنج جای اول هست و هر جایگشتی از آن‌ها به‌اندازهٔ همین یکی درست است.

قاعده همان قاعدهٔ ترمِ ۱ است: هر جا ORDER BY داخلِ OVER می‌نویسی، آن‌قدر ستون اضافه کن که ترتیب یکتا شود. , id هیچ هزینه‌ای ندارد و ابهام را کامل حذف می‌کند.

۳. «N تای اولِ هر گروه»#

این پرکاربردترین کاربردِ رتبه است: دو کتابِ پرامانتِ هر دسته. با GROUP BY نمی‌شود گرفتش — فصلِ ۲ نشان داد که ستونِ گروه‌نشده مقدارش دلبخواهی است — ولی با PARTITION BY به‌اضافهٔ ORDER BY مستقیم درمی‌آید:

ranked = q("""
    SELECT category,
           title,
           borrowed,
           ROW_NUMBER() OVER (PARTITION BY category ORDER BY borrowed DESC, id) AS rn
    FROM books
    ORDER BY category, rn
""")
print(ranked.head(6))
print("سطرهای خروجی:", len(ranked))
  category           title  borrowed  rn
0    تاریخ    برف پشت چراغ       185   1
1    تاریخ  بازگشت به جنگل       184   2
2    تاریخ   چراغ در باران       181   3
3    تاریخ   بازگشت به باد       180   4
4    تاریخ      روزهای باد       175   5
5    تاریخ    ستاره و دریا       174   6
سطرهای خروجی: 1200

رتبه‌ها داخلِ هر دسته از یک شروع می‌شوند، و خروجی هنوز هر ۱٬۲۰۰ سطر را دارد.

حالا فقط سطرهای با رتبهٔ ۱ و ۲ را می‌خواهیم. اولین چیزی که به ذهن می‌رسد یک WHERE است، و فصلِ ۶ توضیح داد چرا کار نمی‌کند:

🔧 اگر کار نکرد: پیامِ خطا این بار خیلی صریح است و کلمهٔ «نامِ مستعار» هم در آن هست:

try:
    q("""
        SELECT id, ROW_NUMBER() OVER (ORDER BY borrowed DESC) AS rn
        FROM books
        WHERE rn <= ?
    """, (3,))
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
pandas.errors.DatabaseError
Execution failed on sql '
        SELECT id, ROW_NUMBER() OVER (ORDER BY borrowed DESC) AS rn
        FROM books
        WHERE rn <= ?
    ': misuse of aliased window function rn

WHERE قبل از پنجره‌ها اجرا می‌شود، پس وقتی WHERE می‌خواهد rn را بخواند هنوز چیزی به آن نام وجود ندارد.

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

print(q("""
    SELECT category, title, borrowed, rn
    FROM (
        SELECT category,
               title,
               borrowed,
               ROW_NUMBER() OVER (PARTITION BY category ORDER BY borrowed DESC, id) AS rn
        FROM books
    )
    WHERE rn <= ?
    ORDER BY category, rn
""", (2,)))
      category             title  borrowed  rn
0        تاریخ      برف پشت چراغ       185   1
1        تاریخ    بازگشت به جنگل       184   2
2         رمان    بازگشت به دریا       190   1
3         رمان   در جست‌وجوی رود       189   2
4   زندگی‌نامه      ابر پشت چراغ       190   1
5   زندگی‌نامه      روزهای پنجره       189   2
6          شعر   در جست‌وجوی آتش       188   1
7          شعر      ابر پشت خانه       186   2
8          علم       پنجره و رود       190   1
9          علم   بازگشت به ستاره       189   2
10       فلسفه    بازگشت به خانه       190   1
11       فلسفه   در جست‌وجوی ابر       189   2
12         هنر   بازگشت به پنجره       190   1
13         هنر     کوچه پشت آینه       188   2
14        کودک  در جست‌وجوی جنگل       188   1
15        کودک      خانه پشت خاک       181   2

شانزده سطر: دو کتاب برای هر یک از هشت دسته.

آن ( ... ) توی FROM یک زیرپرسش است — پرسشی که نتیجه‌اش مثلِ یک جدولِ موقت رفتار می‌کند. فعلاً همین‌قدر بدان که هر پرسشی می‌تواند جای یک جدول در FROM بنشیند؛ ترمِ ۳ فصلِ ۶ کلِ خانوادهٔ زیرپرسش‌ها را باز می‌کند و می‌گوید کِی گران می‌شوند و راهِ خواناترش چیست.

✅ چک کن: خروجی باید دقیقاً ۱۶ سطر باشد: ۸ دسته ضربدر ۲. و یک کنترلِ دوم که مهم‌تر است: هیچ دسته‌ای نباید بیشتر از دو سطر داشته باشد. اگر داشت، ROW_NUMBER را با RANK عوض کرده‌ای — و RANK روی تساوی به چند سطر یک رتبه می‌دهد، پس rn <= 2 می‌تواند سه سطر یا بیشتر برگرداند. کدامش را می‌خواهی؟ اگر «دقیقاً دو سطر» می‌خواهی ROW_NUMBER، اگر «همهٔ کسانی که در دو رتبهٔ اول‌اند» می‌خواهی RANK.

۴. LAG و LEAD: نگاه به سطرِ قبل و بعد#

LAG(x) مقدارِ x را از سطرِ قبلیِ همان پنجره می‌آورد و LEAD(x) از سطرِ بعدی. ترتیبِ «قبل» و «بعد» را همان ORDER BY داخلِ OVER تعیین می‌کند.

و چون فصلِ ۶ نشان داد پنجره روی نتیجهٔ GROUP BY می‌نشیند، می‌شود این را مستقیم روی یک جدولِ گروهی گذاشت:

print(q("""
    SELECT strftime('%Y', added_at) AS added_year,
           COUNT(*)                 AS n,
           LAG(COUNT(*))  OVER (ORDER BY strftime('%Y', added_at)) AS previous_year,
           LEAD(COUNT(*)) OVER (ORDER BY strftime('%Y', added_at)) AS next_year
    FROM books
    GROUP BY added_year
    ORDER BY added_year
"""))
  added_year    n  previous_year  next_year
0       2019  193            NaN      206.0
1       2020  206          193.0      166.0
2       2021  166          206.0      165.0
3       2022  165          166.0      188.0
4       2023  188          165.0      178.0
5       2024  178          188.0      104.0
6       2025  104          178.0        NaN

سطرِ اول previous_year ندارد و سطرِ آخر next_year ندارد — هر دو NaN شده‌اند، یعنی NULL.

این کاملاً درست است: سطرِ اول قبلی ندارد، پس مقدارش «نامعلوم» است، نه صفر. و از ترمِ ۱ فصلِ ۶ می‌دانی که NULL در هر محاسبه‌ای بعدش می‌ماند و در هر تجمیعی بی‌صدا کنار می‌رود.

پس هر ستونی که با LAG می‌سازی، دستِ‌کم یک سطرِ NULL دارد و باید تصمیم بگیری با آن چه می‌کنی — نشانش بدهی، حذفش کنی، یا با COALESCE جایگزینش کنی. هر سه انتخاب‌اند و باید در گزارش نوشته شوند؛ فقط یکی از آن‌ها غلط است: وانمود کردن به اینکه صفر بوده.

۵. تغییرِ دوره‌به‌دوره#

فصلِ ۴ تغییرِ سال‌به‌سال را در پایتون حساب کرد و قول داد ابزارِ SQLی‌اش را اینجا بدهد. حالا کلِ محاسبه در یک پرسش جا می‌شود:

print(q("""
    SELECT strftime('%Y', added_at) AS added_year,
           COUNT(*)                 AS n,
           COUNT(*) - LAG(COUNT(*)) OVER (ORDER BY strftime('%Y', added_at)) AS change,
           ROUND(
               (COUNT(*) - LAG(COUNT(*)) OVER (ORDER BY strftime('%Y', added_at))) * 100.0
               / LAG(COUNT(*)) OVER (ORDER BY strftime('%Y', added_at)), 2
           ) AS pct
    FROM books
    GROUP BY added_year
    ORDER BY added_year
"""))
  added_year    n  change    pct
0       2019  193     NaN    NaN
1       2020  206    13.0   6.74
2       2021  166   -40.0 -19.42
3       2022  165    -1.0  -0.60
4       2023  188    23.0  13.94
5       2024  178   -10.0  -5.32
6       2025  104   -74.0 -41.57

همان شش عددِ فصلِ ۴، این بار بدونِ یک خط پایتون.

سه چیز در همین جدول هست که باید دیده شوند:

  1. * 100.0 سرِ جایش است. بدونش همان تقسیمِ صحیحِ فصلِ ۴ برمی‌گشت و ستونِ درصد پر از صفر می‌شد.
  2. سطرِ اول NULL است و همان درست است. سالِ اول قبلی ندارد.
  3. سطرِ آخر یک عددِ درست‌محاسبه‌شده و کاملاً گمراه‌کننده است. آن سال ناقص است — این پایگاه داده تا اواسطش بیشتر ندارد — و مقایسه‌اش با یک سالِ کامل مثلِ مقایسهٔ شش ماه با دوازده ماه است.

و هیچ‌چیزی در خروجی این را نمی‌گوید. تنها راهِ فهمیدنش این است که خودت لبهٔ داده را نگاه کرده باشی:

print(q("""
    SELECT MIN(added_at) AS first_day,
           MAX(added_at) AS last_day
    FROM books
"""))
    first_day    last_day
0  2019-01-01  2025-06-30

داده وسطِ سال تمام می‌شود. پس آن ۴۱ درصد کاهش، سنجشِ نصفِ یک سال در برابرِ یک سالِ کامل است.

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

📏 اندازه بگیر: یک سطر یعنی چه؟ در بخش‌های ۱ تا ۳ یک سطر یک کتاب است و رتبه‌اش داخلِ دستهٔ خودش معنا دارد، نه در کلِ کتابخانه. در بخش‌های ۴ و ۵ یک سطر یک سال است. با کدام شمارشِ دوم سنجیدی؟ سه‌تا: تعدادِ کتاب‌های مساوی در صدر (۵) که توضیح داد چرا سه تابعِ رتبه سه جواب دادند؛ شمارشِ سطرهای «دو تای اولِ هر دسته» که باید دقیقاً ۱۶ باشد؛ و کمینه و بیشینهٔ added_at که ناقص بودنِ سالِ آخر را ثابت کرد. چه کسی از قلم افتاد؟ سطرِ اولِ هر ستونِ LAG که NULL است و در هر تجمیعی بعدش شمرده نمی‌شود؛ نیمهٔ دومِ سالِ آخر که اتفاق نیفتاده ولی در جدول مثلِ یک سقوط دیده می‌شود؛ و همچنان ۲۴۰ سطرِ arrivals.csv.

🤖 از دستیارت بپرس: «فرقِ RANK و DENSE_RANK در SQL چیست و هرکدام جوابِ چه پرسشی‌اند؟» بعد این را بپرس: «اگر بخواهم دقیقاً سه سطرِ اولِ هر گروه را بگیرم و در گروهی چهار سطر مساوی باشند، RANK چند سطر برمی‌گرداند؟» — جوابِ درست «چهارتا» است و باید توضیح بدهد چرا؛ اگر دستیارت گفت «سه‌تا»، همین حالا با پنج کتابِ ۱۹۰تایی امتحانش کن.

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

کلمه تلفظ به حروف فارسی یعنی چه
ROW_NUMBER رو نامبر شمارهٔ یکتای هر سطر داخلِ پنجره
RANK رنک رتبه با تساوی و با پرش
DENSE_RANK دنس رنک رتبه با تساوی و بدونِ پرش
LAG / LEAD لگ / لید مقدارِ سطرِ قبلی / بعدیِ همان پنجره
tie تای تساوی؛ چند سطر با مقدارِ یکسان
period over period پریود اوور پریود مقایسهٔ هر دوره با دورهٔ قبلش

تمرین‌ها

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

در فصل بعد#

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

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

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