تعداد ردیف JOIN در SQLite؛ سطح رکورد حسگر، NULL و شمارش درست

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

۱. هر ردیف دقیقاً نمایندهٔ چیست؟

جدول device یک ردیف برای هر دستگاه دارد؛ شناسه‌های یک، دو و سه یکتا هستند. جدول sample یک ردیف برای هر اندازه‌گیری دارد: نمونهٔ یازده برای دستگاه یک، مقدار ۱۲۰۰ میلی‌ولت؛ نمونهٔ دوازده برای همان دستگاه، مقدار نامعلوم؛ نمونهٔ بیست‌ویک برای دستگاه دو، مقدار ۲۱۰۰ میلی‌ولت. دستگاه سه هنوز نمونه ندارد. مقدار نامعلوم با NULL ذخیره شده و به معنی ولتاژ صفر نیست.

کلید اصلی sample شناسهٔ نمونه را یکتا می‌کند، نه شناسهٔ دستگاه را. کلید خارجی فقط می‌گوید دستگاهِ ارجاع‌داده‌شده باید وجود داشته باشد؛ دو نمونهٔ متعلق به یک دستگاه کاملاً مجازند. بنابراین رابطه یک‌به‌چند است. اگر نیازمندی «حداکثر یک نمونه برای هر دستگاه» بود، قید و معنای جدول باید عوض می‌شد؛ از وجود PK و FK به‌تنهایی چنین محدودیتی نتیجه نمی‌شود.

همهٔ مقادیر اندازه‌گیریِ حاضر، عدد صحیحِ ازپیش‌تعیین‌شده برحسب میلی‌ولت‌اند و None پایتون به SQL NULL می‌رسد. این مثال رابط تازه‌ای برای اعتبارسنجی نوع ورودی، کالیبراسیون یا صحت فیزیکی حسگر نیست. عبارت سطح رکورد یا grain را برای همین معنای «یک ردیف نمایندهٔ یک چیز» استفاده می‌کنیم؛ پیش از جمع، شمارش یا اتصال، باید این معنا روشن باشد.

۲. INNER و LEFT تعداد دستگاه را حفظ نمی‌کنند

بخش تشکیل JOIN در SQLite SELECT توضیح می‌دهد که شرط اتصال، زوج‌های سازگار دو ورودی را نگه می‌دارد. INNER JOIN دستگاه و نمونه در مثال ما سه ردیف می‌دهد؛ دستگاه یک دو بار، دستگاه دو یک بار و دستگاه سه هیچ بار دیده می‌شود. این تکرار دستگاه یک، نتیجهٔ درستِ سطح نمونه است و با تکراری‌بودن کلید اصلی دستگاه تناقضی ندارد.

LEFT JOIN علاوه بر سه زوج واقعی، یک ردیف برای دستگاه سه با ستون‌های NULL سمت نمونه می‌سازد؛ خروجی چهار ردیف دارد. دو نوع تهی‌شدن را جدا ببینید: در نمونهٔ دوازده، شناسهٔ نمونه موجود ولی مقدار ولتاژ NULL است. در ردیف دستگاه سه، خودِ نمونه‌ای وجود ندارد و شناسهٔ سمت راست نیز NULL شده است. فقط دیدن NULL در ولتاژ این دو حالت را از هم جدا نمی‌کند.

چون sample.id در جدول واقعی کلید اصلیِ غیرتهیِ این ساختار است، شرط s.id IS NULL روی این LEFT JOIN فقط دستگاه بدون نمونه را پیدا می‌کند. شرط s.voltage_mv IS NULL هم آن دستگاه و هم نمونهٔ واقعیِ فاقد مقدار را می‌آورد. معنای ستونِ انتخاب‌شده مهم است؛ استفاده از هر ستون nullable برای تشخیص «نبودن فرزند» نتیجهٔ مشابهی نمی‌دهد.

۳. شمارش و فیلتر را در جای درست بخوانیم

