نوع، عبارت، و تاریخی که متن است

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

دو پرسش می‌نویسیم که کلِ جدول را بینِ خودشان تقسیم می‌کنند: «چاپِ بیشتر از دوم» و «چاپِ دوم یا کمتر». جوابشان ۷۹۶ و ۴۰۴ است و جمعشان دقیقاً ۱۲۰۰ — یعنی حساب می‌خواند و هیچ سطری جا نیفتاده.

و هر دو عدد غلط‌اند. جواب‌های درست ۷۸۹ و ۴۱۱ هستند. هفت سطر در دستهٔ اشتباه نشسته‌اند، چون مقدارشان در ستونی که INTEGER اعلام شده، متن ذخیره شده است.

این فصل دربارهٔ همان چیزی است که این هفت سطر را ساخت: نوعِ مقدار. و در راه، مقایسه‌ای را می‌بینیم که ۳۸۴ جواب می‌دهد جای ۲۰۲، و آخرش با یک تابعِ ساده آن ۱۲۳ نویسندهٔ فصلِ قبل را به ۱۲۰ برمی‌گردانیم.

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

• نوعِ هر مقدار را با typeof ببینی و بگویی چرا نوعِ ستون تضمینش نمی‌کند
• بگویی مقایسهٔ متن با عدد در SQLite چه می‌کند، و با CAST درستش کنی
• تقسیمِ صحیح را بشناسی و جلوی صفر شدنِ ناخواسته را بگیری
• با ROUND و CASE WHEN خروجیِ خواندنی بسازی
• تاریخِ ISO-8601 را با date() و strftime بخوانی و بدانی کجا بی‌صدا اشتباه می‌کند

قبل از شروع#

از فصلِ ۴: آن ۱۲۳ نامِ یکتا که ۱۲۰ نفر بودند. این فصل با SQL درستش می‌کند.

یک جمله که کلِ فصل روی آن سوار است: در SQLite نوع، صفتِ مقدار است نه صفتِ ستون. در بیشترِ پایگاه داده‌های دیگر برعکس است، و همین یک تفاوت منشأِ همهٔ تله‌های این فصل است.

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

۱. پنج نوع، و تابعی که نوعِ هر مقدار را می‌گوید#

SQLite پنج نوعِ ذخیره‌سازی دارد: INTEGER (عددِ صحیح)، REAL (عددِ اعشاری)، TEXT (متن)، BLOB (بایتِ خام) و NULL (نبودِ مقدار). typeof(x) نوعِ همان مقدار را در همان سطر برمی‌گرداند.

print(q("""
    SELECT typeof(id)         AS t_id,
           typeof(pages)      AS t_pages,
           typeof(rating)     AS t_rating,
           typeof(price_text) AS t_price,
           typeof(edition)    AS t_edition
    FROM books
    LIMIT 3
"""))

      t_id  t_pages t_rating t_price t_edition
0  integer  integer     real    text   integer
1  integer  integer     real    text   integer
2  integer  integer     null    text   integer

سه سطر، و ستونِ t_rating در سطرِ سوم null است در حالی که در دو سطرِ بالا real بود. یعنی نوع واقعاً سطر به سطر فرق می‌کند. این همان NULLی است که در فصل‌های قبل چند بار سراغش رفتیم و فصلِ بعد کاملاً بازش می‌کند.

۲. type affinity: ستونی که INTEGER اعلام شده و متن دارد#

وقتی جدول را ساختیم، edition را INTEGER اعلام کردیم. SQLite این را یک گرایش (affinity) می‌فهمد، نه یک قانون: سعی می‌کند مقدارِ ورودی را به عدد تبدیل کند؛ اگر نتوانست، همان‌طور که هست ذخیره می‌کند و خطا نمی‌دهد.

رشتهٔ '3' به عددِ ۳ تبدیل می‌شود. رشتهٔ '۳' — همان سه، با رقمِ فارسی — تبدیل نمی‌شود، چون SQLite رقمِ فارسی را عدد نمی‌شناسد. پس متن می‌ماند.

for kind in ("integer", "text", "real", "null"):
    n = int(q("SELECT COUNT(*) FROM books WHERE typeof(edition) = ?", (kind,)).iloc[0, 0])
    print(f"  edition از نوع {kind:<8}: {n:>5} سطر")

  edition از نوع integer :  1163 سطر
  edition از نوع text    :    37 سطر
  edition از نوع real    :     0 سطر
  edition از نوع null    :     0 سطر

