CODEWITHSUNDEEP
sqloracle-database
nixland
nixland

Reputation: 43

Oracle query to get how many days a record has certain status before today

I need query to get how many days last time a meter has been online. For example :

METER  PDATE  STATUS
ABC    1-Jan  off
ABC    2-Jan  on
ABC    3-Jan  on
ABC    4-Jan  on
ABC    5-Jan  off
ABC    6-Jan  off
ABC    7-Jan  on
ABC    8-Jan  on
ABC    9-Jan  off

If today is Jan 8th than the query will return : 3 (Jan 2-4).

If today is Jan 9th than the query will return : 2 (Jan 7-8).

My query below is working OK, but it takes 40-50 seconds if applied to the real table which has 5 millions records. Please let me know if there are faster ways to get such data.

with last_off as
(  
  select meter,pdate lastoff from
  (
      select meter, pdate, 
             row_number() over (partition by meter order by pdate desc) rnum 
      from mytable 
      where status = 'off'
  )
  where rnum=1    
),
last_on as
(
    select meter, laston from
    (
        select a.meter, a.pdate laston, b.lastoff, 
               row_number() over (partition by a.meter order by a.pdate desc) rnum
        from mytable a, last_off b 
        where status = 'on'
          and a.meter=b.meter(+) and a.pdate < b.lastoff
    )
    where rnum=1
),
days_on as
(
    select meter, laston-pdate dayson from
    (
        select a.meter, a.pdate, b.laston, 
               row_number() over (partition by a.meter order by a.pdate desc) rnum
        from mytable a, last_on b 
        where status = 'off'
          and a.meter=b.meter(+) and a.pdate < b.laston
    )
    where rnum=1
)
select meter, dayson
from days_on

Upvotes: 2

Views: 120

Answers (3)

Ponder Stibbons
Ponder Stibbons

Reputation: 14858

with t as (
  select meter, pdate, status, 
      case when lag(status)  over (partition by meter order by pdate) 
                < status then 1 end chg1,
      case when lead(status) over (partition by meter order by pdate) 
                < status then 1 end chg2
    from mytable),
d2 as (
  select meter, max(pdate) do2 
    from t where chg2 = 1 and pdate < date '2015-01-09' group by meter),
d1 as (
  select meter, max(pdate) do1 from t join d2 using (meter) 
    where chg1 = 1 and pdate < d2.do2 group by meter)
select meter, do2-do1+1 days_on from d1 join d2 using (meter)

SQLFiddle demo

Change value in line containing date '2015-01-09' to whatever value you want, probably trunc(sysdate). Also change last line to: 

select meter, count(1) cnt from t join d1 using (meter) join d2 using (meter) 
  where pdate between do1 and do2 group by (meter)

if you want to count rows from main table instead of simple subtracting days.

Upvotes: 1

Srini V
Srini V

Reputation: 11375

Data Setup:

CREATE TABLE my_table
    (METER varchar2(3), PDATE date, STATUS varchar2(3))
;

INSERT ALL 
    INTO my_table (METER, PDATE, STATUS)
         VALUES ('ABC', '01-Jan-2001', 'off')
    INTO my_table (METER, PDATE, STATUS)
         VALUES ('ABC', '02-Jan-2001', 'on')
    INTO my_table (METER, PDATE, STATUS)
         VALUES ('ABC', '03-Jan-2001', 'on')
    INTO my_table (METER, PDATE, STATUS)
         VALUES ('ABC', '04-Jan-2001', 'on')
    INTO my_table (METER, PDATE, STATUS)
         VALUES ('ABC', '05-Jan-2001', 'off')
    INTO my_table (METER, PDATE, STATUS)
         VALUES ('ABC', '06-Jan-2001', 'off')
    INTO my_table (METER, PDATE, STATUS)
         VALUES ('ABC', '07-Jan-2001', 'on')
    INTO my_table (METER, PDATE, STATUS)
         VALUES ('ABC', '08-Jan-2001', 'on')
    INTO my_table (METER, PDATE, STATUS)
         VALUES ('ABC', '09-Jan-2001', 'off')
SELECT * FROM dual
;

You can make use of Analytical function called lag

Query:

SELECT meter, 
       pdate, 
       pdate - Min (pdate) 
                 over( 
                   ORDER BY grp DESC) + 1 AS daysoff 
FROM   (SELECT meter, 
               pdate, 
               status, 
               Max(grp) 
                 over( 
                   ORDER BY pdate) grp 
        FROM   (SELECT meter, 
                       pdate, 
                       status, 
                       CASE 
                         WHEN Lag(status) 
                                over ( 
                                  ORDER BY pdate) != ( status ) THEN 
                         Row_number() 
                         over ( 
                           ORDER BY pdate) 
                         WHEN Row_number() 
                                over ( 
                                  ORDER BY pdate) = 1 THEN 1 
                       END grp 
                FROM   my_table)) 
WHERE  status = 'on' 
ORDER  BY pdate ASC;

Results:

METER   PDATE   DAYSOFF
ABC January, 02 2001 00:00:00   1
ABC January, 03 2001 00:00:00   2
ABC January, 04 2001 00:00:00   3
ABC January, 07 2001 00:00:00   1
ABC January, 08 2001 00:00:00   2

Upvotes: 0

Marc
Marc

Reputation: 11633

This would get the list of meters that have been on, and how many days they've been on.

(caveat: I don't have an Oracle instance to try this on as I'm writing it)

select maxon.METER,
       (maxon.maxdate - maxoff.maxdate) as dayson
  from
   (select METER,
           Max(PDATE) maxdate
      from MY_TABLE
     where PSTATUS = 'on'
     group by meter) as maxon,
   (select METER,
           Max(PDATE) maxdate
      from MY_TABLE
     where PSTATUS = 'off'
     group by meter) as maxoff
where maxon.meter = maxoff.meter
  and maxon.maxdate > maxoff.maxdate;

You could union a second query to get the meters that have been off, or just be more clever in how you interpret the subtraction result (i.e., do a CASE statement such that if the result is negative, it's off and if positive, it's on) http://www.techonthenet.com/oracle/functions/case.php

Upvotes: 0

Related Questions