چرا گزارش فروش هوش مصنوعی یک سفارش را چند بار می‌شمارد؟

دو سفارش داریم، هرکدام به مبلغ ۱۰۰. دستیار هوش مصنوعی گزارش فروش را می‌سازد و جمع را ۸۰۰ اعلام می‌کند. معامله‌ای جعل نشده و پایگاه داده هم جمع را اشتباه حساب نکرده است. کد، جدول سفارش را به اقلام و ارسال‌های آن وصل کرده و مبلغ هر سفارش را برای هر ترکیب تازه، دوباره جمع زده است. هیچ خطای اجرایی هم دیده نمی‌شود.

این یک مثال آموزشی است، نه حادثه مشتری یا آزمون عملکرد مدل. پرسش آن برای مهندس داده و مدیری که گزارش دستیار را تأیید می‌کند واقعی است: چه چیزی ثابت می‌کند عدد درست است؟ برای پاسخ باید بدانیم هر ردیف نماینده چیست، اتصال‌ها آن را چند بار تکرار می‌کنند و مبلغ به کدام موجودیت تعلق دارد.

مسیر یک سفارش را تا جمع نهایی دنبال کنید

فرض کنیم هر دو سفارش در یک دوره گزارش و با یک واحد پول ثبت شده‌اند. مبلغ اقلام هر سفارش با مبلغ خود سفارش برابر است. برای ساده نگه‌داشتن مثال، تخفیف، مالیات، لغو و بازپرداخت نداریم. سفارش «الف» دو قلم و سه ارسال دارد؛ سفارش «ب» یک قلم و دو ارسال.

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

SELECT SUM(o.order_total) AS booked_value
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
JOIN shipments s ON s.order_id = o.order_id;

این رفتار همان چیزی است که از اتصال انتظار داریم. مستندات عبارت‌های جدولی PostgreSQL توضیح می‌دهد که اتصال داخلی برای هر تطبیق، یک ردیف می‌سازد. اتصال چپ ردیف‌های بی‌تطبیق سمت چپ را نگه می‌دارد، ولی مانع تکثیر ردیف‌های دارای چند تطبیق نمی‌شود.

پس چندردیفی‌شدن همیشه نشانه خرابی داده نیست. یک سفارش واقعاً چند قلم و چند ارسال دارد. خطا از جایی شروع می‌شود که مبلغ مربوط به «یک سفارش» را روی ردیف‌های مربوط به «ترکیب قلم و ارسال» جمع می‌زنیم. حتی جمع مبلغ اقلام در همین اتصال غلط است، چون هر قلم به تعداد ارسال‌های سفارش تکرار شده است.

پیش از انتخاب ستون مبلغ، تعریف فروش را روشن کنید

«فروش این ماه» هنوز نام یک محاسبه دقیق نیست. مبلغ سفارش ثبت‌شده، ارزش کالای ارسال‌شده، وجه دریافت‌شده و درآمد حسابداری می‌توانند چهار عدد متفاوت باشند. هرکدام جمعیت، تاریخ و استثناهای خود را دارند. پیدا کردن ستونی به نام «مبلغ» به مدل اجازه نمی‌دهد یکی را به جای دیگری انتخاب کند.

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

در مدل‌سازی داده، «سطح هر ردیف» می‌گوید یک ردیف دقیقاً چه چیزی را نشان می‌دهد. در جدول سفارش، یک سفارش؛ در جدول اقلام، یک قلم سفارش؛ و در جدول ارسال، یک نوبت ارسال. راهنمای مدل ستاره‌ای مایکروسافت بر یکسان‌بودن این سطح در جدول واقعیت تأکید می‌کند و نقش واقعیت‌ها و ابعاد را جدا می‌سازد. نام جدول به‌تنهایی جای تعریف را نمی‌گیرد.

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

حذف مبلغ تکراری، حذف سفارش تکراری نیست

اگر از دستیار بخواهیم «تکراری‌ها را حذف کن»، شاید پیشنهاد دهد از SUM(DISTINCT o.order_total) استفاده کنیم. این بار نتیجه مثال ۱۰۰ می‌شود. چرا؟ چون دو سفارش واقعی، اتفاقاً مبلغ یکسان دارند و تابع فقط یک مقدار عددی متمایز می‌بیند. اصلاح ظاهری، بیش‌شماری را به کم‌شماری تبدیل کرده است.

