sql - 在进行分析查询时如何避免 DISTINCT 成为拐杖？

Question

我有一个查询，我认为它具有相当普遍的模式。考虑这张表:

id | val | ts
---+-----+-------
 a |  10 | 12:01
 a |  12 | 12:05
 a |   9 | 12:15
 b |  30 | 12:03

我想通过时间戳获取每个 id 的最新值。您可以通过一些方式做到这一点:

-- where in aggregate subquery
-- we avoid this because it's slow for our purposes
select
  id, val
from t
where (id, ts) in
  (select
    id,
    max(ts)
   from t
   group by id);

-- analytic ranking
select
  id, val
from
  (select
    row_number() over (partition by id order by ts desc) as rank,
    id,
    val
  from t) ranked
where rank = 1;

-- distincting analytic
-- distinct effectively dedupes the rows that end up with same values
select
  distinct id, val
from
  (select
    id,
    first_value(val) over (partition by id order by ts desc) as val
  from t) ranked;

分析排名查询感觉就像是最容易提出高效查询计划的查询。但从美学和维护角度来看，它非常难看(尤其是当表格的值列不止 1 个时)。在生产中的一些地方，当测试表明性能等效时，我们使用不同的分析查询。

有没有什么方法可以像 rank = 1 这样的查询而不会以这样一个丑陋的查询结束？

Answer 1

如果您仅按id 分组

select
    id, max(ts)
  from x
  group by id 
  order by id

如果组由id和val组成

select
    id, val, max(ts)
  from
    x
  group by id, val
  order by id, val

所以我不会使用将聚合放在子查询中(可能会更慢)我也不会使用窗口聚合函数(因为你可以用普通的 group by 和 max 来做到这一点)而且我不会使用 distinct，因为这意味着不同的东西(至少对我而言)。

如果您对 id 进行分组，并且您想要val 的值中的一个，我建议使用窗口聚合函数，因为您必须以某种方式定义要选择的哪个 val:并且此意图属于紧接在partition by 之后的order by。

从维护的角度来看，我认为窗口聚合函数确实描述了您的意图 - 您想要实现的目标。其他查询以某种方式隐藏了他们的意图。就个人而言，当我阅读您的查询时，第二个是最容易理解的。

从性能的角度来看，我可以确认窗口聚合速度很快(至少在我的情况下)。优化器也可能从语法中受益。