شمردن، جمع زدن، کمینه و بیشینه

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

یک پرسشِ کاملاً معمولی: «مجموعِ قیمتِ کتاب‌های این کتابخانه چقدر است؟» یک SUM می‌نویسی و یک عدد می‌گیری — ۳۴۰٬۰۹۶٬۰۰۰. بدونِ خطا، بدونِ هشدار، با ظاهری کاملاً سالم.

آن عدد جمعِ ۱٬۰۳۱ کتاب است، نه ۱٬۲۰۰ کتاب. صد و شصت و نه کتاب بی‌صدا صفر حساب شدند، و هیچ چیزی در خروجی این را نمی‌گوید.

ترمِ ۱ سه تابعِ تجمیعی داد: COUNT(*) و SUM و AVG. این فصل جعبه‌ابزار را کامل می‌کند — MIN و MAX و COUNT(DISTINCT ...) و TOTAL — و در همان راه سه شکلِ متفاوتِ سکوت را نشان می‌دهد: تجمیعی که NULL می‌دهد، تجمیعی که صفر می‌دهد، و تجمیعی که عددِ خوش‌قیافهٔ غلط می‌دهد.

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

• کمینه و بیشینهٔ یک ستون را بگیری و بگویی چرا MAX در SQLite دو کارِ کاملاً متفاوت می‌کند
• تعدادِ مقدارهای متفاوت یک ستون را بشماری و بگویی چرا با تعدادِ سطرها یکی نیست
• تفاوتِ «هیچ سطری نبود» را با «سطر بود ولی مقدارش نامعلوم است» با عدد نشان بدهی
• روی یک عبارت تجمیع بگیری، نه فقط روی یک ستون
• ثابت کنی که یک SUM عددِ درستی داده یا فقط عددی داده

قبل از شروع#

از ترمِ ۱: SELECT و WHERE و ORDER BY، سه تابعِ COUNT(*) و SUM و AVG روی کلِ جدول، NULL و منطقِ سه‌ارزشی، و type affinity — اینکه در SQLite نوع، صفتِ مقدار است نه صفتِ ستون.

همان پایگاه دادهٔ ترمِ ۱ است، با همان SEED. پس هر عددی که آنجا دیدی اینجا هم همان است: ۱٬۲۰۰ کتاب، ۹۴٬۱۹۱ امانت، ۹۶۹ کتابِ امتیازدار، ۲۳۱ کتابِ بی‌امتیاز، و ۲۴۰ سطری که هنوز در arrivals.csv روی دیسک منتظرند.

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

۱. MIN و MAX: دو سرِ داده#

MIN(col) کوچک‌ترین مقدارِ یک ستون را می‌دهد و MAX(col) بزرگ‌ترین را. مثلِ SUM و AVG، این دو هم از همهٔ سطرها یک عدد می‌سازند.

print(q("""
    SELECT MIN(borrowed) AS least_borrowed,
           MAX(borrowed) AS most_borrowed,
           MIN(pages)    AS thinnest,
           MAX(pages)    AS thickest,
           MIN(added_at) AS first_added,
           MAX(added_at) AS last_added
    FROM books
"""))

   least_borrowed  most_borrowed  thinnest  thickest first_added  last_added
0               0            190        50       940  2019-01-01  2025-06-30

شش عدد، یک سطر. و دقت کن که MIN و MAX روی متن هم کار می‌کنند — added_at یک ستونِ TEXT است و «کوچک‌ترین» در متن یعنی همان چیزی که ترمِ ۱ فصلِ ۴ نشانش داد: ترتیبِ نویسه‌ها. برای تاریخِ ISO-8601 این دقیقاً همان ترتیبِ زمانی است، پس جواب درست است — ولی به این دلیل درست است، نه به این دلیل که SQLite تاریخ می‌فهمد.

و همین‌جا اولین تلهٔ فصل:

print(q("""
    SELECT MIN(edition)          AS lowest,
           MAX(edition)          AS highest,
           typeof(MIN(edition))  AS type_of_lowest,
           typeof(MAX(edition))  AS type_of_highest
    FROM books
"""))

   lowest highest type_of_lowest type_of_highest
0       1       ۶        integer            text

بیشترین چاپ، «۶» با رقمِ فارسی درآمد — و نوعش text است.

