SQL 查询重复数据删除/加入问题

Question

我一直在尝试编写我认为应该非常简单的查询来处理重复条目时遇到了最糟糕的时间。

对于上下文：我使用Big Query创建了一个数据仓库，并使用Stitch从Hubspot中提取数据。一切按预期工作，如下所示：我已确认我在 BigQuery 中拥有正确数量的记录。

问题在于Stitch如何刷新数据。它不是基于对象 ID 更新记录，而是附加一个新行。根据他们的文档，下面的查询应该有效，但原因很简单，给定记录存在具有相同_sdc_sequence的多个版本(我认为不应该存在(。我可以使用其他_sdc(缝合系统字段(来提供帮助，但由于与上述相同的原因，它也不完全可靠。

SELECT DISTINCT o.*
FROM [sample-table:hubspot.companies] o
INNER JOIN (
SELECT
MAX(_sdc_sequence) AS seq,
id
FROM [sample-table:hubspot.companies]
GROUP BY companyid ) oo
ON o.companyid = oo.companyid
AND o._sdc_sequence = oo.seq

上面的查询返回的结果比应有的少。如果我运行以下查询，我会得到正确数量的结果，但我需要除 companyid 之外的其他字段，如名称、描述、收入等。

SELECT o.companyid
FROM [samples_table:hubspot.companies] o
GROUP BY o.companyid

我正在尝试这样的事情，但它不起作用(我收到以下错误(表达式"oo.properties.name.value"在 GROUP BY 列表中不存在(。

SELECT o.companyid,
oo.properties.name.value,
oo.properties.hubspot_owner_id.value,
oo.properties.description.value
FROM [sample_table:hubspot.companies] o
LEFT JOIN [sample_table:hubspot.companies] oo
ON o.companyid = oo.companyid
GROUP BY o.companyid

我是我的思想，我思考这个问题的方式是：

1. 获取唯一记录 ID 列表(公司 ID(
2. 对按插入时间排序的原始未分组公司表执行 SQL"vlookup 等效项"，以获取与 id 匹配的第一条记录(这将是自表排序以来的最新记录(

我只是不知道怎么写这个...

Answer 1

尝试使用窗口函数：

#standardSQL
SELECT c.*
FROM (SELECT c.*,
ROW_NUMBER() OVER (PARTITION BY companyid ORDER BY _sdc_sequence DESC) as seqnum 
FROM `sample-table.hubspot.companies` c
) c
WHERE seqnum = 1;

Answer 2

下面是 BigQuery Standard SQL

#standardSQL
SELECT AS VALUE ARRAY_AGG(t ORDER BY _sdc_sequence DESC LIMIT 1)[OFFSET(0)]
FROM `sample-table.hubspot.companies` t
GROUP BY companyid