(SQL)如何比较Presto中的每一行与所有其他行

Question

我正在使用使用Presto的AWS Athena。假设我有一个SQL表，其中有列a、B、C和d。假设表按列C升序排序。

我需要将每一行与所有其他行进行比较，并检查当前行的D值是否为C值小于当前行的C值的所有行中的最大值。然后在f列中添加一个布尔值，Python中的代码看起来像这样:

D_val_list = []
for index, row in df.iterrows():
max_val_D = df[:index]['D'].max() #Sorted on column C
if row['D'] < max_val_D:
D_val_list.append(FALSE)
else:
D_val_list.append(TRUE)
df['F'] = D_val_list

在Athena使用临时jupyter笔记本超时(数据集有数百万行长)，我认为通过本地jupyter实例连接到AWS也会有类似的问题。

Answer 1

在SQL中，您将使用窗口函数—类似于以下内容:

select t.*,
(case when d < coalesce(max(d) over (order by c
rows between unbounded preceding and 1 preceding) is null,
d + 1
then 1 else 0
end) as flag          
from t;

这个逻辑假设c是唯一的。也就是说，根据数据的确切性质，可能会有其他选择。

Answer 2

由于Athena的分布式特性，您必须在c上离散地排序您的行。您可以在有序集合之上使用窗口函数来实现您想要的结果:

SELECT 
a,
b,
c,
d,   
CASE WHEN d>lag(max_so_far) OVER () THEN true ELSE false END as f
FROM (
SELECT a,
b,
c,
d,
max(d) OVER (rows BETWEEN unbounded preceding AND current row) AS max_so_far
FROM ( 
-- sorted ON c 
SELECT 
a,
b,
c,
d
FROM dataset.table
ORDER BY  c 
) 
)