طبق تعریف count در SQLite، COUNT(*) همهٔ ردیف‌های گروه و COUNT(X) فقط مقدارهای غیرNULLِ X را می‌شمارد. برای دستگاه یک، تعداد ردیف، نمونه و ولتاژ معلوم به‌ترتیب دو، دو و یک است. برای دستگاه دو، هر سه یک‌اند. برای دستگاه سه، COUNT(*) یک ولی شمارش شناسهٔ نمونه و ولتاژ هر دو صفر می‌شوند؛ ردیفِ گسترش‌یافتهٔ LEFT علت این تفاوت است.

جای شرط هم نتیجه را عوض می‌کند. با WHERE s.voltage_mv>=۱۵۰۰ فقط نمونهٔ بیست‌ویکِ دستگاه دو باقی می‌ماند. شرط برای NULL نامعلوم است و WHERE آن را نگه نمی‌دارد. اگر همین شرط در ON باشد، دستگاه‌های یک و سه پس از نیافتن نمونهٔ مناسب، با سمت راست NULL حفظ می‌شوند. تبدیل مکان شرط در LEFT JOIN یک بازنویسی هم‌معنا نیست.

قواعد NULL و IS در SQLite نشان می‌دهد NULL=NULL پاسخ نامعلوم دارد، درحالی‌که NULL IS NULL درست است. استثناهای منطقی نیز مهم‌اند: صفر AND NULL نادرست و یک OR NULL درست می‌شود. نمی‌گوییم هر عبارتِ دارای NULL حتماً NULL است. همچنین شرط «ولتاژ نابرابر ۱۲۰۰» مقدار نامعلوم را به‌عنوان یک مقدار متفاوت انتخاب نمی‌کند.

"""Original SQLite sensor-table grain, NULL and join fanout acceptance lesson."""
import itertools
import json
import platform
import sqlite3


DEVICES = [(1, 'A'), (2, 'B'), (3, 'C')]
SAMPLES = [(11, 1, 1200), (12, 1, None), (21, 2, 2100)]
SERVICES = [(101, 1), (102, 1), (201, 2)]
SELECT_ROWS = 'SELECT d.id,s.id,s.voltage_mv FROM device AS d {join} sample AS s ON s.device_id=d.id ORDER BY d.id,s.id'
COUNTS = '''SELECT d.id,COUNT(*),COUNT(s.id),COUNT(s.voltage_mv)
            FROM device AS d LEFT JOIN sample AS s ON s.device_id=d.id
            GROUP BY d.id ORDER BY d.id'''


def fixture(samples=SAMPLES):
    con = sqlite3.connect(':memory:', autocommit=True)
    con.execute('PRAGMA foreign_keys=ON')
    con.execute('CREATE TABLE device(id INTEGER PRIMARY KEY,name TEXT NOT NULL)')
    con.execute('CREATE TABLE sample(id INTEGER PRIMARY KEY,device_id INTEGER NOT NULL REFERENCES device(id),voltage_mv INTEGER)')
    con.execute('CREATE TABLE service(id INTEGER PRIMARY KEY,device_id INTEGER NOT NULL REFERENCES device(id))')
    con.executemany('INSERT INTO device VALUES(?,?)', DEVICES)
    con.executemany('INSERT INTO sample VALUES(?,?,?)', samples)
    con.executemany('INSERT INTO service VALUES(?,?)', SERVICES)
    return con


def independent_rows(samples, left):
    rows = []
    for device_id, _ in DEVICES:
        matches = [(device_id, sid, value) for sid, owner, value in samples if owner == device_id]
        if matches:
            rows.extend(matches)
        elif left:
            rows.append((device_id, None, None))
    return sorted(rows, key=lambda r: (r[0], -1 if r[1] is None else r[1]))


def _raises(expected, function, *args):
    try:
        function(*args)
    except expected:
        return True
    return False


