## Cumulative values

Comments enabled. I *really* need your comment

From **Stack Overflow**:

There is a table that stores signal statuses that come from different devices.

SS1andSS2signals are inserted to table in random times- If either of
SS1andSS2signal statuses isup, then the resulting signal should beup- If both
SS1andSS2signal statuses aredown, then resulting signal should bedownI want to prepare a query that shows the result signal status changes according to

SS1andSS2signals

Each record deals with only one signal type here: either **SS1** or **SS2**. To obtain the signal statuses we should query the *cumulative values* of previous records.

If a record describes a change in **SS2**, we should query for the most recent change to **SS1** that had been recorded so far to obtain the **SS1**'s current status.

In systems other than **Oracle**, the previous value of a signal could be easily queried using subselects with `TOP`

/ `LIMIT`

clauses. But **Oracle** does not support correlated queries nested more than one level deep, and limiting a subquery result to a single record (which is required by a subquery) requires it (**ORDER BY** should be nested). This makes constructing such a subquery in **Oracle** quite a pain.

However, in **Oracle**, these things can be queries using analytics functions much more efficiently.

Let's create a sample table:

**Table creation details**

BEGIN DBMS_RANDOM.seed(20100120); END; / CREATE TABLE t_signal ( id NOT NULL, signal NOT NULL, status NOT NULL, ts NOT NULL ) AS SELECT level, CASE WHEN DBMS_RANDOM.value < 0.5 THEN 'SS1' ELSE 'SS2' END, ROUND(DBMS_RANDOM.value), TO_DATE('20.01.2010', 'dd.mm.yyyy') - level / 86400 FROM dual CONNECT BY level <= 1000000 / ALTER TABLE t_signal ADD CONSTRAINT pk_signal_id PRIMARY KEY (id) / CREATE INDEX ix_signal_ts_id ON t_signal (ts, id) / [/sourcecode] </div> This table contains <strong>1,000,000</strong> records filled with random states of random signals once per second. Each record describes a state of a certain signal at a given moment of time. To find out the state of another signal at that moment of time we need to know the state held by the latest record for that signal. To find out that state we can employ analytical function, <code>LAST_VALUE</code>. With a default <code>RANGE</code> (that is <code>BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW</code>), this function just gives the value of the expression held by the current row, and, therefore, is quite useless: it gives the same result as a plain expression would give, without any functions. If <code>col</code> is unique and defines the order of the rows, then <code>LAST_VALUE(expression) OVER (ORDER BY col)</code> is just a quite expensive synonym for <code>expression</code>. However, this function's behavior can be changed by adding <code>IGNORE NULLS</code> clause. This clause makes the function return the last value so far which is not a <code>NULL</code>. Now, returning the cumulative value of any signal becomes quite simple. We should just just make two expressions which substitute <code>NULL</code> instead of the signal status for the <q>wrong</q> signals. The <code>LAST_VALUE (IGNORE NULLS)</code> over these expressions will persist until rewritten by the new states of their corresponding signals. Let's check it: SELECT * FROM ( SELECT /*+ FIRST_ROWS */ s.*, DECODE(signal, 'SS1', status, NULL) AS exp1, DECODE(signal, 'SS2', status, NULL) AS exp2, LAST_VALUE(DECODE(signal, 'SS1', status, NULL) IGNORE NULLS) OVER (ORDER BY ts, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ss1, LAST_VALUE(DECODE(signal, 'SS2', status, NULL) IGNORE NULLS) OVER (ORDER BY ts, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ss2 FROM t_signal s ORDER BY ts, id ) s2 WHERE rownum <= 15 [/sourcecode] <div class="terminal"> <table class="terminal"> <tr> <th>ID</th><th>SIGNAL</th><th>STATUS</th><th>TS</th><th>EXP1</th><th>EXP2</th><th>SS1</th><th>SS2</th></tr> <tr><td class="double_precision">1000000</td><td class="char">SS2</td><td class="double_precision">1</td><td class="date">08.01.2010 10:13:20</td><td class="double_precision"></td><td class="double_precision">1</td><td class="double_precision"></td><td class="double_precision">1</td></tr> <tr><td class="double_precision">999999</td><td class="char">SS2</td><td class="double_precision">0</td><td class="date">08.01.2010 10:13:21</td><td class="double_precision"></td><td class="double_precision">0</td><td class="double_precision"></td><td class="double_precision">0</td></tr> <tr><td class="double_precision">999998</td><td class="char">SS1</td><td class="double_precision">0</td><td class="date">08.01.2010 10:13:22</td><td class="double_precision">0</td><td class="double_precision"></td><td class="double_precision">0</td><td class="double_precision">0</td></tr> <tr><td class="double_precision">999997</td><td class="char">SS2</td><td class="double_precision">1</td><td class="date">08.01.2010 10:13:23</td><td class="double_precision"></td><td class="double_precision">1</td><td class="double_precision">0</td><td class="double_precision">1</td></tr> <tr><td class="double_precision">999996</td><td class="char">SS1</td><td class="double_precision">1</td><td class="date">08.01.2010 10:13:24</td><td class="double_precision">1</td><td class="double_precision"></td><td class="double_precision">1</td><td class="double_precision">1</td></tr> <tr><td class="double_precision">999995</td><td class="char">SS2</td><td class="double_precision">1</td><td class="date">08.01.2010 10:13:25</td><td class="double_precision"></td><td class="double_precision">1</td><td class="double_precision">1</td><td class="double_precision">1</td></tr> <tr><td class="double_precision">999994</td><td class="char">SS1</td><td class="double_precision">0</td><td class="date">08.01.2010 10:13:26</td><td class="double_precision">0</td><td class="double_precision"></td><td class="double_precision">0</td><td class="double_precision">1</td></tr> <tr><td class="double_precision">999993</td><td class="char">SS1</td><td class="double_precision">1</td><td class="date">08.01.2010 10:13:27</td><td class="double_precision">1</td><td class="double_precision"></td><td class="double_precision">1</td><td class="double_precision">1</td></tr> <tr><td class="double_precision">999992</td><td class="char">SS1</td><td class="double_precision">0</td><td class="date">08.01.2010 10:13:28</td><td class="double_precision">0</td><td class="double_precision"></td><td class="double_precision">0</td><td class="double_precision">1</td></tr> <tr><td class="double_precision">999991</td><td class="char">SS2</td><td class="double_precision">1</td><td class="date">08.01.2010 10:13:29</td><td class="double_precision"></td><td class="double_precision">1</td><td class="double_precision">0</td><td class="double_precision">1</td></tr> <tr><td class="double_precision">999990</td><td class="char">SS1</td><td class="double_precision">0</td><td class="date">08.01.2010 10:13:30</td><td class="double_precision">0</td><td class="double_precision"></td><td class="double_precision">0</td><td class="double_precision">1</td></tr> <tr><td class="double_precision">999989</td><td class="char">SS2</td><td class="double_precision">0</td><td class="date">08.01.2010 10:13:31</td><td class="double_precision"></td><td class="double_precision">0</td><td class="double_precision">0</td><td class="double_precision">0</td></tr> <tr><td class="double_precision">999988</td><td class="char">SS1</td><td class="double_precision">0</td><td class="date">08.01.2010 10:13:32</td><td class="double_precision">0</td><td class="double_precision"></td><td class="double_precision">0</td><td class="double_precision">0</td></tr> <tr><td class="double_precision">999987</td><td class="char">SS2</td><td class="double_precision">0</td><td class="date">08.01.2010 10:13:33</td><td class="double_precision"></td><td class="double_precision">0</td><td class="double_precision">0</td><td class="double_precision">0</td></tr> <tr><td class="double_precision">999986</td><td class="char">SS1</td><td class="double_precision">0</td><td class="date">08.01.2010 10:13:34</td><td class="double_precision">0</td><td class="double_precision"></td><td class="double_precision">0</td><td class="double_precision">0</td></tr> <tr class="statusbar"><td colspan="100">15 rows fetched in 0.0012s (0.0007s)</td></tr> </table> </div> <pre> SELECT STATEMENT COUNT STOPKEY VIEW WINDOW NOSORT TABLE ACCESS BY INDEX ROWID, 20100120_signal.T_SIGNAL INDEX FULL SCAN, 20100120_signal.IX_SIGNAL_TS_ID </pre> In the resultset above, <code>EXP1</code> and <code>EXP2</code> show the changes in the signal states of <code>SS1</code> and <code>SS2</code>, with <code>NULL</code>'s if the current record does not describe a change in the appropriate signals. <code>LAST_VALUE (IGNORE NULLS)</code> over these expressions show their cumulative values. These values can be used to calculate the resulting signal state. Note that using <code>ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW</code> made <strong>Oracle</strong> to use <code>WINDOW NOSORT</code> and this query is instant, since the <code>LAST_VALUE</code> can be buffered. To calculate the resulting signal, we should just return the <code>GREATEST</code> of <code>ss1</code> and <code>ss2</code> (returned by <code>LAST_VALUE</code>): SELECT SUM(GREATEST(ss1, ss2)) FROM ( SELECT s.*, DECODE(signal, 'SS1', status, NULL) AS exp1, DECODE(signal, 'SS2', status, NULL) AS exp2, LAST_VALUE(DECODE(signal, 'SS1', status, NULL) IGNORE NULLS) OVER (ORDER BY ts, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ss1, LAST_VALUE(DECODE(signal, 'SS2', status, NULL) IGNORE NULLS) OVER (ORDER BY ts, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ss2 FROM t_signal s ORDER BY ts, id ) s2

SUM(GREATEST(SS1,SS2)) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|

750450 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

1 row fetched in 0.0003s (3.0781s) |

SELECT STATEMENT SORT AGGREGATE VIEW WINDOW SORT TABLE ACCESS FULL, 20100120_signal.T_SIGNAL

For the sake of brevity, we select the `SUM`

of the resulting signal states which gives us the number of records with resulting signal up. As it should be, this number roughly amounts to **75%** of the total number of records.

The query uses a single sort (which in case of the whole table is faster than traversing the index), buffers the results and completes in **3 seconds** which is almost as fast as a plain query with `ORDER BY`

over the same dataset would complete.

Written by Quassnoi

January 20th, 2010 at 11:00 pm

Posted in Oracle

### Leave a Reply

### Subscribe

### Subscribe by email

### Contacts

### Should I?

Yes. Feel free to ask questions and write me. An interesting question is a pleasure to answer, and I really enjoy receiving feedback

### Recent articles

- Happy New Year: GPT in 500 lines of SQL
- Happy New Year: solving the Rubik’s Cube in SQL
- A good first word for Wordle
- Happy New Year: quantum computer emulator in SQL
- Happy New Year: 3D picture of the coronavirus in SQL

### Calendar

### Archives

- December 2023
- December 2022
- January 2022
- December 2021
- December 2020
- December 2019
- December 2018
- December 2017
- December 2016
- December 2015
- December 2014
- July 2014
- December 2013
- October 2013
- September 2013
- August 2013
- July 2013
- June 2013
- May 2013
- March 2013
- January 2013
- December 2012
- December 2011
- June 2011
- April 2011
- March 2011
- February 2011
- December 2010
- November 2010
- October 2010
- September 2010
- August 2010
- July 2010
- June 2010
- May 2010
- April 2010
- March 2010
- February 2010
- January 2010
- December 2009
- November 2009
- October 2009
- September 2009
- August 2009
- July 2009
- June 2009
- May 2009
- April 2009
- March 2009
- February 2009