Hi there,
I'm not sure if this is a bug or a true mis-use on my part... I have an
outer query that formats and organizes the work done by an inner query.
Running the inner queries alone work, but running them together within the
outer query gives me the misuse error below. I can't seem to figure it out.
Error: misuse of aggregate: min()
CREATE TABLE last_trades (date integer, price real, amount real, tid text,
[...snip...]);
I have not created anything else in the DB (except for other unrelated
tables). So there is no index, no views, no triggers and everything else
uses default settings.
The inner-most query (see query in full at bottom):
select cast(date/periodlen as int) bucket,* from last_trades t, (select
5*60 periodlen) t2 where isprimary='Y'
The goal is to have every single trades in the table fall in a 5 minute
bucket (configured with periodlen) so as to yield a summary for that period
of time.
The (mid-) inner grouping query:
select bucket, min(price) as low, max(price) as high, min(tid) as opentr,
max(tid) as closetr, sum(amount) vol from (
[...inner-most query here...]
) group by bucket
So far, everything works fine, but the next query gets things the way I
want (or rather, it should if it could!).
The outer formating query:
select datetime(bucket*5*60,'unixepoch','-5 hour') dt,
(select price from last_trades where tid=opentr) open,
low,high,
(select price from last_trades where tid=closetr) close,
vol
from (
[...all sub-queries here...]
) order by dt desc limit 50;
The misuse I get is related to the min/max(tid). tid is stored with a text
affinity. This is strange as doing a min/max(tid) in any other queries
works just like expected and replacing the min/max(tid) by a null allow
this whole query to run fine (with nulls in the output). The tid is
basically a unix timestamp + microseconds concatenated as a very huge
integer. I treat it as text everywhere to avoid overflows. I am entirely
assuming that every tid has the same length and that only digits are used
in it (both are entirely true so far), so min/max should work the same way
as if they were large integers.
Here's a quick fact about the contents of the db:
sqlite> select max(tid), min(tid) from last_trades;
max(tid) min(tid)
---------------- ----------------
1387218116138616 1387119187313744
And here's an extract of the db data if anyone would like to simulate the
issue.
sqlite> select date,tid,price,amount from last_trades limit 50;
date,tid,price,amount
1387119187,1387119187313744,879.395,0.051281
1387119188,1387119188017913,879.39999,0.25056
1387119188,1387119188087536,879.4,5.0
1387119188,1387119188156470,879.5,3.24
1387119188,1387119188256274,879.8,1.9997
1387119188,1387119188369787,879.9,0.187069
1387119188,1387119188451765,879.9,1.0
1387119188,1387119188519480,879.9,4.0
1387119188,1387119188606901,879.9899,8.04139
1387119205,1387119205706179,879.0,0.023746
1387119212,1387119212500459,879.9849,0.050316
1387119212,1387119212606281,879.9899,0.249684
1387119223,1387119223593847,879.966,0.014517
1387119232,1387119232931536,879.9,0.0
1387119309,1387119309202997,879.0,0.023732
1387119350,1387119350239678,879.0,0.056665
1387119350,1387119350836738,872.105,0.476114
1387119350,1387119350926946,872.10001,0.662057
1387119351,1387119351017666,871.41,2.0
1387119351,1387119351108603,871.0,3.519
1387119351,1387119351327908,871.0,0.68
1387119351,1387119351492518,870.38899,0.01
1387119351,1387119351563975,870.21453,0.01
1387119351,1387119351792659,870.146,0.049
1387119351,1387119351858153,870.11,10.200319
1387119351,1387119351939243,869.45012,2.617445
1387119352,1387119352011661,868.843,0.049
1387119352,1387119352080137,867.542,0.049
1387119352,1387119352140993,867.0,0.01
1387119352,1387119352217685,866.6,0.0666
1387119352,1387119352287223,866.243,0.049
1387119352,1387119352347625,865.92812,1.792754
1387119385,1387119385797755,879.82649,0.010466
1387119386,1387119386001355,879.89498,0.014213
1387119386,1387119386547644,879.89499,0.185137
1387119386,1387119386646332,879.89999,1.508127
1387119387,1387119387931236,879.89999,0.057016
1387119414,1387119414347670,868.10101,0.01
1387119414,1387119414568250,868.101,0.023
1387119414,1387119414651898,867.0,0.1
1387119415,1387119415090830,866.243,0.048
1387119415,1387119415621423,865.94312,0.083818
1387119415,1387119415886414,865.93813,0.010979
1387119416,1387119416098339,865.93812,0.027039
1387119417,1387119417152884,879.895,0.02
1387119498,1387119498221136,879.76498,0.01
1387119499,1387119499039112,879.76499,0.146842
1387119499,1387119499129476,879.76999,3.507113
1387119499,1387119499215422,879.77,1.0
1387119499,1387119499317666,879.9,5.0
And the actual real query (sorry, it's slightly different than above, but
not substantially so)
select datetime(bucket*plen,'unixepoch','-5 hour') dt,
(select price from last_trades where tid=opentr) open,
low,high,
(select price from last_trades where tid=closetr) close,
vol
from (
select bucket, min(price) as low, max(price) as high, min(tid) as opentr,
max(tid) as closetr, sum(amount) vol, max(periodlen) plen from (
select cast(date/periodlen as int) bucket,periodlen,* from last_trades t,
(select 5*60 periodlen) t2 where isprimary='Y'
) group by bucket
) order by dt desc limit 50;
And finally, you probably are interested in this last bit:
sqlite> .version
SQLite 3.7.17 2013-05-20 00:56:22 118a3b35693b134d56ebd780123b7fd6f1497668
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
