I have node.js script that runs every 5 minutes and insert's data into the below table. I am using timestamp datatype in IBM Db2 database to identify each row.
I am trying to write a query in node.js that will connect to this table and display data hourly basis. I am facing trouble understanding how do i pick the row because different hours in a day will have multiple rows since it runs every 5 minutes. I am trying to write a logic that will pick the first row of every hour in the table and display it. Thus one day will have 24 rows returned.
I can either pick the first row of each hour or do a mean calculation of each hour.However, i am confused how to write a query for that. Can someone help me understand what i should use ?
CREATE TABLE TOTALS(
SQLDB_POOLED INT,
SQLDB_MAX INT,
SQLDB_ASSIGNED INT,
SQLDB_TIMESTAMP TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP
) ;
node.js
ibmdb.open(dsnString, function (err,conn) {
if (err) return console.log(err);
conn.query('select SQLDB_MAX,SQLDB_ASSIGNED,(SQLDB_ASSIGNED + SQLDB_POOLED) As SUMOFAP from TOTALS, function (err, data) {
if (err) console.log(err);
console.log(data);
res.send(JSON.stringify(data));
conn.close(function () {
console.log('done');
});
});
});
Here's one possability:
select SQLDB_MAX,SQLDB_ASSIGNED, (SQLDB_ASSIGNED + SQLDB_POOLED) As SUMOFAP
from (
select SQLDB_MAX, SQLDB_ASSIGNED, SQLDB_POOLED
, row_number() over ( partition by date(SQLDB_TIMESTAMP)
, hour(SQLDB_TIMESTAMP)
order by SQLDB_TIMESTAMP ) as rn
from TOTALS
) where rn = 1
The idea is that we within each date and hour order the rows according to their time, we can then choose the first one within each such partition.