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
Query
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
/
Results
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
Running and Final Products: Final first rule, default SEQUENTIAL order
Query
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
/
Results
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
Query
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
/
Results
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
Query
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
/
Results
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
Query
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
/
Results
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
Query
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
/
Results
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
Query
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
/
Results
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
Query
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
/
Results
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
Oracle Doc: Recursive Subquery Factoring
SQL Recursive Subquery Factors in a Tweet
Employee Tree: Connect By
Query
WITH cby AS (
SELECT last_name, employee_id, manager_id, LEVEL lvl
FROM employees
START WITH employee_id = 100
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
/
Results
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
137 ......Ladwig 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:
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
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 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
/
Results
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
137 ......Ladwig 123 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
Query
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
/
Results
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.