هویت را شناسه سفارش مشخص می‌کند، نه مبلغ آن. شمارش شناسه‌های متمایز برای سؤال «چند سفارش داریم؟» مفید است، اما جمع ستون‌های دیگر را درست نمی‌کند. حذف ردیف تکراری پس از محاسبه جمع نیز دیر است؛ ردیف‌ها پیش‌تر در نتیجه اثر گذاشته‌اند. نگه‌داشتن تصادفی اولین قلم یا اولین ارسال هم ممکن است اطلاعات لازم گزارش را حذف کند.

گوگل در توضیح تجمیع متقارن Looker روش متفاوتی را شرح می‌دهد: سنجه را همراه کلید موجودیت در نظر می‌گیرد تا تکرار حاصل از اتصال، مبلغ همان موجودیت را چند بار وارد محاسبه نکند. این روش به کلید اصلی واقعاً یکتا و تعریف درست رابطه وابسته است و هزینه محاسباتی هم دارد. با حذف مقدارهای پولی برابر یکی نیست و سلامت هر مدل معنایی را تضمین نمی‌کند.

پیش از پذیرفتن هر دستور حذف تکرار، سه سؤال بپرسید: کدام موجودیت واقعی تکرار شده؟ کدام کلید این هویت را ثابت می‌کند؟ چرا باقی فیلدهای نسخه‌های تکراری باید یکسان باشند؟ کمترشدن عدد، به‌خودی‌خود نشانه درست‌ترشدن آن نیست.

هر واقعیت مستقل را در سطح خودش جمع کنید

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

اگر مبلغ سفارش، جمع اقلام و تعداد ارسال را کنار هم می‌خواهید، ابتدا هر جدول فرزند را به یک ردیف برای هر سفارش کاهش دهید و بعد اتصال بزنید. شکل زیر برای مثال ما چنین کاری می‌کند:

WITH item_totals AS (
  SELECT order_id, SUM(line_total) AS item_total
  FROM order_items
  GROUP BY order_id
), shipment_counts AS (
  SELECT order_id, COUNT(*) AS shipment_count
  FROM shipments
  GROUP BY order_id
)
SELECT
  o.order_id,
  o.order_total,
  i.item_total,
  COALESCE(s.shipment_count, 0) AS shipment_count
FROM orders o
LEFT JOIN item_totals i ON i.order_id = o.order_id
LEFT JOIN shipment_counts s ON s.order_id = o.order_id;

به شرط یکتابودن شناسه در جدول سفارش، خروجی برای هر سفارش یک ردیف دارد. «الف» مبلغ ۱۰۰، جمع اقلام ۱۰۰ و سه ارسال دارد؛ «ب» مبلغ ۱۰۰، جمع اقلام ۱۰۰ و دو ارسال. جمع ستون مبلغ سفارش ۲۰۰ و جمع تعداد ارسال پنج است. مبلغ اقلامِ ناموجود را صفر نکرده‌ایم تا نبود اطلاعات پنهان نشود.

این قطعه یک الگوی پرس‌وجو است، نه گزارش آماده استفاده در سامانه واقعی. یکتایی کلید، معنای هر قلم و ارسال، مجوز دسترسی، فیلتر جمعیت و سازگاری زمان خواندن را در محیط خود بررسی کنید. اگر بعداً جدول دیگری به خروجی وصل شود، تکثیر ردیف ممکن است برگردد. سطح خروجی نهایی را کنترل کنید، نه فقط جدول‌های میانی را.

فیلتر کوچک ممکن است جمعیت گزارش را عوض کند

فرض کنید مبلغ همه سفارش‌های واجد شرایط را می‌خواهیم و کنار آن تعداد ارسال‌های تحویل‌شده را نشان می‌دهیم. اگر شرط تحویل را پس از اتصال چپ در بخش WHERE بگذاریم، سفارش‌های بدون ارسال تحویل‌شده حذف می‌شوند. اگر همان شرط را داخل خلاصه ارسال‌ها اعمال کنیم، جمعیت سفارش‌ها حفظ می‌شود. هر دو پرس‌وجو ممکن است معتبر باشند، اما به دو سؤال مختلف جواب می‌دهند.

