equivalence of aggregate and analytic functions?

[dbmsuser7]

Are the following generally equivalent?

Code: Select all

SELECT DISTINCT
  a1,
  a2,
  COUNT(*) OVER w AS cnt,
  SUM(b) OVER w AS sum_b
FROM
  ...
WHERE
  ...
WINDOW w AS (PARTITION BY a1, a2);

and

Code: Select all

SELECT
  a1,
  a2,
  COUNT(*) AS cnt,
  SUM(b) AS sum_b
FROM
  ...
WHERE
  ...
GROUP BY a1, a2;

Or are there edge cases where they might not be?

[id10t]

Hi!

[DELETED]

[dbmsuser7]

sKwa,

Thanks for the reply (as usual!).

So there might be performance differences, but no differences in result?

[scutter]

There is a difference in the results. Re-read what sKwa wrote:

Analytical function returns result for each row, while aggregative calculates for group and returns result per group. If you have "good" projection so aggregative function should work much more faster, i.e. with analytical function you returns a result per row and after it you are doing a grouping, while aggregate function calculates a value per group

—Sharon

[dbmsuser7]

Understood. But I'm not referring to the differences in output between aggregate and analytic functions in general, but rather the differences in output between the two queries I posted above.

[scutter]

I see now what you’re asking - if using the DISTINCT keyword produces the same results as the analytical function. Yes - it would for these two queries.

—Sharon

[dbmsuser7]

Yes - it would for these two queries.

And there's no funny edge cases that are treated differently (e.g. NULLs in the grouping columns)?