Build an adjacency matrix from a list of weighted edges in BigQuery

Question

Related problem: How to create dummy variable columns for thousands of categories in Google BigQuery

I have a table of a list of weighted edges, which is a list of the rating of the user element, it looks like this:

| userId | itemId | rating | 001 | 001 | 5.0 | 001 | 002 | 4.0 | 002 | 001 | 4.5 | 002 | 002 | 3.0 

I want to convert this list of weighted edges to an adjacency matrix:

 | userId | item001 | item002 | 001 | 5.0 | 4.0 | 002 | 4.5 | 3.0 

In accordance with this message, we can do this in two stages, the first step is to extract the input value of the matrix to generate the query, and the second step is to run the query that is generated from the 1st step.

But my question is how to extract the rating value and use the rating value in the expression IF() ? My intuition is to put a subquery inside an IF() , for example:

 IF(itemId = blah, (select rating from mytable where userId = blahblah and itemId = blah), 0) 

But this query looks too expensive, can anyone give me an example?

thanks

Answer 1

If I have something missing - this is very similar to the message you referred to

Step 1 - generate a request

 SELECT 'SELECT userID, ' + GROUP_CONCAT_UNQUOTED( 'SUM(IF(itemId = "' + STRING(itemId) + '", rating, 0)) AS item' + STRING(itemId) ) + ' FROM YourTable GROUP BY userId' FROM ( SELECT itemId FROM YourTable GROUP BY itemId ) 

Step 2 - Run the Generated Request

 SELECT userID, SUM(IF(itemId = "001", rating, 0)) AS item001, SUM(IF(itemId = "002", rating, 0)) AS item002 FROM YourTable GROUP BY userId 

Result as expected

 userID item001 item002 001 5.0 4.0 002 4.5 3.0