When to use the OVER clause?

Asked

Viewed 4,187 times

2

To Documentation says :

Determines partitioning and sorting of the row set before application of the associated window function.

An example I created just to demonstrate this:

select ROW_NUMBER() OVER(PARTITION BY cor order by carro  ) from carro

This example would return a ROW_NUMBER counting until changing the condition of the column and restarting . In a table of 7 records with 2 records with the color yellow and 5 with the color blue Row_Number would go from 1 to 2 and reboot and would go from 1 to 5 again.

However for some aggregation functions, no partitioning is required to use this condition, for example this select found here:

SELECT  MAX(date) OVER (ORDER BY id)
FROM    mytable

But for clauses with partition of values the order by is required, but the partition is not, it depends on the function it is calling, so:

  • When should I use the clause OVER?
  • The Group By at the end of a select works the same way(in cases of partition)?
  • When I can use over functions without an order by, there are specific functions that allow this?

There is a question in SOEN but I found very generic the answer so I continue with some doubts.

Other references:

OVER CLAUSE

sql

  • The answer is in the question itself: window function. See article "Window functions (functions window)", in the SQL Port: https://portosql.wordpress.com/2018/10/14/funcoes-de-janela-window-functions/

  • 1

    @Josédiz will see, today I already have a better knowledge and I know how to use better so the explanation will make more sense

1 answer

5


The function OVER from what I understand it determines the partitioning and ordering of a set of N lines before application of the associated window function. Defining a window or set of user-specified lines in query results. In a way that, with a value for each line in the window.

You can use the clause OVER with functions to compute aggregated values such as moving averages, cumulative aggregations, accumulated sums or first N results per group.

Example of syntax:

-For Aggregation Functions(SUM - COUNT - AVG - etc)

    OVER(Partition by Campo)

-For Classification Functions(RANK - NTILE - DENSE_RANK - ROW_NUMBER)

    OVER(Partition by Campo Order by Campo)

Now let’s use it inside a SELECT, let’s demonstrate solution without OVER x with OVER

Solution WITH OVER, note that the clause Partition By works as if it were a GROUP BY. Creating groups, which we call windows. Making the query simpler.

SELECT Nome, Data,count(*) over(partition by Nome) TotalAcessos FROM Acesso

Solution otherwise you want to use OVER

SELECT  A.Nome, A.Data, TabGroup.TotalAcessos
FROM  Acesso A
INNER JOIN
(
    SELECT  Nome, COUNT(*) TotalAcessos
    FROM  Acesso
    GROUP BY  Nome
) TabGroup ON  A.Nome=TabGroup.Nome

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.