سی‌وهفت سطر از ۱٬۲۰۰ سطر در ستونی که «عدد» اعلام شده، متن‌اند. ببینیمشان:

print(q("""
    SELECT id, edition, typeof(edition) AS kind
    FROM books
    WHERE typeof(edition) = ?
    ORDER BY id
    LIMIT 5
""", ("text",)))

    id edition  kind
0   14       ۴  text
1   31       ۳  text
2   46       ۵  text
3  138       ۶  text
4  191       ۴  text

با چشم که نگاه می‌کنی، این‌ها عددند. برای SQLite رشته‌اند.

۳. ۷۹۶ و ۴۰۴، که جمعشان درست است و هر دو غلط‌اند#

حالا سؤالِ واقعی: چند کتاب چاپِ بیشتر از دوم دارند؟

above = int(q("SELECT COUNT(*) FROM books WHERE edition > ?", (2,)).iloc[0, 0])
below = int(q("SELECT COUNT(*) FROM books WHERE edition <= ?", (2,)).iloc[0, 0])
text_rows = int(q("SELECT COUNT(*) FROM books WHERE typeof(edition) = ?", ("text",)).iloc[0, 0])
text_small = int(q("""
    SELECT COUNT(*) FROM books
    WHERE typeof(edition) = ? AND edition IN (?, ?)
""", ("text", "۱", "۲")).iloc[0, 0])

print("edition > 2  :", above)
print("edition <= 2 :", below)
print("جمع دو تکه   :", above + below, "| کل جدول: 1200")
print()
print("از سطرهای دستهٔ اول، چندتا متن‌اند؟", text_rows)
print("و از همان‌ها چندتا واقعاً ۱ یا ۲ هستند؟", text_small)
print("پس جواب درست این است  →  بیشتر از ۲:", above - text_small,
      "| کمتر یا مساوی ۲:", below + text_small)

edition > 2  : 796
edition <= 2 : 404
جمع دو تکه   : 1200 | کل جدول: 1200

از سطرهای دستهٔ اول، چندتا متن‌اند؟ 37
و از همان‌ها چندتا واقعاً ۱ یا ۲ هستند؟ 7
پس جواب درست این است  →  بیشتر از ۲: 789 | کمتر یا مساوی ۲: 411

اینجا مهم‌ترین درسِ کلِ ترم است: شمارشِ کنترلیِ «جمع دو تکه باید کل بشود» کاملاً پاس شد، و هر دو عدد غلط بودند.

چرا هر ۳۷ سطرِ متنی در دستهٔ «بیشتر از ۲» افتادند؟ چون SQLite وقتی متن را با عدد مقایسه می‌کند، همیشه متن را بزرگ‌تر می‌داند — قاعده‌ای ثابت و مستند: NULL < عدد < متن < BLOB. پس '۱' > 2 هم درست است.

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

✅ چک کن: ۷۸۹ + ۴۱۱ باید بشود ۱۲۰۰، و ۷۹۶ − ۷ باید بشود ۷۸۹. اگر عددهایت فرق کرد، احتمالاً IN (?, ?) را با رقمِ انگلیسی نوشته‌ای؛ آن سطرها با رقمِ فارسی ذخیره شده‌اند.

۴. مقایسهٔ متن با عدد، و CAST#

همان قاعده روی ستونِ price_text — که واقعاً و کاملاً متن است — نتیجهٔ بدتری می‌دهد، چون آنجا متن با متن مقایسه می‌شود و مقایسهٔ متنی حرف‌به‌حرف است، نه عددی.

as_text = int(q("SELECT COUNT(*) FROM books WHERE price_text > ?", ("500000",)).iloc[0, 0])
as_number = int(q("""
    SELECT COUNT(*) FROM books WHERE CAST(price_text AS INTEGER) > ?
""", (500000,)).iloc[0, 0])

print("price_text > '500000'            :", as_text)
print("CAST(price_text AS INTEGER) > ... :", as_number)
print("اختلاف                            :", as_text - as_number, "سطر")
print()
print(q("SELECT ? > ? AS text_compare, CAST(? AS INTEGER) > ? AS number_compare",
        ("99000", "500000", "99000", 500000)))

price_text > '500000'            : 384
CAST(price_text AS INTEGER) > ... : 202
اختلاف                            : 182 سطر

   text_compare  number_compare
0             1               0

صد و هشتاد و دو سطر اختلاف، و خطِ آخر کلِ دلیل را در دو عدد نشان می‌دهد.

