چگونه می توان پیام های N را با جمع کلامی کمتر از n انتخاب کرد؟

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

درک مشکل

در مثال ما ، یک جدول ساده به نام داریم messages، که شامل دو ستون است: id وت verbosityبشر با توجه به ورودی ها:

| شناسه | کلامی | | —- | ————- | | 1 | 20 | | 2 | 20 | | 3 | 20 | | 4 | 30 | | 5 | 100 |

هنگامی که می خواهیم ردیف هایی را انتخاب کنیم که تعداد کلامی آنها کمتر از یک مقدار خاص باشد ، می گویند n = 70 ، ما انتظار داریم ردیف ها را با IDS 1 ، 2 و 3 انتخاب کنیم ، جایی که کلین بودن کل به 60 محاسبه می شود.

راه حل پرس و جو SQL

چالش یافتن آن ردیف ها بدون بیش از N. در اینجا ، ما از ویژگی های SQL مانند عبارات جدول مشترک (CTE) قدردانی می کنیم ، که باعث افزایش وضوح و مدولار می شود ، در حالی که همچنان مستقل از پایگاه داده است.

پرس و جو SQL گام به گام

ما می توانیم با استفاده از یک رویکرد بازگشتی یا یک استراتژی جمع تجمعی برای پیگیری کل در حال اجرا در هنگام ساخت مجموعه نتیجه خود ، به این هدف برسیم. در زیر یک راه حل SQL وجود دارد که در هر دو PostgreSQL و SQLITE کار می کند:

WITH RECURSIVE selected_messages AS (
    SELECT id, verbosity, verbosity AS total
    FROM messages
    WHERE id = 1  -- Starting point, consider the first message
    UNION ALL
    SELECT m.id, m.verbosity, sm.total + m.verbosity
    FROM messages m
    JOIN selected_messages sm ON m.id = sm.id + 1
    WHERE sm.total + m.verbosity < 70  -- Check if adding the next message stays < 70
)
SELECT * FROM selected_messages;

تجزیه و تحلیل پرس و جو

1. ایجاد CTE: ما یک عبارت جدول مشترک به نام ایجاد می کنیم selected_messages، به ما این امکان را می دهد تا نتیجه خود را به صورت بازگشتی بسازیم.
2. انتخاب اولیه: اولین پرس و جو در CTE ردیف اول را انتخاب می کند messages جدول ، تنظیم کلبه کل برابر با کلامی آن ردیف.
3. اتحادیه بازگشتی: قسمت دوم UNION ALL ادامه انتخاب پیام های بعدی (با فرض سفارش توسط id) و بررسی می کند که آیا اضافه کردن فعل آنها ، کل را زیر 70 نگه می دارد ، و نتایج معتبر را بیشتر به عقب می اندازد.
4. انتخاب نهایی: در آخر ، ما برای بازیابی پیام های معتبر از CTE خود انتخاب می کنیم.

متداول

اگر n متفاوت باشد چه می شود؟

شما به سادگی می توانید مقدار “70” را با آستانه مورد نظر خود در شرایط WHERE CTE بازگشتی جایگزین کنید.

آیا این راه حل در پایگاه داده های دیگر کار می کند؟

در حالی که این روش از ویژگی های SQL در PostgreSQL و SQLite استفاده می کند ، باید سازگاری نحو و محدودیت های بازگشت را در پایگاه داده های مختلف SQL تأیید کنید. بیشتر پایگاه داده های مدرن SQL نیز از CTE پشتیبانی می کنند و از کاربرد گسترده ای اطمینان می دهند.

پایان

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