## MODEL Clause

### Aggregate and Analytic Expressions

In this tutorial we continue exploring the RULES part of the MODEL clause with a focus on aggregate and analytic expressions. (See Model Expressions for full syntax.)

Aggregate and analytic functions can be used on the right hand side of a rule to process multiple cell values. They can not appear on the left hand side of rules. Both types of functions can take constants, bind variables, measure columns, or expressions involving them as arguments. Beyond that, aggregate functions can also specify a cell reference to restrict which cells they operate on. For example, in this snippet

```select
key ,
num_val ,
m_1
from
t
model
dimension by ( key )
measures     ( num_val, 0 m_1 )
rules        ( m_1 = SUM( NUM_VAL )[ KEY IN (1,2,3) ] )
order by
key
;
```
```   KEY NUM_VAL        M_1
------ ------- ----------
1     100          0
2     200          0
3     300        600
4                  0
5     300          0
6     100          0
7     100          0
8                  0
9     200          0
10     800          0
```

the aggregate function SUM takes the measure column NUM_VAL as an argument. The dimension reference `[ KEY IN (1,2,3) ]` restricts the function to three specific cell values.

Here is an analytic example. Note that analytic functions are not qualified with dimension references.

```select
key ,
m_1
from
t
model
dimension by ( key )
measures     ( 0 m_1 )
rules        ( m_1[any] = ROW_NUMBER() OVER ( ORDER BY KEY DESC ) )
order by
key
;
```
```   KEY        M_1
------ ----------
1         10
2          9
3          8
4          7
5          6
6          5
7          4
8          3
9          2
10          1
```

The following two snippets perform similar logic. One uses analytic functions and the other aggregate functions. By using the `CV()` and `ANY` dimension references with the aggregate functions we see that they can produce the same results analytic functions do.

Aggregate
Analytic
```break on group_1 skip 1 duplicates

select
group_1 ,
num_val ,
m_1 ,
m_2
from
t
model
dimension by ( group_1, group_2 )
measures
(
num_val,
0 m_1,
0 m_2
)
rules
(
m_1[any,any] =
MIN( NUM_VAL )
[ CV(), ANY ] ,
m_2[any,any] =
MAX( NUM_VAL )
[ CV(), ANY ]
)
order by
group_1 ,
group_2
;
```
```GROUP_1    NUM_VAL        M_1        M_2
---------- ------- ---------- ----------
A              100        100        300
A              200        100        300
A              300        100        300

B                         100        300
B              300        100        300
B              100        100        300

C              100        100        100
C                         100        100

200        200        800
800        200        800
```
```break on group_1 skip 1 duplicates

select
group_1 ,
num_val ,
m_1 ,
m_2
from
t
model
dimension by ( group_1, group_2 )
measures
(
num_val,
0 m_1,
0 m_2
)
rules
(
m_1[any,any] =
MIN(NUM_VAL)
OVER (PARTITION BY (GROUP_1)),
m_2[any,any] =
MAX(NUM_VAL)
OVER (PARTITION BY (GROUP_1))
)
order by
group_1 ,
group_2
;
```
```GROUP_1    NUM_VAL        M_1        M_2
---------- ------- ---------- ----------
A              100        100        300
A              200        100        300
A              300        100        300

B                         100        300
B              300        100        300
B              100        100        300

C              100        100        100
C                         100        100

200        200        800
800        200        800
```

#### PARTITION BY and Aggregate Functions

To see how PARTITION BY affects aggregate functions, consider the following snippets. The one on the left does not use PARTITION BY. The one on the right does. As you can see, using PARTITION BY produces different values under M_1 than not using it.

Without PARTITION BY
With PARTITION BY
```break on group_1 skip 1 duplicates

select
group_1 ,
group_2 ,
num_val ,
m_1
from
t
model
dimension by ( group_1, group_2 )
measures     ( num_val, 0 m_1 )
rules
( m_1[any, any] =
SUM(NUM_VAL)[ANY, ANY] )
order by
group_1 ,
group_2 ,
num_val
;
```
```GROUP_1    GROUP_2    NUM_VAL        M_1
---------- ---------- ------- ----------
A          a1             100       2100
A          a2             200       2100
A          a3             300       2100

B          a1                       2100
B          a2             300       2100
B          a3             100       2100

C          a1             100       2100
C          a2                       2100

a1             200       2100
a2             800       2100
```
```break on group_1 skip 1 duplicates

select
group_1 ,
group_2 ,
num_val ,
m_1
from
t
model
PARTITION BY ( GROUP_1 )
dimension by ( group_2 )
measures     ( num_val , 0 m_1 )
rules
( m_1[any] = SUM(NUM_VAL)[ANY] )
order by
group_1 ,
group_2 ,
num_val
;
```
```GROUP_1    GROUP_2    NUM_VAL        M_1
---------- ---------- ------- ----------
A          a1             100        600
A          a2             200        600
A          a3             300        600

B          a1                        400
B          a2             300        400
B          a3             100        400

C          a1             100        100
C          a2                        100

a1             200       1000
a2             800       1000
```

#### PARTITION BY and Analytic Functions

To see how PARTITION BY affects analytic functions, consider the following snippets. The one on the left does not use PARTITION BY. The one on the right does. Again, using PARTITION BY produces different values under M_1 than not using it.

Without PARTITION BY
With PARTITION BY
```break on group_1 skip 1 duplicates

select
group_1 ,
group_2 ,
m_1
from
t
model
--
dimension by ( group_1, group_2 )
measures     ( 0 m_1 )
rules
(
m_1[any, any] =
ROW_NUMBER()
OVER(ORDER BY GROUP_1,GROUP_2)
)
order by
group_1 ,
group_2
;
```
```GROUP_1    GROUP_2           M_1
---------- ---------- ----------
A          a1                  1
A          a2                  2
A          a3                  3

B          a1                  4
B          a2                  5
B          a3                  6

C          a1                  7
C          a2                  8

a1                  9
a2                 10
```
```break on group_1 skip 1 duplicates

select
group_1 ,
group_2 ,
m_1
from
t
model
PARTITION BY ( GROUP_1 )
dimension by ( group_2 )
measures     ( 0 m_1 )
rules
(
m_1[any] =
ROW_NUMBER()
OVER(ORDER BY GROUP_1, GROUP_2)
)
order by
group_1 ,
group_2
;
```
```GROUP_1    GROUP_2           M_1
---------- ---------- ----------
A          a1                  1
A          a2                  2
A          a3                  3

B          a1                  1
B          a2                  2
B          a3                  3

C          a1                  1
C          a2                  2

a1                  1
a2                  2
```

#### Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

• [url=http://www.sqlsnippets.com/en/topic-11672.html]SQL Snippets: MODEL Clause - Aggregate and Analytic Expressions[/url]