def run_example():
    con = fixture()
    try:
        inner = con.execute(SELECT_ROWS.format(join='INNER JOIN')).fetchall()
        left = con.execute(SELECT_ROWS.format(join='LEFT JOIN')).fetchall()
        counts = con.execute(COUNTS).fetchall()
        where_filter = con.execute('SELECT d.id,s.id FROM device d LEFT JOIN sample s ON s.device_id=d.id WHERE s.voltage_mv>=? ORDER BY d.id,s.id', (1500,)).fetchall()
        on_filter = con.execute('SELECT d.id,s.id FROM device d LEFT JOIN sample s ON s.device_id=d.id AND s.voltage_mv>=? ORDER BY d.id,s.id', (1500,)).fetchall()
        null_value_rows = con.execute('SELECT d.id,s.id FROM device d LEFT JOIN sample s ON s.device_id=d.id WHERE s.voltage_mv IS NULL ORDER BY d.id,s.id').fetchall()
        missing_child = con.execute('SELECT d.id FROM device d LEFT JOIN sample s ON s.device_id=d.id WHERE s.id IS NULL ORDER BY d.id').fetchall()
        null_truth = con.execute('SELECT NULL=NULL,NULL<>1200,NULL IS NULL,0 AND NULL,1 OR NULL').fetchone()
        fanout = con.execute('SELECT d.id,s.id,t.id FROM device d INNER JOIN sample s ON s.device_id=d.id INNER JOIN service t ON t.device_id=d.id ORDER BY d.id,s.id,t.id').fetchall()
        fanout_counts = con.execute('SELECT COUNT(s.id),COUNT(DISTINCT s.id) FROM device d JOIN sample s ON s.device_id=d.id JOIN service t ON t.device_id=d.id').fetchone()
        summaries = con.execute('''WITH sc AS (SELECT device_id,COUNT(*) AS n FROM sample GROUP BY device_id),
                                  tc AS (SELECT device_id,COUNT(*) AS n FROM service GROUP BY device_id)
                                  SELECT d.id,COALESCE(sc.n,0),COALESCE(tc.n,0) FROM device d
                                  LEFT JOIN sc ON sc.device_id=d.id LEFT JOIN tc ON tc.device_id=d.id ORDER BY d.id''').fetchall()
        oracle_cases = []
        for sizes in itertools.product(range(3), repeat=3):
            samples = [(device_id*100+i,device_id,None if i==1 else 1000+device_id) for device_id,size in enumerate(sizes,1) for i in range(size)]
            ref = fixture(samples)
            try:
                expected_counts = [(did,max(1,n),n,sum(value is not None for _,owner,value in samples if owner==did)) for did,n in enumerate(sizes,1)]
                oracle_cases.append(ref.execute(SELECT_ROWS.format(join='INNER JOIN')).fetchall()==independent_rows(samples,False)
                                    and ref.execute(SELECT_ROWS.format(join='LEFT JOIN')).fetchall()==independent_rows(samples,True)
                                    and ref.execute(COUNTS).fetchall()==expected_counts)
            finally:
                ref.close()
        duplicate_rejected = _raises(sqlite3.IntegrityError,con.execute,'INSERT INTO sample VALUES(?,?,?)',(11,1,999))
        orphan_rejected = _raises(sqlite3.IntegrityError,con.execute,'INSERT INTO sample VALUES(?,?,?)',(99,99,999))
        checks = {
            'foreign_keys_explicitly_enabled_for_authored_fixture': con.execute('PRAGMA foreign_keys').fetchone()==(1,),
            'sample_primary_key_rejects_duplicate_identity': duplicate_rejected,
            'foreign_key_rejects_nonexistent_device_but_not_multiple_samples': orphan_rejected and con.execute('SELECT COUNT(*) FROM sample WHERE device_id=1').fetchone()==(2,),
            'inner_join_hand_three_measurement_rows': inner==[(1,11,1200),(1,12,None),(2,21,2100)],
            'left_join_hand_four_rows_including_unmatched_device': left==[(1,11,1200),(1,12,None),(2,21,2100),(3,None,None)],
            'count_star_child_id_and_known_value_have_distinct_hand_answers': counts==[(1,2,2,1),(2,1,1,1),(3,1,0,0)],
            'stored_missing_voltage_retains_actual_sample_identity': (1,12,None)in left and (3,None,None)in left,
            'is_null_voltage_cannot_alone_identify_absent_sample': null_value_rows==[(1,12),(3,None)],
            'nonnullable_child_identity_finds_only_unmatched_parent': missing_child==[(3,)],
            'where_filter_discards_false_and_unknown_outer_rows': where_filter==[(2,21)],
            'on_filter_preserves_unmatched_parents_after_predicate': on_filter==[(1,None),(2,21),(3,None)],
            'three_valued_null_comparison_and_boolean_exceptions': null_truth==(None,None,1,0,1),
            'two_one_to_many_joins_multiply_hand_child_pairs': fanout==[(1,11,101),(1,11,102),(1,12,101),(1,12,102),(2,21,201)],
            'count_distinct_identity_repairs_this_count_not_general_join_grain': fanout_counts==(5,3),
            'preaggregated_children_produce_one_summary_per_device': summaries==[(1,2,2),(2,1,1),(3,0,0)],
            'independent_python_nested_loop_and_count_oracle_twenty_seven_fixtures': len(oracle_cases)==27 and all(oracle_cases),
            'unknown_not_equal_value_is_not_retained_by_where': con.execute('SELECT id FROM sample WHERE voltage_mv<>1200 ORDER BY id').fetchall()==[(21,)],
            'read_queries_and_failed_constraint_statements_leave_original_rows': con.execute('SELECT * FROM device ORDER BY id').fetchall()==DEVICES
                and con.execute('SELECT * FROM sample ORDER BY id').fetchall()==SAMPLES and con.execute('SELECT * FROM service ORDER BY id').fetchall()==SERVICES,
        }
        assert len(checks)==18 and all(type(v)is bool and v for v in checks.values())
        return {'runtime':{'python':platform.python_version(),'sqlite_runtime':sqlite3.sqlite_version,'storage':':memory: only; no existing database opened'},
                'contract':{'grain':'device one per device; sample one per measurement; service one per service record',
                            'voltage':'predefined integer millivolts or SQL NULL meaning unknown, not zero',
                            'ordering':'explicit ORDER BY device id/sample id/service id; no order inferred from storage'},
                'metrics':{'inner_rows':inner,'left_rows':left,'count_columns':['device_id','COUNT(*)','COUNT(sample.id)','COUNT(voltage_mv)'],'counts':counts,
                           'where_filtered_rows':where_filter,'on_filtered_rows':on_filter,'null_voltage_rows':null_value_rows,'missing_child_devices':missing_child,
                           'fanout_rows':fanout,'fanout_count_and_distinct_sample_count':fanout_counts,'preaggregated_counts':summaries,'independent_fixture_count':len(oracle_cases)},
                'checks':checks,
                'limitations':['Synthetic fixed relational fixtures only; PK/FK ensure identity/reference, not one sample per device or actual measurement validity.',
                               'No ingestion type API, query plan/performance, concurrent database, materialized reporting or physical sensor calibration claim.',
                               'COUNT(DISTINCT sample.id) fixes this particular identity count; it is not a universal repair for duplicated aggregates or mixed table grains.',
                               'COALESCE of missing preaggregated counts to zero does not replace unknown measured voltage by zero.']}
    finally:
        con.close()


