{"id":52046,"date":"2023-10-12T00:00:00","date_gmt":"2023-10-12T07:00:00","guid":{"rendered":"https:\/\/griddb-linux-hte8hndjf8cka8ht.westus-01.azurewebsites.net\/blog\/exploring-griddbs-group-by-range-functionality\/"},"modified":"2023-10-12T00:00:00","modified_gmt":"2023-10-12T07:00:00","slug":"exploring-griddbs-group-by-range-functionality","status":"publish","type":"post","link":"https:\/\/griddb.net\/en\/blog\/exploring-griddbs-group-by-range-functionality\/","title":{"rendered":"Exploring GridDB’s Group By Range Functionality"},"content":{"rendered":"
\n
\n
\n
\n
\n<\/div>\n
\n<\/div>
\n

In the most recent release of GridDB CE (version 5.3), a new SQL function was added that allows a user to fetch interpolated or aggregated data for a set of time ranges over a total duration from a TIMESERIES<\/code> container. We explored similar functionality before in this blog post GridDB Optimization with Multi-Put and Query<\/a> by building a set of queries to query each individual group and then efficiently executing them with GridDB\u2019s multi-query function.<\/p>\n

The new query function is quite simple:<\/p>\n

SELECT * FROM table WHERE ts_col > TIMESTAMP(start) and ts_col <= TIMESTAMP(end) GROUP BY RANGE (n, duration)<\/code><\/p>\n

In the above example, ts_col<\/code> is is the index of the time-series container. The start<\/code> and end<\/code> variables are the starting and ending timestamps of the data queried and the query must have those clauses. Finally, each group\u2019s duration is n*duration. The duration can be:<\/p>\n

DAY\nHOUR\nMINUTE\nSECOND\nMILLISECOND<\/code><\/pre>\n
You can also query an aggregate of each time group such as the MIN, MAX, AVG, COUNT, SUM, etc.<\/p>\n
SELECT ts_col,AVG(value) FROM table WHERE ts_col > TIMESTAMP(start) and ts_col <= TIMESTAMP(end) GROUP BY RANGE (n, duration)<\/code><\/p>\n
Finally, if your data is sparse, the non-aggregate query\u2019s interpolation can be adjusted by appending FILL(method)<\/code> to the end of the query. Available fill methods include:<\/p>\n
LINEAR\nNONE\nNULL\nPREVIOUS<\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
\n
\n<\/div>\n
\n<\/div>
\n
Hands On\u00b6<\/a><\/h2>
Now, let\u2019s get started trying it out. We\u2019ll use the already ingested dataset from GridDB\u2019s 101 Course https:\/\/www.youtube.com\/playlist?list=PLZiizI6Euect9q64akYBkiqLMS78UTwjO<\/a>. Dataset can be found here on Kaggle<\/a>. Code for ingestion can be found on the GitHub page (it's written in node.js.)<\/p>\n
As this is a SQL function, it is only available via GridDB\u2019s JDBC or WebAPI interface. In this blog we\u2019ll be using the Python module jpype.dbapi2<\/code> as it natively supports SQL and the Timestamp to Python datetime object conversion. Compare the following two outputs using jaydebe<\/code> and jpype.dbapi2<\/code>: jaydebe outputs a string while jype.dbapi2 outputs a datetime object.<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/div>
\n
\n
\n<\/div>\n
\n
In\u00a0[2]:<\/div>\n
\n
\n
<\/span>import<\/span> <\/span>jaydebeapi<\/span>\n                               \njdbeurl<\/span> =<\/span> \"jdbc:gs:\/\/127.0.0.1:20001\/myCluster\/public\"<\/span>\njdbeconn<\/span> =<\/span> jaydebeapi<\/span>.<\/span>connect<\/span>(<\/span>\"com.toshiba.mwcloud.gs.sql.Driver\"<\/span>,<\/span> jdbeurl<\/span>,<\/span> [<\/span>\"admin\"<\/span>,<\/span> \"admin\"<\/span>],<\/span> \".\/lib\/gridstore-jdbc.jar\"<\/span>)<\/span>\n\njdbecurs<\/span> =<\/span> jdbeconn<\/span>.<\/span>cursor<\/span>()<\/span>\njdbecurs<\/span>.<\/span>execute<\/span>(<\/span>\"SELECT ts FROM device2 limit 1\"<\/span>)<\/span>\nrows<\/span> =<\/span> jdbecurs<\/span>.<\/span>fetchall<\/span>()<\/span>\nprint<\/span>(<\/span>type<\/span>(<\/span>rows<\/span>[<\/span>0<\/span>][<\/span>0<\/span>]),<\/span> rows<\/span>[<\/span>0<\/span>][<\/span>0<\/span>])<\/span>\n<\/pre><\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
\n<\/div>\n
\n
\n
<\/div>\n
\n
<class 'str'> 2020-07-11 17:01:34.735000\n<\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>
\n
\n
\n<\/div>\n
\n
In\u00a0[3]:<\/div>\n
\n
\n
<\/span>import<\/span> <\/span>jpype<\/span>\nimport<\/span> <\/span>jpype.dbapi2<\/span>\n    \nurl<\/span> =<\/span> \"jdbc:gs:\/\/127.0.0.1:20001\/myCluster\/public\"<\/span>\nconn<\/span> =<\/span> jpype<\/span>.<\/span>dbapi2<\/span>.<\/span>connect<\/span>(<\/span>url<\/span>,<\/span> driver<\/span>=<\/span>\"com.toshiba.mwcloud.gs.sql.Driver\"<\/span>,<\/span> driver_args<\/span>=<\/span>{<\/span>\"user\"<\/span>:<\/span> \"admin\"<\/span>,<\/span> \"password\"<\/span>:<\/span> \"admin\"<\/span>})<\/span>\n\ncurs<\/span> =<\/span> conn<\/span>.<\/span>cursor<\/span>()<\/span>\ncurs<\/span>.<\/span>execute<\/span>(<\/span>\"SELECT ts FROM device2 limit 1\"<\/span>)<\/span>\nrows<\/span> =<\/span> curs<\/span>.<\/span>fetchall<\/span>()<\/span>\nprint<\/span>(<\/span>type<\/span>(<\/span>rows<\/span>[<\/span>0<\/span>][<\/span>0<\/span>]),<\/span> rows<\/span>[<\/span>0<\/span>][<\/span>0<\/span>])<\/span>\n<\/pre><\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
\n<\/div>\n
\n
\n
<\/div>\n
\n
<class 'datetime.datetime'> 2020-07-11 17:01:34.735000\n<\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
\n
\n<\/div>\n
\n<\/div>
\n
With the connection setup we can create a function that gets the first and last timestamps and build the group by range query. The function itself returns data as Pandas DataFrame with the columns set to match the table columns.<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/div>
\n
\n
\n<\/div>\n
\n
In\u00a0[4]:<\/div>\n
\n
\n
<\/span>import<\/span> <\/span>datetime<\/span>\nimport<\/span> <\/span>pandas<\/span> <\/span>as<\/span> <\/span>pd<\/span>\n\ndef<\/span> <\/span>getRange<\/span>(<\/span>conn<\/span>,<\/span> table<\/span>,<\/span> range<\/span>):<\/span>\n    curs<\/span> =<\/span> conn<\/span>.<\/span>cursor<\/span>()<\/span>\n    curs<\/span>.<\/span>execute<\/span>(<\/span>\"SELECT min(ts),max(ts) from \"<\/span>+<\/span>table<\/span>)<\/span>\n    rows<\/span> =<\/span> curs<\/span>.<\/span>fetchall<\/span>()<\/span>\n    start<\/span> =<\/span> rows<\/span>[<\/span>0<\/span>][<\/span>0<\/span>];<\/span>\n    end<\/span> =<\/span> rows<\/span>[<\/span>0<\/span>][<\/span>1<\/span>]<\/span>\n\n    curs<\/span>.<\/span>execute<\/span>(<\/span>\"\"\"select * from \"\"\"<\/span>+<\/span>table<\/span>+<\/span>\"\"\" where ts >= TO_TIMESTAMP_MS(\"\"\"<\/span>+<\/span>str<\/span>(<\/span>int<\/span>(<\/span>start<\/span>.<\/span>timestamp<\/span>()<\/span>*<\/span>1000<\/span>))<\/span>+<\/span>\"\"\") <\/span>\n                 and ts <= TO_TIMESTAMP_MS(\"\"\"<\/span>+<\/span>str<\/span>(<\/span>int<\/span>(<\/span>end<\/span>.<\/span>timestamp<\/span>()<\/span>*<\/span>1000<\/span>))<\/span>+<\/span>\"\"\") group by range (ts) EVERY (1, \"\"\"<\/span>+<\/span>range<\/span>+<\/span>\"\"\");\"\"\"<\/span>);<\/span>\n\n    cols<\/span> =<\/span> tuple<\/span>(<\/span>zip<\/span>(<\/span>*<\/span>curs<\/span>.<\/span>description<\/span>))[<\/span>0<\/span>]<\/span>\n    df<\/span> =<\/span> pd<\/span>.<\/span>DataFrame<\/span>(<\/span>curs<\/span>.<\/span>fetchall<\/span>(),<\/span> columns<\/span>=<\/span>cols<\/span>)<\/span>\n    return<\/span> df<\/span>\n\ndef<\/span> <\/span>getAll<\/span>(<\/span>conn<\/span>,<\/span> table<\/span>):<\/span>\n    curs<\/span> =<\/span> conn<\/span>.<\/span>cursor<\/span>()<\/span>\n\n    curs<\/span>.<\/span>execute<\/span>(<\/span>\"\"\"select * from \"\"\"<\/span>+<\/span>table<\/span>);<\/span>\n\n    cols<\/span> =<\/span> tuple<\/span>(<\/span>zip<\/span>(<\/span>*<\/span>curs<\/span>.<\/span>description<\/span>))[<\/span>0<\/span>]<\/span>\n    df<\/span> =<\/span> pd<\/span>.<\/span>DataFrame<\/span>(<\/span>curs<\/span>.<\/span>fetchall<\/span>(),<\/span> columns<\/span>=<\/span>cols<\/span>)<\/span>\n    return<\/span> df<\/span> \n\ngetRange<\/span>(<\/span>conn<\/span>,<\/span> \"device2\"<\/span>,<\/span> \"DAY\"<\/span>)<\/span>\n<\/pre><\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
\n<\/div>\n
\n
\n
Out[4]:<\/div>\n
\n
\n