# Analytic and Recursive SQL by Example

In my article A Note on Running Sums and Products in SQL, I used three different SQL techniques to get running products: Analytic Functions, Model Clause and Recursive Subquery Factors. I explained this in a recording on Twitter. I then wondered whether I could explain each of these SQL techniques in general using a single Twitter recording (which has a time limit of 140 seconds) each, and you can see the results in this Twitter thread.

In this article I set out the example queries that I used along with the results. You can get the complete scripts and recordings on GitHub, Oracle SQL Projects.

# Analytic Functions

Oracle Doc: SQL for Analysis and Reporting

SQL Analytic Functions in a Tweet

### Average by Grouping

```SELECT department_id, AVG(salary) avg_salary
FROM employees
GROUP BY department_id
ORDER BY department_id
/
DEPARTMENT_ID AVG_SALARY
------------- ----------
10       4400
20       9500
30       4150
40       6500
50 3475.55556
60       5760
70      10000
80 8955.88235
90 19333.3333
100 8601.33333
110      10154
7000
```

### Analytic Averages: Overall, running and 3-point moving

```SELECT department_id, employee_id, salary,
AVG(salary) OVER (PARTITION BY department_id) avg_salary,
AVG(salary) OVER (PARTITION BY department_id ORDER BY employee_id) run_avg_salary,
AVG(salary) OVER (PARTITION BY department_id ORDER BY employee_id
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) moving_avg_salary_3
FROM employees
ORDER BY department_id, employee_id
/
DEPARTMENT_ID EMPLOYEE_ID     SALARY AVG_SALARY RUN_AVG_SALARY MOVING_AVG_SALARY_3
------------- ----------- ---------- ---------- -------------- -------------------
10         200       4400       4400           4400                4400
20         201      13000       9500          13000               13000
202       6000       9500           9500                9500
30         114      11000       4150          11000               11000
115       3100       4150           7050                7050
116       2900       4150     5666.66667          5666.66667
117       2800       4150           4950          2933.33333
118       2600       4150           4480          2766.66667
119       2500       4150           4150          2633.33333
40         203       6500       6500           6500                6500
50         120       8000 3475.55556           8000                8000
121       8200 3475.55556           8100                8100
122       7900 3475.55556     8033.33333          8033.33333
123       6500 3475.55556           7650          7533.33333
124       5800 3475.55556           7280          6733.33333
125       3200 3475.55556           6600          5166.66667
126       2700 3475.55556     6042.85714                3900
127       2400 3475.55556         5587.5          2766.66667
128       2200 3475.55556     5211.11111          2433.33333
129       3300 3475.55556           5020          2633.33333
130       2800 3475.55556     4818.18182          2766.66667
131       2500 3475.55556           4625          2866.66667
132       2100 3475.55556     4430.76923          2466.66667
133       3300 3475.55556           4350          2633.33333
134       2900 3475.55556     4253.33333          2766.66667
135       2400 3475.55556         4137.5          2866.66667
136       2200 3475.55556     4023.52941                2500
137       3600 3475.55556           4000          2733.33333
138       3200 3475.55556     3957.89474                3000
139       2700 3475.55556           3895          3166.66667
140       2500 3475.55556     3828.57143                2800
141       3500 3475.55556     3813.63636                2900
142       3100 3475.55556      3782.6087          3033.33333
143       2600 3475.55556     3733.33333          3066.66667
144       2500 3475.55556           3684          2733.33333
180       3200 3475.55556     3665.38462          2766.66667
181       3100 3475.55556     3644.44444          2933.33333
182       2500 3475.55556     3603.57143          2933.33333
183       2800 3475.55556     3575.86207                2800
184       4200 3475.55556     3596.66667          3166.66667
185       4100 3475.55556     3612.90323                3700
186       3400 3475.55556        3606.25                3900
187       3000 3475.55556     3587.87879                3500
188       3800 3475.55556     3594.11765                3400
189       3600 3475.55556     3594.28571          3466.66667
190       2900 3475.55556           3575          3433.33333
191       2500 3475.55556     3545.94595                3000
192       4000 3475.55556     3557.89474          3133.33333
193       3900 3475.55556     3566.66667          3466.66667
194       3200 3475.55556         3557.5                3700
195       2800 3475.55556     3539.02439                3300
196       3100 3475.55556     3528.57143          3033.33333
197       3000 3475.55556     3516.27907          2966.66667
198       2600 3475.55556     3495.45455                2900
199       2600 3475.55556     3475.55556          2733.33333
60         103       9000       5760           9000                9000
104       6000       5760           7500                7500
105       4800       5760           6600                6600
106       4800       5760           6150                5200
107       4200       5760           5760                4600
70         204      10000      10000          10000               10000
80         145      14000 8955.88235          14000               14000
146      13500 8955.88235          13750               13750
147      12000 8955.88235     13166.6667          13166.6667
148      11000 8955.88235          12625          12166.6667
149      10500 8955.88235          12200          11166.6667
150      10000 8955.88235     11833.3333               10500
151       9500 8955.88235          11500               10000
152       9000 8955.88235        11187.5                9500
153       8000 8955.88235     10833.3333          8833.33333
154       7500 8955.88235          10500          8166.66667
155       7000 8955.88235     10181.8182                7500
156      10000 8955.88235     10166.6667          8166.66667
157       9500 8955.88235     10115.3846          8833.33333
158       9000 8955.88235     10035.7143                9500
159       8000 8955.88235           9900          8833.33333
160       7500 8955.88235           9750          8166.66667
161       7000 8955.88235     9588.23529                7500
162      10500 8955.88235     9638.88889          8333.33333
163       9500 8955.88235     9631.57895                9000
164       7200 8955.88235           9510          9066.66667
165       6800 8955.88235     9380.95238          7833.33333
166       6400 8955.88235     9245.45455                6800
167       6200 8955.88235     9113.04348          6466.66667
168      11500 8955.88235         9212.5          8033.33333
169      10000 8955.88235           9244          9233.33333
170       9600 8955.88235     9257.69231          10366.6667
171       7400 8955.88235     9188.88889                9000
172       7300 8955.88235     9121.42857                8100
173       6100 8955.88235     9017.24138          6933.33333
174      11000 8955.88235     9083.33333          8133.33333
175       8800 8955.88235     9074.19355          8633.33333
176       8600 8955.88235       9059.375          9466.66667
177       8400 8955.88235     9039.39394                8600
179       6200 8955.88235     8955.88235          7733.33333
90         100      24000 19333.3333          24000               24000
101      17000 19333.3333          20500               20500
102      17000 19333.3333     19333.3333          19333.3333
100         108      12008 8601.33333          12008               12008
109       9000 8601.33333          10504               10504
110       8200 8601.33333           9736                9736
111       7700 8601.33333           9227                8300
112       7800 8601.33333         8941.6                7900
113       6900 8601.33333     8601.33333          7466.66667
110         205      12008      10154          12008               12008
206       8300      10154          10154               10154
178       7000       7000           7000                7000

107 rows selected.
```

### Analytics on Grouping: Running sum of the department average salaries

```DEPARTMENT_ID AVG_SALARY RUN_SUM_AVG_SALARY
------------- ---------- ------------------
10       4400               4400
20       9500              13900
30       4150              18050
40       6500              24550
50 3475.55556         28025.5556
60       5760         33785.5556
70      10000         43785.5556
80 8955.88235         52741.4379
90 19333.3333         72074.7712
100 8601.33333         80676.1046
110      10154         90830.1046
7000         97830.1046
```

# Model Clause

Oracle Doc: SQL for Modeling

SQL Model Clause in a Tweet

### Running and Final Products: Final first rule, default SEQUENTIAL order