'99000' > '500000' درست است. چون مقایسهٔ متنی از چپ شروع می‌کند و می‌بیند '9' بزرگ‌تر از '5' است — همین‌جا تمام. طولِ رشته اصلاً بررسی نمی‌شود. یعنی همهٔ کتاب‌های ۹۰ تا ۹۹ هزار تومانی در فهرستِ «گران‌تر از ۵۰۰ هزار» نشستند.

CAST(x AS INTEGER) مقدار را به عدد تبدیل می‌کند — ولی آنچه با مقدارهای غیرِعددی می‌کند خودش یک تلهٔ تازه است:

print(q("""
    SELECT CAST('123' AS INTEGER)     AS ascii_digits,
           CAST('۱۲۳' AS INTEGER)     AS persian_digits,
           CAST('' AS INTEGER)        AS empty_string,
           CAST('نامشخص' AS INTEGER)  AS a_word
"""))
print()
empty = int(q("SELECT COUNT(*) FROM books WHERE price_text = ?", ("",)).iloc[0, 0])
word = int(q("SELECT COUNT(*) FROM books WHERE price_text = ?", ("نامشخص",)).iloc[0, 0])
zeroed = int(q("SELECT COUNT(*) FROM books WHERE CAST(price_text AS INTEGER) = ?",
               (0,)).iloc[0, 0])
print("رشته خالی            :", empty)
print("کلمه «نامشخص»        :", word)
print("بعد از CAST صفر شدند :", zeroed)
print("یعنی رقم فارسی داشتند:", zeroed - empty - word)

   ascii_digits  persian_digits  empty_string  a_word
0           123               0             0       0

رشته خالی            : 65
کلمه «نامشخص»        : 38
بعد از CAST صفر شدند : 169
یعنی رقم فارسی داشتند: 66

CAST هرگز خطا نمی‌دهد. هر چیزی را که نتواند بخواند، صفر می‌کند.

۱۶۹ سطر بعد از CAST صفر شدند و اسمِ هیچ‌کدامشان «صفر تومان» نیست: ۶۵تا رشتهٔ خالی، ۳۸تا کلمهٔ «نامشخص»، و ۶۶تا قیمتِ کاملاً معتبر که فقط با رقمِ فارسی تایپ شده است.

و حالا ببین این ۱۶۹ صفرِ ساختگی با یک میانگین چه می‌کند:

naive = float(q("SELECT AVG(CAST(price_text AS INTEGER)) FROM books").iloc[0, 0])
careful = float(q("""
    SELECT AVG(CAST(price_text AS INTEGER)) FROM books
    WHERE CAST(price_text AS INTEGER) > ?
""", (0,)).iloc[0, 0])
print("میانگین قیمت، با صفرها  :", naive)
print("میانگین قیمت، بدون صفرها:", careful)
print("اختلاف                  :", round(careful - naive))

میانگین قیمت، با صفرها  : 283413.3333333333
میانگین قیمت، بدون صفرها: 329870.0290979631
اختلاف                  : 46457

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

۵. تقسیمِ صحیح#

print(q("SELECT 7/2 AS a, 7.0/2 AS b, 7/2.0 AS c, -7/2 AS d"))
print()
print(q("""
    SELECT title, pages, pages/100 AS shelves_wrong, pages/100.0 AS shelves_right
    FROM books
    LIMIT 3
"""))

   a    b    c  d
0  3  3.5  3.5 -3

         title  pages  shelves_wrong  shelves_right
0    روزهای پل    730              7           7.30
1  مه در باران    564              5           5.64
2   روزهای خاک    907              9           9.07

عدد تقسیم بر عدد، عددِ صحیح می‌دهد. 7/2 می‌شود ۳ نه ۳٫۵، و اعشار حذف می‌شود نه گرد. برای منفی هم به‌سمتِ صفر بریده می‌شود: -7/2 می‌شود ۳- نه ۴-.

راهِ‌حل ساده است: یک طرف را اعشاری کن. pages/100.0 یا CAST(pages AS REAL)/100.

🌱 ریشه‌اش کجاست: تقسیم دو سؤالِ کاملاً متفاوت است — «بینِ چند نفر پخش کنم؟» و «چند دستهٔ چندتایی می‌شود؟» — و باقی‌مانده در هرکدام معنیِ متفاوتی دارد. تقسیمِ صحیحِ SQL جوابِ سؤالِ دوم را می‌دهد و باقی‌مانده را دور می‌ریزد. اگر این تمایز برایت تازه است، ریشه ترمِ ۱ فصل ۵ با دوازده شکلات روی میز کاملاً بازش می‌کند.

