Oracle Partition By Keyword

Question

Can someone please explain what the partition by keyword does and give a simple example of it in action  as well as why one would want to use it   I have a SQL query written by someone else and I m trying to figure out what it does   An example of partition by   SELECT empno  deptno  COUNT     OVER  PARTITION BY deptno  DEPT COUNT FROM emp   The examples I ve seen online seem a bit too in-depth

User · Answer

It is the SQL extension called analytics. The "over" in the select statement tells oracle that the function is a analytical function, not a group by function. The advantage to using analytics is that you can collect sums, counts, and a lot more with just one pass through of the data instead of looping through the data with sub selects or worse, PL/SQL.

It does look confusing at first but this will be second nature quickly. No one explains it better then Tom Kyte. So the link above is great.

Of course, reading the documentation is a must.

User · Answer

The concept is very well explained by the accepted answer  but I find that the more example one sees  the better it sinks in  Here s an incremental example   1  Boss says  get me number of items we have in stock grouped by brand   You say   no problem   SELECT        BRAND        COUNT ITEM ID   FROM        ITEMS GROUP BY        BRAND    Result    -------------- ---------------     Brand           Count           -------------- ---------------    H amp M                50           -------------- ---------------    Hugo Boss          100          -------------- ---------------    No brand           22           -------------- ---------------    2  The boss says  Now get me a list of all items  with their brand AND number of items that the respective brand has   You may try    SELECT        ITEM NR        BRAND        COUNT ITEM ID    FROM        ITEMS  GROUP BY        BRAND    But you get   ORA-00979  not a GROUP BY expression    This is where the OVER  PARTITION BY BRAND  comes in    SELECT        ITEM NR        BRAND        COUNT ITEM ID  OVER  PARTITION BY BRAND    FROM        ITEMS    Whic means    COUNT ITEM ID  - get the number of items OVER - Over the set of rows  PARTITION BY BRAND  - that have the same brand   And the result is    -------------- --------------- ----------     Items          Brand          Count       -------------- --------------- ----------     Item 1         Hugo Boss        100        -------------- --------------- ----------     Item 2         Hugo Boss        100        -------------- --------------- ----------     Item 3         No brand         22         -------------- --------------- ----------     Item 4         No brand         22         -------------- --------------- ----------     Item 5         H amp M              50         -------------- --------------- ----------    etc

User · Answer

I think  this example suggests a small nuance on how the partitioning works and how group by works  My example is from Oracle 12  if my example happens to be a compiling bug   I tried    SELECT t data key        SUM   CASE when t state    A  THEN 1 ELSE 0 END   OVER    PARTITION BY t data key  count a rows        SUM   CASE when t state    B  THEN 1 ELSE 0 END   OVER    PARTITION BY t data key  count b rows        SUM   CASE when t state    C  THEN 1 ELSE 0 END   OVER    PARTITION BY t data key  count c rows        COUNT  1  total rows from mytable t group by t data key  ---- This does not compile as the compiler feels that t state isn t in the group by and doesn t recognize the aggregation I m looking for   This however works as expected    SELECT distinct t data key        SUM   CASE when t state    A  THEN 1 ELSE 0 END   OVER    PARTITION BY t data key  count a rows        SUM   CASE when t state    B  THEN 1 ELSE 0 END   OVER    PARTITION BY t data key  count b rows        SUM   CASE when t state    C  THEN 1 ELSE 0 END   OVER    PARTITION BY t data key  count c rows        COUNT  1  total rows from mytable t    Producing the number of elements in each state based on the external key  data key    So  if  data key    APPLE  had 3 rows with state  A   2 rows with state  B   a row with state  C   the corresponding row for  APPLE  would be  APPLE   3  2  1  6

User · Answer

the over partition keyword is as if we are partitioning the data by  client id creation a subset of each client id  select client id  operation date         row number   count    over  partition by client id order by client id   as operationctrbyclient from client operations e order by e client id    this query will return the number of operations done by the client id

User · Answer

EMPNO     DEPTNO DEPT COUNT      7839         10          4  5555         10          4  7934         10          4  7782         10          4 --- 4 records in table for dept 10  7902         20          4  7566         20          4  7876         20          4  7369         20          4 --- 4 records in table for dept 20  7900         30          6  7844         30          6  7654         30          6  7521         30          6  7499         30          6  7698         30          6 --- 6 records in table for dept 30   Here we are getting count for respective deptno  As for deptno 10 we have 4 records in table emp similar results for deptno 20 and 30 also

User · Answer

The PARTITION BY clause sets the range of records that will be used for each  GROUP  within the OVER clause   In your example SQL  DEPT COUNT will return the number of employees within that department for every employee record    It is as if you re de-nomalising the emp table  you still return every record in the emp table    emp no  dept no  DEPT COUNT 1       10       3 2       10       3 3       10       3  lt - three because there are three  dept no   10  records 4       20       2 5       20       2  lt - two because there are two  dept no   20  records   If there was another column  e g   state  then you could count how many departments in that State   It is like getting the results of a GROUP BY  SUM  AVG  etc   without the aggregating the result set  i e  removing matching records    It is useful when you use the LAST OVER or MIN OVER functions to get  for example  the lowest and highest salary in the department and then use that in a calculation against this records salary without a sub select  which is much faster   Read the linked AskTom article for further details

[sql] Oracle "Partition By" Keyword

Examples related to sql

Examples related to oracle

Examples related to window-functions