Подскажите с ролапом.

печально вопрошающий
Дата: 22.11.2009 11:27:08
Подскажите пожалуйста реализацию.Дано:
SQL> set autotrace off;
SQL> create table t(p1 number);
Table created.
SQL> set time off;
SQL> set timing off;
SQL> insert into t select 1 from dual connect by level<3;
2 rows created.
SQL> insert into t select 4 from dual connect by level<13;
12 rows created.
SQL> insert into t select 6 from dual connect by level<10;
9 rows created.
SQL> commit;
Commit complete.
SQL> select
  2    case when grouping(p1) = 1 then 1000  else p1 end p1,sum(t1)t1
  3    from
  4   (select p1 p1,
  5    count(case when p1 > 0 then 1 end)t1
  6     from t
  7    group by p1)
  8  group by rollup(p1);

        P1         T1
---------- ----------
         1          2
         4         12
         6          9
      1000         23

SQL> exit
Как подправить rollup чтобы получить:
        P1         T1
---------- ----------
         1          2
         2          0
         3          0
         4         12
         5          0
         6          9
      1000         23

Надеюсь изложил верно.
xymbo
Дата: 22.11.2009 11:52:22
печально вопрошающий,

Надо использовать pivot (где содержится вся последовательность чисел), и можно без подзапроса - сразу группировать.
SELECT 
  DECODE(grouping(pivot.p1), 1, 1000, pivot.p1) p1,
  COUNT(t.p1) t1
FROM t, (SELECT LEVEL p1 FROM dual CONNECT BY LEVEL <= (SELECT MAX(p1) FROM t)) pivot
WHERE pivot.p1 = t.p1(+)
GROUP BY ROLLUP(pivot.p1)
ORDER BY pivot.p1    
печально вопрошающий
Дата: 22.11.2009 12:04:02
xymbo, ой спасибо дорогой:)
Печально вопрошающий
Дата: 22.11.2009 13:01:15
xymbo, а подскажите мне еще пожалуста, если надо включить условие типа:
SELECT 
  DECODE(grouping(pivot.p1), 1, 1000, pivot.p1) p1,
  COUNT(t.p1) t1
FROM t [b]where p2 between to_date and to_date[/b],
печально вопрошающий
Дата: 22.11.2009 13:03:23
Печально вопрошающий
xymbo, а подскажите мне еще пожалуста, если надо включить условие типа:
SELECT 
  DECODE(grouping(pivot.p1), 1, 1000, pivot.p1) p1,
  COUNT(t.p1) t1
FROM t where p2 between to_date and to_date,

то есть есть колонка p2 или любая другая, и необходимо отсечь по диапазону. Как быть? Если вставить после WHERE pivot.p1 = t.p1(+) то 0 не вернет.
xymbo
Дата: 22.11.2009 13:17:30
печально вопрошающий,

Не совсем понял, в чем проблема. Что значит 0 вернет? Может это Вам подойдет:
SQL> SELECT * FROM t;
  2  /

        P1 P2
---------- -----------
         1 23.11.2009
         1 24.11.2009
         4 25.11.2009
         4 26.11.2009
         4 27.11.2009
         4 28.11.2009
         4 29.11.2009
         4 30.11.2009
         4 01.12.2009
         4 02.12.2009
         4 03.12.2009
         4 04.12.2009
         4 05.12.2009
         4 06.12.2009
         6 07.12.2009
         6 08.12.2009
         6 09.12.2009
         6 10.12.2009
         6 11.12.2009
         6 12.12.2009

        P1 P2
---------- -----------
         6 13.12.2009
         6 14.12.2009
         6 15.12.2009

23 rows selected
SQL> SELECT
  2    DECODE(grouping(pivot.p1), 1, 1000, pivot.p1) p1,
  3    NVL(SUM(t.t1), 0)
  4  FROM (
  5    SELECT
  6      p1,
  7      COUNT(t.p1) t1
  8    FROM t
  9    WHERE p2 between to_date('23.11.2009', 'dd.mm.yyyy') and to_date('11.12.2009', 'dd.mm.yyyy')
 10    GROUP BY t.p1) t,
 11    (SELECT LEVEL p1 FROM dual CONNECT BY LEVEL <= (SELECT MAX(p1) FROM t)) pivot
 12  WHERE pivot.p1 = t.p1(+)
 13  GROUP BY ROLLUP(pivot.p1)
 14  ORDER BY pivot.p1
 15  /

        P1 NVL(SUM(T.T1),0)
---------- ----------------
         1                2
         2                0
         3                0
         4               12
         5                0
         6                5
      1000               19

7 rows selected
-2-
Дата: 22.11.2009 13:38:59
Сложно сказать, насколько эффективнее считать max в model, нежели в join с подзапросом, стоит сравнить с таким вариантом:
select
    p1, t1
    from
   (select p1 p1,
    count(case when p1 > 0 then 1 end)t1
     from t
    group by p1)
    model
    return all rows
    dimension by (p1)
    measures (t1, p1 pn)
    rules 
    iterate (999) until (iteration_number+1 >= pn[1000]) 
    (
       pn[1000] = max(pn)[any],
       t1[1000] = sum(t1)[p1 < 1000],
       t1[iteration_number+1] = nvl(t1[cv(p1)], 0)
    )
order by p1
    ;
печально вопрошающий
Дата: 22.11.2009 19:09:36
xymbo, спасибо, буду пробовать. Доберусь до работы, сформулирую более точно. SY спасибо, вариант тоже интересен. Попробую.