OLAP Functions OrderDependent Aggregates and Windows in SQL

OLAP Functions Order-Dependent Aggregates and Windows in SQL: 2003 ---same as SQL: 1999 Copyright: Silberschatz, Korth and Sudarshan 1

Extended Aggregation: OLAP Functions n Rank, row_number and other functions based on explicit order n Many new statistical functions: see, e. g. , DB 2 UDB's High-Function Business Intelligence in e-business---Red Book http: //www. redbooks. ibm. com/redbooks/SG 246546. html H VAR(X): variance, STDDEV(X): its square root H COV(X, Y): covariance is themeasure of the linear association between two variables. H CORR(X, Y): correlation is normalized covariance. +1 max correlation, 0 none, -1 inverse H Regression functions: Y = a X + b 4 REGR_SLOPE (X) -> a 4 REGR_INTERCEPT (X) -> b More: Chi-squared testing, sampling 2 Database System Concepts 4 th Edition 22. 2 ©Silberschatz, Korth and Sudarshan

OLAP Functions n Windows on aggregates: H Physical windows: based on the number of rows in the window H Logical windows: based on the value span of the window. 3 Database System Concepts 4 th Edition 22. 3 ©Silberschatz, Korth and Sudarshan

Ranking n Ranking is done in conjunction with an order by specification. Suppose we are given a relation student-marks(Name, marks) which stores the marks obtained by each student. The following query gives the rank of each student. Select Name, rank ( ) (order by (marks) desc) as s-rank, dense_dank ( ) (order by (marks) desc) as s-rank from student-marks order by s-rank Name Marks Rank Dense. Rank Tom 8 1 1 Jeff 7 2 2 Mary 7 2 2 Alex 6 4 3 4 Database System Concepts 4 th Edition 22. 4 ©Silberschatz, Korth and Sudarshan

Partition By n Ranking can be done within partition of the data. The following query then gives the rank of students within each section: student-marks(student-id, marks); student- section(student-id, section) select student-id, section, rank( ) over (partition by section order by marks desc) as sec-rank from student-marks, student-section where student-marks. student-id = student-section. student-id order by section, sec-rank n dense_rank(): no holes after ties n row_number(): provide row numbering given a specific partitioning and ordering of rows. 5 Database System Concepts 4 th Edition 22. 5 ©Silberschatz, Korth and Sudarshan

Ranking (Cont. ) n For a given constant n, the ranking the function ntile(n) takes the tuples in each partition in the specified order, and divides them into n buckets with equal numbers of tuples. For instance, we sort employees by salary, and use ntile(3) to find which range (bottom third, middle third, or top third) each employee is in, and compute the total salary earned by employees in each range: select threetile, sum(salary) from ( select salary, ntile(3) over (order by (salary) as threetile from employee) as s group by threetile n SQL permits the user to specify where they should occur by using nulls first or nulls last, for instance select student-id, rank ( ) over (order by marks desc nulls last) as s-rank from student-marks 6 Database System Concepts 4 th Edition 22. 6 ©Silberschatz, Korth and Sudarshan

Aggregates on Windows An example of window query is that, given sales values for each date, calculates for each date the average of the sales on that day, the previous day, and the next day; such moving average queries are used to smooth out random variations. n In contrast to group by, the same tuple can exist in multiple windows. Suppose we are given a relation transaction(account-number, date-time, value), n select account-number, date-time, sum(value) over (partition by account-number order by date-time range unbounded preceding) n This query returns a new sum for each new tuple—cumulative sum! n An actual window can also be specified: e. g. range 10 rows preceding range between 10 rows preceding and 4 following range 30 minutes preceding range interval 30 minutes preceding and current row 7 Database System Concepts 4 th Edition 22. 7 ©Silberschatz, Korth and Sudarshan