mysql - Mysql在列值更改时仅选择最新行

Question

这是一个类似的问题，但只有 1 个区别。mysql如下。

ID |    lat   | lng          | timestamp    
1  | 23.21465 | 84.37856     | 2016-01-01 00:00:00  
2  | 23.21465 | 84.37856     | 2016-01-01 00:03:00  
3  | 23.21584 | 84.37877     | 2016-01-01 00:06:00  
4  | 23.21584 | 84.37877     | 2016-01-01 00:09:00  
5  | 23.21465 | 84.37856     | 2016-01-01 00:12:00  
6  | 23.21465 | 84.37856     | 2016-01-01 00:15:00  
7  | 23.21465 | 84.37856     | 2016-01-01 00:18:00  
8  | 23.21465 | 84.37856     | 2016-01-01 00:21:00

尽管在这种情况下，第 3 行和第 5 行的 lat(latitude) 和 lng(longitude) 发生了变化，但它应该只选择 id 为 5 的行。

算法如下：

(a) 按时间戳的升序排列表格

(b) 检查最后一行，即第 8 行。将纬度、经度值与第 7 行进行比较。如果它们相同，则与第 6 行进行比较，依此类推，直到纬度和经度值发生变化。在这种情况下，它将是第 4 行。然后打印第 5 行

(c) 如果第 8 行和第 7 行的纬度、经度值不同，则什么也不做。

编辑：简而言之，我只是在当前值的最早实例之后，或者值最后一次更改之后。

标签： mysqlsql

Answer 1

如果你想最后一次纬度改变时的第一条记录？

那么这是一种获得它的方法。

样本数据

create table YourLatiLongiTudesTable
(
  ID int primary key auto_increment, 
  lat decimal(8,5) not null, 
  lng decimal(8,5) not null,
  `timestamp` timestamp not null
);

insert into YourLatiLongiTudesTable
(lat, lng, `timestamp`) values                              
('23.21465', '84.37856', '2016-01-01 00:00'),
('23.21465', '84.37856', '2016-01-01 00:03'),
('23.21584', '84.37877', '2016-01-01 00:06'),
('23.21584', '84.37877', '2016-01-01 00:09'),
('23.21465', '84.37856', '2016-01-01 00:12'),
('23.21465', '84.37856', '2016-01-01 00:15'),
('23.21465', '84.37856', '2016-01-01 00:18'),
('23.21465', '84.37856', '2016-01-01 00:21')
;

查询 MySql 5.7

--
-- Using variables
-- First calculate a rank on descending date
-- Then calculate a rownum based on the rank
--
SELECT id, lat, lng, `timestamp`
-- , rnk, rnk_rn
FROM
(
  SELECT *
  , CASE 
    WHEN rnk != @prev_rnk
     AND @prev_rnk := rnk
    THEN @rn := 1
    WHEN @prev_rnk := rnk
    THEN @rn := @rn + 1
    END AS rnk_rn
  FROM
  (
    SELECT t.*
    , CASE 
      WHEN lat != @prev_lat
       AND @prev_lat := lat 
      THEN @rnk := @rnk + 1
      WHEN @prev_lat := lat 
      THEN @rnk
      END AS rnk
    FROM YourLatiLongiTudesTable t
    CROSS JOIN (SELECT @rnk:=0, @prev_lat:=0) v
    ORDER BY `timestamp` desc, id desc
  ) q1
  CROSS JOIN (SELECT @rn:=0, @prev_rnk := 0) v
  ORDER BY `timestamp`, id
) q2
WHERE rnk = 1 
  AND rnk_rn = 1
;

编号 | 纬度 | 液化天然气 | 时间戳          
-: | --------: | --------: | :-----------------
 5 | 23.21465 | 84.37856 | 2016-01-01 00:12:00

查询 MySql 8.0+（以及其他支持窗口函数的 RDBMS）：

--
-- Using window functions
-- First calculate a rank by summing changes.
-- Then calculate a rownum based on the rank
--
SELECT id, lat, lng, `timestamp`
-- , rnk, rnk_rn
FROM
(
  SELECT *
  , ROW_NUMBER() OVER (PARTITION BY date(`timestamp`), rnk ORDER BY `timestamp`, id) AS rnk_rn
  FROM 
  (
    SELECT *
    , SUM(IF(lat=prev_lat,0,1)) OVER (PARTITION BY date(`timestamp`) ORDER BY `timestamp` DESC, id DESC) AS rnk
      FROM 
      (
        SELECT *
        , LAG(lat) OVER (PARTITION BY date(`timestamp`) ORDER BY `timestamp` DESC, id DESC) AS prev_lat
        FROM YourLatiLongiTudesTable t
      ) q1
   ) q2
) q3
WHERE rnk = 1
  AND rnk_rn = 1
ORDER BY `timestamp`, id;

编号 | 纬度 | 液化天然气 | 时间戳          
-: | --------: | --------: | :-----------------
 5 | 23.21465 | 84.37856 | 2016-01-01 00:12:00

db<>在这里摆弄