Not sure how to combine the subqueries.
Our TickData table looks close to: TimeStamp, Symbol, Ask, Bid, Last
... IQ_A, IQ_B, IQ_T, IQ_X
We need to update the IQ fields with calculations always made between
the current record and previous symbol record ordered by timestamp. I
don't know how to eliminate the 4 subqueries... Anyone have any code
for implementing oracle style lag / lead analytic functions?
This update query has become a monster... string functions, math
calcs, sign checks, case, case etc. All very simple to do in Excel
but not so easy as a Sql update query.
The IQ fields hold arrays of values formatted into a string and are
used to feed different downstream processes: ie. charting engine and
price analysis.
IQ_A =
{1,-2,3,-19,25,-28,175,-228,359,-611,7105.5,-14165,21247.5;41019.5416927083,41019.5416741898,41019.5416241319,41019.5415546875,41019.5037196181,41019.4764363426,41019.4444939236,41018.6088674769,41016.6075604745,41009.6108888889,41002.4481603009,41002.395833912,41002.395833912}
IQ_B =
{1,-2,3,-19,25,-28,175,-228,359,-611,7105.5,-14163,21244.5;41019.5416927083,41019.5416741898,41019.5416241319,41019.5415546875,41019.5037196181,41019.4764363426,41019.4444939236,41018.6088674769,41016.6075604745,41009.6108888889,41002.4481603009,41002.395833912,41002.395833912}
IQ_T
{-1,3,-19,25,-29,175,-227.5,359.5,-611,7105,-14164,21246;41019.5416741898,41019.5416232639,41019.5415546875,41019.5038145255,41019.4764427083,41019.4444939236,41018.6088674769,41016.6077332176,41009.6107991898,41002.4481603009,41002.395833912,41002.395833912}
IQ_X
{39653,-13516,6737,-3974,-2650,-1874,-1376,-1084,-886,-726,-592,-522,-454,-376,-316,-302,-266,-248,-232,215;39667,-13512,6737,-3974,-2650,-1874,-1378,-1086,-886,-726,-592,-522,-454,-376,-316,-302,-266,-248,-232,215;-19456,9205,5157,-3240,-2234,-1618,-1258,-998,-808,-682,-564,-490,-412,-352,-312,-290,-262,-248,-230,209}
I'm able to run the core calculation portions of the update query very
fast. ie. < 5 seconds to update 1 ... The string / array
manipulations are another story.
I may try a 2 pass approach: Pass 1 update query to record the rowid
of the previous symbol record and pass 2 use a join for performing the
IQ calcs. I think we might have hit the wall and need to code this
externally.
<You appear to be doing the same sub-select four times. I don't know
whether SQLite optimizes <these into one or not, but if you're using a
programming language I bet you could speed the query <up a great deal
by doing that subselect first, and substituting the results into the
UPDATE command.
<
<Simon.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