شمارش مقدار خالی نیز اهمیت دارد. در PostgreSQL، عبارت COUNT(*) ردیف‌ها را می‌شمارد و COUNT(expression) فقط ورودی‌های غیرخالی را. بیشتر توابع تجمیع، از جمله جمع، برای ورودی بدون ردیف مقدار تهی برمی‌گردانند. این رفتار در مرجع توابع تجمیع آمده است. پس از اتصال چپ، سفارش بدون ارسال هنوز یک ردیف خروجی دارد؛ شمارش همه ردیف‌ها، تعداد ارسال را نشان نمی‌دهد.

صفرکردن تعداد ارسال ناموجود فقط وقتی درست است که داده کامل باشد و نبود رکورد واقعاً به معنای نبود ارسال باشد. عقب‌افتادن دریافت داده یا نرسیدن بخشی از فایل روزانه را نباید فعالیت صفر تفسیر کرد. کنترل کامل‌بودن منبع را جدا از عدد نگه دارید و نتیجه‌اش را همراه گزارش ثبت کنید.

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

بعضی تفکیک‌ها قاعده کسب‌وکار می‌خواهند

یک مشتری ممکن است عضو دو کارزار باشد و یک سفارش چند دسته کالا داشته باشد. اگر سفارش را در هر دسته یک بار حساب کنید، نتیجه هر دسته الزاماً غلط نیست؛ اما جمع دسته‌ها لزوماً جمع کل را نمی‌دهد. گروه‌ها هم‌پوشانی دارند.

پرسش را دقیق کنید. «مبلغ سفارش‌های حاوی این دسته کالا» گروه‌های هم‌پوشان می‌سازد و باید غیرقابل‌جمع معرفی شود. «مبلغ اقلام این دسته» از داده سطح قلم محاسبه می‌شود. «سهم این دسته از مبلغ کل سفارش» قاعده تخصیص می‌خواهد؛ از جمله تکلیف تخفیف یا هزینه ارسال. تقسیم مساوی هم یک تصمیم است، نه حقیقتی که مدل حق داشته باشد پنهانی فرض کند.

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

پیشنهاد ژرف این است که اگر دسته‌بندی درخواستی قاعده تخصیص قابل‌دفاع ندارد، جمع قابل‌افزودن منتشر نکنید. یک سؤال روشن از صاحب سنجه مفیدتر از نموداری زیباست که جمع اجزایش با کل سازگار نیست. تغییر نحو کد جای این تصمیم را نمی‌گیرد.

دستیار به واژه‌نامه سنجه نیاز دارد، نه فقط فهرست جدول

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

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

هر کنترل مسئله خودش را حل می‌کند. دسترسی فقط‌خواندنی مانع تغییر داده می‌شود، نه لزوماً افشای غیرمجاز یا پرس‌وجوی پرهزینه. پذیرفته‌شدن کد توسط مفسر، درستی جمعیت و معنای جمع را ثابت نمی‌کند. لایه معنایی فرصت اتصال بداهه را کمتر می‌کند، ولی کلیدها و تعریف‌هایش همچنان آزمون می‌خواهند. موافقت مدل دوم با مدل اول هم شاهد مستقل صحت عدد نیست.

داده آزمون را طوری بسازید که خطا جایی برای پنهان‌شدن نداشته باشد

کنترل داده واقعی و آزمون منطق با چند ردیف ثابت، دو کار متفاوت‌اند. مستندات آزمون داده dbt یکتایی، خالی‌نبودن و وجود رابطه را به‌صورت ادعاهای جدا معرفی می‌کند. اینکه کلید هر فرزند در جدول والد پیدا شود، ثابت نمی‌کند والد فقط یک ردیف برای آن کلید دارد. دو سوی رابطه ادعاشده را بررسی کنید.