۶. ROUND و CASE WHEN: خروجیِ خواندنی#

فصلِ ۲ به عددِ 3.7868937048503613 رسید و گفت بعداً درستش می‌کنیم. ROUND(x, n) همان است:

print(q("""
    SELECT ROUND(AVG(rating), 2)  AS rating_2,
           ROUND(AVG(pages), 1)   AS pages_1,
           ROUND(AVG(pages))      AS pages_0
    FROM books
"""))

   rating_2  pages_1  pages_0
0      3.79    503.8    504.0

⚠️ مواظب باش: ROUND را فقط در آخرین قدم و فقط برای نمایش به‌کار ببر. اگر عددی را گرد کنی و بعد رویش حساب کنی، خطا انباشته می‌شود. و دقت کن که ROUND(x) بدونِ رقمِ دوم، خروجیِ REAL می‌دهد — 504.0 نه 504.

CASE WHEN یک زنجیرهٔ شرط است که به‌جای عدد، برچسب می‌سازد:

print(q("""
    SELECT title,
           borrowed,
           CASE
               WHEN borrowed = 0  THEN 'هرگز امانت نرفته'
               WHEN borrowed < 20 THEN 'کم‌امانت'
               ELSE 'پرامانت'
           END AS band
    FROM books
    ORDER BY id
    LIMIT 4
"""))

         title  borrowed      band
0    روزهای پل        33   پرامانت
1  مه در باران        20   پرامانت
2   روزهای خاک         1  کم‌امانت
3   مه پشت آتش        86   پرامانت

شرط‌ها از بالا به پایین امتحان می‌شوند و اولین شرطی که درست باشد برنده است. به سطرِ دوم نگاه کن: borrowed برابرِ ۲۰ است و برچسبش «پرامانت» شد، چون شرطِ < 20 برای ۲۰ درست نیست. مرزها را همیشه با یک نمونهٔ دقیقاً روی مرز امتحان کن.

۷. متن را دستکاری کن — و ۱۲۳ دوباره ۱۲۰ شود#

|| دو متن را به هم می‌چسباند. lower حروف را کوچک می‌کند، trim فاصله‌های دو سر را می‌برد، substr(x, start, len) تکه‌ای از متن را برمی‌دارد و length طولش را می‌دهد.

print(q("""
    SELECT title || ' — ' || author AS label,
           lower('SQLite')          AS lowered,
           trim('   sqlite   ')     AS trimmed,
           substr(added_at, 1, 4)   AS added_year,
           length(title)            AS title_length
    FROM books
    ORDER BY id
    LIMIT 2
"""))

                      label lowered trimmed added_year  title_length
0    روزهای پل — مهدی سلیمی  sqlite  sqlite       2022             9
1  مه در باران — رویا رحیمی  sqlite  sqlite       2019            11

⚠️ مواظب باش: lower و upper در SQLite فقط روی حروفِ ASCII کار می‌کنند. روی متنِ فارسی هیچ کاری نمی‌کنند و هیچ خطایی هم نمی‌دهند. این محدودیتِ مستندِ موتور است، نه باگ.

و حالا replace(x, old, new) — تابعی که فصلِ ۴ منتظرش بود:

raw = len(q("SELECT DISTINCT author FROM books"))
fixed = len(q("""
    SELECT DISTINCT replace(replace(author, 'ي', 'ی'), 'ك', 'ک') AS author
    FROM books
"""))
maryam = int(q("""
    SELECT COUNT(*) FROM books
    WHERE replace(replace(author, 'ي', 'ی'), 'ك', 'ک') = ?
""", ("مریم کریمی",)).iloc[0, 0])

print("نام‌های یکتا، خام        :", raw)
print("نام‌های یکتا، یکسان‌شده  :", fixed)
print("کتاب‌های «مریم کریمی»    :", maryam)

نام‌های یکتا، خام        : 123
نام‌های یکتا، یکسان‌شده  : 120
کتاب‌های «مریم کریمی»    : 9

۱۲۳ شد ۱۲۰، و ۳ شد ۹. همان عددهایی که فصلِ ۴ اشتباهشان را نشان داد، حالا درست‌اند.

