اندازهی قلم متن
تخمین مدت زمان مطالعهی مطلب:
دو دقیقه
مقدمه (شرح مساله)
چندی پیش در تالار T-SQL سوالی مطرح شد راجع به مساله ای که معروف است به top N per group.
تنها موضوعی که باعث شد من مطلبی راجع به آن بنویسم محدودیتی بود که کاربر مورد نظر داشت؛ که آن محدودیت چیزی نبود جز: query بایستی در نسخه 2000 جوابگو باشد.
قطعا شده است که بخواهید مثلا به ازای هر مشتری آخرین سفارش آن را انتخاب کنید. این مساله Top N نامیده میشود.
فرض کنید جدولی داریم که حاوی سفارشات مشتریان میباشد. هر مشتری میتواند چندین سفارش داشته باشد؛ هر سفارش دارای حداقل دو مقدار "تاریخ سفارش" و "مبلغ سفارش است". هدف پیدا کردن آخرین سفارشات هر مشتری میباشد.
نکته: اگر چند تاریخ برای آخرین سفارش مشتری وجود داشت آنگاه بایستی بر اساس مبلغ سفارش مرتب سازی نزولی صورت بگیرد. یا به عبارت دیگر ابتدا باید مرتب سازی نزولی بر اساس ستون تاریخ سفارش انجام شود و سپس مرتب سازی نزولی بر اساس ستون مبلغ سفارش.
فرض میگیریم دادههای جدول ما چیزیست شبیه به این:
سطرهایی از جدول که رنگی شده اند سطرهای مورد نظر ما هستند که باید در خروجی ظاهر شوند.
دادههای جدول با کمک قابلیت Sort نرم افزار word مرتب سازی شده اند، این تصویر را به این خاطر در اینجا قرار دادم چون که دیدم میتواند در شفاف سازی مساله به من کمک کند.
ابتدا مرتب سازی نزولی بر اساس ستون order_date انجام گرفته و سپس مرتب سازی نزولی بر اساس ستون order_value. و در پایان اولین سطر مربوط به هر مشتری به عنوان خروجی مورد نظر انتخاب میشوند.
راه حل ها
خب پر واضح است که در نسخه 2005 و بعد از آن سادهترین و بهینهترین راه حل استفاده از تابع row_number میباشد.
اما با محدودیتی که در نسخه 2000 وجود دارد راه حلی بهتر از این پیدا نخواهیم کرد:
حالا چه میشود راه حلی بخواهیم مستقل از هر یک از نسخههای SQL Server:
چندی پیش در تالار T-SQL سوالی مطرح شد راجع به مساله ای که معروف است به top N per group.
تنها موضوعی که باعث شد من مطلبی راجع به آن بنویسم محدودیتی بود که کاربر مورد نظر داشت؛ که آن محدودیت چیزی نبود جز: query بایستی در نسخه 2000 جوابگو باشد.
قطعا شده است که بخواهید مثلا به ازای هر مشتری آخرین سفارش آن را انتخاب کنید. این مساله Top N نامیده میشود.
فرض کنید جدولی داریم که حاوی سفارشات مشتریان میباشد. هر مشتری میتواند چندین سفارش داشته باشد؛ هر سفارش دارای حداقل دو مقدار "تاریخ سفارش" و "مبلغ سفارش است". هدف پیدا کردن آخرین سفارشات هر مشتری میباشد.
نکته: اگر چند تاریخ برای آخرین سفارش مشتری وجود داشت آنگاه بایستی بر اساس مبلغ سفارش مرتب سازی نزولی صورت بگیرد. یا به عبارت دیگر ابتدا باید مرتب سازی نزولی بر اساس ستون تاریخ سفارش انجام شود و سپس مرتب سازی نزولی بر اساس ستون مبلغ سفارش.
فرض میگیریم دادههای جدول ما چیزیست شبیه به این:
سطرهایی از جدول که رنگی شده اند سطرهای مورد نظر ما هستند که باید در خروجی ظاهر شوند.
دادههای جدول با کمک قابلیت Sort نرم افزار word مرتب سازی شده اند، این تصویر را به این خاطر در اینجا قرار دادم چون که دیدم میتواند در شفاف سازی مساله به من کمک کند.
ابتدا مرتب سازی نزولی بر اساس ستون order_date انجام گرفته و سپس مرتب سازی نزولی بر اساس ستون order_value. و در پایان اولین سطر مربوط به هر مشتری به عنوان خروجی مورد نظر انتخاب میشوند.
راه حل ها
خب پر واضح است که در نسخه 2005 و بعد از آن سادهترین و بهینهترین راه حل استفاده از تابع row_number میباشد.
SELECT row_id, customer_id, order_date, order_value FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY order_date DESC, order_value DESC) AS rnk FROM table_name )t WHERE rnk = 1;
اما با محدودیتی که در نسخه 2000 وجود دارد راه حلی بهتر از این پیدا نخواهیم کرد:
SELECT * FROM table_name t WHERE row_id = (SELECT TOP 1 row_id FROM table_name WHERE customer_id = t.customer_id ORDER BY order_date DESC, order_value DESC);
حالا چه میشود راه حلی بخواهیم مستقل از هر یک از نسخههای SQL Server:
SELECT MIN(row_id) AS row_id, customer_id, order_date, order_value FROM table_name t WHERE order_date = (SELECT MAX(order_date) FROM table_name WHERE customer_id = t.customer_id) AND order_value = (SELECT MAX(order_value) FROM table_name WHERE customer_id = t.customer_id AND order_date = (SELECT MAX(order_date) FROM table_name WHERE customer_id = t.customer_id)) GROUP BY customer_id, order_date, order_value;