```WITH multipliers AS (
SELECT department_id, employee_id, salary, (1 + salary/10000) mult,
COUNT(*) OVER (PARTITION BY department_id) n_emps
FROM employees
)
SELECT department_id, employee_id, salary, mult, running_prod, final_prod
FROM multipliers
MODEL
PARTITION BY (department_id)
DIMENSION BY (Row_Number() OVER (PARTITION BY department_id
ORDER BY employee_id) rn)
MEASURES (employee_id, salary, mult, mult running_prod, mult final_prod, n_emps)
RULES (
final_prod[any] = running_prod[n_emps[CV()]],
running_prod[rn > 1] = mult[CV()] * running_prod[CV() - 1]
)
ORDER BY department_id, employee_id
/
DEPARTMENT_ID EMPLOYEE_ID     SALARY       MULT RUNNING_PROD FINAL_PROD
------------- ----------- ---------- ---------- ------------ ----------
10         200       4400       1.44         1.44       1.44
20         201      13000        2.3          2.3        1.6
202       6000        1.6         3.68        1.6
30         114      11000        2.1          2.1       1.25
115       3100       1.31        2.751       1.25
116       2900       1.29      3.54879       1.25
117       2800       1.28    4.5424512       1.25
118       2600       1.26   5.72348851       1.25
119       2500       1.25   7.15436064       1.25
40         203       6500       1.65         1.65       1.65
50         120       8000        1.8          1.8       1.26
121       8200       1.82        3.276       1.26
122       7900       1.79      5.86404       1.26
123       6500       1.65     9.675666       1.26
124       5800       1.58   15.2875523       1.26
125       3200       1.32    20.179569       1.26
126       2700       1.27   25.6280526       1.26
127       2400       1.24   31.7787853       1.26
128       2200       1.22    38.770118       1.26
129       3300       1.33    51.564257       1.26
130       2800       1.28   66.0022489       1.26
131       2500       1.25   82.5028112       1.26
132       2100       1.21   99.8284015       1.26
133       3300       1.33   132.771774       1.26
134       2900       1.29   171.275589       1.26
135       2400       1.24    212.38173       1.26
136       2200       1.22    259.10571       1.26
137       3600       1.36   352.383766       1.26
138       3200       1.32   465.146571       1.26
139       2700       1.27   590.736145       1.26
140       2500       1.25   738.420182       1.26
141       3500       1.35   996.867245       1.26
142       3100       1.31   1305.89609       1.26
143       2600       1.26   1645.42907       1.26
144       2500       1.25   2056.78634       1.26
180       3200       1.32   2714.95797       1.26
181       3100       1.31   3556.59495       1.26
182       2500       1.25   4445.74368       1.26
183       2800       1.28   5690.55191       1.26
184       4200       1.42   8080.58372       1.26
185       4100       1.41    11393.623       1.26
186       3400       1.34   15267.4549       1.26
187       3000        1.3   19847.6913       1.26
188       3800       1.38    27389.814       1.26
189       3600       1.36   37250.1471       1.26
190       2900       1.29   48052.6898       1.26
191       2500       1.25   60065.8622       1.26
192       4000        1.4   84092.2071       1.26
193       3900       1.39   116888.168       1.26
194       3200       1.32   154292.382       1.26
195       2800       1.28   197494.248       1.26
196       3100       1.31   258717.465       1.26
197       3000        1.3   336332.705       1.26
198       2600       1.26   423779.208       1.26
199       2600       1.26   533961.802       1.26
60         103       9000        1.9          1.9       1.42
104       6000        1.6         3.04       1.42
105       4800       1.48       4.4992       1.42
106       4800       1.48     6.658816       1.42
107       4200       1.42   9.45551872       1.42
70         204      10000          2            2          2
80         145      14000        2.4          2.4       1.62
146      13500       2.35         5.64       1.62
147      12000        2.2       12.408       1.62
148      11000        2.1      26.0568       1.62
149      10500       2.05     53.41644       1.62
150      10000          2    106.83288       1.62
151       9500       1.95   208.324116       1.62
152       9000        1.9    395.81582       1.62
153       8000        1.8   712.468477       1.62
154       7500       1.75   1246.81983       1.62
155       7000        1.7   2119.59372       1.62
156      10000          2   4239.18744       1.62
157       9500       1.95    8266.4155       1.62
158       9000        1.9   15706.1895       1.62
159       8000        1.8    28271.141       1.62
160       7500       1.75   49474.4968       1.62
161       7000        1.7   84106.6445       1.62
162      10500       2.05   172418.621       1.62
163       9500       1.95   336216.311       1.62
164       7200       1.72   578292.056       1.62
165       6800       1.68   971530.654       1.62
166       6400       1.64   1593310.27       1.62
167       6200       1.62   2581162.64       1.62
168      11500       2.15   5549499.68       1.62
169      10000          2   11098999.4       1.62
170       9600       1.96   21754038.7       1.62
171       7400       1.74   37852027.4       1.62
172       7300       1.73   65484007.4       1.62
173       6100       1.61    105429252       1.62
174      11000        2.1    221401429       1.62
175       8800       1.88    416234687       1.62
176       8600       1.86    774196517       1.62
177       8400       1.84   1424521591       1.62
179       6200       1.62   2307724978       1.62
90         100      24000        3.4          3.4        2.7
101      17000        2.7         9.18        2.7
102      17000        2.7       24.786        2.7
100         108      12008     2.2008       2.2008       1.69
109       9000        1.9      4.18152       1.69
110       8200       1.82    7.6103664       1.69
111       7700       1.77   13.4703485       1.69
112       7800       1.78   23.9772204       1.69
113       6900       1.69   40.5215024       1.69
110         205      12008     2.2008       2.2008       1.83
206       8300       1.83     4.027464       1.83
178       7000        1.7          1.7        1.7

107 rows selected.
```

### Running and Final Products: Final first rule, AUTOMATIC order

```WITH multipliers AS (
SELECT department_id, employee_id, salary, (1 + salary/10000) mult,
COUNT(*) OVER (PARTITION BY department_id) n_emps
FROM employees
)
SELECT department_id, employee_id, salary, mult, running_prod, final_prod
FROM multipliers
MODEL
PARTITION BY (department_id)
DIMENSION BY (Row_Number() OVER (PARTITION BY department_id
ORDER BY employee_id) rn)
MEASURES (employee_id, salary, mult, mult running_prod, mult final_prod, n_emps)
RULES AUTOMATIC ORDER (
final_prod[any] = running_prod[n_emps[CV()]],
running_prod[rn > 1] = mult[CV()] * running_prod[CV() - 1]
)
ORDER BY department_id, employee_id
/
DEPARTMENT_ID EMPLOYEE_ID     SALARY       MULT RUNNING_PROD FINAL_PROD
------------- ----------- ---------- ---------- ------------ ----------
10         200       4400       1.44         1.44       1.44
20         201      13000        2.3          2.3       3.68
202       6000        1.6         3.68       3.68
30         114      11000        2.1          2.1 7.15436064
115       3100       1.31        2.751 7.15436064
116       2900       1.29      3.54879 7.15436064
117       2800       1.28    4.5424512 7.15436064
118       2600       1.26   5.72348851 7.15436064
119       2500       1.25   7.15436064 7.15436064
40         203       6500       1.65         1.65       1.65
50         120       8000        1.8          1.8 533961.802
121       8200       1.82        3.276 533961.802
122       7900       1.79      5.86404 533961.802
123       6500       1.65     9.675666 533961.802
124       5800       1.58   15.2875523 533961.802
125       3200       1.32    20.179569 533961.802
126       2700       1.27   25.6280526 533961.802
127       2400       1.24   31.7787853 533961.802
128       2200       1.22    38.770118 533961.802
129       3300       1.33    51.564257 533961.802
130       2800       1.28   66.0022489 533961.802
131       2500       1.25   82.5028112 533961.802
132       2100       1.21   99.8284015 533961.802
133       3300       1.33   132.771774 533961.802
134       2900       1.29   171.275589 533961.802
135       2400       1.24    212.38173 533961.802
136       2200       1.22    259.10571 533961.802
137       3600       1.36   352.383766 533961.802
138       3200       1.32   465.146571 533961.802
139       2700       1.27   590.736145 533961.802
140       2500       1.25   738.420182 533961.802
141       3500       1.35   996.867245 533961.802
142       3100       1.31   1305.89609 533961.802
143       2600       1.26   1645.42907 533961.802
144       2500       1.25   2056.78634 533961.802
180       3200       1.32   2714.95797 533961.802
181       3100       1.31   3556.59495 533961.802
182       2500       1.25   4445.74368 533961.802
183       2800       1.28   5690.55191 533961.802
184       4200       1.42   8080.58372 533961.802
185       4100       1.41    11393.623 533961.802
186       3400       1.34   15267.4549 533961.802
187       3000        1.3   19847.6913 533961.802
188       3800       1.38    27389.814 533961.802
189       3600       1.36   37250.1471 533961.802
190       2900       1.29   48052.6898 533961.802
191       2500       1.25   60065.8622 533961.802
192       4000        1.4   84092.2071 533961.802
193       3900       1.39   116888.168 533961.802
194       3200       1.32   154292.382 533961.802
195       2800       1.28   197494.248 533961.802
196       3100       1.31   258717.465 533961.802
197       3000        1.3   336332.705 533961.802
198       2600       1.26   423779.208 533961.802
199       2600       1.26   533961.802 533961.802
60         103       9000        1.9          1.9 9.45551872
104       6000        1.6         3.04 9.45551872
105       4800       1.48       4.4992 9.45551872
106       4800       1.48     6.658816 9.45551872
107       4200       1.42   9.45551872 9.45551872
70         204      10000          2            2          2
80         145      14000        2.4          2.4 2307724978
146      13500       2.35         5.64 2307724978
147      12000        2.2       12.408 2307724978
148      11000        2.1      26.0568 2307724978
149      10500       2.05     53.41644 2307724978
150      10000          2    106.83288 2307724978
151       9500       1.95   208.324116 2307724978
152       9000        1.9    395.81582 2307724978
153       8000        1.8   712.468477 2307724978
154       7500       1.75   1246.81983 2307724978
155       7000        1.7   2119.59372 2307724978
156      10000          2   4239.18744 2307724978
157       9500       1.95    8266.4155 2307724978
158       9000        1.9   15706.1895 2307724978
159       8000        1.8    28271.141 2307724978
160       7500       1.75   49474.4968 2307724978
161       7000        1.7   84106.6445 2307724978
162      10500       2.05   172418.621 2307724978
163       9500       1.95   336216.311 2307724978
164       7200       1.72   578292.056 2307724978
165       6800       1.68   971530.654 2307724978
166       6400       1.64   1593310.27 2307724978
167       6200       1.62   2581162.64 2307724978
168      11500       2.15   5549499.68 2307724978
169      10000          2   11098999.4 2307724978
170       9600       1.96   21754038.7 2307724978
171       7400       1.74   37852027.4 2307724978
172       7300       1.73   65484007.4 2307724978
173       6100       1.61    105429252 2307724978
174      11000        2.1    221401429 2307724978
175       8800       1.88    416234687 2307724978
176       8600       1.86    774196517 2307724978
177       8400       1.84   1424521591 2307724978
179       6200       1.62   2307724978 2307724978
90         100      24000        3.4          3.4     24.786
101      17000        2.7         9.18     24.786
102      17000        2.7       24.786     24.786
100         108      12008     2.2008       2.2008 40.5215024
109       9000        1.9      4.18152 40.5215024
110       8200       1.82    7.6103664 40.5215024
111       7700       1.77   13.4703485 40.5215024
112       7800       1.78   23.9772204 40.5215024
113       6900       1.69   40.5215024 40.5215024
110         205      12008     2.2008       2.2008   4.027464
206       8300       1.83     4.027464   4.027464
178       7000        1.7          1.7        1.7

107 rows selected.
```

