Обсуждение: Query question
Hi , I have table that represent a switch activity like this : | date | state | | 2005-04-20 17:00:00 | 0 | | 2005-04-20 17:00:15 | 0 | | 2005-04-20 17:00:30 | 1 | | 2005-04-20 17:00:45 | 1 | | 2005-04-20 17:01:00 | 1 | | 2005-04-20 17:01:15 | 0 | | 2005-04-20 17:01:30 | 0 | | 2005-04-20 17:01:45 | 0 | I want to get the date of each states change but i not a sql expert. Can someone advices me Thanks
If you have a row every 15 seconds, the answer is quite easy: <br /><pre>SELECTA1.date FROMactivity A1LEFT JOIN activity A2 ON (A2.date=A1.date-'15 secs'::interval) WHEREA1.state<>A2.state OR A2.state IS NULL ORDER BY 1 </pre><br /><br /> Now if you don't have a row every 15 seconds, the answer is a bit more complex (at least I couldn't thinkof an easier solution): <br /><br /><pre>SELECTmin(TMP2.new_date) FROM( SELECT DISTINCT TMP.new_date, max(TMP.old_date) AS max_old_date FROM ( SELECT A1.id AS new_id, A1.date AS new_date, A1.state AS new_state, A2.id AS old_id, A2.dateAS old_date, A2.state AS old_state FROM activity A1 LEFT JOIN activity A2ON (A2.date<A1.date) ORDER BY A1.date, A2.date DESC ) AS TMP WHERE TMP.old_state<>TMP.new_stateOR TMP.old_state IS NULL GROUP BY TMP.new_date) TMP2 GROUP BYTMP2.max_old_date ORDER BY 1 </pre><br /> I've tested both queries on postgreSQL 8 with the data you provided, and they both work. Anyway try them withlarger datasets before using them in real life <span class="moz-smiley-s3"><span> ;-) </span></span><br /><br /> Hopeit helps.<br /><br /><br /> Stéphane RIFF wrote: <blockquote cite="mid42667F3C.6080303@cerene.fr" type="cite">Hi , <br/><br /> I have table that represent a switch activity like this : <br /><br /> | date | state | <br /> | 2005-04-20 17:00:00 | 0 | <br /> | 2005-04-20 17:00:15 | 0 | <br /> | 2005-04-20 17:00:30 | 1 | <br /> | 2005-04-20 17:00:45 | 1 | <br /> | 2005-04-20 17:01:00 | 1 | <br /> | 2005-04-20 17:01:15| 0 | <br /> | 2005-04-20 17:01:30 | 0 | <br /> | 2005-04-20 17:01:45 | 0 | <br /><br /> I wantto get the date of each states change but i not a sql expert. <br /> Can someone advices me <br /><br /> Thanks <br /><br/><br /> ---------------------------(end of broadcast)--------------------------- <br /> TIP 7: don't forget to increaseyour free space map settings <br /><br /></blockquote><br />
I do some tests with your first query and it seems to works. Thanks a lot for your answer, i will post the final thought later Thanks again bye Franco Bruno Borghesi wrote: > If you have a row every 15 seconds, the answer is quite easy: > >SELECT > A1.date >FROM > activity A1 > LEFT JOIN activity A2 ON (A2.date=A1.date-'15 secs'::interval) >WHERE > A1.state<>A2.state OR A2.state IS NULL >ORDER BY 1 > > > > > Now if you don't have a row every 15 seconds, the answer is a bit more > complex (at least I couldn't think of an easier solution): > >SELECT > min(TMP2.new_date) >FROM > ( > SELECT > DISTINCT > TMP.new_date, > max(TMP.old_date) AS max_old_date > FROM > ( > SELECT > A1.id AS new_id, A1.date AS new_date, A1.state AS new_state, > A2.id AS old_id, A2.date AS old_date, A2.state AS old_state > FROM > activity A1 > LEFT JOIN activity A2 ON (A2.date<A1.date) > ORDER BY > A1.date, A2.date DESC > ) AS TMP > WHERE > TMP.old_state<>TMP.new_state OR TMP.old_state IS NULL > GROUP BY > TMP.new_date > ) TMP2 >GROUP BY > TMP2.max_old_date >ORDER BY 1 > > > > I've tested both queries on postgreSQL 8 with the data you provided, > and they both work. Anyway try them with larger datasets before using > them in real life ;-) > > Hope it helps. > > > Stéphane RIFF wrote: > >> Hi , >> >> I have table that represent a switch activity like this : >> >> | date | state | >> | 2005-04-20 17:00:00 | 0 | >> | 2005-04-20 17:00:15 | 0 | >> | 2005-04-20 17:00:30 | 1 | >> | 2005-04-20 17:00:45 | 1 | >> | 2005-04-20 17:01:00 | 1 | >> | 2005-04-20 17:01:15 | 0 | >> | 2005-04-20 17:01:30 | 0 | >> | 2005-04-20 17:01:45 | 0 | >> >> I want to get the date of each states change but i not a sql expert. >> Can someone advices me >> >> Thanks >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 7: don't forget to increase your free space map settings >> > >------------------------------------------------------------------------ > >No virus found in this incoming message. >Checked by AVG Anti-Virus. >Version: 7.0.308 / Virus Database: 266.10.1 - Release Date: 20/04/2005 > >