Why is Hibernate so popular? Because it automates a tedious task - persisting your Java objects to a relational database. If Hibernate makes ...
Oracle Analytic Functions
Oracle analytic functions compute an aggregate value based on a group of rows and provide the ability to reference values across multi-level aggregation and granular control of sort order in a subset of data.
OLAP queries perform multidimensional aggregation to support business decision-making processes. It is very important to make these statements maximally efficient. Conventional SQL queries usually perform multiple self-joins, leading to poor performance. Moreover, we often need aggregations at various levels, with aggregated and non-aggregated rows in the result set. Analytic functions return these both values without any self-joins. Window functions enable calculating cumulative and moving aggregates returning multiple rows for each group.
Understanding analytic functions will give you a new way of thinking and allow tuning complex SQL statements to make them highly efficient.
Oracle analytic functions video tutorial. 3 lessons, total length 39 minutes.
- Oracle Partition by clause (09:10)
analytic function syntax, over (), over partition by, order by, nulls first, nulls last, row_number
- Oracle analytic functions (18:30)
group by grouping sets, group by cube, group by rollup, over partition by, within group, accumulative sum, functions: rank, dense_rank, cume_dist, percent rank, ratio_to report, covar_pop, ntile, stddev, variance
- Window functions and performance comparison (11:20)
windowing functions, unbounded preceding, unbounded following, current row, rows between, range between, first_value, last_value, lead function, lag function