ولی این یک وصله است، نه درمان. هر پرسشی که از این به بعد روی author بنویسی باید همان دو replace را داشته باشد، و اولین باری که یادت برود، عددت دوباره غلط می‌شود. درمانِ واقعی این است که داده یک بار موقعِ ورود پاک شود — که کارِ فصلِ ۷ است.

۸. تاریخی که متن است#

SQLite نوعِ «تاریخ» ندارد. تاریخ‌ها متن‌اند، به‌شکلِ YYYY-MM-DD. و این تصادفی نیست: در این قالب، مرتب‌سازیِ متنی دقیقاً همان مرتب‌سازیِ زمانی است، چون سال قبل از ماه و ماه قبل از روز می‌آید و همه‌شان طولِ ثابت دارند.

date(x, ...) روی تاریخ حساب می‌کند و strftime(fmt, x) تکه‌ای از آن را بیرون می‌کشد.

print(q("""
    SELECT added_at,
           date(added_at, '+30 days')  AS due,
           strftime('%Y', added_at)    AS year_part,
           strftime('%m', added_at)    AS month_part
    FROM books
    ORDER BY id
    LIMIT 3
"""))
print()
print("کتاب‌هایی که در ۲۰۲۴ به فهرست اضافه شدند:",
      int(q("SELECT COUNT(*) FROM books WHERE strftime('%Y', added_at) = ?",
            ("2024",)).iloc[0, 0]))

     added_at         due year_part month_part
0  2022-07-06  2022-08-05      2022         07
1  2019-03-16  2019-04-15      2019         03
2  2020-11-03  2020-12-03      2020         11

کتاب‌هایی که در ۲۰۲۴ به فهرست اضافه شدند: 178

strftime متن برمی‌گرداند، نه عدد — به همین دلیل مقایسه‌اش را با '2024' نوشتیم و نه با 2024. اگر عددی مقایسه‌اش کنی، همان قاعدهٔ «عدد کوچک‌تر از متن است» را می‌خوری و صفر سطر می‌گیری.

و یک هشدارِ آخر که در کارِ واقعی گران تمام می‌شود:

print(q("""
    SELECT date('2024-02-30') AS overflow,
           date('2024-13-45') AS nonsense,
           date('۱۴۰۳-۰۱-۰۱') AS persian_digits
"""))

     overflow nonsense persian_digits
0  2024-03-01     None           None

سی‌ام فوریه وجود ندارد و SQLite بی‌صدا اولِ مارس تحویل داد. نه خطا، نه هشدار — یک تاریخِ کاملاً معتبر که تاریخِ تو نیست. دو مقدارِ دیگر NULL شدند، که دستِ‌کم صادقانه است.

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

🔧 اگر کار نکرد: اگر از پایگاه دادهٔ دیگری آمده‌ای، احتمالاً اولین کاری که می‌کنی این است که تابعِ آشنایت را صدا بزنی:

try:
    q("SELECT DATEDIFF(added_at, added_at) FROM books LIMIT 1")
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)

pandas.errors.DatabaseError
Execution failed on sql 'SELECT DATEDIFF(added_at, added_at) FROM books LIMIT 1': no such function: DATEDIFF

SQLite تابعِ DATEDIFF ندارد. معادلش julianday(a) - julianday(b) است، که فاصله را برحسبِ روز — با اعشار — می‌دهد. این یکی از جاهایی است که گویشِ SQL واقعاً بینِ موتورها فرق می‌کند.

🤖 از دستیارت بپرس: «type affinity در SQLite یعنی چه و چه فرقی با نوعِ سخت‌گیرانه در پایگاه داده‌های دیگر دارد؟» بعد این را بپرس: «چطور کاری کنم که یک ستون فقط عددِ صحیح بپذیرد و اگر متن دادم خطا بدهد؟» — جواب یک قید در تعریفِ جدول است، و ترمِ ۴ دقیقاً همان را می‌سازد.

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

در فصل بعد#

سه فصل است می‌گوییم «فصلِ ۶ دربارهٔ NULL است» و هر بار یک تکه‌اش را نشان داده‌ایم: ۲۳۱ سطری که AVG نشمرد، = NULL که صفر جواب داد، و همین حالا typeof که در یک سطر null برگرداند. فصلِ بعد همه‌شان را یک‌جا باز می‌کند — و با عددی تمام می‌شود که کلِ روایتِ این ترم را عوض می‌کند: آن ۲۳۱ کتابِ بی‌امتیاز تصادفی انتخاب نشده‌اند؛ بیشترین امانتِ کلِ آن گروه ۲ است.