Re: table with sparce data

"Mikito Harakiri" <mikharakiri_at_ywho.com> wrote in message news:<9vt1b.18$3j7.31_at_news.oracle.com>...
> I have data like this
>
> Name Value
> ---- ------
> a001 0
> a002 0
> a003 0
> a004 0
> a005 2
> a006 0
> a007 0
> a010 0
> a011 3
> a017 0
> ...
>
> and I want a report where sparcely populated frequency is compressed like
> this
>
> Min Value Frequency
> Name
> ---- ------ -----------
> a001 0 4
> a023 5 1
> a006 0 3
> a011 3 1
> ...
>
> Any simple analytical solution?

Non analytical solution, input data:

create view commissions as
select rownum rn, ename, comm from (
  select ename, case when comm is null then 0 else comm end comm   from emp
  order by ename
)

select * from commissions

        RN ENAME COMM
---------- ---------- ----------

         1 ADAMS               0
         2 ALLEN             300
         3 BLAKE               0
         4 CLARK               0
         5 FORD                0
         6 JAMES               0
         7 JONES               0
         8 KING                0
         9 MARTIN           1400
        10 MILLER              0
        11 SCOTT               0
        12 SMITH               0
        13 TURNER              0
        14 WARD              500

create view intervals as
select c1.rn head, c2.rn tail, c1.comm from commissions c1, commissions c2
where c1.comm = c2.comm and c1.rn <= c2.rn and not exists( select 1 from commissions cc where c1.comm <> cc.comm and cc.rn between c1.rn and c2.rn)

select head, tail, comm from intervals i where not exists (select 1 from intervals ii where i.comm=ii.comm and ii.head<=i.head and ii.tail>i.tail)
and not exists (select 1 from intervals ii where i.comm=ii.comm and ii.head<i.head and ii.tail>=i.tail)
order by head

      HEAD TAIL COMM
---------- ---------- ----------

         1          1          0
         2          2        300
         3          8          0
         9          9       1400
        10         13          0
        14         14        500