### Average and Moving Average

```SELECT department_id, employee_id, salary, avg_salary, moving_avg_salary_3
FROM employees
MODEL
PARTITION BY (department_id)
DIMENSION BY (Row_Number() OVER (PARTITION BY department_id
ORDER BY employee_id) rn)
MEASURES (employee_id, salary, salary avg_salary, salary moving_avg_salary_3)
RULES (
avg_salary[ANY] = AVG(salary)[ANY],
moving_avg_salary_3[ANY] = AVG(salary)[rn BETWEEN CV()-2 AND CV()]
)
ORDER BY department_id, employee_id
/
DEPARTMENT_ID EMPLOYEE_ID     SALARY AVG_SALARY MOVING_AVG_SALARY_3
------------- ----------- ---------- ---------- -------------------
10         200       4400       4400                4400
20         201      13000       9500               13000
202       6000       9500                9500
30         114      11000       4150               11000
115       3100       4150                7050
116       2900       4150          5666.66667
117       2800       4150          2933.33333
118       2600       4150          2766.66667
119       2500       4150          2633.33333
40         203       6500       6500                6500
50         120       8000 3475.55556                8000
121       8200 3475.55556                8100
122       7900 3475.55556          8033.33333
123       6500 3475.55556          7533.33333
124       5800 3475.55556          6733.33333
125       3200 3475.55556          5166.66667
126       2700 3475.55556                3900
127       2400 3475.55556          2766.66667
128       2200 3475.55556          2433.33333
129       3300 3475.55556          2633.33333
130       2800 3475.55556          2766.66667
131       2500 3475.55556          2866.66667
132       2100 3475.55556          2466.66667
133       3300 3475.55556          2633.33333
134       2900 3475.55556          2766.66667
135       2400 3475.55556          2866.66667
136       2200 3475.55556                2500
137       3600 3475.55556          2733.33333
138       3200 3475.55556                3000
139       2700 3475.55556          3166.66667
140       2500 3475.55556                2800
141       3500 3475.55556                2900
142       3100 3475.55556          3033.33333
143       2600 3475.55556          3066.66667
144       2500 3475.55556          2733.33333
180       3200 3475.55556          2766.66667
181       3100 3475.55556          2933.33333
182       2500 3475.55556          2933.33333
183       2800 3475.55556                2800
184       4200 3475.55556          3166.66667
185       4100 3475.55556                3700
186       3400 3475.55556                3900
187       3000 3475.55556                3500
188       3800 3475.55556                3400
189       3600 3475.55556          3466.66667
190       2900 3475.55556          3433.33333
191       2500 3475.55556                3000
192       4000 3475.55556          3133.33333
193       3900 3475.55556          3466.66667
194       3200 3475.55556                3700
195       2800 3475.55556                3300
196       3100 3475.55556          3033.33333
197       3000 3475.55556          2966.66667
198       2600 3475.55556                2900
199       2600 3475.55556          2733.33333
60         103       9000       5760                9000
104       6000       5760                7500
105       4800       5760                6600
106       4800       5760                5200
107       4200       5760                4600
70         204      10000      10000               10000
80         145      14000 8955.88235               14000
146      13500 8955.88235               13750
147      12000 8955.88235          13166.6667
148      11000 8955.88235          12166.6667
149      10500 8955.88235          11166.6667
150      10000 8955.88235               10500
151       9500 8955.88235               10000
152       9000 8955.88235                9500
153       8000 8955.88235          8833.33333
154       7500 8955.88235          8166.66667
155       7000 8955.88235                7500
156      10000 8955.88235          8166.66667
157       9500 8955.88235          8833.33333
158       9000 8955.88235                9500
159       8000 8955.88235          8833.33333
160       7500 8955.88235          8166.66667
161       7000 8955.88235                7500
162      10500 8955.88235          8333.33333
163       9500 8955.88235                9000
164       7200 8955.88235          9066.66667
165       6800 8955.88235          7833.33333
166       6400 8955.88235                6800
167       6200 8955.88235          6466.66667
168      11500 8955.88235          8033.33333
169      10000 8955.88235          9233.33333
170       9600 8955.88235          10366.6667
171       7400 8955.88235                9000
172       7300 8955.88235                8100
173       6100 8955.88235          6933.33333
174      11000 8955.88235          8133.33333
175       8800 8955.88235          8633.33333
176       8600 8955.88235          9466.66667
177       8400 8955.88235                8600
179       6200 8955.88235          7733.33333
90         100      24000 19333.3333               24000
101      17000 19333.3333               20500
102      17000 19333.3333          19333.3333
100         108      12008 8601.33333               12008
109       9000 8601.33333               10504
110       8200 8601.33333                9736
111       7700 8601.33333                8300
112       7800 8601.33333                7900
113       6900 8601.33333          7466.66667
110         205      12008      10154               12008
206       8300      10154               10154
178       7000       7000                7000

107 rows selected.
```

### UPSERT with FOR Loop: Split records into two with salary halved

