Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted
##
##### QUALIFY ... OVER (PARTITION BY ...) phrase

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-03-2012
03:24 PM

04-03-2012
03:24 PM

I'm pretty new to TD, and thought I was at the intermediate level of SQL knowledge until one of my bosses (actually my boss' boss) helped me to write a query, where he added the statement below to root out dupes:

SELECT a.*

FROM tmp_TEMP a

QUALIFY ROW_NUMBER() OVER(PARTITION BY TransID, TransLine ORDER BY ABS(Date1-Date2)) = 1

I have searched the Web as well as the TD documentation, and I am unable to find what I'm looking for. Can someone please provide, in layman's terms, what the three functions QUALIFY, OVER() and PARTITION BY actually do? I've figured out tha QUALIFY has something to do with a filter, but that's about all I've discovered.

Thanks much,

-- Matt

9 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-03-2012
11:42 PM

04-03-2012
11:42 PM

Hi,

Qualify filters on OLAP functions - simmilar to having clause in aggregate functions.

over and partition are both part of the row_number() function

check

OLAP functions in general and ROW_NUMBER in particular.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-09-2012
02:33 PM

04-09-2012
02:33 PM

The description didn't help much. IHowever, 've found a little more. The PARTITION BY is equivalent to a GROUP BY clause. I'm still kind of stuck on the purpose of the QUALIFY and OVER clauses, though. The example in the doc that you linked to has this short query:

SELECT StoreID, SUM(profit) OVER (PARTITION BY StoreID)

FROM facts

QUALIFY SUM(profit) OVER (PARTITION BY StoreID) > 2;

a) Why would you use a QUALIFY clause? What would be the result of the above query without the QUALIFY, OVER and PARTITION BY functions/clauses?

b) What does the OVER() function do?

I'm usually pretty good at finding the info I want, but this one has me stumped.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-10-2012
03:38 AM

04-10-2012
03:38 AM

You have a query and you have questions to the syntax. Why don't you try yourself?

The OVER is part of the OLAP functions syntax as the SET in an UPDATE.

QUALIFY will limit the result set - without you would get one row per row of facts back.

Partiton will reset the sum per storeid...

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-10-2012
04:32 AM

04-10-2012
04:32 AM

And some extracts from the documantation

The ANSI SQL:2008 window feature provides a way to dynamically define a subset of data, or

window, in an ordered relational database table. A window is specified by the OVER() phrase,

which can include the following clauses inside the parentheses:

• PARTITION BY

• ORDER BY

• RESET WHEN

• ROWS

PARTITION BY takes a column reference list and groups the rows based on the specified

column reference list over which the ordered analytical function executes. Such a grouping is

static.

If there is no PARTITION BY phrase or RESET WHEN phrase, then the entire result set,

delivered by the FROM clause, constitutes a single partition, over which the ordered analytical

function executes.

Use of QUALIFY Clause

Rows can be eliminated by applying conditions on the new column value. The QUALIFY

clause is analogous to the HAVING clause of aggregate functions. The QUALIFY clause

eliminates rows based on the function value, returning a new value for each of the

participating rows.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-10-2012
12:36 PM

04-10-2012
12:36 PM

That's the description that didn't help much (the doc's, not yours). With some of your earlier statements and what I found, this is sort of what I was looking for (i.e., layman's terms) ...

QUALIFY = start new filtered subset

ROW_NUMBER = self explanatory

OVER {req. by ROW_NUMBER} = start a grouping statement

PARTITION BY = equivalent to GROUP BY

Put it all together from the code I supplied

`QUALIFY ROW_NUMBER() OVER(PARTITION `

`BY`

`TransID, TransLine `

`ORDER`

`BY`

`ABS`

`(Date1-Date2)) = 1`

and you get a way to only return the top row of a set within a query. I realize you most likely knew that, but I was trying to understand not only what the statement did, but also each clause/function of the statement. A more simplified explanation might be:

Return the first row of a subset created by a Filter (QUALIFY) ROW_NUMBER by (OVER) grouping (PARTITION BY) <fields> with an answer set that is ORDERed BY <fields>.

Thanks for the help.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-09-2014
10:37 PM

11-09-2014
10:37 PM

Over (partition by column_name order by column_ Name) ------ This is OLTP Function SO we should use "Qualify" even though you use count,sum or any aggereate function before it.

We can use this indifferent ways:

Count(sal) over(partition by sal order by id)

Row number() over (partition by sal order by id)

rank() over(partition by sal order by id)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-27-2014
08:50 AM

12-27-2014
08:50 AM

http://pauldhip.blogspot.dk/2014/12/analytical-function-rownumber-with.html

The above URL will help with step by step explanation of QUALIFY ... OVER (PARTITION BY ...) phrase

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-28-2014
08:28 PM

12-28-2014
08:28 PM

Qualify basically tells the database to apply the analytical function (e.g: sum(),max(),min(),avg(),row_number(),rank() etc..) over the number of rows specified insider the over() clause.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-17-2015
04:57 AM

02-17-2015
04:57 AM

Thanks guys, i was also looking for the meaning of the same Keywords and i am happy to find it here.

Thanks again guys

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.