CODEWITHSUNDEEP
sqloracle-database
Christian
Christian

Reputation: 65

Check rows for monotonically increasing values

I am looking for a way to check, if values in a table are monotonically increasing for a specific subset of rows. The table contains the columns id, name, meas_date and value. For all rows with the same name, the rows should be sorted by meas_date and it needs to be checked if the value column for these sorted rows is monotonically increasing. As as result, I need id and a flag that is 1 if the row with the id breaks the monotoy, 0 otherwise. Example data could be:

id   name   meas_date   value
1    name1  2018/01/01  1
2    name1  2018/01/02  2
3    name2  2018/01/04  2
4    name1  2018/01/03  1
5    name1  2018/01/04  5
6    name2  2018/01/05  4
7    name2  2018/01/06  2
8    name1  2018/01/05  2

Desired result:

id    flag
1     0
2     0
3     0
4     1
5     0
6     0
7     1
8     1

If possible, the result is calculated without any PL/SQL, just pure SQL.

I think I need some kind of inner join of the table with itself, joining by rownumber of sorted subsets, where each subset represents one possible value of the name column. In the joined table, I need to calculate the difference between the value columns of each row with its predecessor and if the difference is zero or below, this needs to result in a flag=1.

I am using Oracle 11g.

Thank you very much,

Christian

Upvotes: 3

Views: 2508

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

If your version of Oracle supports LAG then there is an easy option:

SELECT
    id,
    CASE WHEN value >= LAG(value, 1, value) OVER (PARTITION BY name ORDER BY meas_date)
         THEN 0 ELSE 1 END AS flag
FROM yourTable

Upvotes: 4

DirkNM
DirkNM

Reputation: 2664

You can use a analytic function to achieve this.

Here is a short example:

SELECT id,
       name,
       meas_date,
       value,
       CASE WHEN value > value_prev THEN 0 ELSE 1 END AS flag
  FROM(SELECT id,
              name,
              meas_date,
              value,
              LAG(value, 1, 0) OVER (PARTITION BY name ORDER BY meas_date ) AS value_prev
         FROM your_table
      )

I don't test it, but I hope it helps.

Upvotes: 0

Related Questions