NTH_VALUE

توابع پنجره ای SQL NTH_VALUE(): توضیح جامع با مثال

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

نحوه عملکرد NTH_VALUE:

1. پنجره: اولین قدم تعریف پنجره ای است که NTH_VALUE در آن عمل خواهد کرد. این کار با استفاده از کلیدواژه های OVER و PARTITION BY انجام می شود.
2. ترتیب: سپس، ترتیب ردیف ها در داخل پنجره با استفاده از کلیدواژه ORDER BY مشخص می شود. این ترتیب برای تعیین Nامین مقداری که باید برگردانده شود، حیاتی است.
3. N: در نهایت، عدد N را با استفاده از آرگومان N مشخص می کنید. این عدد نشان می دهد که کدام مقدار در ترتیب مرتب شده باید برگردانده شود.

سینتکس:

NTH_VALUE(expr, N)
    OVER (
        PARTITION BY partition_expression
        ORDER BY order_expression
    )

اجزاء:

• expr: عبارتی که مقداری را برای هر ردیف در پنجره برمی گرداند.
• N: عددی که موقعیت مقدار مورد نظر را در ترتیب مرتب شده نشان می دهد.
• partition_expression: عبارتی که پنجره ها را بر اساس مقادیر مشخص پارتیشن بندی می کند.
• order_expression: عبارتی که ترتیب ردیف ها را در داخل هر پارتیشن تعیین می کند.

مثال:

فرض کنید جدولی به نام sales با ستون های customer_id, product_id, price و quantity داریم. می خواهیم قیمتی را پیدا کنیم که دومین بار برای هر مشتری برای یک محصول خاص ظاهر می شود.

SELECT
    customer_id,
    product_id,
    price,
    NTH_VALUE(price, ۲) OVER (
        PARTITION BY customer_id, product_id
        ORDER BY price
    ) AS second_price
FROM sales;

در این مثال:

• PARTITION BY customer_id, product_id: پنجره ها را بر اساس customer_id و product_id پارتیشن بندی می کند.
• ORDER BY price: ردیف ها را در هر پارتیشن بر اساس price به ترتیب صعودی مرتب می کند.
• NTH_VALUE(price, 2): دومین قیمت را در هر پارتیشن برمی گرداند.

نکات مهم:

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

منابع مفید:

• https://chartio.com/resources/tutorials/using-window-functions/
• https://mode.com/sql-tutorial/sql-window-functions/