SQL(配置单元):使用GROUP BY聚合时使用窗口函数

Question

我在Athena(Hive/Presto(中有下表：

CREATE EXTERNAL TABLE tmp (
id STRING,
updated_at TIMESTAMP,
location STRING,
direction STRING
)
LOCATION 's3://path'; 

我需要对id字段进行聚合和计数，同时选择相对于组内最新的timestamp的location和direction(分区再次位于id上(。

到目前为止，我首先提出了以下利用窗口函数并在之后分组的查询：

SELECT
b.id,
MAX(b.latest_location) AS "latest_location",  -- It seems it is not possible to use first_value() on GROUP BY
MAX(b.latest_direction) AS "latest_direction",
COUNT(*) AS "total"
FROM (
SELECT
a.id,
first_value(a.location) OVER (PARTITION BY a.id ORDER BY a.updated_at DESC) AS "latest_location",
first_value(a.direction) OVER (PARTITION BY a.id ORDER BY a.updated_at DESC) AS "latest_direction"
FROM tmp a
) b
GROUP BY b.id;

我首先尝试了通过聚合和窗口聚合同时进行分组，但这似乎是引擎不允许的。是否可以编写更高效的查询(可能没有子查询(？

Answer 1

SELECT DISTINCT
id,
first_value(a.location)  OVER (PARTITION BY id ORDER BY updated_at DESC) AS latest_location,
first_value(a.direction) OVER (PARTITION BY id ORDER BY updated_at DESC) AS latest_direction,
count(*) OVER (PARTITION BY id) as total
FROM tmp

在最初的查询中，max基本上是一个伪聚合，因为所有行都具有相同的值。group by本质上是在做distinct在这里做的事情。

Answer 2

添加到首选答案中——考虑更正式，因为您的窗口定义支持DRY(不要重复自己(首选项：

SELECT DISTINCT
id,
first_value(a.location)  OVER w AS latest_location,
first_value(a.direction) OVER w AS latest_direction,
count(*) OVER (PARTITION BY id) as total
FROM tmp
WINDOW w AS (PARTITION BY id ORDER BY updated_at DESC)

这将允许更复杂的窗口定义只保留在一个位置，并确保两列计算都使用相同的窗口逻辑。

Answer 3

您可以混合窗口函数和聚合函数。但在另一个方向：先聚合，然后窗口函数。

也就是说，如果您消除了聚合，那么您的查询应该会更快。只需使用row_number()和过滤：

SELECT a.id, a.location, a.updated_at
FROM (SELECT a.*,
ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY a.updated_at DESC) AS seqnum
FROM tmp a
) a
WHERE seqnum = 1;