Google BigQuery 内部联接未按预期运行

Question

我有一个名为T的表，其结构如下：

Row date    a   b   c   d   e   f   g    
1   2.0051012E7 4.0 5.0 1.0 0.9 21.0    2.0170716E7 0.8
2   2.0131101E7 1.0 5.0 0.0 1.0 21.0    2.0170716E7 0.6  
3   2.0060908E7 3.0 5.0 0.0 1.0 21.0    2.0170716E7 0.7

我有以下查询：

select * from 
(SELECT date,max(a) as w FROM [T] 
group by date
order by date asc) as tableA
inner  join
(select date,b from 
[T]   ) as tableB
on tableB.date=tableA.date and tableB.b=tableA.w
order by a.date asc

然而，我的结果是：

Row tableA.date a   tableB.date b    
1   2.0040329E7 1.0 2.0040329E7 1.0  
2   2.0040329E7 1.0 2.0040329E7 1.0  
3   2.0040329E7 1.0 2.0040329E7 1.0  
4   2.0040329E7 1.0 2.0040329E7 1.0

为什么我有重复的行？这难道不是内部连接应该消除的吗？

Answer 1

我建议改用 BigQuery Standard SQL，这样就不会有那么多混淆了

我有将日期映射到最大值的结果。 然后，我想加入这些唯一日期并评估这些日期中的其他条件

在下面尝试 BigQuery Standard SQL

#standardSQL
SELECT entry.* 
FROM (
SELECT ARRAY_AGG(row ORDER BY a DESC LIMIT 1)[OFFSET(0)] AS entry
FROM `yourProject.yourDataset.yourTable` row 
GROUP BY date
)
-- ORDER BY date

您可以使用虚拟数据进行测试，如下所示

#standardSQL
WITH T AS (
SELECT 2.0051012E7 AS date, 5.0 AS a, 5.0 AS b, 1.0 AS c, 0.9 AS d, 21.0 AS e, 2.0170716E7 AS f, 0.8 AS g UNION ALL
SELECT 2.0131101E7, 1.0, 5.0, 0.0, 1.0, 21.0, 2.0170716E7, 0.6 UNION ALL  
SELECT 2.0060908E7, 3.0, 5.0, 0.0, 1.0, 21.0, 2.0170716E7, 0.7 
)
SELECT entry.* 
FROM (
SELECT ARRAY_AGG(row ORDER BY a DESC LIMIT 1)[OFFSET(0)] AS entry
FROM `T` row 
GROUP BY date
)
ORDER BY date