优化 MySQL 查询，在日志记录表上按顺序进行多个联接，以查找最大差异(最佳记录)

Question

>假设我们有一个表来记录用户的权重和其他信息，如下所示：

health_indexes
id | user_id | weight | created_at
---+---------+--------+-----------
1  | 50      | 100    | 2020-01-01
2  | 50      | 98     | 2020-01-05
3  | 50      | 98.5   | 2020-01-10
4  | 50      | 92     | 2020-01-15
5  | 50      | 80     | 2020-01-20
.
.
.
10 | 100     | 130    | 2018-01-01
11 | 100     | 149999 | 2018-01-05
12 | 100     | 159999 | 2018-01-10
13 | 100     | 120    | 2018-01-15
.
.
.
20 | 200     | 87     | 2020-02-01
.
.
.
30 | 300     | 140    | 2020-01-01

我确实到了下表，但我正在寻找更好的方法：

user_id | first_weight | first_created_at | last_weight | last_created_at
--------+--------------+------------------+-------------+----------------
50      | 100          | 2020-01-01       | 80          | 2020-01-20
100     | 130          | 2018-01-01       | 120         | 2018-01-15

查询：

select u.id user_id,
(select weight from health_indexes where user_id = u.id order by created_at limit 1) first_weight,
(select created_at from health_indexes where user_id = u.id order by created_at limit 1) first_created_at,
(select weight from health_indexes where user_id = u.id order by created_at desc limit 1) last_weight,
(select created_at from health_indexes where user_id = u.id order by created_at desc limit 1) last_created_at
from users u
group by u.id
having first_weight > last_weight
order by (first_weight - last_weight) desc
limit 50;

我正在寻找一种在health_indexes上加入两次以获得相同结果的方法。有什么想法吗？

Answer 1

如果您使用的是 MySQL 8.0，则可以仅使用窗口函数执行此操作，而无需任何连接。在极少数情况下，distinct与窗口函数结合使用非常有用：

select distinct
user_id,
first_value(weight) over(partition by user_id order by created_at)       first_weight,
min(created_at)     over(partition by user_id)                           first_created_at,
first_value(weight) over(partition by user_id order by created_at desc)  last_weight,
max(created_at)     over(partition by user_id)                           last_created_at
from health_indexes 

在早期版本中，一个选项使用联接和筛选：

select 
hi1.user_id,
hi1.weight first_weight,
hi1.created_at first_created_at,
hi2.weight last_weight,
hi2.created_at last_created_at
from health_indexes hi1
inner join health_indexes hi2 on hi2.user_id = hi1.user_id
where
hi1.created_at = (select min(h3.created_at) from health_indexes hi3 where hi3.user_id = hi1.user_id)
and hi2.created_at = (select max(h3.created_at) from health_indexes hi3 where hi3.user_id = hi2.user_id)