•Hierarchical Queries
•START WITH
•CONNECT BY PRIOR
•CONNECT BY FOLLOWING
•ORDER by level
•Analytics Functions
•Windowing
•PRECEDING, UNBOUNDED_PRECEDING
•ROWS BETWEEN Unbounded Preceding
•CURRENT ROW
•RANGE BETWEEN INTERVAL 30 DAY PRECEDING AND INTERVAL ‘30’ DAY Following
•ROWS BETWENN 1 PRECEDING and 1 FOLLOWING
Other Analytics Functions and Clauses:
•OVER()
•PARTITION OVER
•Over Partition By
•Avg () OVER Partition BY
•RANK()
•DENSE_RANK()
•DENSE_RANK FIRST
•DENSE_RANK LAST
•RANK or DENSE_RANK OVER
•KEEP
•Aggregate by keeping
••KEEP (DENSE_RANK FIRST ORDER BY …)
•KEEP (DENSE_RANK LAST ORDER BY …)
•RANK()
•RANK() OVER
•RANK() OVER PARTITION BY
•DENSE RANK() OVER
•DENSE RANK() OVER PARTITION BY
•PARTITION BY …. ORDER BY
•ROW_NUMBER() OVER (ORDER By …)
•ROWS BETWEEN Unbounded Preceding
•CURRENT ROW
•RANGE BETWEEN INTERVAL 30 DAY PRECEDING AND INTERVAL ‘30’ DAY Following
•ROWS BETWENN 1 PRECEDING and 1 FOLLOWING
oKEEP
–Aggregate by keeping
oDENSE_RANK()
oDENSE_RANK FIRST
oDENSE_RANK LAST
oOver()
oOver Partition By
–Avg () OVER Partition BY
oROW_NUMBER
oRANK()
oDENSE_RANK
oVariations
–RANK() OVER
–RANK() OVER PARTITION BY
–DENSE RANK() OVER
–DENSE RANK() OVER PARTITION BY
–PARTITION BY …. ORDER BY
–(Partition … order by)