sql - Row aggregation by time based distance -

-

cheers, working on postgres table

create table my_table (   "id" serial,   "sensorid" integer,   "actorid" integer,   "timestamp" timestamp without time zone, )

with example data

id, sensorid, actorid, timestamp 1; 2267; 3023; "2013-07-09 12:20:06.446" 2; 2267; 3023; "2013-07-09 12:20:16.421" 3; 2267; 3023; "2013-07-09 12:20:30.661" 4; 2267; 3023; "2013-07-09 12:20:36.958" 5; 2267; 3023; "2013-07-09 12:20:49.508" 6; 2267; 3023; "2013-07-09 12:20:57.683" 7; 3301; 3023; "2013-08-15 06:03:03.428" 8; 2267; 3024; "2013-07-09 12:19:52.196" 9; 2267; 3024; "2013-07-09 12:20:16.515" 10; 2267; 3024; "2013-07-09 12:20:42.341" 11; 2267; 3025; "2013-07-09 12:21:05.98" 12; 2268; 3026; "2013-07-09 12:22:35.03" 13; 2268; 3026; "2013-07-09 12:22:45.066" 14; 3192; 3026; "2013-08-09 07:41:31.206"

i want group records following criteria

  1. they have same sensorid
  2. they have same actorid
  3. (the problem:) timespan between each of them less (say) 5 minutes. is, there might group spans on more hour there never more 5 minutes between 2 records in group. timespan may aggregates average.
  4. additionally, number of aggregated records each group must given, since large groups must identified.

so, output should like

id; sensorid, actorid, avg, count 1; 2267; 3023; "2013-07-09 12:20:30.000"; 7; 2; 3301; 3023; "2013-08-15 06:03:03.428"; 1; 3; 2267; 3024; "2013-07-09 12:20:06.415"; 3; 5; 2267; 3025; "2013-07-09 12:21:05.98"; 1; 6; 2268; 3026; "2013-07-09 12:22:40.626"; 2; 7; 3192; 3026; "2013-08-09 07:41:31.206"; 1;

thanks help! dennis

first, want use lag() determine previous time , whether starts new period. each sensorid/actorid combination, can cumulative sum of isstart identify groups each pair.

then aggregation including new group in result:

select sensorid, actorid, min(timestamp), max(timestamp), count(*) numingroup (select t.*,              sum(isstart) on (partition sensorid, actorid order timestamp) grp       (select t.*,                    (case when prevts null or prevts < timestamp - interval '5 minutes'                          1 else 0                     end) isstart             (select t.*,                          lag(timestamp) on (partition sensorid, actorid                                               order timestamp) prevts                   my_table t                  ) t            ) t      ) t group sensorid, actorid, grp

Comments

Post a Comment

Popular posts from this blog

Detect support for Shoutcast ICY MP3 without navigator.userAgent in Firefox? -

-
the current version of mozilla firefox 23.0.1, version not support play mp3 shoutcast streams tcp port different 80 (most common 8000 shoutcast 1.9.8). i use flash when mp3 support not available in html5 audio, way detect is: try{ var = document.createelement('audio'); r = !!(a.canplaytype && !!a.canplaytype("audio/mpeg; codecs=mp3").replace(/^no$/,'')) }catch(e){ r = false; } the support mp3 shoutcast streams in firefox added in version 24 . a.canplaytype("audio/mpeg; codecs=mp3") = in chrome , firefox, chrome support, firefox not support, due current code detect not work firefox. the current version of jquery support ie 6 1.10.2, version not has .browser i think "stylized" way testing features , not querying browsers / versions, notwithstanding here see hard not violate "principle". what "stylized" way of detect mp3 icy support without navigator.useragent in firefox? ther...
Read more

web - SVG not rendering properly in Firefox -

-
i have problem svg renderring in firefox. whilst works in safari, chrome or in ie, doesn't work in firefox. inspecting element seems layers there, background visible if it's z-index higher z-index of others. rendered in firefox: svg logo in firefox link original source file: svg file thanks help. ps: used sketch 3 export svg. if helps somehow. you're experiencing bug 995813 . i fixed bug time ago change still making way through release process example display correctly firefox 31 onwards released on 22 july 2014. in meantime if move fill="white" <mask> element child <use> element work around firefox bug.
Read more

java - JavaFX 2 slider labelFormatter not being used -

-
i have following code not label slider "foo50", etc... 1 expect setlabelformatter call, instead seems have no effect (the slider labelled 50, 100, etc , println's dont run). there missing? not implemented in default 2.2 skin? using oracle java 7 package javafxbugtest; import javafx.application.application; import javafx.scene.*; import javafx.scene.control.*; import javafx.stage.*; import javafx.util.stringconverter; public class javafxbugtest extends application { @override public void start(final stage primarystage) { slider cp = new slider(); cp.setlabelformatter(new stringconverter<double>() { @override public string tostring(double t) { system.out.println("nope?"); return "foo" + t.tostring(); } @override public double fromstring(string string) { system.out.println("he...
Read more