```SELECT department_id, employee_id, old_salary, split_salary
FROM employees
MODEL
PARTITION BY (department_id)
DIMENSION BY (Row_Number() OVER (PARTITION BY department_id
ORDER BY employee_id) rn)
MEASURES (employee_id, salary old_salary, salary split_salary,
Count(*) OVER (PARTITION BY department_id) as n_emps)
RULES UPSERT (
employee_id[FOR rn FROM n_emps[1]+1 TO 2*n_emps[1] INCREMENT 1] =
employee_id[CV() - n_emps[1]],
split_salary[FOR rn FROM n_emps[1]+1 TO 2*n_emps[1] INCREMENT 1] =
old_salary[CV() - n_emps[1]],
split_salary[ANY] = 0.5 * split_salary[CV()]
)
ORDER BY department_id, employee_id
/
DEPARTMENT_ID EMPLOYEE_ID OLD_SALARY SPLIT_SALARY
------------- ----------- ---------- ------------
10         200       4400         2200
200                    2200
20         201      13000         6500
201                    6500
202                    3000
202       6000         3000
30         114                    5500
114      11000         5500
115       3100         1550
115                    1550
116                    1450
116       2900         1450
117                    1400
117       2800         1400
118                    1300
118       2600         1300
119       2500         1250
119                    1250
40         203       6500         3250
203                    3250
50         120       8000         4000
120                    4000
121                    4100
121       8200         4100
122                    3950
122       7900         3950
123       6500         3250
123                    3250
124                    2900
124       5800         2900
125                    1600
125       3200         1600
126       2700         1350
126                    1350
127       2400         1200
127                    1200
128                    1100
128       2200         1100
129                    1650
129       3300         1650
130                    1400
130       2800         1400
131                    1250
131       2500         1250
132                    1050
132       2100         1050
133       3300         1650
133                    1650
134       2900         1450
134                    1450
135                    1200
135       2400         1200
136       2200         1100
136                    1100
137       3600         1800
137                    1800
138                    1600
138       3200         1600
139                    1350
139       2700         1350
140                    1250
140       2500         1250
141                    1750
141       3500         1750
142                    1550
142       3100         1550
143                    1300
143       2600         1300
144                    1250
144       2500         1250
180       3200         1600
180                    1600
181       3100         1550
181                    1550
182       2500         1250
182                    1250
183       2800         1400
183                    1400
184       4200         2100
184                    2100
185       4100         2050
185                    2050
186                    1700
186       3400         1700
187                    1500
187       3000         1500
188       3800         1900
188                    1900
189       3600         1800
189                    1800
190                    1450
190       2900         1450
191                    1250
191       2500         1250
192       4000         2000
192                    2000
193                    1950
193       3900         1950
194                    1600
194       3200         1600
195       2800         1400
195                    1400
196                    1550
196       3100         1550
197       3000         1500
197                    1500
198       2600         1300
198                    1300
199       2600         1300
199                    1300
60         103       9000         4500
103                    4500
104       6000         3000
104                    3000
105       4800         2400
105                    2400
106                    2400
106       4800         2400
107                    2100
107       4200         2100
70         204                    5000
204      10000         5000
80         145                    7000
145      14000         7000
146      13500         6750
146                    6750
147      12000         6000
147                    6000
148      11000         5500
148                    5500
149      10500         5250
149                    5250
150      10000         5000
150                    5000
151       9500         4750
151                    4750
152       9000         4500
152                    4500
153                    4000
153       8000         4000
154                    3750
154       7500         3750
155                    3500
155       7000         3500
156                    5000
156      10000         5000
157                    4750
157       9500         4750
158                    4500
158       9000         4500
159       8000         4000
159                    4000
160                    3750
160       7500         3750
161                    3500
161       7000         3500
162      10500         5250
162                    5250
163                    4750
163       9500         4750
164                    3600
164       7200         3600
165                    3400
165       6800         3400
166       6400         3200
166                    3200
167       6200         3100
167                    3100
168      11500         5750
168                    5750
169      10000         5000
169                    5000
170       9600         4800
170                    4800
171       7400         3700
171                    3700
172                    3650
172       7300         3650
173       6100         3050
173                    3050
174      11000         5500
174                    5500
175       8800         4400
175                    4400
176       8600         4300
176                    4300
177       8400         4200
177                    4200
179       6200         3100
179                    3100
90         100                   12000
100      24000        12000
101      17000         8500
101                    8500
102      17000         8500
102                    8500
100         108      12008         6004
108                    6004
109       9000         4500
109                    4500
110       8200         4100
110                    4100
111                    3850
111       7700         3850
112       7800         3900
112                    3900
113       6900         3450
113                    3450
110         205      12008         6004
205                    6004
206       8300         4150
206                    4150
178                    3500
178       7000         3500

214 rows selected.
```

### ITERATE: Take square root of salary iteratively until average < 10

```SELECT department_id, employee_id, salary, avg_salary
FROM employees
MODEL
PARTITION BY (department_id)
DIMENSION BY (Row_Number() OVER (PARTITION BY department_id ORDER BY employee_id) rn)
MEASURES (employee_id, salary, salary avg_salary, salary moving_avg_salary_3)
RULES ITERATE (100) UNTIL avg_salary[1] < 10.0 (
salary[ANY] = SQRT(salary[CV()]),
avg_salary[ANY] = AVG(salary)[ANY]
)
ORDER BY department_id, employee_id
/
DEPARTMENT_ID EMPLOYEE_ID     SALARY AVG_SALARY
------------- ----------- ---------- ----------
10         200  8.1444764  8.1444764
20         201 10.6778997 9.73950855
202 8.80111737 9.73950855
30         114 10.2411369 7.75455528
115 7.46174535 7.75455528
116 7.33836822 7.75455528
117 7.27427153 7.75455528
118 7.14074192 7.75455528
119 7.07106781 7.75455528
40         203  8.9790076  8.9790076
50         120 9.45741609 7.58389021
121 9.51597874 7.58389021
122 9.42772211 7.58389021
123  8.9790076 7.58389021
124 8.72683969 7.58389021
125 7.52120619 7.58389021
126 7.20843424 7.58389021
127 6.99927102 7.58389021
128 6.84866101 7.58389021
129 7.57928931 7.58389021
130 7.27427153 7.58389021
131 7.07106781 7.58389021
132 6.76947243 7.58389021
133 7.57928931 7.58389021
134 7.33836822 7.58389021
135 6.99927102 7.58389021
136 6.84866101 7.58389021
137 7.74596669 7.58389021
138 7.52120619 7.58389021
139 7.20843424 7.58389021
140 7.07106781 7.58389021
141 7.69160567 7.58389021
142 7.46174535 7.58389021
143 7.14074192 7.58389021
144 7.07106781 7.58389021
180 7.52120619 7.58389021
181 7.46174535 7.58389021
182 7.07106781 7.58389021
183 7.27427153 7.58389021
184 8.05030478 7.58389021
185 8.00195241 7.58389021
186 7.63606698 7.58389021
187 7.40082804 7.58389021
188 7.85137822 7.58389021
189 7.74596669 7.58389021
190 7.33836822 7.58389021
191 7.07106781 7.58389021
192 7.95270729 7.58389021
193 7.90252997 7.58389021
194 7.52120619 7.58389021
195 7.27427153 7.58389021
196 7.46174535 7.58389021
197 7.40082804 7.58389021
198 7.14074192 7.58389021
199 7.14074192 7.58389021
60         103 9.74003746 8.64772508
104 8.80111737 8.64772508
105  8.3235829 8.64772508
106  8.3235829 8.64772508
107 8.05030478 8.64772508
70         204 3.16227766 3.16227766
80         145 10.8775731  9.6838805
146 10.7791234  9.6838805
147 10.4663514  9.6838805
148 10.2411369  9.6838805
149 10.1227223  9.6838805
150         10  9.6838805
151 9.87258545  9.6838805
152 9.74003746  9.6838805
153 9.45741609  9.6838805
154 9.30604859  9.6838805
155 9.14691219  9.6838805
156         10  9.6838805
157 9.87258545  9.6838805
158 9.74003746  9.6838805
159 9.45741609  9.6838805
160 9.30604859  9.6838805
161 9.14691219  9.6838805
162 10.1227223  9.6838805
163 9.87258545  9.6838805
164  9.2115587  9.6838805
165 9.08086519  9.6838805
166 8.94427191  9.6838805
167 8.87356066  9.6838805
168 10.3555808  9.6838805
169         10  9.6838805
170 9.89846401  9.6838805
171 9.27487211  9.6838805
172 9.24337803  9.6838805
173  8.8375617  9.6838805
174 10.2411369  9.6838805
175 9.68546928  9.6838805
176 9.62996287  9.6838805
177 9.57347972  9.6838805
179 8.87356066  9.6838805
90         100 3.52798236 3.42875376
101 3.37913945 3.42875376
102 3.37913945 3.42875376
100         108 10.4680953  9.6005671
109 9.74003746  9.6005671
110 9.51597874  9.6005671
111 9.36747799  9.6005671
112 9.39774487  9.6005671
113 9.11406817  9.6005671
110         205 3.23544361 3.16245958
206 3.08947554 3.16245958
178 9.14691219 9.14691219

107 rows selected.
```

### Within-Rule Order Default Ascending: Set salary = previous salary

```PROMPT Within-Rule Order Default Ascending: Set salary = previous salary
SELECT department_id, employee_id, old_salary, salary
FROM employees
MODEL
PARTITION BY (department_id)
DIMENSION BY (Row_Number() OVER (PARTITION BY department_id ORDER BY employee_id) rn)
MEASURES (employee_id, salary old_salary, salary)
RULES (
salary[rn > 1] = salary[CV()-1]
)
ORDER BY department_id, employee_id
/
DEPARTMENT_ID EMPLOYEE_ID OLD_SALARY     SALARY
------------- ----------- ---------- ----------
10         200       4400       4400
20         201      13000      13000
202       6000      13000
30         114      11000      11000
115       3100      11000
116       2900      11000
117       2800      11000
118       2600      11000
119       2500      11000
40         203       6500       6500
50         120       8000       8000
121       8200       8000
122       7900       8000
123       6500       8000
124       5800       8000
125       3200       8000
126       2700       8000
127       2400       8000
128       2200       8000
129       3300       8000
130       2800       8000
131       2500       8000
132       2100       8000
133       3300       8000
134       2900       8000
135       2400       8000
136       2200       8000
137       3600       8000
138       3200       8000
139       2700       8000
140       2500       8000
141       3500       8000
142       3100       8000
143       2600       8000
144       2500       8000
180       3200       8000
181       3100       8000
182       2500       8000
183       2800       8000
184       4200       8000
185       4100       8000
186       3400       8000
187       3000       8000
188       3800       8000
189       3600       8000
190       2900       8000
191       2500       8000
192       4000       8000
193       3900       8000
194       3200       8000
195       2800       8000
196       3100       8000
197       3000       8000
198       2600       8000
199       2600       8000
60         103       9000       9000
104       6000       9000
105       4800       9000
106       4800       9000
107       4200       9000
70         204      10000      10000
80         145      14000      14000
146      13500      14000
147      12000      14000
148      11000      14000
149      10500      14000
150      10000      14000
151       9500      14000
152       9000      14000
153       8000      14000
154       7500      14000
155       7000      14000
156      10000      14000
157       9500      14000
158       9000      14000
159       8000      14000
160       7500      14000
161       7000      14000
162      10500      14000
163       9500      14000
164       7200      14000
165       6800      14000
166       6400      14000
167       6200      14000
168      11500      14000
169      10000      14000
170       9600      14000
171       7400      14000
172       7300      14000
173       6100      14000
174      11000      14000
175       8800      14000
176       8600      14000
177       8400      14000
179       6200      14000
90         100      24000      24000
101      17000      24000
102      17000      24000
100         108      12008      12008
109       9000      12008
110       8200      12008
111       7700      12008
112       7800      12008
113       6900      12008
110         205      12008      12008
206       8300      12008
178       7000       7000

107 rows selected.
```

