CODEWITHSUNDEEP
orientdborientdb2.2
OpsEngineer
OpsEngineer

Reputation: 33

Grouping minutes by time intervals

If I were using Oracle, this issue would be resolved in a few minutes using rank over functions, however, not something that orientdb has, or is in it's roadmap.

I currently have a requirement, to do avg on a property and the group data by time intervals at the minute level.

Taking a 15 minute time span which gives me 15 records / 1 record per minute, I need to be able to group the data in intervals of 5 Minutes

My data structure is as followsenter image description here

My end results should be something like

avg(Last).............|..... GroupedMinutes

0.00103445 ....... |......5

0.00103441 ....... |......10

0.0010344 ......... |......15

Sample Data

create class times
insert into times (exe, Last, month, day, hour, minute) values ('EXE','0.1049611',7,29,19,0)
insert into times (exe, Last, month, day, hour, minute) values ('EXE','0.1049634',7,29,19,1)
insert into times (exe, Last, month, day, hour, minute) values ('EXE','0.1049654',7,29,19,2)
insert into times (exe, Last, month, day, hour, minute) values ('EXE','0.1049660',7,29,19,3)
insert into times (exe, Last, month, day, hour, minute) values ('EXE','0.1049655',7,29,19,4)
insert into times (exe, Last, month, day, hour, minute) values ('EXE','0.1049654',7,29,19,5)
insert into times (exe, Last, month, day, hour, minute) values ('EXE','0.1049677',7,29,19,6)
insert into times (exe, Last, month, day, hour, minute) values ('EXE','0.1049654',7,29,19,7)
insert into times (exe, Last, month, day, hour, minute) values ('EXE','0.1049698',7,29,19,8)
insert into times (exe, Last, month, day, hour, minute) values ('EXE','0.1049687',7,29,19,9)
insert into times (exe, Last, month, day, hour, minute) values ('EXE','0.1049633',7,29,19,10)
insert into times (exe, Last, month, day, hour, minute) values ('EXE','0.1049622',7,29,19,11)
insert into times (exe, Last, month, day, hour, minute) values ('EXE','0.1049643',7,29,19,12)
insert into times (exe, Last, month, day, hour, minute) values ('EXE','0.1049634',7,29,19,13)
insert into times (exe, Last, month, day, hour, minute) values ('EXE','0.1049657',7,29,19,14)
insert into times (exe, Last, month, day, hour, minute) values ('EXE','0.1049698',7,29,19,15)

Upvotes: 0

Views: 101

Answers (1)

dgiannotti
dgiannotti

Reputation: 371

I quickly wrote a js function that might help you get started, bear in mind that it's not optimized as it doesn't calculate the averages between different days:

var g = orient.getGraph();
var query = g.command("SQL", "SELECT * FROM times ORDER BY month, day, hour, minute");

var avg = 0, group = 0;
var prev_month, prev_day, prev_hour, prev_minute;
var record = [];

for(var i = 0; i < query.length; i++)
{
    var month = query[i].getRecord().field("month").toString();
    var day = query[i].getRecord().field("day").toString();
    var hour = query[i].getRecord().field("hour").toString();
    var minute = parseInt(query[i].getRecord().field("minute").toString());

    if((minute == (prev_minute + 1) && day == prev_day && hour == prev_hour && month == prev_month) || i == 0)
    {
        avg += parseFloat(query[i].getRecord().field("Last").toString());
        group++;

        if(group >= 5 || group >= (query.length-i)+2)
        {
            record.push((avg / group).toString().substring(0,11));
            avg = 0;
            group = 0;
        }
    }
    else
    {
        record.push((avg / group).toString().substring(0,11));
        avg = 0;
        group = 0;

        avg += parseFloat(query[i].getRecord().field("Last").toString());
        group++;

        if(group >= (query.length-i)+2)
        {
            record.push((avg / group).toString().substring(0,11));
            avg = 0;
            group = 0;
        }
    }

    prev_month = month;
    prev_day = day;
    prev_hour = hour;
    prev_minute = minute;
}

return record;

Upvotes: 4

Related Questions