Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / Analytical Functions

 

Displaying which rows are used in the moving average calculations with two other analytical functions

These two functions tell us which rows are used in the calculation of the window function for each row. SQL> SQL> -- create demo table SQL> create table myTable(   2    id           NUMBER(2),   3    value        NUMBER(6,2)   4  )   5  / Table created. SQL> SQL> -- prepare data SQL> insert into myTable(ID,  value)values (1,9); 1 row created. SQL> insert into myTable(ID,  value)values (2,2.11); 1 row created. SQL> insert into myTable(ID,  value)values (3,3.44); 1 row created. SQL> insert into myTable(ID,  value)values (4,-4.21); 1 row created. SQL> insert into myTable(ID,  value)values (5,10); 1 row created. SQL> insert into myTable(ID,  value)values (6,3); 1 row created. SQL> insert into myTable(ID,  value)values (7,-5.88); 1 row created. SQL> insert into myTable(ID,  value)values (8,123.45); 1 row created. SQL> insert into myTable(ID,  value)values (9,98.23); 1 row created. SQL> insert into myTable(ID,  value)values (10,938.23); 1 row created. SQL> insert into myTable(ID,  value)values (11,984.23); 1 row created. SQL> insert into myTable(ID,  value)values (12,198.23); 1 row created. SQL> insert into myTable(ID,  value)values (13,928.87); 1 row created. SQL> insert into myTable(ID,  value)values (14,25.37); 1 row created. SQL> insert into myTable(ID,  value)values (15,918.3); 1 row created. SQL> insert into myTable(ID,  value)values (16,9.23); 1 row created. SQL> insert into myTable(ID,  value)values (17,8.23); 1 row created. SQL> SQL> select * from myTable   2  /         ID      VALUE ---------- ----------          1          9          2       2.11          3       3.44          4      -4.21          5         10          6          3          7      -5.88          8     123.45          9      98.23         10     938.23         11     984.23         12     198.23         13     928.87         14      25.37         15      918.3         16       9.23         17       8.23 17 rows selected. SQL> SQL> COLUMN first FORMAT 999999.99 SQL> COLUMN last LIKE first SQL> SELECT id, value,   2    AVG(value) OVER(ORDER BY id   3    ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) ma,   4    FIRST_VALUE(value) OVER(ORDER BY id   5    ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) first,   6    LAST_VALUE(value) OVER(ORDER BY id   7    ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) last   8  FROM myTable   9  ORDER BY id;         ID      VALUE         MA      FIRST       LAST ---------- ---------- ---------- ---------- ----------          1          9      5.555       9.00       2.11          2       2.11      4.850       9.00       3.44          3       3.44      2.585       9.00      -4.21          4      -4.21      4.068       9.00      10.00          5         10      2.868       2.11       3.00          6          3      1.270       3.44      -5.88          7      -5.88     25.272      -4.21     123.45          8     123.45     45.760      10.00      98.23          9      98.23    231.406       3.00     938.23         10     938.23    427.652      -5.88     984.23         11     984.23    468.474     123.45     198.23         12     198.23    629.558      98.23     928.87         13     928.87    614.986     938.23      25.37         14      25.37    611.000     984.23     918.30         15      918.3    416.000     198.23       9.23         16       9.23    378.000     928.87       8.23         17       8.23    240.283      25.37       8.23 17 rows selected. SQL> SQL> -- clean the table SQL> drop table myTable   2  / Table dropped. SQL>