时间戳

首页 > 解决方案 > MySQL - 为每个标识符保留最新的非空值

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

问题描述

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

+-----------+-------------+-----------+--------+-----------+---------------+-------+
| 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 。

标签： mysql

解决方案

子查询有效地填满，外部查询选择最后一个记录的_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)

推荐阅读