2 ms·
I tried to replicate this in SQLite. The first few steps worked OK, e.g. https://lite.datasette.io/?json=https://gist.github.com/simonw/f750e08b33851a5ac8786d0
by simonw 4y ago
I tried to replicate this in SQLite. The first few steps worked OK, e.g.
https://lite.datasette.io/?json=https://gist.github.com/simonw/f750e08b33851a5ac8786d0b717a0152#/data?sql=WITH+RECURSIVE%0A++++stream+AS+%28%0A++++++++SELECT+ROW_NUMBER%28%29+OVER+%28%29+AS+step%2C+%22Adj+Close%22+AS+x%0A++++++++FROM+raw%0A++++++++ORDER+BY+step%0A++++%29%2C%0A++++state%28step%2C+x%2C+avg%29+AS+%28%0A++++++++--+Initialize%0A++++++++SELECT+step%2C+x%2C+x+AS+avg%0A++++++++FROM+stream%0A++++++++WHERE+step+%3D+1%0A++++++++UNION+ALL%0A++++++++--+Update%0A++++++++SELECT%0A++++++++++++stream.step%2C%0A++++++++++++stream.x%2C%0A++++++++++++state.avg+%2B+%28stream.x+-+state.avg%29+%2F+stream.step+AS+avg%0A++++++++FROM+stream%0A++++++++INNER+JOIN+state+ON+state.step+%2B+1+%3D+stream.step%0A++++%29%0A%0ASELECT+*%0AFROM+state%0AORDER+BY+step+DESC%0ALIMIT+5 https://lite.datasette.io/?json=https://gist.github.com/simo...
(I replaced "figures" with "raw" due to the way Datasette Lite assigns a default table name to the imported JSON)
But the more complex recursive queries gave me this error and I'm not sure how to work around it:
recursive reference in a subquery: state
E.g. https://lite.datasette.io/?json=https://gist.github.com/simonw/f750e08b33851a5ac8786d0b717a0152#/data?sql=WITH+RECURSIVE%0A++++stream+AS+%28%0A++++++++SELECT%0A++++++++++++ROW_NUMBER%28%29+OVER+%28%29+AS+step%2C%0A++++++++++++%22Adj+Close%22+AS+x%2C%0A++++++++++++%22Close%22+AS+y%0A++++++++FROM+raw%0A++++%29%2C%0A++++state%28step%2C+x%2C+x_avg%2C+y%2C+y_avg%2C+cov%29+AS+%28%0A++++++++--+Initialize%0A++++++++SELECT%0A++++++++++++step%2C%0A++++++++++++x%2C%0A++++++++++++x+AS+x_avg%2C%0A++++++++++++y%2C%0A++++++++++++y+AS+y_avg%2C%0A++++++++++++0.0+AS+cov%0A++++++++FROM+stream%0A++++++++WHERE+step+%3D+1%0A++++++++UNION+ALL%0A++++++++--+Update%0A++++++++SELECT%0A++++++++++++step%2C%0A++++++++++++x%2C%0A++++++++++++x_new_avg+AS+x_avg%2C%0A++++++++++++y%2C%0A++++++++++++y_new_avg+AS+y_avg%2C%0A++++++++++++cov+%2B+%28%28x+-+x_prev_avg%29+*+%28y+-+y_new_avg%29+-+cov%29+%2F+step+AS+cov%0A++++++++FROM+%28%0A++++++++++++SELECT%0A++++++++++++++++stream.step%2C%0A++++++++++++++++stream.x%2C%0A++++++++++++++++stream.y%2C%0A++++++++++++++++state.x_avg+AS+x_prev_avg%2C%0A++++++++++++++++state.x_avg+%2B+%28stream.x+-+state.x_avg%29+%2F+stream.step+AS+x_new_avg%2C%0A++++++++++++++++state.y_avg+AS+y_prev_avg%2C%0A++++++++++++++++state.y_avg+%2B+%28stream.y+-+state.y_avg%29+%2F+stream.step+AS+y_new_avg%2C%0A++++++++++++++++state.cov%0A++++++++++++FROM+stream%0A++++++++++++INNER+JOIN+state+ON+state.step+%2B+1+%3D+stream.step%0A++++++++%29%0A++++%29%0A%0ASELECT+step%2C+cov%0AFROM+state%0AORDER+BY+step+DESC%0ALIMIT+5&DOUBLE= https://lite.datasette.io/?json=https://gist.github.com/simo...
- Lemaxoxo 4y agoThat's too bad, I would have expected it to work out of the box. Other than rewriting the query in a different way, I'm not sure I see an easy workaround. Are you still working on this?
- simonw 4y agoNo I managed to drag myself away from it and get back to what I'd been intending to work on!