if __name__=='__main__':
    print(json.dumps(run_example(),ensure_ascii=False,indent=2))

۴. اتصال دو وابستگی و مرجع مستقل

جدول service برای دستگاه یک دو ردیف و برای دستگاه دو یک ردیف دارد. اگر دستگاه، نمونه و خدمت را مستقیم INNER JOIN کنیم، دو نمونهٔ دستگاه یک با دو خدمت آن چهار زوج می‌سازند؛ با زوج دستگاه دو، مجموع پنج ردیف می‌شود. COUNT(s.id) اکنون پنج است، درحالی‌که فقط سه نمونهٔ اصلی داریم. یکی از ریشه‌های شمارش بادکرده در گزارش مهندسی همین ترکیب دو رابطهٔ یک‌به‌چند است.

COUNT(DISTINCT s.id) در همین پرسشِ شمارشِ شناسه، سه می‌دهد؛ این راه، درمان عمومیِ همهٔ مجموع‌ها و گزارش‌ها نیست. در نسخهٔ دیگر کد، نمونه و خدمت را جدا تا سطح دستگاه می‌شماریم و بعد دو خلاصه را به device وصل می‌کنیم. نتیجه برای دستگاه‌ها، تعداد نمونه و خدمتِ دو/دو، یک/یک و صفر/صفر است. COALESCE فقط شمارشِ خلاصهٔ غایب را صفر می‌کند؛ ولتاژ نامعلوم را صفر نمی‌سازیم.

