CODEWITHSUNDEEP
sqlpostgresql
iv67
iv67

Reputation: 4151

How to decrement / increment values based on value differences of the same group using postgresql?

I have table as below. Each val1 and val2 of same no have same value.

no    val1    val2
------------------
"1"   289.4   289
"1"   289.4   289
"1"   289.4   289
"1"   289.4   289
"2"   13.2    13.6
"2"   13.2    13.6
"2"   13.2    13.6
"2"   13.2    13.6
"3"   181     181.2
"3"   181     181.2

I want the table to be like below. The total of rows of same no always represent the difference in 0.1 scale. e.g : 4 rows of same no = 0.4 difference, 3 rows of same no = 0.3 difference.

If val1 > val2 of the same no : decrement value by 0.1 using val1 as upper bound and val2 as lower bound, as below (no 1).
If val1 < val2 of the same no : increment value by 0.1 using val1 as lower bound and val2 as upper bound, as below (no 2 and 3).

no    val1    val2
------------------
"1"   289.4   289.3
"1"   289.3   289.2
"1"   289.2   289.1
"1"   289.1   289
"2"   13.2    13.3
"2"   13.3    13.4
"2"   13.4    13.5
"2"   13.5    13.6
"3"   181     181.1
"3"   181.1   181.2

How to achieve this in postgresql? Thanks

Upvotes: 0

Views: 250

Answers (1)

Nick
Nick

Reputation: 147206

You can use a CTE to assign row numbers to each set of values for a given no and compute the diff value to use for each row; then compute the new val1 and val2 values from the original val1 value, the row number and the diff:

WITH CTE AS (
  SELECT no, val1,
         ROW_NUMBER() OVER (PARTITION BY no ORDER BY NULL) AS rn,
         SIGN(val1 - val2) * 0.1 AS diff
  FROM data
)
SELECT no, 
       val1 - (rn - 1) * diff AS val1,
       val1 - rn * diff AS val2
FROM CTE

Output:

no      val1    val2
"1"     289.4   289.3
"1"     289.3   289.2
"1"     289.2   289.1
"1"     289.1   289
"2"     13.2    13.3
"2"     13.3    13.4
"2"     13.4    13.5
"2"     13.5    13.6
"3"     181     181.1
"3"     181.1   181.2

Demo on SQLFiddle

Upvotes: 1

Related Questions