CODEWITHSUNDEEP
sqlpostgresqlmathwindow-functionsthreshold
fabvys
fabvys

Reputation: 423

PostgreSQL calculate threshold query

i have postgresql db with a table t1 and i want to calculate a threshold. the threshold should be for example car 1 uses more fuel than 75 % of all cars, car2 uses more fuel than 50% of all cars, .... mathematically i understand what i want to do, but i dont know how to build the query

id | name | value | threshold
________________________

1  | car1 |  30   |  ...%
2  | car2 |  15   |  ..%
3  | car3 |   7   |
4  | car4 |   5   |

here is a sql fiddle http://sqlfiddle.com/#!15/1e914/1

UPDATE t1
SET threshold = 
    select count(value)
     from t1

where (value > [over each row]) and followed by *100/the overall count()

sorry for that bad try but i am kind of lost. also tried some aggregate functions.

Upvotes: 3

Views: 1947

Answers (3)

Patrick
Patrick

Reputation: 32224

You can solve this quite elegantly with a window function:

UPDATE t1
SET threshold = sub.thr
FROM (
  SELECT id, 100. * (rank() OVER (ORDER BY value) - 1) / count(*) OVER () AS thr
  FROM t1) sub
WHERE t1.id = sub.id;

The rank() function gives the rank (starting from 1) in an ordered set, in this case over the column value, which is then divided by the total number of rows in the set. Note that count(*) OVER () calculates the total number of rows in th partition but it does not aggregate the rows like a regular count(*) would.

Upvotes: 5

Quassnoi
Quassnoi

Reputation: 425371

WITH    q AS
        (
        SELECT  *,
                (RANK() OVER (ORDER BY value) - 1) * 100. / COUNT(*) OVER () nt
        FROM mytable
        )
UPDATE  mytable
SET     threshold = nt
FROM    q
WHERE   mytable.id = q.id

Upvotes: 1

Pham X. Bach
Pham X. Bach

Reputation: 5442

Do you want to calculate percentage of using fuel?

UPDATE t1 
SET threshold = 
    (select value * 100 / t2.sumValue
    from (select sum(value) sumValue from t1) t2
    )

Or if you want to calculate specific threshold, may you show your expected result?

Upvotes: 0

Related Questions