{
  "runtime": {
    "python": "3.14.5",
    "sqlite_runtime": "3.50.4",
    "storage": ":memory: only; no existing database opened"
  },
  "contract": {
    "grain": "device one per device; sample one per measurement; service one per service record",
    "voltage": "predefined integer millivolts or SQL NULL meaning unknown, not zero",
    "ordering": "explicit ORDER BY device id/sample id/service id; no order inferred from storage"
  },
  "metrics": {
    "inner_rows": [
      [
        1,
        11,
        1200
      ],
      [
        1,
        12,
        null
      ],
      [
        2,
        21,
        2100
      ]
    ],
    "left_rows": [
      [
        1,
        11,
        1200
      ],
      [
        1,
        12,
        null
      ],
      [
        2,
        21,
        2100
      ],
      [
        3,
        null,
        null
      ]
    ],
    "count_columns": [
      "device_id",
      "COUNT(*)",
      "COUNT(sample.id)",
      "COUNT(voltage_mv)"
    ],
    "counts": [
      [
        1,
        2,
        2,
        1
      ],
      [
        2,
        1,
        1,
        1
      ],
      [
        3,
        1,
        0,
        0
      ]
    ],
    "where_filtered_rows": [
      [
        2,
        21
      ]
    ],
    "on_filtered_rows": [
      [
        1,
        null
      ],
      [
        2,
        21
      ],
      [
        3,
        null
      ]
    ],
    "null_voltage_rows": [
      [
        1,
        12
      ],
      [
        3,
        null
      ]
    ],
    "missing_child_devices": [
      [
        3
      ]
    ],
    "fanout_rows": [
      [
        1,
        11,
        101
      ],
      [
        1,
        11,
        102
      ],
      [
        1,
        12,
        101
      ],
      [
        1,
        12,
        102
      ],
      [
        2,
        21,
        201
      ]
    ],
    "fanout_count_and_distinct_sample_count": [
      5,
      3
    ],
    "preaggregated_counts": [
      [
        1,
        2,
        2
      ],
      [
        2,
        1,
        1
      ],
      [
        3,
        0,
        0
      ]
    ],
    "independent_fixture_count": 27
  },
  "checks": {
    "foreign_keys_explicitly_enabled_for_authored_fixture": true,
    "sample_primary_key_rejects_duplicate_identity": true,
    "foreign_key_rejects_nonexistent_device_but_not_multiple_samples": true,
    "inner_join_hand_three_measurement_rows": true,
    "left_join_hand_four_rows_including_unmatched_device": true,
    "count_star_child_id_and_known_value_have_distinct_hand_answers": true,
    "stored_missing_voltage_retains_actual_sample_identity": true,
    "is_null_voltage_cannot_alone_identify_absent_sample": true,
    "nonnullable_child_identity_finds_only_unmatched_parent": true,
    "where_filter_discards_false_and_unknown_outer_rows": true,
    "on_filter_preserves_unmatched_parents_after_predicate": true,
    "three_valued_null_comparison_and_boolean_exceptions": true,
    "two_one_to_many_joins_multiply_hand_child_pairs": true,
    "count_distinct_identity_repairs_this_count_not_general_join_grain": true,
    "preaggregated_children_produce_one_summary_per_device": true,
    "independent_python_nested_loop_and_count_oracle_twenty_seven_fixtures": true,
    "unknown_not_equal_value_is_not_retained_by_where": true,
    "read_queries_and_failed_constraint_statements_leave_original_rows": true
  },
  "limitations": [
    "Synthetic fixed relational fixtures only; PK/FK ensure identity/reference, not one sample per device or actual measurement validity.",
    "No ingestion type API, query plan/performance, concurrent database, materialized reporting or physical sensor calibration claim.",
    "COUNT(DISTINCT sample.id) fixes this particular identity count; it is not a universal repair for duplicated aggregates or mixed table grains.",
    "COALESCE of missing preaggregated counts to zero does not replace unknown measured voltage by zero."
  ]
}

