Countif statement in Postgresql

Gulya 0
Viewed 40

How can I use countif statement in PostgreSQL?

max(COUNTIF(t1.A1:C10,t2.a1),COUNTIF(t1.A1:C10,t2.b1),COUNTIF(t1.A1:C10,t2.c1))

I have table1 which is more then a million rows

a b c M5
16 27 31
3 7 27

and table2 more then 100 rows including different dates after column c

a b c
10 15 16
30 40 50
60 70 80
16 18 37
5 12 16
8 31 28
11 12 13
7 9 31
2 7 21
20 16 27
8 12 17
2 8 14
3 14 15

The outcome should be something like this

a b c M5
16 27 31 3
3 7 27 2

Tried the below query but the outcome is not correct

UPDATE table1 SET m5 = greatest(
  case When a in(select unnest(array[a,b,c]) from (select * from table2 order by date DESC limit 10) foo) then 1 else 0 END,
  case When b in(select unnest(array[a,b,c]) from (select * from table2 order by date DESC limit 10) foo) then 1 else 0 END,
  case When c in(select unnest(array[a,b,c]) from (select * from table2 order by date DESC limit 10) foo) then 1 else 0 END)
sql postgresql
1 Answers

Assuming your columns are fixed and predictable, I think you could put all possible table values into a single column and then do counts for each occurrence:

with exploded as (
  select a from table2
  union all
  select b from table2
  union all
  select c from table2
)
select a, count (*) as count
from exploded e
group by a

So for example, the value 7 occurs twice (which would be reflected in this output).

From there, you can just do the updates from the CTE:

with exploded as (
  select a from table2
  union all
  select b from table2
  union all
  select c from table2
),
counted as (
  select a, count (*) as count
  from exploded e
  group by a
)
update table1 t
  set m5 = greatest (ca.count, cb.count, cc.count)
from
  counted ca,
  counted cb,
  counted cc
where
  t.a = ca.a and
  t.b = cb.a and
  t.c = cc.a

The only issue I see is if one of the values does not come up (the inner join fails), but in your example that doesn't seem to happen.

If it is possible, I would think that could be resolved with one more CTE to fill in missing values from table1 in the set of possible occurrences.

Hambone0
Related