3 ms·
If I could ask something related to this question - what is the best way to grok the OVER clause, such as RANK() OVER? I'm having a bit of difficulty trying t
by mustntmumble 10y ago
If I could ask something related to this question - what is the best way to grok the OVER clause, such as RANK() OVER? I'm having a bit of difficulty trying to really understand how it works and how to use it effectively.
- schemathings 10y agoSomeone already mentioned the JOOQ blog .. this post should help you out https://blog.jooq.org/2014/08/12/the-difference-between-row_number-rank-and-dense_rank/ https://blog.jooq.org/2014/08/12/the-difference-between-row_...
- jrjames83 10y agoBest way is to see it in action and experiment. A few videos I made (b and c relate to common queries written for BI or CLTV stuff). a) row_number over() and partition: in the abstract: https://www.youtube.com/watch?v=-X3eIyZV728 https://www.youtube.com/watch?v=-X3eIyZV728 b) applied to customer value analysis: https://www.youtube.com/watch?v=iHxJvF0tZOA https://www.youtube.com/watch?v=iHxJvF0tZOA c) applied to time differences (also uses materials from b above) https://www.youtube.com/watch?v=5f8tF4U70Ic https://www.youtube.com/watch?v=5f8tF4U70Ic After a while you can write these from scratch and they generally work the 1st or 2nd time, but it takes lots of trial and error at first when setting up the partition and ordering clause within the OVER() expression.
- lukaseder 10y agoI always link to this article: http://tapoueh.org/blog/2013/08/20-Window-Functions http://tapoueh.org/blog/2013/08/20-Window-Functions