فهرست مقاله
دو رویداد یک دستگاه می‌توانند زمان برابر داشته باشند، اما دو افزایش انرژیِ جدا را ثبت کنند. اگر کنار هر ردیف «جمع تا اینجا» بخواهیم، خودِ «اینجا» باید تعریف شود: پس از همین ردیف یا پس از همهٔ رویدادهای هم‌زمان؟ این درس اختلاف را با جمع پنجره‌ای SQLite و دو مرجع مستقلِ پایتونی قابل مشاهده می‌کند. داده‌ها کاملاً ساختگی‌اند؛ هیچ توانِ اندازه‌گیری‌شده، انتگرال‌گیری از سیگنال یا دستگاه واقعی در کار نیست.
۱. جمعِ تجمعی باید ردیف را حفظ کند
هر رویداد چهار عدد صحیح دارد: شناسهٔ یکتای سراسری، شناسهٔ دستگاه، زمانِ میلی‌ثانیه از مبدأ دلخواه و افزایشِ علامت‌دارِ انرژی برحسب میکروژول. این افزایش از پیش داده شده است؛ برنامه آن را از ولتاژ، جریان یا فاصلهٔ نمونه‌ها محاسبه نمی‌کند. علامت منفی می‌تواند در قرارداد دفترِ خالصِ تبادل انرژی معنا داشته باشد. از این مثال نباید دقت فیزیکی یک میکروژول یا صحت مدلِ اندازه‌گیری را برداشت کرد.
شش رویداد آموزشی داریم. برای دستگاه یک، افزایش‌ها در زمان ده برابر چهار و هفت، در زمان بیست منفی دو و در زمان سی برابر سه‌اند. دستگاه دو در زمان‌های ده و بیست، صد و پنج میکروژول دارد. جمع نهایی دو دستگاه به‌ترتیب دوازده و صد و پنج است. هدف گزارش، حفظ هر شش ردیف و افزودن جمع متناظر به همان هویت است؛ خلاصه‌سازی به یک ردیف برای هر دستگاه پرسش دیگری خواهد بود.
طبق مرجع رسمی توابع پنجره‌ای SQLite، افزودن تجمیع پنجره‌ای تعداد ردیف‌های این SELECT را کاهش نمی‌دهد. PARTITION BY device_id نیز محاسبهٔ هر دستگاه را جدا می‌کند. بنابراین وجود ردیف دستگاه دو میان دو ردیف دستگاه یک در نمایش، نباید صد میکروژول را وارد جمع دستگاه یک کند. آزمونِ جداییِ دستگاه‌ها و حفظ شناسه‌ها، پیش از مقایسهٔ عددهای جمع اهمیت دارد.
۲. همتایانِ زمانی و قابِ پیش‌فرض
در پرس‌وجوی اول، ترتیبِ داخل پنجره زمان و سپس شناسه است و قاب ROWS از ابتدای بخش تا ردیف جاری امتداد دارد. شناسهٔ یکتا، ترتیبِ رویدادهای هم‌زمان را مشخص می‌کند. برای دو ردیف نخست دستگاه یک، حاصل چهار و سپس یازده است. این ترتیب، یک سیاستِ گزارش‌گیریِ قطعی است؛ از کوچک‌تر بودن شناسه نتیجه نمی‌گیریم که رویداد در دنیای واقعی زودتر رخ داده است.
پرس‌وجوی دوم عمداً داخل پنجره فقط بر زمان مرتب می‌شود و قاب صریح ندارد. قاب پیش‌فرض RANGE از ابتدای بخش تا ردیف جاری، همتایان آن را هم شامل می‌شود. پس هر دو رویداد دستگاه یک با زمان ده، جمع یازده می‌گیرند. اصطلاح همتا در اینجا به برابری عبارتِ ترتیبِ پنجره مربوط است. رویداد دستگاه دو با همان زمان، در بخش دیگری است و عضو آن جمع نمی‌شود.
افزودن شناسه به ترتیب پنجرهٔ دوم، یک تغییر معنایی است: با کلید یکتا دیگر گروهِ چندردیفیِ همتا نداریم. در بررسی جانبیِ همین مرزهای بدون فاصلهٔ عددی، RANGE با زوج یکتا و ROWS نتیجهٔ یکسان دارند. این مشاهده را به قاب‌های دارای فاصلهٔ زمانی، NULL، ترتیب نزولی یا هر عبارت دلخواه تعمیم نمی‌دهیم. در پرس‌وجوی نمایشِ اختلاف، زمان تنها کلیدِ داخل RANGE باقی می‌ماند تا اختلافِ مورد نظر پنهان نشود.
۳. کد کاملِ ROWS با ترتیبِ یکتا
هر دو خروجی، ORDER BY بیرونیِ زمان و شناسه دارند. بخش ORDER BY در مستند SELECT می‌گوید بدون آن، ترتیب خروجی معین نیست و برابری همهٔ کلیدهای ترتیب نیز ابهام باقی می‌گذارد. ترتیب داخل OVER وظیفهٔ انتخاب قاب را دارد؛ ترتیب بیرونی وظیفهٔ ارائهٔ ردیف‌ها را. چون شناسه در کل جدول یکتاست، زوج بیرونی حتی میان دو دستگاه نیز تساویِ حل‌نشده ندارد.
ورودیِ برنامه tuple دقیقِ حداکثر شصت‌وچهار ردیف است و هر ردیف tuple چهارعضوی دارد. تمام مؤلفه‌ها int دقیق‌اند؛ bool، اعشاری، رشته و زیرکلاس رد می‌شوند. شناسهٔ رویداد یک تا یک‌میلیون، دستگاه یک تا چهار و زمان صفر تا یک‌میلیارد میلی‌ثانیه است. قدرمطلق هر افزایش حداکثر یک‌میلیون میکروژول است؛ بنابراین قدرمطلقِ هر جمع حداکثر شصت‌وچهارمیلیون می‌شود و به سرریز صحیحِ ۶۴ بیتی نزدیک نیست.
پایگاه داده فقط در حافظه ساخته می‌شود و همهٔ مقدارهای درج، پارامتر دارند. اتصالِ متعلق به همین تابع، در پایان صریح بسته می‌شود؛ خطای ساخت نیز ابتدا اتصال را می‌بندد و دوباره منتشر می‌شود. راهنمای sqlite3 پایتون مبنای اتصال مقدار و تفکیک بستن اتصال از مدیر زمینه است. فایل را sqlite_window_peer_frames.py بنامید و با python sqlite_window_peer_frames.py اجرا کنید؛ تنها کتابخانهٔ استاندارد لازم است.
"""Bounded integer event ledger: deterministic ROWS versus default RANGE."""
from contextlib import closing
import itertools
import json
import platform
import sqlite3
ROWS_SQL = """SELECT event_id,device_id,sample_ms,delta_uj,
SUM(delta_uj) OVER (
PARTITION BY device_id ORDER BY sample_ms,event_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_uj
FROM event ORDER BY sample_ms,event_id"""
PEERS_SQL = """SELECT event_id,device_id,sample_ms,delta_uj,
SUM(delta_uj) OVER (
PARTITION BY device_id ORDER BY sample_ms) AS cumulative_uj
FROM event ORDER BY sample_ms,event_id"""
UNIQUE_RANGE_SQL = """SELECT event_id,device_id,sample_ms,delta_uj,
SUM(delta_uj) OVER (
PARTITION BY device_id ORDER BY sample_ms,event_id
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_uj
FROM event ORDER BY sample_ms,event_id"""
def validate(records):
if type(records) is not tuple or any(type(row) is not tuple for row in records):
raise TypeError("records and rows must be exact tuples")
if len(records) > 64 or any(len(row) != 4 for row in records):
raise ValueError("at most 64 four-field events required")
identifiers = set()
for event_id, device_id, sample_ms, delta_uj in records:
if any(type(x) is not int for x in (event_id, device_id, sample_ms, delta_uj)):
raise TypeError("all fields require exact int, excluding bool")
if not 1 <= event_id <= 1_000_000 or event_id in identifiers:
raise ValueError("unique event identifiers in 1..1000000 required")
if not 1 <= device_id <= 4 or not 0 <= sample_ms <= 1_000_000_000:
raise ValueError("device or timestamp outside bounds")
if not -1_000_000 <= delta_uj <= 1_000_000:
raise ValueError("energy increment outside +/-1000000 microjoules")
identifiers.add(event_id)
def database(records):
validate(records)
connection = sqlite3.connect(":memory:", autocommit=True)
try:
connection.execute("CREATE TABLE event(event_id INTEGER PRIMARY KEY, device_id INTEGER NOT NULL, sample_ms INTEGER NOT NULL, delta_uj INTEGER NOT NULL)")
connection.executemany("INSERT INTO event VALUES(?,?,?,?)", records)
except BaseException:
connection.close()
raise
return connection
def window_report(records):
with closing(database(records)) as connection:
return dict(rows=tuple(connection.execute(ROWS_SQL)),
time_peers=tuple(connection.execute(PEERS_SQL)))
def prefix_reference(records):
# Sequential accumulation over explicitly sorted Python records, no SQL.
totals = {}
result = []
for row in sorted(records, key=lambda row: (row[2], row[0])):
totals[row[1]] = totals.get(row[1], 0) + row[3]
result.append(row + (totals[row[1]],))
return tuple(result)
def peer_reference(records):
# Independently select all same-device events whose time is <= this time.
# No cumulative state or ROWS/prefix helper is reused.
return tuple(row + (sum(other[3] for other in records
if other[1] == row[1] and other[2] <= row[2]),)
for row in sorted(records, key=lambda row: (row[2], row[0])))
def raises(exception, function, *args):
try:
function(*args)
except exception:
return True
return False
def run():
records = ((1, 1, 10, 4), (2, 1, 10, 7), (3, 2, 10, 100),
(4, 1, 20, -2), (5, 2, 20, 5), (6, 1, 30, 3))
own = window_report(records)
checks = {}
checks["hand_ROWS_with_unique_order"] = tuple(row[4] for row in own["rows"]) == (4, 11, 100, 9, 105, 12)
checks["hand_default_RANGE_includes_whole_time_peer_group"] = tuple(row[4] for row in own["time_peers"]) == (11, 11, 100, 9, 105, 12)
checks["window_preserves_row_identity_and_cardinality"] = all(tuple(row[:4] for row in result) == records for result in own.values())
checks["input_insertion_order_does_not_choose_ties"] = window_report(tuple(reversed(records))) == own
one_device = tuple(row for row in records if row[1] == 1)
checks["partition_isolation"] = window_report(one_device) == {key: tuple(row for row in value if row[1] == 1) for key, value in own.items()}
checks["empty_and_single_event"] = window_report(()) == {"rows": (), "time_peers": ()} and window_report(((7, 4, 0, -9),)) == {"rows": ((7, 4, 0, -9, -9),), "time_peers": ((7, 4, 0, -9, -9),)}
checks["signed_increments_need_not_be_monotone"] = own["rows"][3][4] < own["rows"][1][4]
with closing(database(records)) as connection:
checks["unique_RANGE_keys_remove_multirow_peers"] = tuple(connection.execute(UNIQUE_RANGE_SQL)) == own["rows"]
# All candidate total sums are bounded by64*1000000, well inside int64.
boundary = tuple((i + 1, 1, 1_000_000_000, 1_000_000) for i in range(64))
extreme = window_report(boundary)
checks["integer_sum_caps_and_shared_time_boundary"] = (extreme["rows"][-1][4] == 64_000_000
and all(row[4] == 64_000_000 and type(row[4]) is int for row in extreme["time_peers"]))
checks["exact_type_and_collection_rejections"] = (raises(TypeError, window_report, [[1, 1, 0, 0]])
and raises(TypeError, window_report, ((1, True, 0, 0),))
and raises(TypeError, window_report, ((1, 1, 0.0, 0),)))
invalid = [((1, 1, 0),), ((1, 1, 0, 0), (1, 2, 1, 1)),
((0, 1, 0, 0),), ((1, 5, 0, 0),), ((1, 1, -1, 0),),
((1, 1, 0, 1_000_001),), tuple((i + 1, 1, 0, 0) for i in range(65))]
checks["shape_unique_id_and_value_bounds"] = all(raises(ValueError, window_report, rows) for rows in invalid)
last = {1: own["rows"][-1][4], 2: own["rows"][4][4]}
checks["final_partition_sums_and_total"] = last == {1: 12, 2: 105} and sum(last.values()) == sum(row[3] for row in records)
prefix_cases = peer_cases = 0
for length in range(1, 5):
for pattern in itertools.product(range(3), repeat=length):
sample = tuple((i + 1, 1 + i % 2, pattern[i], (i + 1) * (-1 if i % 2 else 1)) for i in range(length))
actual = window_report(tuple(reversed(sample)))
assert actual["rows"] == prefix_reference(sample)
prefix_cases += 1
assert actual["time_peers"] == peer_reference(sample)
peer_cases += 1
checks["independent_Python_sorted_prefix_oracle_family"] = prefix_cases == 120
checks["independent_Python_whole_peer_oracle_family"] = peer_cases == 120
assert len(checks) == 14 and all(type(value) is bool and value for value in checks.values())
return dict(python=platform.python_version(), sqlite=sqlite3.sqlite_version, checks=checks,
own_example=dict(input=records, explicit_ROWS=own["rows"], default_RANGE_time_only=own["time_peers"],
final_microjoules_by_device=last),
independent_prefix_cases_one_group=prefix_cases, independent_peer_cases_one_group=peer_cases,
boundary_sum_microjoules=extreme["rows"][-1][4],
scope="Synthetic signed integer microjoule increments, not integration of sampled power or measured energy. Explicit outer order, no unspecified tie order or runtime-performance claim.")
if __name__ == "__main__":
print(json.dumps(run(), ensure_ascii=True, indent=2))
نسخهٔ اجرای واقعی Python 3.14.5 و SQLite 3.50.4 بوده است؛ مستندات برخط شاخهٔ پایتون هنگام مطالعه 3.14.8 را نشان می‌داد. نتیجهٔ ROWS به ترتیب نمایش، چهار، یازده، صد، نه، صد و پنج و دوازده است. در خروجیِ پیش‌فرض، فقط مقدارِ ردیف نخست به یازده تغییر می‌کند. این ردیف چیزی از زمان بیست نمی‌خواند؛ رویداد دوم با همان زمان ده در قاب همتای آن حضور دارد.
۴. مرجعِ پیشوندی و جمعِ گروهِ همتا
مرجع نخست، ورودی را در پایتون با زوج زمان و شناسه مرتب و برای هر دستگاه یک مجموع جاری نگه می‌دارد. مرجع دوم برای هر ردیف، همهٔ رویدادهای همان دستگاه با زمان کوچک‌تر یا مساوی را جدا انتخاب و جمع می‌کند. ساختار دو مرجع متفاوت است و هیچ‌کدام خروجی SQL را به‌عنوان پاسخِ مورد انتظار برنمی‌گردانند. مرجع دوم آگاهانه گران‌تر و مستقیم‌تر است؛ در این مجموعهٔ محدود، شفافیتِ معنا هدف آن است.
برای طول‌های یک تا چهار، همهٔ ترکیب‌های سه زمانِ صفر، یک و دو را بررسی می‌کنیم؛ مجموعاً صدوبیست الگوی زمانی. تطبیق با مرجع پیشوندی یک گروه و تطبیق با مرجع همتا یک گروه از چهارده گروه‌اند. ورودیِ معکوس، بخش‌های مستقل، افزایش منفی، مجموعهٔ خالی، تک‌رویداد، یکتایی و دامنهٔ ورودی و مرز شصت‌وچهار ردیف نیز بررسی می‌شوند. همهٔ گروه‌ها موفق‌اند؛ شمارِ الگوها، گروه‌های ادعاشده را به صدها آزمون مستقل تبدیل نمی‌کند.
{
"python": "3.14.5",
"sqlite": "3.50.4",
"checks": {
"hand_ROWS_with_unique_order": true,
"hand_default_RANGE_includes_whole_time_peer_group": true,
"window_preserves_row_identity_and_cardinality": true,
"input_insertion_order_does_not_choose_ties": true,
"partition_isolation": true,
"empty_and_single_event": true,
"signed_increments_need_not_be_monotone": true,
"unique_RANGE_keys_remove_multirow_peers": true,
"integer_sum_caps_and_shared_time_boundary": true,
"exact_type_and_collection_rejections": true,
"shape_unique_id_and_value_bounds": true,
"final_partition_sums_and_total": true,
"independent_Python_sorted_prefix_oracle_family": true,
"independent_Python_whole_peer_oracle_family": true
},
"own_example": {
"input": [
[
1,
1,
10,
4
],
[
2,
1,
10,
7
],
[
3,
2,
10,
100
],
[
4,
1,
20,
-2
],
[
5,
2,
20,
5
],
[
6,
1,
30,
3
]
],
"explicit_ROWS": [
[
1,
1,
10,
4,
4
],
[
2,
1,
10,
7,
11
],
[
3,
2,
10,
100,
100
],
[
4,
1,
20,
-2,
9
],
[
5,
2,
20,
5,
105
],
[
6,
1,
30,
3,
12
]
],
"default_RANGE_time_only": [
[
1,
1,
10,
4,
11
],
[
2,
1,
10,
7,
11
],
[
3,
2,
10,
100,
100
],
[
4,
1,
20,
-2,
9
],
[
5,
2,
20,
5,
105
],
[
6,
1,
30,
3,
12
]
],
"final_microjoules_by_device": {
"1": 12,
"2": 105
}
},
"independent_prefix_cases_one_group": 120,
"independent_peer_cases_one_group": 120,
"boundary_sum_microjoules": 64000000,
"scope": "Synthetic signed integer microjoule increments, not integration of sampled power or measured energy. Explicit outer order, no unspecified tie order or runtime-performance claim."
}درس نشانگر دوکلیدی و زمان برابر دلیلِ نیاز به ترتیب کامل را در پیمایش نشان می‌دهد. درس سطح رکورد و شمارش JOIN نیز کمک می‌کند افزودن ستونِ پنجره را با تکثیر یا خلاصه‌شدن ردیف اشتباه نگیریم. این برنامه زمان اجرا، ایندکس، حافظهٔ واقعی یا برتری یک روش را اندازه نگرفته است. درست‌بودن جمعِ این قرارداد نیز بهبود دقت حسگر یا مدل یادگیری را ثابت نمی‌کند.
۵. چهار پرسش دربارهٔ قاب و ترتیب
پرسش اول: چرا نخستین رویداد گاهی چهار و گاهی یازده دارد؟ پاسخ: ROWS با ترتیب یکتا فقط همان رویداد را تا آن نقطه دارد؛ قاب پیش‌فرضِ زمان‌محور، رویداد هم‌زمانِ هفت‌میکروژولی را هم می‌گیرد. هر دو پاسخ برای دو تعریف متفاوت درست‌اند. پیش از انتخاب SQL باید معنای «تا اینجا» را در نیازمندی گزارش بنویسیم.
پرسش دوم: آیا ORDER BY بیرونی می‌تواند اختلافِ دو قاب را برطرف کند؟ پاسخ: خیر؛ آن فقط نمایش را مرتب می‌کند. برای تغییر اعضای قاب باید تعریف داخل OVER را عوض کنیم. در کد، زوج بیرونی ثابت است ولی ترتیب داخل دو پنجره تفاوت دارد؛ همین جدایی امکان مقایسهٔ منصفانهٔ هویت‌های یکسان را فراهم می‌کند.
پرسش سوم: آیا جمع تجمعی الزاماً صعودی است؟ پاسخ: خیر؛ افزایشِ منفی دو، جمع دستگاه یک را از یازده به نه می‌رساند. قرارداد علامت‌دار چنین کاهشی را مجاز کرده است. اگر مسئله واقعاً شمارندهٔ انرژیِ فقط افزایشی بخواهد، دامنهٔ ورودی و آزمونِ یکنوایی باید جدا تغییر کنند؛ از واژهٔ تجمعی به‌تنهایی آن شرط نتیجه نمی‌شود.
پرسش چهارم: چرا برگرداندن ردیف‌ها هنگام درج، جواب را عوض نمی‌کند؟ پاسخ: پرس‌وجوی ROWS ترتیب یکتای زمان و شناسه دارد و RANGE نیز گروه‌های زمان را کامل می‌گیرد. هر دو خروجی بیرونی هم مرتب‌اند. این پاسخ دربارهٔ همین جدولِ بدون تغییرِ هم‌زمان است؛ ترتیب درج، زمان واقعی وقوع، نسخهٔ ثابتِ سرویس و هماهنگی ساعت دستگاه‌ها را تضمین نمی‌کند.
منابع و مطالعهٔ بیشتر
- SQLite؛ تجمیع پنجره‌ای، بخش، قاب و همتا
- SQLite SELECT؛ ترتیب خروجی و شکستن تساوی
- Python3.14؛ جای‌نگهدارها و بستن اتصال
بررسی منابع: ۲۶ مهر ۱۴۰۵. تحلیل‌ها و پیشنهادهای اجرایی، جمع‌بندی نویسنده‌اند.
