MySQL - 为每个标识符保留最近的非空值

Question

我想创建一个查询，其中包含每个玩家的最新信息。该查询应该为每个玩家返回一个结果。如果玩家更改了其玩家代号，则查询结果应返回该玩家最近的非空玩家代号。此行为应该扩展到其他标识符，例如城市、州和国家/地区。以下是两名玩家的一些示例数据:

+-----------+-------------+-----------+--------+-----------+---------------+-------+
| player_id | recorded_at | gamertag  | prefix | city      | country       | state |
+-----------+-------------+-----------+--------+-----------+---------------+-------+
|     26640 |  1461421800 | Wobbaduck | NULL   | Toronto   | Canada        | ON    |
|     26640 |  1484931600 | Wobbaduck | NULL   | Saskatoon | Canada        | SK    |
|     26640 |  1510416000 | Mimic     | NULL   | NULL      | NULL          | NULL  |
|     26640 |  1516388400 | Mimic     | NULL   | NULL      | Canada        | SK    |
|     26640 |  1518278400 | Mimic     | NULL   | NULL      | NULL          | NULL  |
|      4507 |  1491678000 | The Moon  | NULL   | NULL      | NULL          | NULL  |
|      4507 |  1500645600 | The Moon  | MVG    | Buffalo   | United States | NY    |
|      4507 |  1533830400 | La Luna   | NULL   | New York  | United States | NY    |
+-----------+-------------+-----------+--------+-----------+---------------+-------+

查询应产生结果:

+-----------+----------+--------+-----------+---------------+-------+
| player_id | gamertag | prefix | city      | country       | state |
+-----------+----------+--------+-----------+---------------+-------+
|     26640 | Mimic    | NULL   | Saskatoon | Canada        | SK    |
|      4507 | La Luna  | MVG    | New York  | United States | NY    |
+-----------+----------+--------+-----------+---------------+-------+

字段recorded_at用于跟踪以纪元秒为单位的日期。我之前为实现此目的所做的尝试包括对每个标识符 gamertag、prefix、city、country 进行子查询和联接 和状态。如果可能的话，我想避免这样做。我使用的是 MySQL 8.0.11。

我使用示例数据创建了 db-fiddle。

Answer 1

子查询有效地向下填充，外部查询选择最后一个recorded_at

drop table if exists t;
create table t( player_id int,  recorded_at int, gamertag varchar(20),  prefix varchar(20), city varchar(20), country varchar(20)
, state varchar(20));
insert into t values
(    26640 ,  1461421800 , 'Wobbaduck' , NULL   , 'Toronto'   , 'Canada'        , 'ON') ,   
(    26640 ,  1484931600 , 'Wobbaduck' , NULL   , 'Saskatoon' , 'Canada'        , 'SK' ) ,  
(    26640 ,  1510416000 , 'Mimic'     , NULL   ,  NULL       , NULL            , NULL ) ,  
(    26640 ,  1516388400 , 'Mimic'     , NULL   ,  NULL       , 'Canada'        , 'SK' ) ,  
(    26640 ,  1518278400 , 'Mimic'     , NULL   ,  NULL       , NULL            , NULL ), 
(     4507 ,  1491678000 , 'The Moon'  , NULL   ,  NULL       , NULL            , NULL ), 
(     4507 ,  1500645600 , 'The Moon'  , 'MVG'  , 'Buffalo'   , 'United States' , 'NY' ),   
(     4507 ,  1533830400 , 'La Luna'   , NULL   , 'New York'  , 'United States' , 'NY' );

select * 
from
(
select t.player_id,t.recorded_at,

            if(t.gamertag is null,
            (select t1.gamertag from t t1 where t1.gamertag is not null and t1.player_id = t.player_id and t1.recorded_at < t.recorded_at order by t1.recorded_at desc limit 1),
            t.gamertag) gamertag, 

            if(t.prefix is null,
            (select t1.prefix from t t1 where t1.prefix is not null and t1.player_id = t.player_id and t1.recorded_at < t.recorded_at order by t1.recorded_at desc limit 1),
            t.prefix) prefix,

            if(t.city is null,
            (select t1.city from t t1 where t1.city is not null and t1.player_id = t.player_id and t1.recorded_at < t.recorded_at order by t1.recorded_at desc limit 1),
            t.city) city,

            if(t.country is null,
            (select t1.country from t t1 where t1.country is not null and t1.player_id = t.player_id and t1.recorded_at < t.recorded_at order by t1.recorded_at desc limit 1),
            t.country) country  

 from t
 order by t.player_id, t.recorded_at
 ) s
 where s.recorded_at = (select max(recorded_at) from t t1 where t1.player_id = s.player_id);   

+-----------+-------------+----------+--------+-----------+---------------+
| player_id | recorded_at | gamertag | prefix | city      | country       |
+-----------+-------------+----------+--------+-----------+---------------+
|     26640 |  1518278400 | Mimic    | NULL   | Saskatoon | Canada        |
|      4507 |  1533830400 | La Luna  | MVG    | New York  | United States |
+-----------+-------------+----------+--------+-----------+---------------+
2 rows in set (0.00 sec)