mysql - 如何只获取不同范围的最新记录？

Question

我正在看一个案例，其中我们有许多装满液体的水箱。测量液体量并将信息存储在数据库中。此更新每 5 分钟进行一次。这里存储了以下信息:

• 坦克编号
• 填充级别
• 时间戳

每个水箱都归类为以下“填充水平”范围之一:

• 范围 A:0 - 40%
• 范围 B:40 - 75%
• 范围 C:75 - 100%

我在每个范围内计算每个 tankId 的事件数量。

SELECT sum(
        CASE
            WHEN filllevel>=0 and filllevel<40 
            THEN 1
            ELSE 0
        END) AS 'Range A',
        sum(
        CASE
            WHEN filllevel>=40 and filllevel<=79 
            THEN 1
            ELSE 0
        END) AS 'Range B',
        sum(
        CASE
            WHEN filllevel>79 and filllevel<=100 
            THEN 1
            ELSE 0
        END) AS 'Range C'
FROM TEST ;

挑战是只计算每个坦克的最新记录。因此，对于每个 tankId，只有一个计数(并且必须是具有最新时间戳的记录)。

对于以下数据:

insert into tank_db1.`TEST` (ts, tankId, fill_level) values 
('2017-08-11 03:31:18', 'tank1', 10),
('2017-08-11 03:41:18', 'tank1', 45),
('2017-08-11 03:51:18', 'tank1', 95),
('2017-08-11 03:31:18', 'tank2', 20),
('2017-08-11 03:41:18', 'tank2', 30),
('2017-08-11 03:51:18', 'tank2', 80),
('2017-08-11 03:31:18', 'tank3', 30),
('2017-08-11 03:41:18', 'tank3', 45),
('2017-08-11 03:51:18', 'tank4', 55);

我希望结果是(只计算每个 tankId 具有最新时间戳的记录):

- RANGE A: 0
- RANGE B: 1 (tankdId 3)
- RANGE C: 2 (tankId 1 and tankId2)

如果您是专家，可能很容易，但对我来说，真的很难看出有哪些选择。

谢谢

Answer 1

您可以使用以下查询获取每组最新的时间戳值:

select tankId, max(ts) as max_ts
from test
group by tankId;

输出:

    tankId  max_ts
--------------------------------
1   tank1   11.08.2017 03:51:18
2   tank2   11.08.2017 03:51:18
3   tank3   11.08.2017 03:41:18
4   tank4   11.08.2017 03:51:18

将上述查询用作派生表，您可以提取每个组的最新 fill_level 值。这样您就可以应用计算每个范围级别的逻辑:

select sum(
    CASE
        WHEN t1.fill_level>=0 and t1.fill_level<40 
        THEN 1
        ELSE 0
    END) AS 'Range A',
    sum(
    CASE
        WHEN t1.fill_level>=40 and t1.fill_level<=79 
        THEN 1
        ELSE 0
    END) AS 'Range B',
    sum(
    CASE
        WHEN t1.fill_level>79 and t1.fill_level<=100 
        THEN 1
        ELSE 0
    END) AS 'Range C'
from test as t1
join (
   select tankId, max(ts) as max_ts
   from test
   group by tankId
) as t2 on t1.tankId = t2.tankId and t1.ts = t2.max_ts

输出:

    Range A Range B Range C
---------------------------
1   0       2       2

Demo here