sql - Potsgresql Query with overlapping dates, multiple arrays -
edit: have edited question make more understandable. excuse me misunderstandings.
i have temporary table columns
zone_name, nodeid, nodelabel, nodegainedservice, nodelostservice zone1, 3, windows-srv1, "2012-11-27 13:10:30+08", "2012-11-27 13:00:40+08" zone1, 5, windows-srv2, "2012-12-20 13:10:30+08", "2012-12-18 13:00:40+08" .... ....
many zones , many nodes , same nodes gained service , lost service many times.
nodegainedservice
meaning node has come alive , nodelostservice
meaning node has gone down.
how make query fetch each zone availability in period?
e.g., zone1 have windows-srv1, windows-srv2. find how many times , how long zone1 down. these servers replication servers, zone goes down when servers in zone down @ time , comes if of them comes alive.
please use below sample data
zonename nodeid nodelabel noderegainedservice nodelostservice zone1 27 windows-srv1 2013-02-21 10:04:56+08 2013-02-21 09:48:48+08 zone1 27 windows-srv1 2013-02-21 10:14:01+08 2013-02-21 10:09:27+08 zone1 27 windows-srv1 2013-02-22 10:26:29+08 2013-02-22 10:24:20+08 zone1 27 windows-srv1 2013-02-22 11:27:24+08 2013-02-22 11:25:15+08 zone1 27 windows-srv1 2013-02-28 16:24:59+08 2013-02-28 15:52:59+08 zone1 27 windows-srv1 2013-02-28 16:56:19+08 2013-02-28 16:40:18+08 zone1 39 windows-srv2 2013-02-21 13:15:53+08 2013-02-21 12:26:04+08 zone1 39 windows-srv2 2013-02-23 13:23:10+08 2013-02-22 10:21:14+08 zone1 39 windows-srv2 2013-02-24 13:35:23+08 2013-02-23 13:33:32+08 zone1 39 windows-srv2 2013-02-26 15:17:25+08 2013-02-25 14:25:51+08 zone1 39 windows-srv2 2013-02-28 18:49:56+08 2013-02-28 15:43:01+08 zone1 13 windows-srv3 2013-02-22 17:23:59+08 2013-02-22 10:19:13+08 zone1 13 windows-srv3 2013-02-28 16:54:27+08 2013-02-28 16:13:48+08
output zone_outages follows e.g.,
zonename duration from_time to_time zone1 00:02:09 2013-02-22 10:24:20+08 2013-02-22 10:26:29+08 zone1 00:02:09 2013-02-22 11:25:15+08 2013-02-22 11:27:24+08 zone1 00:11:11 2013-02-28 16:13:48+08 2013-02-28 16:24:59+08 zone1 00:14:09 2013-02-28 16:40:18+08 2013-02-28 16:54:27+08
note: there entries this
zone2 24 windows-srv12 \n \n
in case zone2 windows-srv12 has never gone down , zone2 availability 100%.
have considered pg 9.2's range type instead of 2 separate timestamp fields?
http://www.postgresql.org/docs/9.2/static/rangetypes.html
something like:
create table availability ( zone_name varchar, nodeid int, nodelabel varchar, during tsrange ); insert availability values (zone1, 3, 'srv1', '[2013-01-01 14:30, 2013-01-01 15:30)');
unless i'm mistaking, you'd able work unions, intersections , such, should make work simpler. there few aggregate functions i'm unfamiliar cater latter, too.
if needed, additionally statements , window functions more complex queries:
http://www.postgresql.org/docs/9.2/static/tutorial-window.html
http://www.postgresql.org/docs/9.2/static/functions-window.html
some testing reveals sum() doesn't work tsrange types.
that being said, sql schema used in follow-up queries:
drop table if exists nodes; create table nodes ( zone int not null, node int not null, uptime tsrange ); -- requires btree_gist extension: -- alter table nodes add exclude using gist (uptime &&, zone =, node =);
the data (slight variation sample):
insert nodes values (1, 1, '[2013-02-20 00:00:00, 2013-02-21 09:40:00)'), (1, 1, '[2013-02-21 09:48:48, 2013-02-21 10:04:56)'), (1, 1, '[2013-02-21 10:09:27, 2013-02-21 10:14:01)'), (1, 1, '[2013-02-22 10:24:20, 2013-02-22 10:26:29)'), (1, 1, '[2013-02-22 11:25:15, 2013-02-22 11:27:24)'), (1, 1, '[2013-02-28 15:52:59, 2013-02-28 16:24:59)'), (1, 1, '[2013-02-28 16:40:18, 2013-02-28 16:56:19)'), (1, 1, '[2013-02-28 17:00:00, infinity)'), (1, 2, '[2013-02-20 00:00:01, 2013-02-21 12:15:00)'), (1, 2, '[2013-02-21 12:26:04, 2013-02-21 13:15:53)'), (1, 2, '[2013-02-22 10:21:14, 2013-02-23 13:23:10)'), (1, 2, '[2013-02-23 13:33:32, 2013-02-24 13:35:23)'), (1, 2, '[2013-02-25 14:25:51, 2013-02-26 15:17:25)'), (1, 2, '[2013-02-28 15:43:01, 2013-02-28 18:49:56)'), (2, 3, '[2013-02-20 00:00:01, 2013-02-22 09:01:00)'), (2, 3, '[2013-02-22 10:19:13, 2013-02-22 17:23:59)'), (2, 3, '[2013-02-28 16:13:48, 2013-02-28 16:54:27)');
raw data in order (for clarity):
select * nodes order zone, uptime, node;
yields:
zone | node | uptime ------+------+----------------------------------------------- 1 | 1 | ["2013-02-20 00:00:00","2013-02-21 09:40:00") 1 | 2 | ["2013-02-20 00:00:01","2013-02-21 12:15:00") 1 | 1 | ["2013-02-21 09:48:48","2013-02-21 10:04:56") 1 | 1 | ["2013-02-21 10:09:27","2013-02-21 10:14:01") 1 | 2 | ["2013-02-21 12:26:04","2013-02-21 13:15:53") 1 | 2 | ["2013-02-22 10:21:14","2013-02-23 13:23:10") 1 | 1 | ["2013-02-22 10:24:20","2013-02-22 10:26:29") 1 | 1 | ["2013-02-22 11:25:15","2013-02-22 11:27:24") 1 | 2 | ["2013-02-23 13:33:32","2013-02-24 13:35:23") 1 | 2 | ["2013-02-25 14:25:51","2013-02-26 15:17:25") 1 | 2 | ["2013-02-28 15:43:01","2013-02-28 18:49:56") 1 | 1 | ["2013-02-28 15:52:59","2013-02-28 16:24:59") 1 | 1 | ["2013-02-28 16:40:18","2013-02-28 16:56:19") 1 | 1 | ["2013-02-28 17:00:00",infinity) 2 | 3 | ["2013-02-20 00:00:01","2013-02-22 09:01:00") 2 | 3 | ["2013-02-22 10:19:13","2013-02-22 17:23:59") 2 | 3 | ["2013-02-28 16:13:48","2013-02-28 16:54:27") (17 rows)
nodes available @ 2013-02-21 09:20:00:
with upnodes ( select zone, node, uptime nodes '2013-02-21 09:20:00'::timestamp <@ uptime ) select * upnodes order zone, uptime, node;
yields:
zone | node | uptime ------+------+----------------------------------------------- 1 | 1 | ["2013-02-20 00:00:00","2013-02-21 09:40:00") 1 | 2 | ["2013-02-20 00:00:01","2013-02-21 12:15:00") 2 | 3 | ["2013-02-20 00:00:01","2013-02-22 09:01:00") (3 rows)
nodes available 2013-02-21 00:00:00 incl 2013-02-24 00:00:00 excl:
with upnodes ( select zone, node, uptime nodes '[2013-02-21 00:00:00, 2013-02-24 00:00:00)'::tsrange && uptime ) select * upnodes order zone, uptime, node;
yields:
zone | node | uptime ------+------+----------------------------------------------- 1 | 1 | ["2013-02-20 00:00:00","2013-02-21 09:40:00") 1 | 2 | ["2013-02-20 00:00:01","2013-02-21 12:15:00") 1 | 1 | ["2013-02-21 09:48:48","2013-02-21 10:04:56") 1 | 1 | ["2013-02-21 10:09:27","2013-02-21 10:14:01") 1 | 2 | ["2013-02-21 12:26:04","2013-02-21 13:15:53") 1 | 2 | ["2013-02-22 10:21:14","2013-02-23 13:23:10") 1 | 1 | ["2013-02-22 10:24:20","2013-02-22 10:26:29") 1 | 1 | ["2013-02-22 11:25:15","2013-02-22 11:27:24") 1 | 2 | ["2013-02-23 13:33:32","2013-02-24 13:35:23") 2 | 3 | ["2013-02-20 00:00:01","2013-02-22 09:01:00") 2 | 3 | ["2013-02-22 10:19:13","2013-02-22 17:23:59") (11 rows)
zones available 2013-02-21 00:00:00 incl 2013-02-24 00:00:00 excl'
with upnodes ( select zone, node, uptime nodes '[2013-02-21 00:00:00, 2013-02-24 00:00:00)'::tsrange && uptime ), upzones_max ( select u1.zone, tsrange(lower(u1.uptime), max(upper(u2.uptime))) uptime upnodes u1 join upnodes u2 on u2.zone = u1.zone , u2.uptime && u1.uptime group u1.zone, lower(u1.uptime) ), upzones ( select u1.zone, tsrange(min(lower(u2.uptime)), upper(u1.uptime)) uptime upzones_max u1 join upzones_max u2 on u2.zone = u1.zone , u2.uptime && u1.uptime group u1.zone, upper(u1.uptime) ) select zone, uptime, upper(uptime) - lower(uptime) duration upzones order zone, uptime;
yields:
zone | uptime | duration ------+-----------------------------------------------+----------------- 1 | ["2013-02-20 00:00:00","2013-02-21 12:15:00") | 1 day 12:15:00 1 | ["2013-02-21 12:26:04","2013-02-21 13:15:53") | 00:49:49 1 | ["2013-02-22 10:21:14","2013-02-23 13:23:10") | 1 day 03:01:56 1 | ["2013-02-23 13:33:32","2013-02-24 13:35:23") | 1 day 00:01:51 2 | ["2013-02-20 00:00:01","2013-02-22 09:01:00") | 2 days 09:00:59 2 | ["2013-02-22 10:19:13","2013-02-22 17:23:59") | 07:04:46 (6 rows)
there might better way write latter query if write (or find) custom aggregate function sums overlapping range types -- non-trivial issue ran isolate adequate group clause; ended settling 2 nested group clauses.
the queries rewritten accommodate current schema, either replacing uptime field expression such tsrange(start_date, end_date), or writing view so.
Comments
Post a Comment