تفاوت Aggregate function و analytic function در SQL چیست؟

Question

بسیاری از توابع داخلی SQL مانند SUM و COUNT علاوه بر اینکه به صورت Aggregate قابل استفاده اند، عنوان می شود که به صورت analytic نیز قابل استفاده اند. توابع analytic چیستند و اصولا این دو نوع تابع چه تفاوتی با هم داند؟

Answer 1

توابع Aggregrate بر روی یک یا چند ستون عمل کرده و نتیجه تجمیع شده آن را برای شما به نمایش در می آورد. در حالی که توابع Analytic پس از تحلیل یک یا چند ستون آن را در کنار سایر ستونهای عادی به شما نمایش می دهد.

 

برای مثال جدول زیر را در نظر بگیرید:

ELECT *
  FROM test_mohi;

که دارای مقادیر زیر است:

FIRST_FIELD SECOUND_FIELD
----------- -------------
          1             1
          2             1
          1             2
          2             2
          2             0


5 rows selected.

با استفاده از تابع COUNT به صورت تجمعی نتیجه زیر را خواهیم یافت:

SELECT   first_field, COUNT (first_field)
    FROM test_mohi
GROUP BY first_field;

FIRST_FIELD COUNT(FIRST_FIELD)
----------- ------------------
          1                  2
          2                  3


2 rows selected.

در حالی که به صورت analytic مقدار زیر را خواهیم داشت:

SELECT first_field, secound_field,
       COUNT (first_field) OVER (PARTITION BY first_field)
  FROM test_mohi;


FIRST_FIELD SECOUND_FIELD COUNT(FIRST_FIELD)OVER(PARTITIONBYFIRST_FIELD)
----------- ------------- ----------------------------------------------
          1             1                                              2
          1             2                                              2
          2             0                                              3
          2             2                                              3
          2             1                                              3


5 rows selected.

حتما تفاوتش را متوجه شدید!

هنگام کار به صورت aggregrate نتیجه به group by محدود می شود و تنها در خروجی می توانیم از فیلدهای استفاده شده در group by استفاده نماییم. و برای مثال کوئری زیر در اوراکل منجر به خطا می شود:

SELECT   first_field, secound_field, COUNT (first_field)
    FROM test_mohi
GROUP BY first_field
                      *
Error at line 1
ORA-00979: not a GROUP BY expression

اما در توابع analytic می توانیم از تمام فیلدها استفاده نماییم و نتیجه تحلیل سایر فیلدها را نیز در کنار نتیجه مان داشته باشیم.