3 ms·
A few cool tricks I use with window functions: 1- To find blocks of contiguous values, you can use something similar to Gauss' trick for calculating arithmetic
by forinti 6y ago
A few cool tricks I use with window functions:
1- To find blocks of contiguous values, you can use something similar to Gauss' trick for calculating arithmetic progressions: sort them by descending order and add each value to the row number. All contiguous values will add to the same number. You can then apply max/min and get rows that correspond to the blocks of values.
select min(n), max(n) from (
select n, n+row_number() over (order by n desc) group
from numbers
)
group by group
order by 1
2- You can use a window function with exponential/logarithms in order to calculate the accumulated inflation for the last n months:
select date, inflation, (exp(accumulated)-1)*100 from (
select date, inflation, sum(ln(1+(inflation/100))) over (order by date desc rows between current row and 11 following) as accumulated
from inflation
)
3- You can do all the paging in SQL (fetch page n of m) or simply add a column with the total number of rows (this often makes it easier to process the results).
- pascalmahe 6y agoCould you please develop how to do paging with window functions? It's been some time since I've done serious work with SQL yet I remember that all paging solutions I've found (eg. top results on SO) are always platform-specific so having a platform-independent way of doing it would mean I could finally try to remember it.
- hospadar 6y agorow_number() ! As in: SELECT * FROM (SELECT *, row_number() OVER (order by <some column - primary key column would be a good choice>) as rowidx FROM your_table) as numbered WHERE rowidx > ? AND rowid < ?
- forinti 6y agoTry this: select ord, table_name, first_name, last_name, total_rows from ( select table_name, row_number() over (order by table_name asc) ord, count(1) over () total_rows, from all_tables where table_name like 'B%' order by table_name desc ) where ord between 10 and 19 order by ord This is basically what Oracle Apex does. You can do this without window functions, but it gets a bit more complicated. I like to add total_rows so that I can show a "rows 10 - 19 of 81" header. You can also get the very first and last values by adding theses columns: first_value(table_name) over (order by table_name asc rows between unbounded preceding and unbounded following) first_name, last_value(table_name) over (order by table_name asc rows between unbounded preceding and unbounded following) last_name Alternatively, you could get the very last and very first whole rows by changing the outer where clause: ord=1 or rownum=1 or ord between 10 and 19 And then it would be easy to have a nice header with "rows 10 - 19 of 81 (Algiers to Zimbabwe)" with very little code.
- bmn__ 6y agoIf you mean pagination, then window functions are not the right tool. Keyset pagination in standard SQL: • https://use-the-index-luke.com/sql/partial-results/fetch-next-page https://use-the-index-luke.com/sql/partial-results/fetch-nex... • https://use-the-index-luke.com/no-offset https://use-the-index-luke.com/no-offset
- e12e 6y agoBoth of these are bad for pagination - if the dataset isn't read-only. If the search criteria matches 100 rows at the time of the request, you may want to page through those matches, even if, by the time the client (human or machine) gets to page three, the query matches 80 or 110 rows - or worse, if the query still matches 100 rows, but not all of them are the same as the original 100! You would normally capture such state by using cursors.
- fuy 6y agoCould you elaborate on "using cursors" part? Are you talking about database-level cursors? If so, do you know any resources which cover this approach?