Re: add dummy rows

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Fri, 24 May 2013 14:41:52 +0400
Message-ID: <CAOVevU40WUjrvm34uUejtML7YWeXR=Wq-7i=UO5p+TQRpCjaug_at_mail.gmail.com>

Example:
http://pastebin.com/KL5VCxsr
WITH t AS (

   SELECT 1 c1, 100 C2, TO_DATE('24-MAY-13','dd-mon-rr') MYDATE FROM dual UNION ALL

   SELECT 2 c1, 200 C2, TO_DATE('24-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL

   SELECT 2 c1, 201 C2, TO_DATE('24-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL

   SELECT 3 c1, 300 C2, TO_DATE('24-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL

   SELECT 4 c1, 400 C2, TO_DATE('24-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL

   SELECT 6 c1, 600 C2, TO_DATE('24-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL

   SELECT 1 c1, 150 C2, TO_DATE('25-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL

   SELECT 2 c1, 250 C2, TO_DATE('25-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL

   SELECT 3 c1, 350 C2, TO_DATE('25-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL

   SELECT 4 c1, 450 C2, TO_DATE('25-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL

   SELECT 5 c1, 550 C2, TO_DATE('25-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL

   SELECT 7 c1, 750 C2, TO_DATE('25-MAY-13','dd-mon-yy') MYDATE FROM dual )
SELECT mydate,c1_aux,c1,c2
FROM

      (SELECT level-1 c1_aux FROM dual CONNECT BY level<24) gen
      left join t
      PARTITION BY (mydate)
      ON c1=c1_aux

ORDER BY mydate,c1_aux;

On Fri, May 24, 2013 at 1:47 PM, Ls Cheng <exriscer_at_gmail.com> wrote:

> Hi
> The example I posted was with test data. The real thing is that we have
> data for each hour in a day (24 rows) but when Daylight Savings Time kicks
> in we will have 25 rows because the hour 2 is accounted twice in that
> specific day.
>
> So basically I have to deal with 0 to 24 rows (this is because some hours
> possible has no data) in normal days and 0 to 25 rows in DST days, to write
> a generic query to deal with this data I have thought of generate always 25
> rows no matter day and hour, even those hours with no data.
>
> The data can look like
>
> *DATE HOUR VALUE
> ----------- ------ ------
> 24-MAR-2013 0 0
> 24-MAR-2013 1 100
> 24-MAR-2013 2 200
> 24-MAR-2013 3 300
> 24-MAR-2013 4 400
> 24-MAR-2013 5 500
> 24-MAR-2013 7 700
> 24-MAR-2013 9 900
> 24-MAR-2013 13 1300
> 24-MAR-2013 15 1500
> 24-MAR-2013 16 1600
> 24-MAR-2013 17 1700
> 24-MAR-2013 23 2300
> 30-MAR-2013 0 0
> 30-MAR-2013 1 100
> 30-MAR-2013 2 200
> 30-MAR-2013 2 200
> 30-MAR-2013 4 400
> 30-MAR-2013 5 500
> 30-MAR-2013 8 800
> 30-MAR-2013 11 1100
> 30-MAR-2013 14 1400
> 30-MAR-2013 15 1500
> 30-MAR-2013 16 1600
> 30-MAR-2013 20 2000
> 30-MAR-2013 23 2300*
>
> 30 of March was the DST day this year
>
> Thanks
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Best regards,
Sayan Malakshinov
Senior performance tuning engineer
PSBank
http://orasql.org


--
http://www.freelists.org/webpage/oracle-l

Received on Fri May 24 2013 - 12:41:52 CEST