Changeset: 20d6b364cbb0 for MonetDB URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=20d6b364cbb0 Added Files: sql/test/timetrails/Tests/derivative.stable.err sql/test/timetrails/Tests/derivative.stable.out sql/test/timetrails/Tests/labels.stable.err sql/test/timetrails/Tests/labels.stable.out Modified Files: sql/test/timetrails/Tests/derivative.sql sql/test/timetrails/Tests/rieman.sql sql/test/timetrails/Tests/simple_queries.sql sql/test/timetrails/Tests/simple_queries.stable.err sql/test/timetrails/Tests/simple_queries.stable.out sql/test/timetrails/Tests/time_rollup.sql sql/test/timetrails/Tests/time_rollup.stable.out Branch: timetrails Log Message:
The primitives required for timeseries ala influxdb diffs (truncated from 368 to 300 lines): diff --git a/sql/test/timetrails/Tests/derivative.sql b/sql/test/timetrails/Tests/derivative.sql --- a/sql/test/timetrails/Tests/derivative.sql +++ b/sql/test/timetrails/Tests/derivative.sql @@ -1,18 +1,10 @@ ---derivative by time -CREATE FUNCTION rooms_derivative( stride bigint) -RETURNS TABLE( - tick timestamp, - room string, - level integer, - temp double) -BEGIN - RETURN - WITH bounds(first, last, period) - AS (SELECT min(tick) AS first, max(tick) as last, epoch(tick)/stride AS period FROM rooms GROUP BY period) - SELECT r2.tick, r2.room, r2.level, (r2.temp - r1.temp)/ (epoch(bounds.last) - epoch(bounds.first)) FROM bounds, rooms r1, rooms r2 - WHERE r1.tick = bounds.first and r2.tick = bounds.last and r1.room = r2.room and r1.level = r2.level; -END; +--derivative by period +WITH T(rowid, tick, room, level, temp) + AS (SELECT row_number() over() AS rowid, H.tick, H.room, H.level, H.temp + FROM (SELECT max(tick) as tick, epoch(tick)/60 AS period, room, level, avg(temp) as temp + FROM rooms + WHERE tick BETWEEN timestamp '2017/01/01 09:00:00' AND timestamp '2017/01/31 23:59:59' + GROUP BY period, room,level) AS H) +SELECT A.tick, A.room, A.level, (B.temp - A.temp) FROM T A, T B +WHERE B.rowid = A.rowid+1 and A.room = B.room and A.level = B.level; ---TODO simplify using CREATE AGGREGATE derivative(tick,temp) RETURNS TABLE ( tick timestamp, result double) EXTERNAL NAME xyz.yy ---SELECT min(tick), derivative(tick, temp) FROM rooms WHERE tick BETWEEN timestamp '2017/01/01 09:00:00' AND timestamp '2017/01/31 23:59:59' GROUP BY room, level; - diff --git a/sql/test/timetrails/Tests/derivative.stable.err b/sql/test/timetrails/Tests/derivative.stable.err new file mode 100644 --- /dev/null +++ b/sql/test/timetrails/Tests/derivative.stable.err @@ -0,0 +1,34 @@ +stderr of test 'derivative` in directory 'sql/test/timetrails` itself: + + +# 21:17:14 > +# 21:17:14 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set" "mapi_open=true" "--set" "mapi_port=34420" "--set" "mapi_usock=/var/tmp/mtest-29917/.s.monetdb.34420" "--set" "monet_prompt=" "--forcemito" "--dbpath=/export/scratch1/mk/timetrails//Linux/var/MonetDB/mTests_sql_test_timetrails" +# 21:17:14 > + +# builtin opt gdk_dbpath = /export/scratch1/mk/timetrails//Linux/var/monetdb5/dbfarm/demo +# builtin opt gdk_debug = 0 +# builtin opt gdk_vmtrim = no +# builtin opt monet_prompt = > +# builtin opt monet_daemon = no +# builtin opt mapi_port = 50000 +# builtin opt mapi_open = false +# builtin opt mapi_autosense = false +# builtin opt sql_optimizer = default_pipe +# builtin opt sql_debug = 0 +# cmdline opt gdk_nr_threads = 0 +# cmdline opt mapi_open = true +# cmdline opt mapi_port = 34420 +# cmdline opt mapi_usock = /var/tmp/mtest-29917/.s.monetdb.34420 +# cmdline opt monet_prompt = +# cmdline opt gdk_dbpath = /export/scratch1/mk/timetrails//Linux/var/MonetDB/mTests_sql_test_timetrails +# cmdline opt gdk_debug = 536870922 + +# 21:17:14 > +# 21:17:14 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e" "--host=/var/tmp/mtest-29917" "--port=34420" +# 21:17:14 > + + +# 21:17:14 > +# 21:17:14 > "Done." +# 21:17:14 > + diff --git a/sql/test/timetrails/Tests/derivative.stable.out b/sql/test/timetrails/Tests/derivative.stable.out new file mode 100644 --- /dev/null +++ b/sql/test/timetrails/Tests/derivative.stable.out @@ -0,0 +1,46 @@ +stdout of test 'derivative` in directory 'sql/test/timetrails` itself: + + +# 21:17:14 > +# 21:17:14 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set" "mapi_open=true" "--set" "mapi_port=34420" "--set" "mapi_usock=/var/tmp/mtest-29917/.s.monetdb.34420" "--set" "monet_prompt=" "--forcemito" "--dbpath=/export/scratch1/mk/timetrails//Linux/var/MonetDB/mTests_sql_test_timetrails" +# 21:17:14 > + +# MonetDB 5 server v11.28.0 +# This is an unreleased version +# Serving database 'mTests_sql_test_timetrails', using 8 threads +# Compiled for x86_64-unknown-linux-gnu/64bit with 128bit integers +# Found 15.589 GiB available main-memory. +# Copyright (c) 1993-July 2008 CWI. +# Copyright (c) August 2008-2017 MonetDB B.V., all rights reserved +# Visit http://www.monetdb.org/ for further information +# Listening for connection requests on mapi:monetdb://vienna.da.cwi.nl:34420/ +# Listening for UNIX domain connection requests on mapi:monetdb:///var/tmp/mtest-29917/.s.monetdb.34420 +# MonetDB/GIS module loaded +# MonetDB/SQL module loaded + +Ready. + +# 21:17:14 > +# 21:17:14 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e" "--host=/var/tmp/mtest-29917" "--port=34420" +# 21:17:14 > + +#WITH T(rowid, tick, room, level, temp) +# AS (SELECT row_number() over() AS rowid, H.tick, H.room, H.level, H.temp +# FROM (SELECT max(tick) as tick, epoch(tick)/60 AS period, room, level, avg(temp) as temp +# FROM rooms +# WHERE tick BETWEEN timestamp '2017/01/01 09:00:00' AND timestamp '2017/01/31 23:59:59' +# GROUP BY period, room,level) AS H) +#SELECT A.tick, A.room, A.level, (B.temp - A.temp) FROM T A, T B +#WHERE B.rowid = A.rowid+1 and A.room = B.room and A.level = B.level; +% sys.a, sys.a, sys.a, sys.L33 # table_name +% tick, room, level, L33 # name +% timestamp, clob, int, double # type +% 26, 4, 1, 24 # length +[ 2017-01-01 09:00:45.000000, "L302", 3, 4.9 ] +[ 2017-01-01 10:00:45.000000, "L302", 3, 2.65 ] +[ 2017-01-01 11:00:45.000000, "L302", 3, 0.075 ] + +# 21:17:14 > +# 21:17:14 > "Done." +# 21:17:14 > + diff --git a/sql/test/timetrails/Tests/labels.stable.err b/sql/test/timetrails/Tests/labels.stable.err new file mode 100644 --- /dev/null +++ b/sql/test/timetrails/Tests/labels.stable.err @@ -0,0 +1,34 @@ +stderr of test 'labels` in directory 'sql/test/timetrails` itself: + + +# 22:10:24 > +# 22:10:24 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set" "mapi_open=true" "--set" "mapi_port=35402" "--set" "mapi_usock=/var/tmp/mtest-31926/.s.monetdb.35402" "--set" "monet_prompt=" "--forcemito" "--dbpath=/export/scratch1/mk/timetrails//Linux/var/MonetDB/mTests_sql_test_timetrails" +# 22:10:24 > + +# builtin opt gdk_dbpath = /export/scratch1/mk/timetrails//Linux/var/monetdb5/dbfarm/demo +# builtin opt gdk_debug = 0 +# builtin opt gdk_vmtrim = no +# builtin opt monet_prompt = > +# builtin opt monet_daemon = no +# builtin opt mapi_port = 50000 +# builtin opt mapi_open = false +# builtin opt mapi_autosense = false +# builtin opt sql_optimizer = default_pipe +# builtin opt sql_debug = 0 +# cmdline opt gdk_nr_threads = 0 +# cmdline opt mapi_open = true +# cmdline opt mapi_port = 35402 +# cmdline opt mapi_usock = /var/tmp/mtest-31926/.s.monetdb.35402 +# cmdline opt monet_prompt = +# cmdline opt gdk_dbpath = /export/scratch1/mk/timetrails//Linux/var/MonetDB/mTests_sql_test_timetrails +# cmdline opt gdk_debug = 536870922 + +# 22:10:24 > +# 22:10:24 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e" "--host=/var/tmp/mtest-31926" "--port=35402" +# 22:10:24 > + + +# 22:10:24 > +# 22:10:24 > "Done." +# 22:10:24 > + diff --git a/sql/test/timetrails/Tests/labels.stable.out b/sql/test/timetrails/Tests/labels.stable.out new file mode 100644 --- /dev/null +++ b/sql/test/timetrails/Tests/labels.stable.out @@ -0,0 +1,41 @@ +stdout of test 'labels` in directory 'sql/test/timetrails` itself: + + +# 22:10:24 > +# 22:10:24 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set" "mapi_open=true" "--set" "mapi_port=35402" "--set" "mapi_usock=/var/tmp/mtest-31926/.s.monetdb.35402" "--set" "monet_prompt=" "--forcemito" "--dbpath=/export/scratch1/mk/timetrails//Linux/var/MonetDB/mTests_sql_test_timetrails" +# 22:10:24 > + +# MonetDB 5 server v11.28.0 +# This is an unreleased version +# Serving database 'mTests_sql_test_timetrails', using 8 threads +# Compiled for x86_64-unknown-linux-gnu/64bit with 128bit integers +# Found 15.589 GiB available main-memory. +# Copyright (c) 1993-July 2008 CWI. +# Copyright (c) August 2008-2017 MonetDB B.V., all rights reserved +# Visit http://www.monetdb.org/ for further information +# Listening for connection requests on mapi:monetdb://vienna.da.cwi.nl:35402/ +# Listening for UNIX domain connection requests on mapi:monetdb:///var/tmp/mtest-31926/.s.monetdb.35402 +# MonetDB/GIS module loaded +# MonetDB/SQL module loaded + +Ready. + +# 22:10:24 > +# 22:10:24 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e" "--host=/var/tmp/mtest-31926" "--port=35402" +# 22:10:24 > + +#SELECT min(tick), concat(room, concat('_', cast(level as string))), count(*) +#FROM rooms +#WHERE tick BETWEEN timestamp '2017/01/01 09:00:00' AND timestamp '2017/01/31 23:59:59' +#GROUP BY room, level; +% sys.L3, sys.L6, sys.L11 # table_name +% L2, L6, L10 # name +% timestamp, varchar, bigint # type +% 26, 6, 2 # length +[ 2017-01-01 09:00:00.000000, "L302_3", 16 ] +[ 2017-01-01 13:00:00.000000, "L301_3", 4 ] + +# 22:10:24 > +# 22:10:24 > "Done." +# 22:10:24 > + diff --git a/sql/test/timetrails/Tests/rieman.sql b/sql/test/timetrails/Tests/rieman.sql --- a/sql/test/timetrails/Tests/rieman.sql +++ b/sql/test/timetrails/Tests/rieman.sql @@ -1,6 +1,8 @@ ---TODO integral, calculate the Rieman integral. If the events are regularly spread it is equal to the sum --- introduce the following CREATE AGGREGATE integral(col1,col2) RETURNS double EXTERNAL NAME xyz.yy; -SELECT min(tick), integral(tick, temp) -FROM rooms -WHERE tick BETWEEN timestamp '2017/01/01 09:00:00' AND timestamp '2017/01/31 23:59:59' -GROUP BY room, level; +--TODO cumulative sum required in windows function : cumulative_sum(H.temp) +WITH T(tick, room, level, temp) + AS (SELECT H.tick, H.room, H.level, H.temp + FROM (SELECT max(tick) as tick, epoch(tick)/60 AS period, room, level, sum(temp) as temp + FROM rooms + WHERE tick BETWEEN timestamp '2017/01/01 09:00:00' AND timestamp '2017/01/31 23:59:59' + GROUP BY period, room,level) AS H) +SELECT * FROM T ; diff --git a/sql/test/timetrails/Tests/simple_queries.sql b/sql/test/timetrails/Tests/simple_queries.sql --- a/sql/test/timetrails/Tests/simple_queries.sql +++ b/sql/test/timetrails/Tests/simple_queries.sql @@ -10,18 +10,23 @@ FROM rooms WHERE tick BETWEEN timestamp GROUP BY room, level; --mean -SELECT min(tick), CAST(mean(cast(tick AS int)) AS timestamp) +SELECT min(tick), avg(temp) FROM rooms WHERE tick BETWEEN timestamp '2017/01/01 09:00:00' AND timestamp '2017/01/31 23:59:59' GROUP BY room, level; --median -SELECT min(tick), CAST(median(cast(tick AS int)) AS timestamp) +SELECT min(tick), median(temp) FROM rooms WHERE tick BETWEEN timestamp '2017/01/01 09:00:00' AND timestamp '2017/01/31 23:59:59' GROUP BY room, level; +--std_dev +SELECT min(tick), std_dev(temp) +FROM rooms +WHERE tick BETWEEN timestamp '2017/01/01 09:00:00' AND timestamp '2017/01/31 23:59:59' +GROUP BY room, level; --avg SELECT min(tick), avg(temp) FROM rooms diff --git a/sql/test/timetrails/Tests/simple_queries.stable.err b/sql/test/timetrails/Tests/simple_queries.stable.err --- a/sql/test/timetrails/Tests/simple_queries.stable.err +++ b/sql/test/timetrails/Tests/simple_queries.stable.err @@ -27,21 +27,8 @@ stderr of test 'simple_queries` in direc # 22:51:13 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e" "--host=/var/tmp/mtest-19761" "--port=30810" # 22:51:13 > -MAPI = (monetdb) /var/tmp/mtest-19761/.s.monetdb.30810 -QUERY = SELECT min(tick), CAST(mean(cast(tick AS int)) AS timestamp) - FROM rooms - WHERE tick - BETWEEN timestamp '2017/01/01 09:00:00' AND timestamp '2017/01/31 23:59:59' - GROUP BY room, level; -MAPI = (monetdb) /var/tmp/mtest-19761/.s.monetdb.30810 -QUERY = SELECT min(tick), CAST(median(cast(tick AS int)) AS timestamp) - FROM rooms - WHERE tick BETWEEN timestamp '2017/01/01 09:00:00' AND timestamp '2017/01/31 23:59:59' - GROUP BY room, level; +# 21:33:06 > +# 21:33:06 > "Done." +# 21:33:06 > - -# 22:51:13 > -# 22:51:13 > "Done." -# 22:51:13 > - diff --git a/sql/test/timetrails/Tests/simple_queries.stable.out b/sql/test/timetrails/Tests/simple_queries.stable.out --- a/sql/test/timetrails/Tests/simple_queries.stable.out +++ b/sql/test/timetrails/Tests/simple_queries.stable.out @@ -45,6 +45,27 @@ Ready. [ 2017-01-01 13:00:00.000000, 4 ] #SELECT min(tick), avg(temp) #FROM rooms +#WHERE tick +#BETWEEN timestamp '2017/01/01 09:00:00' AND timestamp '2017/01/31 23:59:59' +#GROUP BY room, level; +% sys.L3, sys.L6 # table_name +% L2, L5 # name +% timestamp, double # type +% 26, 24 # length +[ 2017-01-01 09:00:00.000000, 18.89375 ] +[ 2017-01-01 13:00:00.000000, 17.125 ] +#SELECT min(tick), median(temp) +#FROM rooms +#WHERE tick BETWEEN timestamp '2017/01/01 09:00:00' AND timestamp '2017/01/31 23:59:59' _______________________________________________ checkin-list mailing list [email protected] https://www.monetdb.org/mailman/listinfo/checkin-list
