محتویات سایت
        برچسب های محبوب 








 
   تجمع ستونی (Column Aggregates)
  همه ما می دانیم که چگونه توابعی تجمعی روی سطرها اعمال می شوند ولی آیا تابعی وجود دارد که تجمع ستونی را انجام دهد؟ این مقاله به این موضوع اختصاص دارد.
   SQL Server
   ۱۶۰۷۴
   این مقاله حاوی فایل ضمیمه نمی باشد
   محمد سلیم آبادی
   ۱۳۸۹/۵/۱۳
نسخه قابل چاپ نسخه قابل چاپ

همه ی ما می دانیم که چگونه از توابع تجمعی مثل 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 ENDAS max_col,       
        MAX(value) AS maximum_value,       
        MAX(CASE WHEN minimum = 1 THEN attribute ENDAS 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 INTAS 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 INTAS 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;

 

برچسب های مرتبط