Equivalents for analytic functions page 1
Variables can also be used to emulate analytic functions. A few examples follow. For simplicity, let’s assume all data fields NOT NULL, and sorting and partitioning (PARTITION BY) use one column only. Including NULLs and multi-column sorting/partitioning would make the examples more cumbersome and difficult to read without changing their basic idea.
Let’s create a table named TestTable for our examples:
Here, group_id is the group identifier (the equivalent of an analytic function window), order_id contains unique values used for sorting, and value is just a numeric value.
Let’s fill our table with test data:
Some examples of analytic function substitutes.
1) ROW_NUMBER() OVER(ORDER BY order_id)
2) ROW_NUMBER() OVER(PARTITION BY group_id ORDER BY order_id)
3) SUM(value) OVER(PARTITION BY group_id ORDER BY order_id)