این همان ۳۷ سطری است که در ترمِ ۱ فصلِ ۵ پیدا کردیم: ستونی که INTEGER اعلام شده و در ۳۷ سطر متن دارد. قاعدهٔ ترتیبِ SQLite می‌گوید متن از هر عددی بزرگ‌تر است، پس MAX مجبور بود یکی از آن ۳۷ سطر را برگرداند. کمینه و بیشینه هیچ‌وقت میانگین نیستند؛ همیشه یک سطرِ واقعی‌اند — و به همین دلیل خراب‌ترین سطرِ جدولت را پیدا می‌کنند.

MIN و MAX را به‌عنوان ارزان‌ترین بازرسِ داده به کار ببر. قبل از هر گزارشی، دو سرِ هر ستونی را که رویش حساب می‌کنی نگاه کن؛ اگر یکی از آن دو معنا نداشت، هیچ میانگینی روی آن ستون معنا ندارد.

۲. MAX در SQLite دو تابعِ کاملاً متفاوت است#

این یکی از آن رفتارهایی است که تا وقتی گرفتارش نشوی باور نمی‌کنی: MAX با یک ورودی تجمیعی است و با دو ورودی نیست.

print("MAX با یک ورودی — یک عدد از کل جدول:")
print(q("SELECT MAX(borrowed) AS aggregate_max FROM books"))
print()
print("MAX با دو ورودی — یک مقدار برای هر سطر:")
print(q("""
    SELECT id, borrowed, pages, MAX(borrowed, pages) AS row_max
    FROM books
    ORDER BY id
    LIMIT 3
"""))

MAX با یک ورودی — یک عدد از کل جدول:
   aggregate_max
0            190

MAX با دو ورودی — یک مقدار برای هر سطر:
   id  borrowed  pages  row_max
0   1        33    730      730
1   2        20    564      564
2   3         1    907      907

پرسشِ اول یک سطر داد، پرسشِ دوم سه سطر. با یک ورودی، MAX همهٔ سطرها را می‌بلعد و یک عدد می‌سازد. با دو ورودی، MAX یک تابعِ معمولی است که در هر سطر بزرگ‌ترِ دو مقدار را برمی‌گرداند و هیچ سطری را نمی‌بلعد.

این برای MIN هم همین‌طور است، و در بیشترِ پایگاه داده‌های دیگر اصلاً چنین چیزی وجود ندارد. اگر جایی MAX(a, b) نوشتی و انتظارِ یک عدد داشتی، بی‌صدا یک ستونِ کامل می‌گیری.

🔧 اگر کار نکرد: همین شوخی روی COUNT جواب نمی‌دهد، و خوشبختانه خطا می‌دهد:

try:
    q("SELECT COUNT(borrowed, pages) FROM books")
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)

pandas.errors.DatabaseError
Execution failed on sql 'SELECT COUNT(borrowed, pages) FROM books': wrong number of arguments to function COUNT()

COUNT فقط یک ورودی می‌گیرد (یا *)، پس اشتباهِ تایپی سرِ جایش گیر می‌افتد. MAX نمی‌گیرد، و این دقیقاً همان تفاوتِ «خطای پرسروصدا» و «خطای بی‌صدا» است که کلِ این دوره دربارهٔ آن است.

۳. COUNT(DISTINCT col): شمردنِ چیزهای متفاوت، نه سطرها#

ترمِ ۱ دو شمارش داد: COUNT(*) سطرها را می‌شمارد و COUNT(col) سطرهایی را که در آن ستون مقدار دارند. شمارشِ سوم این است: چند مقدارِ متفاوت؟

print("سه شمارش روی یک ستون:")
print(q("""
    SELECT COUNT(*)               AS rows_total,
           COUNT(rating)          AS rows_with_rating,
           COUNT(DISTINCT rating) AS different_ratings
    FROM books
"""))
print()
print("و همان شمارش سوم روی سه ستون دیگر:")
print(q("""
    SELECT COUNT(DISTINCT author)   AS authors,
           COUNT(DISTINCT category) AS categories,
           COUNT(DISTINCT branch)   AS branches
    FROM books
"""))

سه شمارش روی یک ستون:
   rows_total  rows_with_rating  different_ratings
0        1200               969                 25

و همان شمارش سوم روی سه ستون دیگر:
   authors  categories  branches
0      123           8         6

