CODEWITHSUNDEEP
postgresql
YVS1997
YVS1997

Reputation: 680

unnest array value and count the number of occurence of each value

The original question are based on my previous question here

But as i have found there is an K-Means algorithm application for PostgreSQL, i decided to rather weighing the score to count the number of occurance of each value in the array

so if the previous result look like this

ids|uid|pattern|score|
---|---|-------|-----|
  1|  1|[1,1,2]|  280|
  2|  2|[2]    |   80|

the result must be count of each pattern but i want to keep the score empty since the score are the result from the K-Means alghorithm

ids|uid|pattern|b1|b2|b3|b4|b5|b6|b7|score|
---|---|-------|--|--|--|--|--|--|--|-----|
  1|  1|[1,1,2]| 2| 1| 0| 0| 0| 0| 0|     |
  2|  2|[2]    | 0| 1| 0| 0| 0| 0| 0|     |

Upvotes: 0

Views: 202

Answers (1)

S-Man
S-Man

Reputation: 23676

demo:db<>fiddle

I took my answer from your linked question.

Essentially you have to replace the part for the SUM() with a conditional COUNT() using either FILTER or a CASE clause:

SELECT 
    uid,
    values,
    COUNT(*) FILTER (WHERE group_id = 1) as b1,
    COUNT(*) FILTER (WHERE group_id = 2) as b2,
    COUNT(*) FILTER (WHERE group_id = 3) as b3,
    COUNT(*) FILTER (WHERE group_id = 4) as b4,
    COUNT(*) FILTER (WHERE group_id = 5) as b5,
    COUNT(*) FILTER (WHERE group_id = 6) as b6,
    COUNT(*) FILTER (WHERE group_id = 7) as b7
FROM (
    -- step (b) in linked answer
) s,
  unnest(values) as group_id
GROUP BY uid, values

Please note: It is NOT simply possible to generate an arbitrary number of columns, so you need to know how many score groups are available.

Upvotes: 1

Related Questions