برنامه با Python 3.14.5 و SQLite 3.50.4 اجرا شده است. مرجع مستقل، فهرست‌های پایتون را با حلقه‌های صریح پیمایش می‌کند و زوج‌ها و شمارش‌ها را می‌سازد؛ از JOIN دوم برای تولید پاسخ مرجع استفاده نمی‌کند. بیست‌وهفت ترکیبِ تعداد نمونهٔ صفر تا دو برای هر سه دستگاه، یک گروه از هجده گروه‌اند. جواب‌های دستی، اثر فیلتر، تکثیر دو وابستگی و تفاوت تهی‌ها، شاهدهای جداگانه‌اند.

همهٔ خروجی‌ها ORDER BY صریح دارند؛ ترتیب ذخیره‌سازی را ترتیب گزارش فرض نمی‌کنیم. تمام اتصال‌ها :memory: هستند و بسته می‌شوند. آزمون حذف کلید خارجی زمان بررسی سازگاری را دنبال می‌کند و آزمون قیود رکورد حسگر مرز نوع ذخیره‌شده را. این درس جای هیچ‌یک یا اعتبارسنجی دادهٔ ورودی را نمی‌گیرد. منابع در ۲۴ مهر ۱۴۰۵ خوانده شدند؛ نسخهٔ مستندات پایتون 3.14.8 از اجرای واقعی جداست.

۵. چهار پرسش با پاسخ کامل

پرسش اول: چرا دستگاه سه COUNT(*) برابر یک دارد؟ پاسخ: LEFT JOIN برای دستگاهِ بدون تطبیق یک ردیف با سمت راست NULL ساخته است. شمارش شناسهٔ نمونه صفر است؛ یک ردیف خروجی، یک نمونهٔ واقعی نیست.

پرسش دوم: NULL بودن ولتاژ یعنی نمونه نداریم؟ پاسخ: خیر؛ نمونهٔ دوازده شناسهٔ واقعی دارد و فقط مقدارش نامعلوم است. برای نبود فرزند در این اتصال، ستون غیرتهیِ شناسهٔ فرزند را بررسی می‌کنیم.

پرسش سوم: انتقال شرط ۱۵۰۰ از ON به WHERE بی‌اثر است؟ پاسخ: خیر؛ در این LEFT JOIN، WHERE دستگاه‌های بدون مقدارِ پذیرفته‌شده را حذف می‌کند. ON اجازه می‌دهد آن والدها با سمت راست تهی حفظ شوند.

پرسش چهارم: پنج ردیفِ اتصال سه جدول یعنی پنج نمونه؟ پاسخ: خیر؛ سطح خروجی زوجِ نمونه و خدمت است. برای شمارش نمونه در این مثال، شناسهٔ یکتا را جدا بشمارید یا ابتدا هر وابستگی را تا سطح دستگاه خلاصه کنید.

منابع و مطالعهٔ بیشتر

• SQLite SELECT؛ تشکیل JOIN، فیلتر ON و WHERE و گروه‌بندی
• SQLite؛ تفاوت count(*) و count(X)
• SQLite؛ NULL، IS و منطق سه‌مقداری
• Python 3.14؛ sqlite3 و پایگاه دادهٔ حافظه‌ای

بررسی منابع: ۲۴ مهر ۱۴۰۵. تحلیل‌ها و پیشنهادهای اجرایی، جمع‌بندی نویسنده‌اند.