چهار عدد و چهار دانه‌بندیِ کاملاً متفاوت. ۱٬۲۰۰ یعنی «کتاب»، ۹۶۹ یعنی «کتابِ امتیازدار»، ۲۵ یعنی «مقدارِ متفاوتِ امتیاز»، و ۱۲۳ یعنی «رشتهٔ متفاوت در ستونِ نویسنده». هیچ‌کدام از این‌ها با هم قابلِ‌مقایسه نیستند، و همین است که این فصل را لازم می‌کند.

و آن ۱۲۳ همان دامِ ترمِ ۱ فصلِ ۴ است. نویسنده‌های واقعی ۱۲۰ نفرند؛ سه نفرشان نامشان با نویسهٔ عربی هم ثبت شده و COUNT(DISTINCT ...) آن‌ها را دو نفر می‌شمارد، چون ي و ی برای SQLite دو حرفِ متفاوت‌اند.

print(q("""
    SELECT COUNT(DISTINCT author) AS raw,
           COUNT(DISTINCT replace(replace(author, 'ي', 'ی'), 'ك', 'ک')) AS normalised
    FROM books
"""))

   raw  normalised
0  123         120

✅ چک کن: ۱۲۳ منهای ۱۲۰ باید بشود ۳. اگر عددت فرق کرد، یکی از دو replace را جا انداخته‌ای — هم ي لازم است و هم ك. و اگر همین حالا وسوسه شدی که «پس همیشه COUNT(DISTINCT ...) را با replace بنویس»، جوابِ درست‌تر این است که داده یک بار موقعِ ورود پاک شود؛ ترمِ ۱ فصلِ ۷ نشان داد چرا.

COUNT(DISTINCT ...) اولین جایی است که یک گزارش پنهانی دانه‌بندی‌اش را عوض می‌کند. «۱۲۳ نویسنده» جمله‌ای دربارهٔ آدم‌هاست، ولی عددش از رشته‌ها ساخته شده. هر بار که این تابع را می‌نویسی، بپرس: چیزی که می‌شمارم واقعاً همان چیزی است که اسمش را می‌برم؟

۴. «هیچ سطری نبود» با «مقدارش نامعلوم است» فرق دارد#

این تفاوت در گزارش‌های واقعی گران‌ترین است، چون هر دو حالت یک چیز چاپ می‌کنند: None.

اول حالتِ «هیچ سطری نبود». دستهٔ «ورزش» در این کتابخانه وجود ندارد:

print(q("""
    SELECT COUNT(*)       AS n,
           SUM(borrowed)  AS total,
           AVG(borrowed)  AS mean,
           MIN(borrowed)  AS lo,
           MAX(borrowed)  AS hi,
           TOTAL(borrowed) AS total_function
    FROM books
    WHERE category = ?
""", ("ورزش",)))

   n total  mean    lo    hi  total_function
0  0  None  None  None  None             0.0

یک سطرِ خروجی گرفتیم، با اینکه هیچ سطرِ ورودی‌ای نبود. یک تجمیعِ بدونِ GROUP BY همیشه دقیقاً یک سطر می‌دهد، حتی وقتی هیچ داده‌ای برای تجمیع کردن نیست.

و در آن یک سطر، COUNT(*) صفر است ولی بقیه NULL هستند. این کاملاً درست است: تعدادِ چیزی که وجود ندارد صفر است، ولی جمع و میانگین و کمینه‌اش «نامعلوم» است، نه صفر.

و TOTAL دقیقاً همان SUM است با یک تفاوت: هرگز NULL نمی‌دهد، صفر می‌دهد. این یک تابعِ مخصوصِ SQLite است و تصمیمی راحت‌کننده، نه درست‌تر. اگر برنامه‌ات با None می‌شکند TOTAL نجاتت می‌دهد؛ ولی اگر گزارشت قرار است بگوید «چقدر؟»، آن صفر ادعایی بی‌پشتوانه است که خودت به داده اضافه کرده‌ای.

حالا حالتِ دوم: سطر هست، مقدار نیست. ستونِ audio_minutes در هر ۱٬۲۰۰ سطر خالی است.

print(q("""
    SELECT COUNT(*)             AS n,
           COUNT(audio_minutes) AS with_value,
           SUM(audio_minutes)   AS total,
           TOTAL(audio_minutes) AS total_function,
           MAX(audio_minutes)   AS hi
    FROM books
"""))

      n  with_value total  total_function    hi
0  1200           0  None             0.0  None

