时间戳

首页 > 解决方案 > 处理 BigQuery 中未嵌套记录导致的重复行的最佳做法？

sql - 处理 BigQuery 中未嵌套记录导致的重复行的最佳做法？

问题描述

处理来自 Facebook 的数据往往涉及处理记录，在我的例子中，所有“辣”数据都在其中。但是，有一个缺点，即大量重复行，如果处理不当，可能会导致过度报告和/或数据差异。

下面是一个用例，当与我的主要数据（来自不涉及任何取消嵌套的表）连接时，最终数字会略有差异。

使用的技术 - Facebook 数据 -> Stitch -> BigQuery -> dbt -> Google Data Studio

我通常会创建单独的模型，在其中取消嵌套记录，转换数据，然后将其加入到我的其余模型中。例如，从 ads_insights 的操作记录中获取所有网站购买转化。不过，这里有区别：


询问：

SELECT count(*) AS row_count
FROM ads_insights

结果：row_count - 316 </p>

询问：

SELECT count(*) AS row_count
FROM ads_insights,
UNNEST(actions) AS actions

结果：row_count - 5612


取消嵌套后，我将使用行数据为每次转换创建列，如下所示：

CASE WHEN value.action_type = 'offsite_conversion.fb_pixel_purchase' THEN COALESCE(value._28d_click, 0) + COALESCE(value._1d_view, 0) ELSE 0 END AS website_purchase


最后，我会将这个模型加入到我的其他模型中。唯一的问题是这 5600 行在与其余行连接时会导致轻微差异，并且由于我已经使用行数据创建列，我不再关心未嵌套的记录数据，我可以返回到我原来的 316 行。唯一的问题是如何？有哪些技术可以帮助我清理模型？

解决方案：即使在某些时候我会像 dylanbaker 在他的回答中建议的那样聚合和分组查询中的所有字段，但差异仍然存在，在深入研究我的数据后，我发现未嵌套的查询将返回 279行，而嵌套的将返回 314。这将我的注意力集中在取消嵌套查询上，它将删除 35 行，而这 35 行恰好为空。在做了一些谷歌搜索后，我发现这篇 StackOverflow文章建议使用 LEFT JOIN UNNEST 来保留所有具有空记录值的行，而不是 CROSS JOIN UNNEST 将删除它们。

标签： sqlgoogle-bigquerydbt

解决方案

您通常会想在这里做一个“枢轴”。您已经完成了大部分工作，您只需要按相关列进行汇总和分组，以便将其恢复到您最初拥有和想要的粒度。

我相信你会想要这样的东西：

select
    ads_insights.some_column,
    ads_insights.some_other_column,
    sum(case 
          when value.action_type = 'offsite_conversion.fb_pixel_purchase' 
          then coalesce(value._28d_click, 0) + coalesce(value._1d_view, 0) 
          else 0 
        end) AS website_purchase
from ads_insights,
unnest(actions) as actions
group by 1,2

初始列将是原始表中您想要的任何列。'sum case whens' 是对未嵌套的数据进行透视和聚合。

推荐阅读