🗄 10 قابلیت کمترشناخته‌شده SQL که هر Developer باید بداند(پارت 2️⃣)

🪟 2. Window Functions

گاهی به محاسبه‌ای روی Rowهای مرتبط نیاز دارید، اما همچنان می‌خواهید هر Row به‌صورت جداگانه در نتیجه باقی بماند.
یک GROUP BY، Rowها را به یک Row برای هر Group تبدیل می‌کند. اما یک Window Function روی مجموعه‌ای از Rowها — یعنی همان Window — محاسبه انجام می‌دهد، در حالی که هر Row را دست‌نخورده حفظ می‌کند.

SELECT
    number,
    carrier,
    created_at,
    ROW_NUMBER() OVER (
        PARTITION BY carrier
        ORDER BY created_at DESC
    ) AS shipment_sequence,
    RANK() OVER (
        PARTITION BY carrier
        ORDER BY created_at DESC
    ) AS shipment_rank
FROM shipments.shipments;

SELECT
    number,
    status,
    created_at,
    LAG(status) OVER (
        ORDER BY created_at
    ) AS previous_status,
    LEAD(carrier) OVER (
        ORDER BY created_at
    ) AS next_carrier
FROM shipments.shipments;

ءQuery اول، Shipmentهای هر Carrier را بر اساس تاریخ رتبه‌بندی می‌کند. ROW_NUMBER() یک Sequence یکتا درون هر Carrier ایجاد می‌کند؛ یعنی همان PARTITION BY carrier. اما RANK() نیز همین کار را انجام می‌دهد، با این تفاوت که Rowهای دارای مقدار برابر، رتبه‌ی مشترک دریافت می‌کنند.
ءQuery دوم از LAG و LEAD استفاده می‌کند تا بدون نیاز به Self-Join، به Row قبلی و بعدی در ترتیب مشخص‌شده نگاه کند؛ در اینجا، Status قبلی و Carrier بعدی.
ءWindow Functionها روشی هستند که با استفاده از آن‌ها می‌توانید Running Total، Ranking، Moving Average و مقایسه‌ی Rowبه‌Row بسازید.
آن‌ها بخشی از Standard SQL هستند و در PostgreSQL، SQL Server، Oracle و MySQL 8+ کار می‌کنند.

🔗 3. LATERAL Joins

یک JOIN معمولی، دو Table را بر اساس یک شرط با یکدیگر Match می‌کند. اما نمی‌تواند برای هر Row از Table اول، یک Query جداگانه اجرا کند.
یک LATERAL JOIN می‌تواند این کار را انجام دهد. این قابلیت به یک Subquery در سمت راست اجازه می‌دهد به Columnهای Table سمت چپ Reference داشته باشد و برای هر Row، یک‌بار اجرا شود؛ بنابراین برای مسئله‌های Top-N-Per-Group بسیار مناسب است.

-- For each carrier, grab their single most recent shipment

SELECT
    c.carrier,
    s.number,
    s.status,
    s.created_at
FROM (
    SELECT DISTINCT carrier
    FROM shipments.shipments
) c
CROSS JOIN LATERAL (
    SELECT
        number,
        status,
        created_at
    FROM shipments.shipments
    WHERE carrier = c.carrier
    ORDER BY created_at DESC
    LIMIT 1
) s;

برای هر Carrier منحصربه‌فرد، Subquery مربوط به LATERAL جدیدترین Shipment آن Carrier را انتخاب می‌کند؛ یعنی ORDER BY created_at DESC LIMIT 1.
نکته‌ی اصلی این بخش عبارت WHERE carrier = c.carrier است. Query داخلی می‌تواند carrier مربوط به Row بیرونی را ببیند؛ قابلیتی که یک Subquery معمولی نمی‌تواند انجام دهد.
این روش، تمیزترین راه برای بیان مسئله‌ی «جدیدترین Row برای هر Group» یا «۳ مورد برتر برای هر Category» است، بدون اینکه به Window Function نیاز داشته باشید.
نکته: در SQL Server، همین قابلیت با CROSS APPLY نوشته می‌شود و برای نسخه‌ی مشابه Left Join از OUTER APPLY استفاده می‌شود. PostgreSQL از CROSS JOIN LATERAL و LEFT JOIN LATERAL استفاده می‌کند.