همه ی ما می دانیم که چگونه از توابع تجمعی مثل
MAX و ... برای محاسبه تجمعی یکسری سطر استفاده کنیم.
ولی آیا تابعی وجود دارد که محاسبات را روی مجموعه ای از ستون ها انجام دهد. مثلا
فرض کنید جدول ما شامل 10 ستون عددی می باشد می خواهیم به ازای هر سطر بزگترین
مقدار در ستون های مختلف را بدست آورد.
اجازه بدین موضوع را با مثال ادامه دهیم.
ابتدا جدول را ایجاد می کنیم و سپس چند نمونه داده آزمایشی انتشار می دهیم.
CREATE TABLE Foo
(keycol INTEGER NOT NULL PRIMARY KEY,
col0 INTEGER NOT NULL,
col1 INTEGER NOT NULL,
col2 INTEGER NOT NULL,
col3 INTEGER NOT NULL,
col4 INTEGER NOT NULL,
col5 INTEGER NOT NULL,
col6 INTEGER NOT NULL,
col7 INTEGER NOT NULL,
col8 INTEGER NOT NULL,
col9 INTEGER NOT NULL);
INSERT INTO Foo (keycol, col0, col1, col2, col3, col4, col5, col6, col7, col8, col9)
VALUES (1, 0, 4, 3, 8, 9, 45, 8, 4, 15, 0),
(2, 30, 8, 11, 10, 8, 5, 90, 50, 4, 16),
(3, 18, 60, 5, 5, 0, 0, 0, 0, 0, 0),
(4, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1);
هدف ما بدست آوردن بزرگترین و کوچکترین مقدار موجود در 10 ستون مورد
نظر به ازای هر سطر است. پس با وجود داده های فوق نتیجه ی زیر صحیح است:
keycol max_col max_val min_col min_val
----------- ---------------- ----------- ---------------- -----------
1 col5 45 col0 0
2 col6 90 col8 4
3 col1 60 col4 0
4 col9 1 col0 1
آقای MVP Plamen Ratchev در
وبلاگ خود در
Post
مربوطه به روش های
XML در کنار روش های
Unpivoting پرداخته است که در صورت تمایل می توانید با
آنها آشنا شوید. در اینجا من علاوه بر روشهای معرفی شده توسط ایشان روشهای دیگری را
معرفی می کنم که مورد تایید ایشان هم واقع شده است.
روش CASE و
=<
اولین و ساده ترین روش که ممکنه به ذهن هر انسانی که با
Data در ارتباط هست برسد بهره مندی از
CASE است. یعنی در دو عبارت CASE
بزرگترین و
کوچکترین مقدار را بدست آوریم. مثلا برای بزرگترین عدد در WHEN اول
بررسی می کنیم که آیا مقدار ستون اول بزرگترین هست یا خیر اگر بود که برگشت می خورد
ولی اگر نبود در WHEN های بعدی دیگر نیازی به آن نیست
چرا که مشخص شده است مقداری بزرگتر از آن وجود دارد و همین طور تا آخر.
SELECT keycol,
CAST(PARSENAME(max_parsing, 1) AS VARCHAR(4))AS max_col,
PARSENAME(max_parsing, 2) * 1 AS max_val,
CAST(PARSENAME(min_parsing, 1) AS VARCHAR(4)) AS max_col,
PARSENAME(min_parsing, 2) * 1 AS min_val
FROM (SELECT keycol,
CASE WHEN col0 >= col1 AND col0 >= col2 AND col0 >= col3 AND col0 >= col4 AND col0 >= col5
AND col0 >= col6 AND col0 >= col7 AND col0 >= col8 AND col0 >= col9
THEN CAST(col0 AS VARCHAR(10)) + '.col0'
WHEN col1 >= col2 AND col1 >= col3 AND col1 >= col4
AND col1 >= col5 AND col1 >= col6 AND col1 >= col7 AND col1 >= col8 AND col1 >= col9
THEN CAST(col1 AS VARCHAR(20)) + '.col1'
WHEN col2 >= col3 AND col2 >= col4 AND col2 >= col5 AND col2 >= col6 AND col2 >= col7
AND col2 >= col8 AND col2 >= col9
THEN CAST(col2 AS VARCHAR(10)) + '.col2'
WHEN col3 >= col4 AND col3 >= col5 AND col3 >= col6 AND col3 >= col7 AND col3 >= col8
AND col3 >= col9
THEN CAST(col3 AS VARCHAR(10)) + '.col3'
WHEN col4 >= col5 AND col4 >= col6 AND col4 >= col7 AND col4 >= col8 AND col4 >= col9
THEN CAST(col4 AS VARCHAR(10)) + '.col4'
WHEN col5 >= col6 AND col5 >= col7 AND col5 >= col8 AND col5 >= col9
THEN CAST(col5 AS VARCHAR(10)) + '.col5'
WHEN col6 >= col7 AND col6 >= col8 AND col6 >= col9
THEN CAST(col6 AS VARCHAR(10)) + '.col6'
WHEN col7 >= col8 AND col7 >= col9
THEN CAST(col7 AS VARCHAR(10)) + '.col7'
WHEN col8 >= col9
THEN CAST(col8 AS VARCHAR(10)) + '.col8'
ELSE CAST(col9 AS VARCHAR(10)) + '.col9'
END AS max_parsing,
CASE WHEN col0 <= col1 AND col0 <= col2 AND col0 <= col3 AND col0 <= col4 AND col0 <= col5
AND col0 <= col6 AND col0 <= col7 AND col0 <= col8 AND col0 <= col9
THEN CAST(col0 AS VARCHAR(10)) + '.col0'
WHEN col1 <= col2 AND col1 <= col3 AND col1 <= col4 AND col1 <= col5 AND col1 <= col6
AND col1 <= col7 AND col1 <= col8 AND col1 <= col9
THEN CAST(col1 AS VARCHAR(20)) + '.col1'
WHEN col2 <= col3 AND col2 <= col4 AND col2 <= col5 AND col2 <= col6 AND col2 <= col7
AND col2 <= col8 AND col2 <= col9
THEN CAST(col2 AS VARCHAR(10)) + '.col2'
WHEN col3 <= col4 AND col3 <= col5 AND col3 <= col6 AND col3 <= col7 AND col3 <= col8
AND col3 <= col9
THEN CAST(col3 AS VARCHAR(10)) + '.col3'
WHEN col4 <= col5 AND col4 <= col6 AND col4 <= col7 AND col4 <= col8 AND col4 <= col9
THEN CAST(col4 AS VARCHAR(10)) + '.col4'
WHEN col5 <= col6 AND col5 <= col7 AND col5 <= col8 AND col5 <= col9
THEN CAST(col5 AS VARCHAR(10)) + '.col5'
WHEN col6 <= col7 AND col6 <= col8 AND col6 <= col9
THEN CAST(col6 AS VARCHAR(10)) + '.col6'
WHEN col7 <= col8 AND col7 <= col9
THEN CAST(col7 AS VARCHAR(10)) + '.col7'
WHEN col8 <= col9
THEN CAST(col8 AS VARCHAR(10)) + '.col8'
ELSE CAST(col9 AS VARCHAR(10)) + '.col9'
END AS min_parsing,
col1
FROM foo f1
) AS D;
ممکنه از لحاظ عملکرد و سرعت روش مناسبی باشد ولی
همانطور که مشاهده می شود طول
Query بشکل وحشتناکی زیاد است در نتیجه هنگام توسعه، اصلاح و
تایپ آن با مشکل مواجه خواهیم شد.
روش ALL=< و
CASE
روش بعدی کمی خلاقانه تر است و از ترکیب گزاره ی
ALL و عملگر UNION ALL تشکیل
شده است. در اینجا از نظر منطقی فرقی نمی کند که از UNION
استفاده شود یا UNION ALL ولی به لحاظ عملکرد ممکن است
متفاوت باشند چرا که UNION ALL مقادیر تکراری را حذف نمی
کند و در نتیجه سریعتر اجرا خواهد شد.
SELECT keycol,
CAST(PARSENAME(max_parsing, 1) AS VARCHAR(4)) AS max_col,
PARSENAME(max_parsing, 2) * 1 AS max_val,
CAST(PARSENAME(min_parsing, 1) AS VARCHAR(4)) AS min_col,
PARSENAME(min_parsing, 2) * 1 AS min_val
FROM (SELECT keycol,
CASE WHEN col0 >=ALL (SELECT col1 UNION ALL
SELECT col2 UNION ALL
SELECT col3 UNION ALL
SELECT col4 UNION ALL
SELECT col5 UNION ALL
SELECT col6 UNION ALL
SELECT col7 UNION ALL
SELECT col8 UNION ALL
SELECT col9)
THEN CAST(col0 AS VARCHAR(10)) + '.col1'
WHEN col1 >=ALL (SELECT col2 UNION ALL
SELECT col3 UNION ALL
SELECT col4 UNION ALL
SELECT col5 UNION ALL
SELECT col6 UNION ALL
SELECT col7 UNION ALL
SELECT col8 UNION ALL
SELECT col9)
THEN CAST(col1 AS VARCHAR(10)) + '.col1'
WHEN col2 >=ALL (SELECT col3 UNION ALL
SELECT col4 UNION ALL
SELECT col5 UNION ALL
SELECT col6 UNION ALL
SELECT col7 UNION ALL
SELECT col8 UNION ALL
SELECT col9)
THEN CAST(col2 AS VARCHAR(10)) + '.col2'
WHEN col3 >=ALL (SELECT col4 UNION ALL
SELECT col5 UNION ALL
SELECT col6 UNION ALL
SELECT col7 UNION ALL
SELECT col8 UNION ALL
SELECT col9)
THEN CAST(col3 AS VARCHAR(10)) + '.col3'
WHEN col4 >=ALL (SELECT col5 UNION ALL
SELECT col6 UNION ALL
SELECT col7 UNION ALL
SELECT col8 UNION ALL
SELECT col9)
THEN CAST(col4 AS VARCHAR(10)) + '.col4'
WHEN col5 >=ALL (SELECT col6 UNION ALL
SELECT col7 UNION ALL
SELECT col8 UNION ALL
SELECT col9)
THEN CAST(col5 AS VARCHAR(10)) + '.col5'
WHEN col6 >=ALL (SELECT col7 UNION ALL
SELECT col8 UNION ALL
SELECT col9)
THEN CAST(col6 AS VARCHAR(10)) + '.col6'
WHEN col7 >=ALL (SELECT col8 UNION ALL
SELECT col9)
THEN CAST(col7 AS VARCHAR(10)) + '.col7'
WHEN col8 >=ALL (SELECT col9)
THEN CAST(col8 AS VARCHAR(10)) + '.col8'
ELSE CAST(col9 AS VARCHAR(10)) + '.col9'
END AS max_parsing,
CASE WHEN col0 <=ALL (SELECT col1 UNION ALL
SELECT col2 UNION ALL
SELECT col3 UNION ALL
SELECT col4 UNION ALL
SELECT col5 UNION ALL
SELECT col6 UNION ALL
SELECT col7 UNION ALL
SELECT col8 UNION ALL
SELECT col9)
THEN CAST(col0 AS VARCHAR(10)) + '.col1'
WHEN col1 <=ALL (SELECT col2 UNION ALL
SELECT col3 UNION ALL
SELECT col4 UNION ALL
SELECT col5 UNION ALL
SELECT col6 UNION ALL
SELECT col7 UNION ALL
SELECT col8 UNION ALL
SELECT col9)
THEN CAST(col1 AS VARCHAR(10)) + '.col1'
WHEN col2 <=ALL (SELECT col3 UNION ALL
SELECT col4 UNION ALL
SELECT col5 UNION ALL
SELECT col6 UNION ALL
SELECT col7 UNION ALL
SELECT col8 UNION ALL
SELECT col9)
THEN CAST(col2 AS VARCHAR(10)) + '.col2'
WHEN col3 <=ALL (SELECT col4 UNION ALL
SELECT col5 UNION ALL
SELECT col6 UNION ALL
SELECT col7 UNION ALL
SELECT col8 UNION ALL
SELECT col9)
THEN CAST(col3 AS VARCHAR(10)) + '.col3'
WHEN col4 <=ALL (SELECT col5 UNION ALL
SELECT col6 UNION ALL
SELECT col7 UNION ALL
SELECT col8 UNION ALL
SELECT col9)
THEN CAST(col4 AS VARCHAR(10)) + '.col4'
WHEN col5 <=ALL (SELECT col6 UNION ALL
SELECT col7 UNION ALL
SELECT col8 UNION ALL
SELECT col9)
THEN CAST(col5 AS VARCHAR(10)) + '.col5'
WHEN col6 <=ALL (SELECT col7 UNION ALL
SELECT col8 UNION ALL
SELECT col9)
THEN CAST(col6 AS VARCHAR(10)) + '.col6'
WHEN col7 <=ALL (SELECT col8 UNION ALL
SELECT col9)
THEN CAST(col7 AS VARCHAR(10)) + '.col7'
WHEN col8 <=ALL (SELECT col9)
THEN CAST(col8 AS VARCHAR(10)) + '.col8'
ELSE CAST(col9 AS VARCHAR(10)) + '.col9'
END AS min_parsing,
col1
FROM foo f1
) AS D;
این روش هم از لحاظ طولانی بودن کد دست کمی از روش قبلی
ندارد در نتیجه در پروژه های واقعی امکان استفاده از چنین روشهایی تقریبا غیر ممکن
است.
روش Unpivoting توسط
UNPIVOT
SELECT keycol,
MAX(CASE WHEN maximum = 1 THEN attribute END) AS max_col,
MAX(value) AS maximum_value,
MAX(CASE WHEN minimum = 1 THEN attribute END) AS min_col,
MIN(value) AS minimum_value
FROM (SELECT *,
ROW_NUMBER() OVER(PARTITION BY keycol
ORDER BY value DESC, attribute) AS maximum,
ROW_NUMBER() OVER(PARTITION BY keycol
ORDER BY value ASC, attribute) AS minimum
FROM Foo
UNPIVOT (value FOR attribute IN (col0, col1, col2, col3, col4,
col5, col6, col7, col8, col9)
) AS U
) AS D
/* Unnecessary Filter */
WHERE minimum = 1
OR maximum = 1
GROUP BY keycol;
روش Unpivoting توسط
CROSS APPLY
روش زیر نه تنها جایگزینی شایسته برای
UNPIVOT هست بلکه کاربرد ماده ی
VALUES و عملگر APPLY را به زیبایی به نمایش می
گذارد.
SELECT keycol,
CAST(SUBSTRING(MAX(CAST(value AS BINARY(4)) +
CAST(attribute AS BINARY(4))), 5, 4) AS VARCHAR(16)) AS max_col,
CAST(SUBSTRING(MAX(CAST(value AS BINARY(4)) +
CAST(attribute AS BINARY(4))), 1, 4) AS INT) AS max_val,
CAST(SUBSTRING(MIN(CAST(value AS BINARY(4)) +
CAST(attribute AS BINARY(4))), 5, 4) AS VARCHAR(16)) AS min_col,
CAST(SUBSTRING(MIN(CAST(value AS BINARY(4)) +
CAST(attribute AS BINARY(4))), 1, 4) AS INT) AS min_val
FROM Foo
CROSS APPLY (VALUES (col0, 'col0'), (col1, 'col1'), (col2, 'col2'), (col3, 'col3'), (col4, 'col4'),
(col5, 'col5'), (col6, 'col6'), (col7, 'col7'), (col8, 'col8'), (col9, 'col9')
) AS D1(value, attribute)
GROUP BY keycol;