### Within-Rule Order Descending: Set salary = previous salary

```SELECT department_id, employee_id, old_salary, salary
FROM employees
MODEL
PARTITION BY (department_id)
DIMENSION BY (Row_Number() OVER (PARTITION BY department_id ORDER BY employee_id) rn)
MEASURES (employee_id, salary old_salary, salary)
RULES (
salary[rn > 1] ORDER BY rn DESC = salary[CV()-1]
)
ORDER BY department_id, employee_id
/
DEPARTMENT_ID EMPLOYEE_ID OLD_SALARY     SALARY
------------- ----------- ---------- ----------
10         200       4400       4400
20         201      13000      13000
202       6000      13000
30         114      11000      11000
115       3100      11000
116       2900       3100
117       2800       2900
118       2600       2800
119       2500       2600
40         203       6500       6500
50         120       8000       8000
121       8200       8000
122       7900       8200
123       6500       7900
124       5800       6500
125       3200       5800
126       2700       3200
127       2400       2700
128       2200       2400
129       3300       2200
130       2800       3300
131       2500       2800
132       2100       2500
133       3300       2100
134       2900       3300
135       2400       2900
136       2200       2400
137       3600       2200
138       3200       3600
139       2700       3200
140       2500       2700
141       3500       2500
142       3100       3500
143       2600       3100
144       2500       2600
180       3200       2500
181       3100       3200
182       2500       3100
183       2800       2500
184       4200       2800
185       4100       4200
186       3400       4100
187       3000       3400
188       3800       3000
189       3600       3800
190       2900       3600
191       2500       2900
192       4000       2500
193       3900       4000
194       3200       3900
195       2800       3200
196       3100       2800
197       3000       3100
198       2600       3000
199       2600       2600
60         103       9000       9000
104       6000       9000
105       4800       6000
106       4800       4800
107       4200       4800
70         204      10000      10000
80         145      14000      14000
146      13500      14000
147      12000      13500
148      11000      12000
149      10500      11000
150      10000      10500
151       9500      10000
152       9000       9500
153       8000       9000
154       7500       8000
155       7000       7500
156      10000       7000
157       9500      10000
158       9000       9500
159       8000       9000
160       7500       8000
161       7000       7500
162      10500       7000
163       9500      10500
164       7200       9500
165       6800       7200
166       6400       6800
167       6200       6400
168      11500       6200
169      10000      11500
170       9600      10000
171       7400       9600
172       7300       7400
173       6100       7300
174      11000       6100
175       8800      11000
176       8600       8800
177       8400       8600
179       6200       8400
90         100      24000      24000
101      17000      24000
102      17000      17000
100         108      12008      12008
109       9000      12008
110       8200       9000
111       7700       8200
112       7800       7700
113       6900       7800
110         205      12008      12008
206       8300      12008
178       7000       7000

107 rows selected.
```

# Recursive Subquery Factors

SQL Recursive Subquery Factors in a Tweet

### Employee Tree: Connect By

```WITH cby AS (
SELECT last_name, employee_id, manager_id, LEVEL lvl
FROM employees
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name
)
SELECT employee_id,
LPad('.', 3*(lvl - 1), '.') || last_name last_name,
manager_id,
lvl
FROM cby
/
EMPLOYEE_ID LAST_NAME            MANAGER_ID        LVL
----------- -------------------- ---------- ----------
100 King                                     1
148 ...Cambrault                100          2
172 ......Bates                 148          3
169 ......Bloom                 148          3
170 ......Fox                   148          3
173 ......Kumar                 148          3
168 ......Ozer                  148          3
171 ......Smith                 148          3
102 ...De Haan                  100          2
103 ......Hunold                102          3
105 .........Austin             103          4
104 .........Ernst              103          4
107 .........Lorentz            103          4
106 .........Pataballa          103          4
147 ...Errazuriz                100          2
166 ......Ande                  147          3
167 ......Banda                 147          3
163 ......Greene                147          3
165 ......Lee                   147          3
164 ......Marvins               147          3
162 ......Vishney               147          3
121 ...Fripp                    100          2
130 ......Atkinson              121          3
129 ......Bissot                121          3
185 ......Bull                  121          3
187 ......Cabrio                121          3
186 ......Dellinger             121          3
131 ......Marlow                121          3
132 ......Olson                 121          3
184 ......Sarchand              121          3
201 ...Hartstein                100          2
202 ......Fay                   201          3
122 ...Kaufling                 100          2
188 ......Chung                 122          3
189 ......Dilly                 122          3
190 ......Gates                 122          3
135 ......Gee                   122          3
133 ......Mallin                122          3
191 ......Perkins               122          3
136 ......Philtanker            122          3
134 ......Rogers                122          3
101 ...Kochhar                  100          2
204 ......Baer                  101          3
108 ......Greenberg             101          3
110 .........Chen               108          4
109 .........Faviet             108          4
113 .........Popp               108          4
111 .........Sciarra            108          4
112 .........Urman              108          4
205 ......Higgins               101          3
206 .........Gietz              205          4
203 ......Mavris                101          3
200 ......Whalen                101          3
124 ...Mourgos                  100          2
142 ......Davies                124          3
197 ......Feeney                124          3
199 ......Grant                 124          3
143 ......Matos                 124          3
198 ......OConnell              124          3
141 ......Rajs                  124          3
144 ......Vargas                124          3
196 ......Walsh                 124          3
146 ...Partners                 100          2
160 ......Doran                 146          3
156 ......King                  146          3
158 ......McEwen                146          3
161 ......Sewall                146          3
159 ......Smith                 146          3
157 ......Sully                 146          3
114 ...Raphaely                 100          2
116 ......Baida                 114          3
119 ......Colmenares            114          3
118 ......Himuro                114          3
115 ......Khoo                  114          3
117 ......Tobias                114          3
145 ...Russell                  100          2
151 ......Bernstein             145          3
154 ......Cambrault             145          3
152 ......Hall                  145          3
153 ......Olsen                 145          3
150 ......Tucker                145          3
155 ......Tuvault               145          3
123 ...Vollman                  100          2
192 ......Bell                  123          3
193 ......Everett               123          3
195 ......Jones                 123          3
194 ......McCain                123          3
140 ......Patel                 123          3
139 ......Seo                   123          3
138 ......Stiles                123          3
120 ...Weiss                    100          2
181 ......Fleaur                120          3
183 ......Geoni                 120          3
127 ......Landry                120          3
128 ......Markle                120          3
126 ......Mikkilineni           120          3
125 ......Nayer                 120          3
182 ......Sullivan              120          3
180 ......Taylor                120          3
149 ...Zlotkey                  100          2
174 ......Abel                  149          3
178 ......Grant                 149          3
175 ......Hutton                149          3
179 ......Johnson               149          3
177 ......Livingston            149          3
176 ......Taylor                149          3

107 rows selected.
```

### Employee Tree: Recursive subquery factors, depth first

The result for the following query is the same as for the above CONNECT BY query:

```WITH rsf(employee_id, last_name, manager_id, lvl) AS (
SELECT employee_id,
last_name,
manager_id,
1 lvl
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id,
e.last_name,
e.manager_id,
r.lvl + 1
FROM rsf r
JOIN employees e ON e.manager_id = r.employee_id
) SEARCH DEPTH FIRST BY last_name SET ord_by
SELECT employee_id,
LPad('.', 3*(lvl - 1), '.') || last_name last_name,
manager_id,
lvl
FROM rsf
ORDER BY ord_by
/
```

### Employee Tree: Recursive subquery factors, breadth first

