SPREADSHEET PARTITION BY : PARTITION « Analytical Functions « Oracle PL / SQL






SPREADSHEET PARTITION BY

  

SQL>
SQL> create table myTable (
  2   id varchar2(10),
  3   animal varchar2(10),
  4   group_id number,
  5   percent number );

Table created.

SQL>
SQL> set feedback off
SQL>
SQL> insert into myTable values ('a','Cat',1,0.993);
SQL> insert into myTable values ('a','Cat',2,0.93);
SQL> insert into myTable values ('a','Dog',3,0.90);
SQL> insert into myTable values ('b','Cat',1,0.993);
SQL> insert into myTable values ('b','Dog',3,0.90);
SQL> set feedback on
SQL>
SQL> SELECT id, animal, group_id, s FROM myTable
  2   SPREADSHEET PARTITION BY (id)
  3   DIMENSION BY (animal, group_id)
  4   MEASURES (percent s) IGNORE nav (s['Dog',-1]=s['Dog',1], s['Cat',-1]=s['Cat',1]);

ID         ANIMAL       GROUP_ID          S
---------- ---------- ---------- ----------
a          Cat                 1       .993
a          Cat                 2        .93
a          Dog                 3         .9
b          Cat                 1       .993
b          Dog                 3         .9
a          Cat                -1       .993
a          Dog                -1          0
b          Cat                -1       .993
b          Dog                -1          0

9 rows selected.

SQL>
SQL> drop table myTable;

Table dropped.

SQL>
SQL>

   
    
  








Related examples in the same category

1.partition clause
2.Partitioning with PARTITION_BY
3.Use partitioning in the OVER clause of the aggregate-analytical function like this
4.PARTITION BY: divide the groups into subgroups
5.Count(*) over partition
6.Dense_rank over partition by
7.rank and dense_rank over partition
8.count(*) over partition by, order by and range unbounded preceding
9.dense_rank() over partition by, order by
10.Top with partition
11.Partition Window
12.PARTITION BY (JOB title) and right outer join
13.sum salary over PARTITION BY