ORACLE SQL - please explain OVER PARTITION BY

This is the syntax for Oracle Analytics.

Basically, a normal query is run and the results are retrieves intanally by the Database Engine, then the Analytics are applied to the results set and the Analytic function columns are computed.
There are several functions, Sum, Min, Max, Rank, Dense_rank, count, etc.

They sound similar to the agregate functions, but agregate function are either applied to every record in the results (without group by clause), or to groups or records. In eihter case, they reduce the number or rows returned with a normal aggregate function.

With the analytic functions, the number of rows returned is not reduced.  You can tell is is an analytic function, not a group by function, by the OVER keyword.  Over is followed by a 'windowing clause'. This is what is included inside the ( ).  The 'partition by' portion of a windowing clause works similar to the group by, it determines what portion of the result set to apply each analytic function to.  For some analytic function, the windowing clause must have an order by also.

Here are a couple examples:
SQL> select * from udttest;

IP           DEST             LINENO
------------ ------------ ----------
AAA          BBB                   1
AAA          CCC                   2
AAA          DDD                   3
AAA          EEE                   4
AAA          DDD                  -1
AAA          DDD                  -3
AAA          HHH

7 rows selected.

SQL> -- normal aggregate function
SQL> select DEST,count(*) from udttest group by DEST;

DEST           COUNT(*)
------------ ----------
BBB                   1
CCC                   1
DDD                   3
EEE                   1
HHH                   1

SQL> --- analytic count function - notice all rows are returned still
SQL> select DEST,count(*) over (partition by DEST) from udttest;

DEST         COUNT(*)OVER(PARTITIONBYDEST)
------------ -----------------------------
BBB                                      1
CCC                                      1
DDD                                      3
DDD                                      3
DDD                                      3
EEE                                      1
HHH                                      1

7 rows selected.

SQL> -- analytic sum function
SQL> select ip,dest,lineno,sum(lineno) over (partition by dest) sum_line,
  2                        sum(lineno) over (partition by ip) sum_ip
  3    from udttest;

IP           DEST             LINENO   SUM_LINE     SUM_IP
------------ ------------ ---------- ---------- ----------
AAA          BBB                   1          1          6
AAA          CCC                   2          2          6
AAA          DDD                   3         -1          6
AAA          DDD                  -3         -1          6
AAA          DDD                  -1         -1          6
AAA          EEE                   4          4          6
AAA          HHH                                         6

7 rows selected.

SQL> -- another example is using rank
SQL> select ip, dest,lineno,
  2     rank() over (partition by dest order by lineno) dest_rank_line
  3*   from udttest;

IP           DEST             LINENO DEST_RANK_LINE
------------ ------------ ---------- --------------
AAA          BBB                   1              1
AAA          CCC                   2              1
AAA          DDD                  -3              1
AAA          DDD                  -1              2
AAA          DDD                   3              3
AAA          EEE                   4              1
AAA          HHH                                  1

7 rows selected.

SQL> -- and the same, but we will order by lineno...
SQL> ---   notice the column values don't change, just the order as expected
SQL> select ip, dest,lineno,
  2     rank() over (partition by dest order by lineno) dest_rank_line
  3    from udttest order by lineno;

IP           DEST             LINENO DEST_RANK_LINE
------------ ------------ ---------- --------------
AAA          DDD                  -3              1
AAA          DDD                  -1              2
AAA          BBB                   1              1
AAA          CCC                   2              1
AAA          DDD                   3              3
AAA          EEE                   4              1
AAA          HHH                                  1

7 rows selected.

SQL>
SQL> -- this can be good for 'top N' queries
SQL> -- For instance, to get the top 3 records by lineno, we use a nested query
SQL> select * from (
  2    select ip,dest,lineno,
  3       rank() over (partition by ip order by lineno desc nulls last) rank
  4      from udttest
  5*  ) where rank <= 3;

IP           DEST             LINENO       RANK
------------ ------------ ---------- ----------
AAA          EEE                   4          1
AAA          DDD                   3          2
AAA          CCC                   2          3

SQL>