SUM باز هم NULL داد و TOTAL باز هم صفر — ولی این بار COUNT(*) هزار و دویست است.

پس همان دو خروجیِ یکسان از دو وضعیتِ کاملاً متفاوت آمدند، و تنها چیزی که آن دو را از هم جدا می‌کند COUNT(*) کنارِ COUNT(col) است. هر جا یک تجمیع NULL داد، این دو شمارش را کنارش بگذار؛ بدونِ آن‌ها نمی‌دانی با کدام‌یک طرفی.

۵. تجمیع روی یک عبارت، نه فقط یک ستون#

هر چیزی که در SELECT می‌شود نوشت، در یک تابعِ تجمیعی هم می‌شود گذاشت. این کوچک به نظر می‌رسد و در عمل نصفِ کارِ گزارش‌نویسی است.

print(q("""
    SELECT SUM(pages)             AS all_pages,
           SUM(pages * borrowed)  AS pages_read,
           SUM(rating >= 4.0)     AS four_and_up,
           SUM(rating IS NULL)    AS unrated,
           AVG(pages > 500)       AS share_thick
    FROM books
"""))

   all_pages  pages_read  four_and_up  unrated  share_thick
0     604558    47402053          416      231          0.5

سه ستونِ آخر با اولی فرق دارند و باید بدانی چرا: در SQLite یک شرط، مقدارِ ۱ یا ۰ می‌دهد. پس SUM(rating >= 4.0) یعنی «چند سطر این شرط برایشان درست بود» — یعنی یک شمارشِ شرطی، در همان پرسش، بدونِ WHERE. و AVG(شرط) مستقیم سهم را می‌دهد، چون میانگینِ یک ستون از صفر و یک همان نسبتِ یک‌هاست.

ستونِ آخر دقیقاً 0.5 درآمد، و همین باید مشکوکت کند: ۰٫۵ ضربدر ۱٬۲۰۰ یعنی ۶۰۰ کتاب، نصفِ دقیقِ جدول — و در این دادهٔ ساختگی یک تصادف. این را با یک SELECT COUNT(*) FROM books WHERE pages > 500ِ مستقل بسنج، نه با ضرب. قاعدهٔ عملی: هر عددِ گِردی را با یک شمارشِ مستقل بسنج، حتی وقتی خوشحالت می‌کند.

این الگو در ادامهٔ ترم مدام برمی‌گردد، چون می‌گذارد چند شمارشِ متفاوت را در یک پرسش کنارِ هم بگیری و مطمئن باشی همه‌شان دقیقاً از یک مجموعه سطر آمده‌اند.

⚠️ مواظب باش: SUM(rating >= 4.0) سطرهایی را که ratingشان NULL است نمی‌شمارد — شرط برایشان «نامعلوم» می‌شود، نه ۱ و نه ۰. پس ۴۱۶ به‌اضافهٔ سطرهای زیرِ چهار، ۱٬۲۰۰ نمی‌شود؛ ۹۶۹ می‌شود. همان تلهٔ ترمِ ۱ فصلِ ۶، این بار داخلِ یک تابعِ تجمیعی. فصلِ ۴ همین را به یک مسئلهٔ مخرج تبدیل می‌کند.

۶. جمعی که خطا نمی‌دهد و عددِ غلط برمی‌گرداند#

حالا پرسشِ اولِ فصل. price_text یک ستونِ TEXT است — ترمِ ۱ فصلِ ۵ نشان داد که مقدارهایش سه شکلِ خراب دارند: رشتهٔ خالی، کلمهٔ «نامشخص»، و قیمتِ کاملاً درستی که با رقمِ فارسی تایپ شده.

SUM روی چنین ستونی چه می‌کند؟ خطا نمی‌دهد.

naive = q("SELECT SUM(price_text) AS total, COUNT(*) AS rows_counted FROM books")
print(naive)
print()
print(q("""
    SELECT COUNT(*)                              AS rows_total,
           SUM(CAST(price_text AS INTEGER) > 0)  AS rows_that_helped,
           SUM(price_text = '')                  AS empty_string,
           SUM(price_text = 'نامشخص')            AS the_word
    FROM books
"""))

         total  rows_counted
0  340096000.0          1200

   rows_total  rows_that_helped  empty_string  the_word
0        1200              1031            65        38

عدد آمد، COUNT(*) هم ۱٬۲۰۰ گفت، و آن جمع فقط از ۱٬۰۳۱ سطر ساخته شده بود.