```WITH rsf(employee_id, last_name, manager_id, lvl) AS (
SELECT employee_id,
last_name,
manager_id,
1 lvl
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id,
e.last_name,
e.manager_id,
r.lvl + 1
FROM rsf r
JOIN employees e ON e.manager_id = r.employee_id
) SEARCH BREADTH FIRST BY last_name SET ord_by
SELECT employee_id,
LPad('.', 3*(lvl - 1), '.') || last_name last_name,
manager_id,
lvl
FROM rsf
ORDER BY ord_by
/
EMPLOYEE_ID LAST_NAME            MANAGER_ID        LVL
----------- -------------------- ---------- ----------
100 King                                     1
148 ...Cambrault                100          2
102 ...De Haan                  100          2
147 ...Errazuriz                100          2
121 ...Fripp                    100          2
201 ...Hartstein                100          2
122 ...Kaufling                 100          2
101 ...Kochhar                  100          2
124 ...Mourgos                  100          2
146 ...Partners                 100          2
114 ...Raphaely                 100          2
145 ...Russell                  100          2
123 ...Vollman                  100          2
120 ...Weiss                    100          2
149 ...Zlotkey                  100          2
174 ......Abel                  149          3
166 ......Ande                  147          3
130 ......Atkinson              121          3
204 ......Baer                  101          3
116 ......Baida                 114          3
167 ......Banda                 147          3
172 ......Bates                 148          3
192 ......Bell                  123          3
151 ......Bernstein             145          3
129 ......Bissot                121          3
169 ......Bloom                 148          3
185 ......Bull                  121          3
187 ......Cabrio                121          3
154 ......Cambrault             145          3
188 ......Chung                 122          3
119 ......Colmenares            114          3
142 ......Davies                124          3
186 ......Dellinger             121          3
189 ......Dilly                 122          3
160 ......Doran                 146          3
193 ......Everett               123          3
202 ......Fay                   201          3
197 ......Feeney                124          3
181 ......Fleaur                120          3
170 ......Fox                   148          3
190 ......Gates                 122          3
135 ......Gee                   122          3
183 ......Geoni                 120          3
199 ......Grant                 124          3
178 ......Grant                 149          3
108 ......Greenberg             101          3
163 ......Greene                147          3
152 ......Hall                  145          3
205 ......Higgins               101          3
118 ......Himuro                114          3
103 ......Hunold                102          3
175 ......Hutton                149          3
179 ......Johnson               149          3
195 ......Jones                 123          3
115 ......Khoo                  114          3
156 ......King                  146          3
173 ......Kumar                 148          3
127 ......Landry                120          3
165 ......Lee                   147          3
177 ......Livingston            149          3
133 ......Mallin                122          3
128 ......Markle                120          3
131 ......Marlow                121          3
164 ......Marvins               147          3
143 ......Matos                 124          3
203 ......Mavris                101          3
194 ......McCain                123          3
158 ......McEwen                146          3
126 ......Mikkilineni           120          3
125 ......Nayer                 120          3
198 ......OConnell              124          3
153 ......Olsen                 145          3
132 ......Olson                 121          3
168 ......Ozer                  148          3
140 ......Patel                 123          3
191 ......Perkins               122          3
136 ......Philtanker            122          3
141 ......Rajs                  124          3
134 ......Rogers                122          3
184 ......Sarchand              121          3
139 ......Seo                   123          3
161 ......Sewall                146          3
171 ......Smith                 148          3
159 ......Smith                 146          3
138 ......Stiles                123          3
182 ......Sullivan              120          3
157 ......Sully                 146          3
180 ......Taylor                120          3
176 ......Taylor                149          3
117 ......Tobias                114          3
150 ......Tucker                145          3
155 ......Tuvault               145          3
144 ......Vargas                124          3
162 ......Vishney               147          3
196 ......Walsh                 124          3
200 ......Whalen                101          3
105 .........Austin             103          4
110 .........Chen               108          4
104 .........Ernst              103          4
109 .........Faviet             108          4
206 .........Gietz              205          4
107 .........Lorentz            103          4
106 .........Pataballa          103          4
113 .........Popp               108          4
111 .........Sciarra            108          4
112 .........Urman              108          4

107 rows selected.
```

### Products using Recursive Subquery Factors: Passing through expressions

```WITH multipliers AS (
SELECT department_id, employee_id, salary, (1 + salary/10000) mult,
Row_Number() OVER (PARTITION BY department_id ORDER BY employee_id) rn
FROM employees
), rsf (department_id, employee_id, rn, salary, mult, running_prod, lvl) AS (
SELECT department_id, employee_id, rn, salary, mult,
mult running_prod, 1 lvl
FROM multipliers
WHERE rn = 1
UNION ALL
SELECT m.department_id, m.employee_id, m.rn, m.salary, m.mult,
r.running_prod * m.mult, r.lvl + 1
FROM rsf r
JOIN multipliers m
ON m.rn = r.rn + 1
AND m.department_id = r.department_id
)
SELECT department_id, employee_id, salary, mult, running_prod, lvl
FROM rsf
ORDER BY department_id, employee_id
/
DEPARTMENT_ID EMPLOYEE_ID     SALARY       MULT RUNNING_PROD        LVL
------------- ----------- ---------- ---------- ------------ ----------
10         200       4400       1.44         1.44          1
20         201      13000        2.3          2.3          1
202       6000        1.6         3.68          2
30         114      11000        2.1          2.1          1
115       3100       1.31        2.751          2
116       2900       1.29      3.54879          3
117       2800       1.28    4.5424512          4
118       2600       1.26   5.72348851          5
119       2500       1.25   7.15436064          6
40         203       6500       1.65         1.65          1
50         120       8000        1.8          1.8          1
121       8200       1.82        3.276          2
122       7900       1.79      5.86404          3
123       6500       1.65     9.675666          4
124       5800       1.58   15.2875523          5
125       3200       1.32    20.179569          6
126       2700       1.27   25.6280526          7
127       2400       1.24   31.7787853          8
128       2200       1.22    38.770118          9
129       3300       1.33    51.564257         10
130       2800       1.28   66.0022489         11
131       2500       1.25   82.5028112         12
132       2100       1.21   99.8284015         13
133       3300       1.33   132.771774         14
134       2900       1.29   171.275589         15
135       2400       1.24    212.38173         16
136       2200       1.22    259.10571         17
137       3600       1.36   352.383766         18
138       3200       1.32   465.146571         19
139       2700       1.27   590.736145         20
140       2500       1.25   738.420182         21
141       3500       1.35   996.867245         22
142       3100       1.31   1305.89609         23
143       2600       1.26   1645.42907         24
144       2500       1.25   2056.78634         25
180       3200       1.32   2714.95797         26
181       3100       1.31   3556.59495         27
182       2500       1.25   4445.74368         28
183       2800       1.28   5690.55191         29
184       4200       1.42   8080.58372         30
185       4100       1.41    11393.623         31
186       3400       1.34   15267.4549         32
187       3000        1.3   19847.6913         33
188       3800       1.38    27389.814         34
189       3600       1.36   37250.1471         35
190       2900       1.29   48052.6898         36
191       2500       1.25   60065.8622         37
192       4000        1.4   84092.2071         38
193       3900       1.39   116888.168         39
194       3200       1.32   154292.382         40
195       2800       1.28   197494.248         41
196       3100       1.31   258717.465         42
197       3000        1.3   336332.705         43
198       2600       1.26   423779.208         44
199       2600       1.26   533961.802         45
60         103       9000        1.9          1.9          1
104       6000        1.6         3.04          2
105       4800       1.48       4.4992          3
106       4800       1.48     6.658816          4
107       4200       1.42   9.45551872          5
70         204      10000          2            2          1
80         145      14000        2.4          2.4          1
146      13500       2.35         5.64          2
147      12000        2.2       12.408          3
148      11000        2.1      26.0568          4
149      10500       2.05     53.41644          5
150      10000          2    106.83288          6
151       9500       1.95   208.324116          7
152       9000        1.9    395.81582          8
153       8000        1.8   712.468477          9
154       7500       1.75   1246.81983         10
155       7000        1.7   2119.59372         11
156      10000          2   4239.18744         12
157       9500       1.95    8266.4155         13
158       9000        1.9   15706.1895         14
159       8000        1.8    28271.141         15
160       7500       1.75   49474.4968         16
161       7000        1.7   84106.6445         17
162      10500       2.05   172418.621         18
163       9500       1.95   336216.311         19
164       7200       1.72   578292.056         20
165       6800       1.68   971530.654         21
166       6400       1.64   1593310.27         22
167       6200       1.62   2581162.64         23
168      11500       2.15   5549499.68         24
169      10000          2   11098999.4         25
170       9600       1.96   21754038.7         26
171       7400       1.74   37852027.4         27
172       7300       1.73   65484007.4         28
173       6100       1.61    105429252         29
174      11000        2.1    221401429         30
175       8800       1.88    416234687         31
176       8600       1.86    774196517         32
177       8400       1.84   1424521591         33
179       6200       1.62   2307724978         34
90         100      24000        3.4          3.4          1
101      17000        2.7         9.18          2
102      17000        2.7       24.786          3
100         108      12008     2.2008       2.2008          1
109       9000        1.9      4.18152          2
110       8200       1.82    7.6103664          3
111       7700       1.77   13.4703485          4
112       7800       1.78   23.9772204          5
113       6900       1.69   40.5215024          6
110         205      12008     2.2008       2.2008          1
206       8300       1.83     4.027464          2
178       7000        1.7          1.7          1

107 rows selected.
```

