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

Question

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

+-----------+-------------+-----------+--------+-----------+---------------+-------+
| 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和state提供一个子查询和一个连接。如果可能的话，我想避免这样做。我使用的是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)

Answer 2

这不完全是您想要的，但我认为它可以工作。对于列，它将获得每列的逗号分隔的值列表。您所要做的就是获取第一个值，它将是该列的最新值。

select *
from player a
JOIN (SELECT MAX(`recorded_at`) as `recorded_at`,
        `player_id`,
        GROUP_CONCAT(`gamertag` ORDER BY `recorded_at` DESC) as `gamertag`,
        GROUP_CONCAT(`prefix` ORDER BY `recorded_at` DESC) as `prefix`,
        GROUP_CONCAT(`city` ORDER BY `recorded_at` DESC) as `city`,
        GROUP_CONCAT(`country` ORDER BY `recorded_at` DESC) as `country`,
        GROUP_CONCAT(`state` ORDER BY `recorded_at` DESC) as `state`
      FROM `player`
      GROUP BY `player_id`) b
    ON a.`player_id` = b.`player_id`
        AND a.`recorded_at` = b.`recorded_at`;

SELECT *
FROM `ideal_player`;

查询#1

select *
from player a
JOIN (SELECT MAX(`recorded_at`) as `recorded_at`,
        `player_id`,
        GROUP_CONCAT(`gamertag` ORDER BY `recorded_at` DESC) as `gamertag`,
        GROUP_CONCAT(`prefix` ORDER BY `recorded_at` DESC) as `prefix`,
        GROUP_CONCAT(`city` ORDER BY `recorded_at` DESC) as `city`,
        GROUP_CONCAT(`country` ORDER BY `recorded_at` DESC) as `country`,
        GROUP_CONCAT(`state` ORDER BY `recorded_at` DESC) as `state`
      FROM `player`
      GROUP BY `player_id`) b
    ON a.`player_id` = b.`player_id`
        AND a.`recorded_at` = b.`recorded_at`;

| player_id | recorded_at | gamertag | prefix | city     | country       | state | recorded_at | player_id | gamertag                                    | prefix | city              | country                     | state    |
| --------- | ----------- | -------- | ------ | -------- | ------------- | ----- | ----------- | --------- | ------------------------------------------- | ------ | ----------------- | --------------------------- | -------- |
| 4507      | 1533830400  | La Luna  |        | New York | United States | NY    | 1533830400  | 4507      | La Luna,The Moon,The Moon                   | MVG    | New York,Buffalo  | United States,United States | NY,NY    |
| 26640     | 1518278400  | Mimic    |        |          |               |       | 1518278400  | 26640     | Mimic,Mimic,Mimic,Mimic,Wobbaduck,Wobbaduck |        | Saskatoon,Toronto | Canada,Canada,Canada        | SK,SK,ON |

View on DB Fiddle