I have table like
WITH vals (k, v, z, y, m) AS (VALUES (0, 0, 1, 8, 0), (1, 2, 1, 1, 6), (1, 0, 0, 2, 2),
(5, 4, 6, 8, 9), (0, 0, 0, 6, 6))
select vals.* from vals
k | v | z | y | m |
---|---|---|---|---|
0 | 0 | 1 | 8 | 0 |
1 | 2 | 1 | 1 | 6 |
1 | 0 | 0 | 2 | 2 |
5 | 4 | 6 | 8 | 9 |
0 | 0 | 0 | 6 | 6 |
I want to calculate modal value for each row. If there are multiple modal values, return such value, that occurs first.
k | v | z | y | m | modal |
---|---|---|---|---|---|
0 | 0 | 1 | 8 | 0 | 0 |
1 | 2 | 1 | 1 | 6 | 1 |
1 | 0 | 0 | 2 | 2 | 2 |
5 | 4 | 6 | 8 | 9 | 5 |
0 | 0 | 0 | 6 | 6 | 0 |
How can I do it in PostreSQL?
2
Answers
Assuming only three value columns, we can handle your requirement using
CASE
expressions:Demo
The logic here is that if we can find two columns in agreement, then by definition their common value must be the mode of the three columns. If we cannot find any pair with a common value, then we default to returning the first value, which is the
k
column.PostgreSQL has a built-in
mode()within group(order by e)
function:Which matches the Wikipedia definition of a modal value:
The function is an ordered-set aggregate, not a variadic, so you’d have to pretend values in each row are a column. You can pack them into an
array[]
, thenunnest()
it:demo at db-fiddle
Or try unpivoting, then re-aggregating: