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

Question

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|     |

S-Man · Accepted Answer · 2021-02-15 11:14:13Z

1

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.

answered Feb 15, 2021 at 11:14

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

YVS1997 Over a year ago

i want to make the column b1 to b7 as my feature to the K-Means algorithm and i have know that there's only 7 group_id.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related