BigQuery/SQL:如何联接两个表并将列值用作列名? [英] BigQuery/SQL: How do I join two tables and use a column value as column name?

问题描述

我有这些表:

食物

| food_id | title    |
| 1       | soy milk |
| 2       | banana   |
| 3       | apple    |

营养素

| food_id | nutrient_id | amount |
| 1       | n1          | 0.05   |
| 1       | n2          | 2      |
| 1       | n3          | 34     |
...

我需要这个:

| food_id | title    | n1   | n2 | n3 |
| 1       | soy milk | 0.05 | 2  | 34 |
| 2       | banana   |      |    |    |  
| 3       | apple    |      |    |    |

Struct也可以.

我知道所有联接，但是无法解决这个问题……如何将nutrient_id放在列标题或Struct键中?

I know all the joins, but can't wrap my head around this... how do I put nutrient_id into a column title or a Struct key?

推荐答案

下面是BigQuery标准SQL，并假定每种食物的营养成分数量不是固定的，因此数据透视的方法并不简单，而是回答以下问题:

Below is for BigQuery Standard SQL and assumes that number of nutrients is not fixed per food so pivot'ing approach will not be simple and rather answering below question :

如何将Nutrition_id放入...结构键?

how do I put nutrient_id into ... a Struct key?

#standardSQL
SELECT *
FROM `project.dataset.Foods` 
LEFT JOIN (
  SELECT food_id, ARRAY_AGG(STRUCT(nutrient_id, amount)) nutrients_facts
  FROM `project.dataset.Nutrients`
  GROUP BY food_id
)
USING(food_id)  

如果将以上内容应用于您的问题的示例数据-结果为

If to apply above to sample data from your question - result is

这篇关于BigQuery/SQL:如何联接两个表并将列值用作列名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！