... 这些行完全相同，只是它们的获取日期不同
... 如何消除重复数据并保留任何采集日期都可以
下面是bigquery标准sql

# standardSQL

SELECT AS VALUE ANY_VALUE(t)
FROM `project.dataset.table` t
GROUP BY FORMAT('%t', (SELECT AS STRUCT * EXCEPT(acquisitionDate) FROM UNNEST([t])))

您可以使用完全简化的虚拟数据测试上述方法，如下面的示例所示

# standardSQL

WITH `project.dataset.table` AS (
  SELECT 1 id, 'aaa' info, 1 acquisitionDate UNION ALL
  SELECT 1, 'aaa', 2 UNION ALL
  SELECT 2, 'ccc', 3 UNION ALL
  SELECT 2, 'ccc', 4
)
SELECT AS VALUE ANY_VALUE(t)
FROM `project.dataset.table` t
GROUP BY FORMAT('%t', (SELECT AS STRUCT * EXCEPT(acquisitionDate) FROM UNNEST([t])))

有输出

Row id  info    acquisitionDate  
1   1   aaa     1    
2   2   ccc     3

如果您需要保留最新的收购日期，您可以使用以下版本：

# standardSQL

SELECT AS VALUE ARRAY_AGG(t ORDER BY acquisitionDate DESC LIMIT 1)[OFFSET(0)]
FROM `project.dataset.table` t
GROUP BY FORMAT('%t', (SELECT AS STRUCT * EXCEPT(acquisitionDate) FROM UNNEST([t])))

这将回到下面

Row id  info    acquisitionDate  
1   1   aaa     2    
2   2   ccc     4

最后，如果您需要保留所有的收购日期-您可以使用下面的

# standardSQL

SELECT 
  ARRAY_AGG(x ORDER BY acquisitionDate DESC LIMIT 1)[OFFSET(0)].*,
  ARRAY_AGG(acquisitionDate) acquisitionDates
FROM `project.dataset.table` t,
UNNEST([(SELECT AS STRUCT * EXCEPT(acquisitionDate) FROM UNNEST([t]))]) x
GROUP BY FORMAT('%t', x)

哪些输出

Row id  info    acquisitionDates     
1   1   aaa     1    
                2    
2   2   ccc     3    
                4