Re: table with sparce data

From: Mikito Harakiri <mikharakiri_at_ywho.com>
Date: Fri, 22 Aug 2003 17:59:16 -0700
Message-ID: <O7z1b.26$3j7.138@news.oracle.com>

"Vadim Tropashko" <vadimtro_at_yahoo.com> wrote in message news:22d2e427.0308221648.aea080a_at_posting.google.com...
> "Mikito Harakiri" <mikharakiri_at_ywho.com> wrote in message
news:<9vt1b.18$3j7.31_at_news.oracle.com>...
> > Any simple analytical solution?
>
> Here is analytical solution:
>
> SQL> select rn, comm from (
> 2 select ename, comm
> 3 ,count(1) over (order by ename) rn
> 4 ,lead(comm) over (order by ename) lead
> 5 from commissions
> 6 order by ename
> 7 ) where comm-lead <> 0 or comm-lead is null
> 8 ;
>
> RN COMM
> ---------- ----------
> 1 0
> 2 300
> 8 0
> 9 1400
> 13 0
> 14 500
>
> Then, all you need to do is subtract RN-LAG(RN) to get the count.

Cool!

select comm,

       rn-lag(rn) over (order by rn) count from (
select ename, comm

           ,count(1) over (order by ename) rn
           ,lead(comm) over (order by ename) lead

    from commissions
order by ename
) where comm-lead <> 0 or comm-lead is null

      COMM COUNT
---------- ----------

         0
       300          1
         0          6
      1400          1
         0          4
       500          1

fixing NULL however
"lag(rn)" -> "case when lag(rn) is null then 0 else lag(rn) end"

doesn't work... Received on Fri Aug 22 2003 - 19:59:16 CDT