Here's a query structure diagram for the query:

and here's a diagram showing partitioning and flow through the iterations:

You can see the scripts and full output on my new GitHub project,
Small SQL projects, in the analytics_and_recursion_explainers folder.

Here's an article from 2017 where you can see recursive SQL techniques used to solve a variety of difficult combinatorial optimization problems: Knapsacks and Networks in SQL.

# A Note on Running Sums and Products in SQL

[There is a recording on this article here: Tweet, and also in my GitHub project, Small SQL projects.]

It’s a common requirement to compute running sums over time in SQL; for example, to find sales volumes to date. This is easy to do using analytic functions, like this, schematically:

```SELECT SUM(sales) OVER (PARTITION By partition_key ORDER BY date)
FROM ...
```

The ORDER BY clause implicitly adds a window range of UNBOUNDED PRECEDING, and if you omit it you get the overall totals by partition_key.

Recently I needed to compute the products of some numeric factors across records over time. This kind of requirement often arises in financial calculations, and is slightly more tricky since Oracle doesn’t have an analytic function for products as it does for sums. However, we can achieve the same functionality using the well known mathematical equivalence:

```	log(xy) = log(x) + log(y)
```

and therefore:

`	xy = exp(log(x) + log(y))`

More generally, if we have a set of N records , and a function, f, defined on a record, we can write the running sum, for function f, and n in (1..N) as:

and the running product as:

Then from the above equivalence, we can write:

This means we can get the running products in SQL using the analytic SUM combined with the (natural) log (LN in Oracle SQL) and exp functions. Let’s see how it works using Oracle’s HR demo schema. We’ll take the employees table and use:

• department_id as the partition key
• employee_id as the dimension to order by
• salary as the measure

To start with, let’s get the running and total sums, showing results for department_id = 60:

```SELECT department_id, employee_id, salary,
SUM(salary) OVER (PARTITION BY department_id ORDER BY employee_id) running_sum,
SUM(salary) OVER (PARTITION BY department_id) total_sum
FROM employees
ORDER BY department_id, employee_id
/
Sums

DEPARTMENT_ID EMPLOYEE_ID     SALARY RUNNING_SUM  TOTAL_SUM
------------- ----------- ---------- ----------- ----------
60         103       9000        9000      28800
60         104       6000       15000      28800
60         105       4800       19800      28800
60         106       4800       24600      28800
60         107       4200       28800      28800
```

Next, let’s use the above equivalence to get the running and total products of the expression (1 + salary/10000), which we’ll call mult:

```SELECT department_id, employee_id, salary, (1 + salary/10000) mult,
EXP(SUM(LN((1 + salary/10000))) OVER (PARTITION BY department_id ORDER BY employee_id)) running_prod,
EXP(SUM(LN((1 + salary/10000))) OVER (PARTITION BY department_id)) total_prod
FROM employees
ORDER BY department_id, employee_id
/
Products

DEPARTMENT_ID EMPLOYEE_ID     SALARY       MULT RUNNING_PROD TOTAL_PROD
------------- ----------- ---------- ---------- ------------ ----------
60         103       9000        1.9          1.9 9.45551872
60         104       6000        1.6         3.04 9.45551872
60         105       4800       1.48       4.4992 9.45551872
60         106       4800       1.48     6.658816 9.45551872
60         107       4200       1.42   9.45551872 9.45551872

```

If we didn’t have this technique we could compute the results using explicit recursion, either by MODEL clause, or by recursive subquery factors. Let’s do it those ways out of interest. First here’s a MODEL clause solution:

```WITH multipliers AS (
SELECT department_id, employee_id, salary, (1 + salary/10000) mult,
COUNT(*) OVER (PARTITION BY department_id) n_emps
FROM employees
)
SELECT department_id, employee_id, salary, mult, running_prod, total_prod
FROM multipliers
MODEL
PARTITION BY (department_id)
DIMENSION BY (Row_Number() OVER (PARTITION BY department_id ORDER BY employee_id) rn)
MEASURES (employee_id, salary, mult, mult running_prod, mult total_prod, n_emps)
RULES (
running_prod[rn > 1] = mult[CV()] * running_prod[CV() - 1],
total_prod[any] = running_prod[n_emps[CV()]]
)
ORDER BY department_id, employee_id
```

Finally, here’s a solution using recursive subquery factors:

```WITH multipliers AS (
SELECT department_id, employee_id, salary, (1 + salary/10000) mult,
Row_Number() OVER (PARTITION BY department_id ORDER BY employee_id) rn,
COUNT(*) OVER (PARTITION BY department_id) n_emps
FROM employees
WHERE department_id = 60
), rsf (department_id, employee_id, rn, salary, mult, running_prod) AS (
SELECT department_id, employee_id, rn, salary, mult, mult running_prod
FROM multipliers
WHERE rn = 1
UNION ALL
SELECT m.department_id, m.employee_id, m.rn, m.salary, m.mult, r.running_prod * m.mult
FROM rsf r
JOIN multipliers m
ON m.rn = r.rn + 1
AND m.department_id = r.department_id
)
SELECT department_id, employee_id, salary, mult, running_prod,
Last_Value(running_prod) OVER (PARTITION BY department_id) total_prod
FROM rsf
ORDER BY department_id, employee_id
```

You can see the scripts and full output on my new GitHub project,
Small SQL projects, in the sums_products folder.

You can get the full detail on using analytic functions from the Oracle doc:
SQL for Analysis and Reporting

# Oracle PL/SQL API Demos Github Module

This post is essentially the readme for my Github module, Oracle PL/SQL API Demos

This is a Github module demonstrating instrumentation and logging, code timing and unit testing of Oracle PL/SQL APIs.

PL/SQL procedures were written against Oracle’s HR demo schema to represent the different kinds of API across two axes: Setter/Getter and Real Time/Batch.

```Mode          | Setter Example (S)          | Getter Example (G)
--------------|-----------------------------|----------------------------------
Real Time (R) | Web service saving          | Web service getting by ref cursor
```

The PL/SQL procedures and view were written originally to demonstrate unit testing, and are as follows:

• RS: Emp_WS.Save_Emps – Save a list of new employees to database, returning list of ids with Julian dates; logging errors to err\$ table
• RG: Emp_WS.Get_Dept_Emps – For given department id, return department and employee details including salary ratios, excluding employees with job ‘AD_ASST’, and returning none if global salary total < 1600, via ref cursor
• BS: Emp_Batch.Load_Emps – Load new/updated employees from file via external table
• BG: hr_test_view_v – View returning department and employee details including salary ratios, excluding employees with job ‘AD_ASST’, and returning none if global salary total < 1600

Each of these is unit tested, as described below, and in addition there is a driver script, api_driver.sql, that calls each of them and lists the results of logging and code timing.

I presented on Writing Clean Code in PL/SQL and SQL at the Ireland Oracle User Group Conference on 4 April 2019 in Dublin. The modules demonstrated here are written in the style recommended in the presentation where, in particular:

• ‘functional’ code is preferred
• object-oriented code is used only where necessary, using a package record array approach, rather than type bodies
• record types, defaults and overloading used extensively to provide clean API interfaces

# Screen Recordings on this Module

I initially made a series of screen recordings that are available at the links below, and later condensed each recording to a length that would upload directly to Twitter, i.e. less than 140 seconds. You can find the Twitter thread here. Both sets of recordings are also available in the recordings subfolder of the repository. The links below are to the initial, longer set of recordings.

# Unit Testing

The PL/SQL APIs are tested using the Math Function Unit Testing design pattern, with test results in HTML and text format included. The design pattern is based on the idea that all API testing programs can follow a universal design pattern, using the concept of a ‘pure’ function as a wrapper to manage the ‘impurity’ inherent in database APIs. I explained the concepts involved in a presentation at the Ireland Oracle User Group Conference in March 2018:

The Database API Viewed As A Mathematical Function: Insights into Testing

In this data-driven design pattern a driver program reads a set of scenarios from a JSON file, and loops over the scenarios calling the wrapper function with the scenario as input and obtaining the results as the return value. Utility functions from the Trapit module convert the input JSON into PL/SQL arrays, and, conversely, the output arrays into JSON text that is written to an output JSON file. This latter file contains all the input values and output values (expected and actual), as well as metadata describing the input and output groups. A separate nodejs module can be run to process the output files and create HTML files showing the results: Each unit test (say `pkg.prc`) has its own root page `pkg.prc.html` with links to a page for each scenario, located within a subfolder `pkg.prc`. Here, they have been copied into a subfolder test_output, as follows:

• tt_emp_ws.get_dept_emps
• tt_emp_ws.save_emps
• tt_view_drivers.hr_test_view_v

Where the actual output record matches expected, just one is represented, while if the actual differs it is listed below the expected and with background colour red. The employee group in scenario 4 of tt_emp_ws.save_emps has two records deliberately not matching, the first by changing the expected salary and the second by adding a duplicate expected record.

