时间戳

首页 > 解决方案 > Mysql：按最新查询优化分组

mysql - Mysql：按最新查询优化分组

问题描述

我一直在优化这个查询。我希望我没有过度简化我的例子。

我有一张表格，以下表格：

运动员： id, name, team_id

测量： id, type, value, athlete_id, measured_at

我需要找到最新、最旧和倒数第二个测量值，因此我编写了以下查询：

select 
    m.athlete_id, 
    m.`type`,
    (
        select ml.`value`
        from measurements ml 
        where ml.athlete_id = m.athlete_id
        and ml.`type` = m.`type`
        order by ml.measured_at desc limit 1
    )
    as latest_record,
    (
        select mo.`value`
        from measurements mo 
        where mo.athlete_id = m.athlete_id
        and mo.`type` = m.`type`
        order by mo.measured_at asc limit 1
    )
    as oldest_record,
    (
        select mp.`value`
        from measurements mp
        where mp.athlete_id = m.athlete_id
        and mp.`type` = m.`type`
        order by mp.measured_at desc limit 1 offset 1
    )
    as penultimate_record
    from measurements m
    group by m.athlete_id, m.`type`;

这得到了我所要求的一切，但它不是高性能的，或者至少我需要修改我的应用程序中的其他东西，如果它必须这么慢的话。

我还尝试将选择逻辑移动到左侧连接，例如。

left join lateral
(
    SELECT mlr.id as measurement_id, mlr.value as `value` 
    FROM measurements mlr 
    WHERE mlr.athlete_id = s.id
    and mlr.type = v.type
    ORDER BY mlr.measured_at ASC LIMIT 1
) 
as latest_record on m.id = latest_record.measurement_id

但这与上述速度相似。我还尝试将数据库索引放在列上，但没有看到任何改进。

有没有我可以放在这里的索引，或者用另一种方法来编写它以使查询更好地执行？

谢谢

标签： mysqlsql

解决方案

我建议条件聚合：

select m.athlete_id, m.`type`,
       max(case when seqnum_desc = 1 then value end) as latest_record,
       max(case when seqnum_asc = 1 then value end) as oldest_record,
       max(case when seqnum_desc = 2 then value end) as penultimate_record
    from (select m.*,
                 row_number() over (partition by athlete_id, type order by measured_at asc) as seqnum_asc,
                 row_number() over (partition by athlete_id, type order by measured_at desc) as seqnum_desc
          from measurements m
         ) m
group by m.athlete_id, m.`type`;

这仍然会产生分组的开销——这可能很昂贵。您可能需要在measures(athlete_id, type, measured_at).

推荐阅读