SUM هر مقداری را که نتواند به عدد تبدیل کند صفر می‌گیرد و جلو می‌رود — همان کاری که CAST در ترمِ ۱ کرد، این بار بدونِ اینکه اسمِ CAST جایی نوشته شده باشد. و COUNT(*) کنارش هیچ کمکی نمی‌کند، چون سطرها را می‌شمارد نه سهمشان را در جمع.

حالا رقم‌های فارسی را نجات بدهیم و ببینیم چقدر عوض می‌شود:

LATIN = "replace(replace(replace(replace(replace(price_text, '۰','0'), '۱','1'), '۲','2'), '۳','3'), '۴','4')"
LATIN = "replace(replace(replace(replace(replace(" + LATIN + ", '۵','5'), '۶','6'), '۷','7'), '۸','8'), '۹','9')"

print(q(f"""
    SELECT SUM(CAST({LATIN} AS INTEGER))       AS rescued_total,
           SUM(CAST({LATIN} AS INTEGER) > 0)   AS rows_that_helped
    FROM books
"""))

   rescued_total  rows_that_helped
0      361156000              1097

۳۴۰٬۰۹۶٬۰۰۰ شد ۳۶۱٬۱۵۶٬۰۰۰، و ۱٬۰۳۱ سطر شد ۱٬۰۹۷ سطر. بیست‌ویک میلیون تومان از ۶۶ کتابی آمد که قیمتشان از اول درست بود و فقط رقمشان فارسی بود.

و عددِ تازه هم هنوز جوابِ نهایی نیست: ۱٬۲۰۰ منهای ۱٬۰۹۷ می‌شود ۱۰۳ سطر که هنوز صفر حساب می‌شوند — ۶۵ رشتهٔ خالی و ۳۸ کلمهٔ «نامشخص». برای آن ۱۰۳ سطر هیچ عددی وجود ندارد که بشود در جمع گذاشت، و درست‌ترین کار این است که در گزارش بنویسی جمع روی ۱٬۰۹۷ کتاب از ۱٬۲۰۰ کتاب گرفته شده.

دقت کن که آن رشتهٔ LATIN متنِ خودِ پرسش است، نه یک مقدار. ساختنِ متنِ پرسش در پایتون مجاز است و اینجا فقط برای این است که ده replace تودرتو در یک خط جا نشوند؛ مقدارها همچنان با ? می‌روند و هیچ‌وقت به متنِ پرسش چسبانده نمی‌شوند.

📏 اندازه بگیر: یک سطر یعنی چه؟ خروجیِ هر پرسشِ این فصل یک سطر دارد و آن سطر یعنی «کلِ فهرست» — نه یک کتاب، نه یک نویسنده. حتی وقتی نتیجه صفر سطرِ ورودی داشت (دستهٔ «ورزش») باز هم یک سطرِ خروجی گرفتیم. با کدام شمارشِ دوم سنجیدی؟ سه‌تا: COUNT(*) در برابرِ COUNT(col) روی هر ستونی که رویش حساب کردیم؛ ۱۲۳ در برابرِ ۱۲۰ بعد از یکسان‌سازیِ نویسه؛ و ۱٬۰۳۱ در برابرِ ۱٬۰۹۷ در برابرِ ۱٬۲۰۰ برای جمعِ قیمت‌ها. چه کسی از قلم افتاد؟ ۱۰۳ کتاب که قیمتِ عددی ندارند و در هر جمعی صفر می‌شوند؛ ۲۳۱ کتاب که در هر شرطی روی rating نامعلوم می‌مانند؛ و همچنان ۲۴۰ سطرِ arrivals.csv که وارد نشده‌اند.

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

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

در فصل بعد#

تا اینجا هر تجمیعی از همهٔ سطرها یک عدد ساخت. فصلِ بعد GROUP BY را می‌آورد و همان یک عدد را به یک جدول تبدیل می‌کند: ۱٬۲۰۰ سطرِ کتاب به ۶ سطرِ شعبه، که پرشمارترینش ۲۲۴ کتاب دارد و کم‌شمارترینش ۱۸۰. و بلافاصله سراغِ سؤالی می‌رود که همه‌جا آدم را زمین می‌زند: چرا شرطی که با WHERE می‌نویسی و شرطی که با HAVING می‌نویسی دو چیزِ متفاوت‌اند.