Each of the `pkg.prc` subfolders also includes a JSON Structure Diagram, `pkg.prc.png`, showing the input/output structure of the pure unit test wrapper function. For example:

Running a test causes the actual values to be inserted to the JSON object, which is then formatted as HTML pages:

Here is the output JSON for the 4’th scenario of the corresponding test:

```    "2 valid records, 1 invalid job id (2 deliberate errors)":{
"inp":{
"Employee":[
"LN 4|EM 4|IT_PROG|3000",
"LN 5|EM 5|NON_JOB|4000",
"LN 6|EM 6|IT_PROG|5000"
]
},
"out":{
"Employee":{
"exp":[
"3|LN 4|EM 4|IT_PROG|1000",
"5|LN 6|EM 6|IT_PROG|5000",
"5|LN 6|EM 6|IT_PROG|5000"
],
"act":[
"3|LN 4|EM 4|IT_PROG|3000",
"5|LN 6|EM 6|IT_PROG|5000"
]
},
"Output array":{
"exp":[
"3|LIKE /^[A-Z -]+[A-Z]\$/",
"5|LIKE /^[A-Z -]+[A-Z]\$/"
],
"act":[
"3|ONE THOUSAND NINE HUNDRED NINETY-EIGHT",
"5|TWO THOUSAND"
]
},
"Exception":{
"exp":[
],
"act":[
]
}
}
}
```

Here are images of the unit test summary and 4’th scenario pages for the corresponding test:

# Logging and Instrumentation

Program instrumentation means including lines of code to monitor the execution of a program, such as tracing lines covered, numbers of records processed, and timing information. Logging means storing such information, in database tables or elsewhere.

The Log_Set module allows for logging of various data in a lines table linked to a header for a given log, with the logging level configurable at runtime. The module also uses Oracle’s DBMS_Application_Info API to allow for logging in memory only with information accessible via the V\$SESSION and V\$SESSION_LONGOPS views.

The two web service-type APIs, Emp_WS.Save_Emps and Emp_WS.Get_Dept_Emps, use a configuration that logs only via DBMS_Application_Info, while the batch API, Emp_Batch.Load_Emps, also logs to the tables. The view of course does not do any logging itself but calling programs can log the results of querying it.

The driver script api_driver.sql calls all four of the demo APIs and performs its own logging of the calls and the results returned, including the DBMS_Application_Info on exit. The driver logs using a special DEBUG configuration where the log is constructed implicitly by the first Put, and there is no need to pass a log identifier when putting (so debug lines can be easily added in any called package). At the end of the script queries are run that list the contents of the logs created during the session in creation order, first normal logs, then a listing for error logs (of which one is created by deliberately raising an exception handled in WHEN OTHERS).

Here, for example, is the text logged by the driver script for the first call:

```Call Emp_WS.Save_Emps to save a list of employees passed...
===========================================================
DBMS_Application_Info: Module = EMP_WS: Log id 127
...................... Action = Log id 127 closed at 12-Sep-2019 06:20:2
...................... Client Info = Exit: Save_Emps, 2 inserted
Print the records returned...
=============================
1862 - ONE THOUSAND EIGHT HUNDRED SIXTY-TWO
1863 - ONE THOUSAND EIGHT HUNDRED SIXTY-THREE
```

# Code Timing

The code timing module Timer_Set is used by the driver script, api_driver.sql, to time the various calls, and at the end of the main block the results are logged using Log_Set.

The timing results are listed for illustration below:

```Timer Set: api_driver, Constructed at 12 Sep 2019 06:20:28, written at 06:20:29
===============================================================================
Timer             Elapsed         CPU       Calls       Ela/Call       CPU/Call
-------------  ----------  ----------  ----------  -------------  -------------
Save_Emps            0.00        0.00           1        0.00100        0.00000
Get_Dept_Emps        0.00        0.00           1        0.00100        0.00000
Write_File           0.00        0.02           1        0.00300        0.02000
Load_Emps            0.22        0.15           1        0.22200        0.15000
Delete_File          0.00        0.00           1        0.00200        0.00000
View_To_List         0.00        0.00           1        0.00200        0.00000
(Other)              0.00        0.00           1        0.00000        0.00000
-------------  ----------  ----------  ----------  -------------  -------------
Total                0.23        0.17           7        0.03300        0.02429
-------------  ----------  ----------  ----------  -------------  -------------
[Timer timed (per call in ms): Elapsed: 0.00794, CPU: 0.00873]
```

# Functional PL/SQL

The recordings 1.5 and 1.6 show examples of the functional style of PL/SQL used in the utility packages demonstrated, and here is a diagram from 1.6 illustrating a design pattern identified in refactoring the main subprogram of the unit test programs.

# Installation

## Install 1: Install pre-requisite tools

### Oracle database with HR demo schema

The database installation requires a minimum Oracle version of 12.2, with Oracle’s HR demo schema installed Oracle Database Software Downloads.

If HR demo schema is not installed, it can be got from here: Oracle Database Sample Schemas.

### Github Desktop

In order to clone the code as a git repository you need to have the git application installed. I recommend Github Desktop UI for managing repositories on windows. This depends on the git application, available here: git downloads, but can also be installed from within Github Desktop, according to these instructions:
How to install GitHub Desktop.

### nodejs (Javascript backend)

nodejs is needed to run a program that turns the unit test output files into formatted HTML pages. It requires no javascript knowledge to run the program, and nodejs can be installed here.

## Install 2: Clone git repository

• Open Github desktop and click [File/Clone repository…]
• Paste into the url field on the URL tab: https://github.com/BrenPatF/oracle_plsql_api_demos.git
• Choose local path as folder where you want your GitHub root to be
• Click [Clone]

## Install 3: Install pre-requisite modules

The demo install depends on the pre-requisite modules Utils, Trapit, Log_Set, and Timer_Set, and `lib` and `app` schemas refer to the schemas in which Utils and examples are installed, respectively.

The pre-requisite modules can be installed by following the instructions for each module at the module root pages listed in the `See also` section below. This allows inclusion of the examples and unit tests for those modules. Alternatively, the next section shows how to install these modules directly without their examples or unit tests here.

### [Schema: sys; Folder: install_prereq] Create lib and app schemas and Oracle directory

• install_sys.sql creates an Oracle directory, `input_dir`, pointing to ‘c:\input’. Update this if necessary to a folder on the database server with read/write access for the Oracle OS user
• Run script from slqplus:

SQL> @install_sys

### [Schema: lib; Folder: install_prereq\lib] Create lib components

• Run script from slqplus:

SQL> @install_lib_all

### [Schema: app; Folder: install_prereq\app] Create app synonyms

• Run script from slqplus:

SQL> @c_syns_all

### [Folder: (npm root)] Install npm trapit package

The npm trapit package is a nodejs package used to format unit test results as HTML pages.

Open a DOS or Powershell window in the folder where you want to install npm packages, and, with nodejs installed, run:

\$ npm install trapit

This should install the trapit nodejs package in a subfolder .\node_modules\trapit

## Install 4: Create Oracle PL/SQL API Demos components

### [Folder: (root)]

• Copy the following files from the root folder to the server folder pointed to by the Oracle directory INPUT_DIR:
• tt_emp_ws.save_emps_inp.json
• tt_emp_ws.get_dept_emps_inp.json
• tt_view_drivers.hr_test_view_v_inp.json
• There is also a bash script to do this, assuming C:\input as INPUT_DIR:

\$ ./cp_json_to_input.sh

### [Schema: lib; Folder: lib]

• Run script from slqplus:

SQL> @install_jobs app

### [Schema: hr; Folder: hr]

• Run script from slqplus:

SQL> @install_hr app

### [Schema: app; Folder: app]

• Run script from slqplus:

SQL> @install_api_demos lib

# Running Driver Script and Unit Tests

## Running driver script

### [Schema: app; Folder: app]

• Run script from slqplus:

SQL> @api_driver

The output is in api_driver.log

## Running unit tests

### [Schema: app; Folder: app]

• Run script from slqplus:

SQL> @r_tests

Testing is data-driven from the input JSON objects that are loaded from files into the table tt_units (at install time), and produces JSON output files in the INPUT_DIR folder, that contain arrays of expected and actual records by group and scenario. These files are:

• tt_emp_ws.get_dept_emps_out.json
• tt_emp_ws.save_emps_out.json
• tt_view_drivers.hr_test_view_v_out.json

The output files are processed by a nodejs program that has to be installed separately, from the `npm` nodejs repository, as described in the Installation section above. The nodejs program produces listings of the results in HTML and/or text format, and result files are included in the subfolders below test_output. To run the processor (in Windows), open a DOS or Powershell window in the trapit package folder after placing the output JSON files in the subfolder ./examples/externals and run:

\$ node ./examples/externals/test-externals

# Operating System/Oracle Versions

## Windows

Tested on Windows 10, should be OS-independent

## Oracle

• Tested on Oracle Database Version 19.3.0.0.0 (minimum required: 12.2)