Oracle PLSQL Analytical Functions 254 codes

  1. Home
  2. Oracle PLSQL
  3. Analytical Functions
1 (expr1, expr2)
2 A seven-day MAX and MIN on Tuesdays
3 Accessing a Range of Cells Using BETWEEN and AND
4 Accessing Cells Using a FOR Loop
5 Add an analytic order by clause
6 Adding an Analytical Function to a Query that Contains a Join (and Other WHERE Conditions)
7 Adding an Analytical Function to the GROUP BY with ORDER BY Version
8 An Example of the MODEL Clause
9 An Example Query that Illustrates the Use of GROUPING_ID()
10 Analytic order-by clause
11 ANY with positional notation
12 Average 5 before, after
13 Avg over and avg over order by
14 Avg over order by range
15 Avg over range between
16 Avg over ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
17 Avg over, first_value over
18 Browse Products with ranking function
19 Changing the Final Ordering after Having Added an Analytical Function
20 Changing the Position of Columns Passed to ROLLUP
21 Column alias for the analytical function is used in the final ORDER BY
22 Combine analytical function with count()
23 Compare rank() and dense_rank()
24 Compute
25 COMPUTE sum max min
26 Connect by prior
27 Control whether nulls are the highest or lowest in a group using NULLS FIRST
28 Control whether nulls are the highest or lowest in a group using NULLS LAST
29 Corr
30 Count employees, group by CUBE(department no, job title)
31 Count employees, group by ROLLUP(department no, job title)
32 Count() over partition
33 Count() over partition by, order by and range unbounded preceding
34 Count() over partition by, order by, range unbounded preceding
35 Count() over partition, order by, range unbounded preceding
36 Count() over partition, order by, range unbounded preceding for employee table
37 COVAR_POP(expr1, expr2)
38 COVAR_SAMP(expr1, expr2)
39 Create a view based on row_number function
40 CUBE clause
41 Cube implies reducing tables by rolling up different columns (dimensions)
42 CUME_DIST()
43 Cume_dist over
44 Cumulative total_order_price for all orders
45 Deal with Null in dense_rank
46 Deal with Null in Rank
47 Decode dense_rank
48 Decode the result from row_number over, partition by, order by
49 Demo the effect of WHERE on CONNECT BY
50 Demonstrate a partial rollup
51 Demonstrate GROUPING in the HAVING clause
52 Demonstrate Partial CUBE operation
53 DENSE_RANK function
54 Dense_rank over partition by
55 Dense_rank() over ( partition by deptno order by sal desc )
56 Dense_rank() over (order by comm desc nulls last)
57 Dense_rank() over partition by, order by
58 DENSE_RANK() with NULLS FIRST
59 DENSE_RANK() with NULLS LAST
60 DENSE_RANK()rank items, leaves no gaps when there is a tie
61 Display a cumulative total_order_price for all orders(Partition by the truncated order_date
62 Display cumulative total_order_price and cumulative by customer
63 Displaying a Running Total Using SUM as an Analytical Function
64 Eliminate duplicate rows using a HAVING clause that only allows rows whose GROUP_ID() is 0
65 Employee salary report with avg salary for the previous 12 months
66 FIRST and LAST functions to get the first and last values in an ordered group
67 FIRST_VALUE and LAST_VALUE
68 FIRST_VALUE() get the first rows in a window
69 First_value order by range between preceding and following
70 First_value over order by
71 First_value over order by range
72 First_value over order by rows
73 First_value over partition
74 Get median salary
75 Get Products
76 Getting the Current Value of a Dimension Using CURRENTV()
77 Group by cube
78 Group by CUBE(department no, job title)
79 Group by rollup
80 Group by ROLLUP(department no, job title)
81 GROUPING SETS
82 GROUPING() returns 0 for the rows that have non-null values, and 1 for the last row that has a null value
83 GROUPING(x)
84 Grouping with Cube
85 Having grouping(c class_desc) = 1
86 If more analytical functions are added, yet more sorting may result
87 If NULLS LAST is left out of the final ORDER BY, the effect will be lost
88 If the statement were without NULLS LAST, the values of the NVLd nulls occur first
89 Include a final ordering of the result set with an ORDER BY at the end of the query
90 IS ANY with symbolic notation
91 Is our average total_order_price increasing or decreasing
92 IS PRESENT returns true if the row specified by the cell reference existed prior to the execution of the MODEL clause
93 Lag over
94 Lag over order
95 Lag salary over, lead salary over
96 LAG() and LEAD()
97 LAG, LEAD
98 Last_value over range
99 Last_value over range between
100 LAST_VALUE()
101 Lead over order by
102 Lead over partition
103 MEDIAN(expr1)
104 More Than One Analytical Function May Be Used in a Single Statement
105 More Than One Analytical Function May Be Used in a Single Statement ROW_NUMBER(), RANK(), DENSE_RANK()
106 Multiple COMPUTEs are also allowable
107 Notice that the PORTION column adds up to 100%
108 NTILE(2) OVER (ORDER BY SUM(amount) DESC) AS ntile
109 NTILE(4) function is used for that ordering
110 NTILE(4) in reverse order
111 NTILE(buckets) to calculate n-tiles
112 Ntile and row_number
113 Ntile as quartile
114 NTile with NULLS LAST
115 Nulls could also be handled with a default value using the NVL function in the analytical function
116 NULLS FIRST
117 NULLS LAST
118 One useful application of GROUPING_ID() is to filter rows using a HAVING clause
119 Open a cursor created by using row_number function
120 Order by nulls last
121 PARTITION BY
122 PARTITION BY (JOB title) and right outer join
123 Partition clause
124 Partition Window
125 Partitioning with PARTITION_BY
126 Passing Multiple Columns to ROLLUP
127 Percent_rank (PR) = (Rank-1)(Number of rows-1)
128 PERCENT_RANK()
129 PERCENTILE_DISC(x) examines the cumulative distribution values in each group until it finds one that is greater than
130 Performing a Centered Average
131 Performing a Cumulative Sum
132 Performing a Moving Average
133 Portion amounts add to 1 000 in each region
134 PRESENTV(cell, expr1, expr2)
135 Range unbounded preceding
136 Rank and dense_rank over partition
137 Rank and dense_rank with group by
138 Rank and percent_rank
139 Rank row without analytical functions
140 Rank with order by clause
141 RANK()
142 Rank() over (partition by deptno order by sal desc )
143 Rank() over partition
144 Rank() with null values
145 RANK() with NULLS FIRST
146 Rank() with nulls last
147 RANK() with NULLS LAST demo
148 Rank, Percent_Rank, and Cume_Dist
149 Rank, Percent_Rank, and Cume_Dist, NTILE
150 RATIO_TO_REPORT can easily be partioned as well
151 RATIO_TO_REPORT Demo
152 RATIO_TO_REPORT()
153 RATIO_TO_REPORT, break on and compute
154 REGR_AVGX(expr1, expr2)
155 REGR_AVGX(y, x)
156 REGR_AVGY(expr1, expr2)
157 REGR_AVGY(y, x) Returns the average of y after eliminating x and y pairs where either x or y is null
158 REGR_COUNT(expr1, expr2)
159 REGR_COUNT(y, x)
160 REGR_INTERCEPT(expr1, expr2)
161 REGR_INTERCEPT(y, x) Returns the intercept on the y-axis of the regression line
162 REGR_R2(expr1, expr2)
163 REGR_R2(y, x) Returns the coefficient of determination, or R-squared, of the regression line
164 REGR_SLOPE(expr1, expr2)
165 REGR_SLOPE(y, x) Returns the slope of the regression line
166 REGR_SXX(y, x) Returns REG_COUNT(y, x) VAR_POP(x)
167 REGR_SXY(expr1, expr2)
168 REGR_SXY(y, x) Returns REG_COUNT(y, x) COVAR_POP(y, x)
169 REGR_SYY(expr1, expr2)
170 REGR_SYY(y, x) Returns REG_COUNT(y, x) VAR_POP(y)
171 Reporting on a Sum
172 Rollup
173 ROLLUP and RANK() to get the sales rankings by product type ID
174 Rollup function in group by clause
175 ROW_NUMBER function with an ordering on salary in descending order
176 Row_number over partition by and order by
177 ROW_NUMBER()
178 Row_number() over (partition by deptno order)
179 ROW_NUMBER() with order in descending order
180 Row-ordering is done first and then the moving average
181 RULES UPDATE, which specifies that if the cell doesnt exist, dont create a new row
182 Salary report with cumulative salary, using analytic function SUM
183 Salary report with cumulative salary, without using analytic function
184 See the difference between a persons salary and the average for his or her region
185 Select percentile_cont(0 5) within group (order by salary desc )
186 SPREADSHEET PARTITION BY
187 Start connect by
188 START WITH mgr is null CONNECT BY NOCYCLE PRIOR empno = mgr
189 Start with and connect by
190 Start with column is null
191 STATS_MODE(expr)
192 STATS_WSR_TEST(expr1, expr2, ONE_SIDED_SIG)
193 STATS_WSR_TEST(expr1, expr2, TWO_SIDED_SIG)
194 STATS_WSR_TEST(y, x, STATISTIC)
195 STDDEV([DISTINCT ALL] value) [OVER (analytic_clause)]
196 STDDEV(x)
197 STDDEV_POP(expr) [OVER(analytic_clause)]
198 STDDEV_SAMP(expr) [OVER(analytic_clause)]
199 Sum over (nothing)
200 Sum over and order by
201 Sum over and row_number() over
202 Sum over order by
203 Sum over partition
204 Sum over partition and sum over order as running total
205 Sum over partition by and order by
206 Sum over partition by, order by
207 Sum over partition demo
208 Sum over range
209 Sum salary over PARTITION BY
210 SUM(y) OVER(ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
211 The default is NULLS FIRST
212 The direct application of a WHERE clause in the query is not allowed
213 The query divides the current months sales amount by the previous months sales amount
214 The ranking as opposed to a row-numbering problem (the problem of ties)
215 The ROLLUP clause extends GROUP BY to return a row containing a subtotal for each group along with a total for all gr
216 The row comparison function can also be partitioned as with other aggregates
217 The Row Comparison Functions LEAD and LAG
218 The sequence of the columns passed into Cube function
219 The top five salaries
220 The use of ROWS UNBOUNDED PRECEDING to implicitly indicate the end of the window is
221 The WHERE is applied before the RANK()
222 To get a clearer picture of the NTILE function
223 To see how the moving average window can expand
224 Top with partition
225 UNBOUNDED FOLLOWING is used for the end of the window
226 Use an analytical function in a WHERE clause
227 Use CUBE and RANK() to get all rankings of sales by product type ID and employee ID
228 Use DENSE_RANK() to get the top rank
229 Use GROUPING SETS and RANK() to get just the sales amount subtotal rankings
230 Use GROUPING SETS to produce just a subset of CUBE
231 Use lag over and lead over in inner statement
232 Use lag over in inner query
233 Use multiple analytical functions
234 Use over partition in subquery
235 Use partitioning in the OVER clause of the aggregate-analytical function like this
236 Use rank and dense_rank in subquery
237 Use symbolic notation to explicitly indicate the meaning of the dimensions
238 Use the COUNT aggregate analytical function to show how many rows are included in each window
239 Uses dates and logical offset of seven days preceding
240 Using Analytic Functions
241 Using Analytic Functions AVG(Mark) OVER (PARTITION BY StudentID ORDER BY StudentID, Mark)
242 Using AVG with ROLLUP
243 Using DECODE() and GROUPING() to Convert Multiple Column Values
244 Using DECODE() to Convert the Returned Value from GROUPING()
245 Using GROUPING() with CUBE
246 Using IGNORE NAV and KEEP NAV
247 VAR_POP(expr)
248 VAR_SAMP(expr)
249 VARIANCE()
250 VARIANCE([DISTINCT ALL] expr)
251 What fraction of the total salary budget goes to which individual
252 When RANK() is added to the statement, the RANK function is applied last, just before the ordering
253 With ROLLUP and ROW_NUMBER added
254 You can use the GROUP_ID() function to remove duplicate rows returned by a GROUP BY clause