در مقابل، راهنمای آزمون واحد dbt از ورودی‌های کوچک و ثابت برای آزمودن منطق SQL پیش از ساخت کامل مدل استفاده می‌کند. بدون این ابزار هم می‌توان همین کار را کرد: پاسخ مورد انتظار را مستقل تعریف کنید و کد پیشنهادی را در گویش پایگاه داده مقصد، روی نمونه کنترل‌شده اجرا کنید.

نمونه‌ها فقط مسیر آسان نباشند. دو سفارش با مبلغ برابر، چند فرزند در هر دو جدول، سفارش بدون ارسال، کلید خالی، فرزند بدون والد، والد تکراری، بازپرداخت و نسخه‌های تاریخی هم‌پوشان را وارد کنید. بعضی ورودی‌ها باید رد شوند؛ پاسخ درست همه وضعیت‌های ناقص «صفر» نیست. همان کد تولیدشده نباید پاسخ‌نامه آزمون خودش را بسازد.

آزمون تغییر هم اضافه کنید: افزودن یک ارسال نباید مبلغ سفارش ثبت‌شده را تغییر دهد. شکستن یک قلم به دو قلم با جمع برابر نباید مبلغ سفارش را عوض کند. افزودن یک سفارش واجد شرایط باید جمع را به اندازه مبلغ آن افزایش دهد، حتی اگر سفارش دیگری دقیقاً همان مبلغ را داشته باشد. این آزمون‌ها فرض هویت و سطح ردیف را مستقیم زیر سؤال می‌برند.

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

عدد را بپذیریم، کد را اصلاح کنیم یا پاسخ را معلق بگذاریم؟

پس از استفاده، شکست یکتایی، تغییر تعداد تطبیق‌ها، کلید بی‌تطبیق، کامل‌بودن منبع و اختلاف با مرجع را به تفکیک بخش دنبال کنید. تاریخی‌شدن یک جدول، اضافه‌شدن نوع تازه ارسال، تغییر مسیر اتصال پیشنهادی مدل یا اصلاح تعریف سنجه، همگی دلیل بازبینی‌اند.

ادعای قابل‌قبول این نیست که «هوش مصنوعی SQL معتبر نوشت». باید بتوان گفت: «این نتیجه، موجودیت‌های موردنظر را مطابق یک تعریف مشخص می‌شمارد و اتصال‌هایش با نمونه‌هایی آزموده شده‌اند که در غیر این صورت جواب را عوض می‌کردند.»

یادداشت منابع — بررسی‌شده در ۱۶ سپتامبر ۲۰۲۶

این راهنما رفتار مستند پایگاه داده را با روش پیشنهادی ژرف برای بررسی گزارش ترکیب می‌کند. مثال تبدیل ۲۰۰ به ۸۰۰، نمونه محاسباتی ساخته‌شده است، نه نتیجه سنجش مدل یا مشاوره حسابداری.

• عبارت‌های جدولی PostgreSQL 18 — مستندات نسخه مشخص برای اتصال، فیلتر و گروه‌بندی؛ بررسی در تاریخ بالا.
• توابع تجمیع PostgreSQL 18 — شمارش، مقدار تهی و ورودی بدون ردیف؛ بررسی در تاریخ بالا.
• تجمیع متقارن در Looker گوگل — به‌روزرسانی ۲۲ ژوئیه ۲۰۲۵؛ محاسبه وابسته به کلید و پیش‌نیازهای مدل‌سازی.
• راهنمای مدل ستاره‌ای Power BI مایکروسافت — به‌روزرسانی ۳۰ دسامبر ۲۰۲۴؛ سطح واقعیت، رابطه‌ها و ابعاد تاریخی.
• آزمون داده dbt — به‌روزرسانی ۱۱ سپتامبر ۲۰۲۶؛ ادعاهای جدا درباره داده موجود.
• آزمون واحد dbt — به‌روزرسانی ۱۴ سپتامبر ۲۰۲۶؛ آزمون منطق با ورودی ثابت، از نسخه ۱٫۸ با محدودیت‌های مستند مدل و رابط پایگاه داده. مستندات جاری به معنی پشتیبانی همه نسخه‌های نصب‌شده از همه قابلیت‌ها نیست.