7

Can someone please explain me, what is the difference between qualify...over...partition by and group by...having in Teradata?
I would also like to know if there are any differences in their performances.

pri
  • 1,521
  • 2
  • 13
  • 26

2 Answers2

23

QUALIFY is a proprietary extension to filter the result of a Windowed Aggregate Function.

A query is logically processed in a specific order:

  1. FROM: create the basic result set
  2. WHERE: remove rows from the previous result set
  3. GROUP BY: apply aggregate functions on the previous result set
  4. HAVING: remove rows from the previous result set
  5. OVER: apply windowed aggregate functions on the previous result set
  6. QUALIFY: remove rows from the previous result set
dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • But OVER can for instance go in the SELECT too; for instance SELECT a, b, Row_number() OVER (PARTITION BY a, b ORDER BY abweight) AS rownum_ab from Table1. The above ordering doesn't necessarily hold for such cases? – samsamara May 23 '23 at 01:09
  • 1
    @samsamara Of course, just like using an aggregate in the Select list. The columns in the SELECT list are actually created *after* all these steps, before the final ORDER BY. – dnoeth May 23 '23 at 06:53
3
Having clause is used to filter the result set of the aggregate functions like (COUNT,min,max etc)
they eliminate rows based from groups based on some criteria like this :-
SELECT dept_no, MIN(salary), MAX(salary), AVG(salary)
     FROM employee
     WHERE dept_no IN (100,300,500,600)
     GROUP BY dept_no
     HAVING AVG(salary) > 37000;

The QUALIFY clause eliminates rows based on the function value, returning a new value for each of the participating rows.
It works on the final result set.

SELECT NAME,LOCATION FROM EMPLOYEE
 QUALIFY ROW_NUMBER() OVER ( PARTITION BY NAME ORDER BY JOINING_DATE DESC) = 1;


We can club both having and qualify as well in a query if we use both aggregate and analytical fucntion like below:-
SELECT StoreID, SUM(sale),
   SUM(profit) OVER (PARTITION BY StoreID)
   FROM facts
   GROUP BY StoreID, sale, profit
   HAVING SUM(sale) > 15
   QUALIFY SUM(profit) OVER (PARTITION BY StoreID) > 2;   



You can see there order of execution from dnoeth answer.
anwaar_hell
  • 756
  • 5
  • 23