Oracle PLSQL Tutorial Analytical Functions 143 codes

  1. Home
  2. Oracle PLSQL Tutorial
  3. Analytical Functions
1 A different ordering with the use of analytical functions
2 A seven-day MAX and MIN on Tuesdays
3 A SUM using ROLLUP
4 A two-dimensional grouping with ROLLUP
5 Adding an Analytical Function to a Query that Contains a Join (and Other WHERE Conditions)
6 Adding an Analytical Function to the GROUP BY with ORDER BY Version
7 An Application of GROUPING_ID()
8 An Expanded Example of a Physical Window
9 AVG(salary) OVER
10 Calculate with Analytical functions
11 Case with grouping and rollup
12 Changing the Final Ordering after Having Added an Analytical Function
13 Changing the Position of Columns Passed to ROLLUP
14 Computing the GROUPING Bit Vector
15 Count employees, group by CUBE(department no, job title)
16 Count employees, group by ROLLUP(department no, job title)
17 CUBE
18 CUME_DIST functions
19 CUME_DIST() calculates the position of a specified value relative to a group of values
20 Cume_dist over
21 Decode dense_rank
22 Default is NULLS FIRST
23 DENSE_RANK NULL value
24 Depict the rank of the salaries with ROWNUM
25 Desacending order
26 Displaying a Running Total Using SUM as an Analytical Function
27 Displaying which rows are used in the moving average calculations with two other analytical functions
28 Eliminate duplicate rows using a HAVING clause that only allows rows whose GROUP_ID() is 0
29 FIRST functions return the first values in an ordered group
30 Get a clearer picture of the NTILE function
31 Getting the First Rows Using FIRST_VALUE()
32 Getting the Last Rows Using LAST_VALUE()
33 Getting Values and Subtotals in One Go with GROUPING SETS
34 Group by CUBE(department no, job title)
35 Group by ROLLUP(department no, job title)
36 Group_id with cube
37 GROUPING() with a Single Column in a ROLLUP
38 GROUPING() with CUBE demo
39 Grouping with Rollup and Cube
40 Grouping_id function with case statement
41 Include two columns in CUBE function
42 LAG and LEAD Options
43 Lag salary over, lead salary over
44 LAST functions return the last values in an ordered group
45 More Than One Analytical Function May Be Used in a Single Statement
46 Moving average
47 NTILE function works from row order after a ranking takes place
48 NTILE groups data by sort order into a variable number of percentile groupings
49 NTILE with NULLS FIRST (the default)
50 NTile with NULLS LAST
51 Nulls and Analytical Functions
52 Nulls and Analytical Functions ROW_NUMBER
53 Nulls could also be handled with a default value using the NVL function in the analytical function
54 NULLS FIRST
55 NULLS FIRST demo
56 NULLS LAST
57 NULLS LAST and the ROW_NUMBER
58 NULLS LAST demo
59 Nulls may be excluded with a WHERE clause when using Analytical functions
60 Nulls with the NTILE function
61 NVL without NULLS LAST
62 Order by range unbounded preceding
63 PARTITION BY (JOB title) and right outer join
64 PARTITION BY clause divides the groups into subgroups
65 PARTITION BY, order by and range unbounded preceding
66 Partitioning with PARTITION_BY
67 Passing Multiple Columns to ROLLUP
68 PERCENT_RANK
69 PERCENT_RANK() calculates the percent rank of a value relative to a group of values
70 Performing a Centered Average
71 Performing a Cumulative Sum
72 Performing a Moving Average
73 RANK NULL value
74 Ratio-to-Report
75 Restrict the MEASURESRULES to cover only one of the dimensions
76 Rollup with two columns
77 ROUND(AVG(salary) OVER())
78 ROW_NUMBER function with an ordering on salary in descending order
79 ROW_NUMBER() with Partition
80 ROW_NUMBER() with Partition demo
81 Row-ordering is done first and then the moving average
82 See the top five salaries
83 See the top five salaries (DENSE_RANK)
84 Select percentile_cont(0 5) within group (order by salary desc )
85 SPREADSHEET
86 SUM analytical function can easily be partioned
87 Sum over (nothing)
88 Sum over partition by, order by
89 Sum salary over PARTITION BY
90 Sum with Order by range unbounded preceding
91 SUM, Partition and order by
92 Take a probability value (between 0 and 1) and returns a percentile value (for a continuous distribution)
93 The Analytical Functions in Oracle (Analytical Functions I)
94 The DENSE_RANK and tie
95 The Join Without the Analytical Function
96 The MODEL statement does calculations on a column in a row based on other rows in a result set
97 The Order in Which the Analytical Function Is Processed in the SQL Statement
98 The RANK function will not only produce the row numbering but will skip a rank if there is a tie
99 The ROLLUP function was provided to conveniently give the sum on the aggregate
100 The row comparison function partitioned as with other aggregates
101 The Row Comparison Functions - LEAD and LAG
102 There is an option to place nulls first or last with the analytical function
103 To depict the rank of the salaries in descending order with ROWNUM
104 Unbounded Following
105 Use an analytical function in a WHERE clause
106 Use CUBE and RANK() to get all rankings of salaries by city and description
107 Use GROUPING SETS and RANK() to get just the salary subtotal rankings
108 Use GROUPING(x) function in a DECODE or CASE to enhance the result
109 Use partitioning in the OVER clause of the aggregate-analytical function
110 Use PERCENT_RANK Function
111 Use RANK(), PERCENT_RANK(), CUME_DIST()
112 Use ROW_NUMBER(), RANK() and DENSE_RANK() together
113 Use ROWS UNBOUNDED PRECEDING to implicitly indicate the end of the window is the current row
114 Use SUM for windowing
115 Use the BREAK reporting tool to space the display conveniently
116 Use the COUNT aggregate analytical function to show how many rows are included in each window
117 Uses AVG() with ROLLUP
118 Using a Column Multiple Times in a GROUP BY Clause
119 Using Analytic Functions
120 Using Analytic Functions AVG(Mark) OVER (PARTITION BY StudentID ORDER BY StudentID, Mark)
121 Using DECODE() and GROUPING() to Convert Multiple Column Values
122 Using DECODE() to Convert the Returned Value from GROUPING()
123 Using GROUPING() with CUBE
124 Using HAVING with an Analytical Function
125 Using NULLS FIRST
126 Using NULLS LAST and the ROW_NUMBER
127 Using ROLLUP, CUBE, and GROUPING SETS Operators with Analytic Functions
128 Using the Analytic Functions
129 Using the CUBE Clause
130 Using the DENSE_RANK()
131 Using the GROUP_ID() Function
132 Using the GROUPING SETS Clause
133 Using the Hypothetical Rank and Distribution Functions
134 Using the LAG() and LEAD() Functions
135 Using the NTILE() Function
136 Using the RANK()
137 Using the Ranking Functions
138 Using the RATIO_TO_REPORT() Function
139 Using the ROLLUP Clause
140 Using the ROW_NUMBER() Function
141 Using the ROWNUM function
142 Using the Window Functions
143 WHERE is applied before the RANK()