为什么我的表联接返回未指定的值？

Question

我正在尝试连接两个表，以创建一个可视化，显示体重、BMI和总步数之间的关系，使用以下代码:

SELECT
Date AS date_join,
TotalSteps AS Total_Steps,
Weight,
BMI AS BMI_join
FROM
`bellabeat-case-study-347518.fitbit_data.activity` activity
JOIN
`bellabeat-case-study-347518.fitbit_data.weight` weight
ON
activity.Id = weight.Id
AND activity.ActivityDate = weight.Date
ORDER BY
TotalSteps DESC;

但是，返回的结果不只是给我与权重相关的值:

Row
date_join
Total_Steps
Weight
BMI_join
1 
2016-04-16 
29326 
Id
Date
Weight
BMI
8877689391 
2016-04-16 
188.5 
25.59 
25.59 
2 
2016-04-30 
27745 
Id
Date
Weight
BMI
8877689391 
2016-04-30 
188.5 
25.59 
25.59 
3 
2016-04-27 
23629 
{ "Id": "8877689391", "Date": "2016-04-27", "Weight": "188.27", "BMI": "25.56" }
25.56 
4 
2016-04-12 
23186 
{ "Id": "8877689391", "Date": "2016-04-12", "Weight": "189.16", "BMI": "25.68" }
25.68 
5 
2016-05-06 
21727 
{ "Id": "8877689391", "Date": "2016-05-06", "Weight": "187.39", "BMI": "25.44" }
25.44 
6 
2016-05-11 
21420 
{ "Id": "8877689391", "Date": "2016-05-11", "Weight": "188.27", "BMI": "25.56" }
25.56 

我怎样才能只得到返回的权重，以便我可以使用Tableau中的值?

Answer 1

我意识到Weight列的数据类型是RECORD，所以我使用CAST函数将其更改为INTEGER。下面的代码运行得很好:

SELECT
Date AS date_join,
TotalSteps AS Total_Steps,
CAST(weight.Weight AS INTEGER) AS Weight_lbs,
BMI AS BMI_join
FROM `bellabeat-case-study-347518.fitbit_data.activity` activity 
RIGHT OUTER JOIN `bellabeat-case-study-347518.fitbit_data.weight` weight 
ON activity.Id = weight.Id 
AND activity.ActivityDate = weight.Date
ORDER BY TotalSteps DESC;