设计设备版本变迁

Posted on 2025-11-21 • 1811 words • 4 minute read

这阵子忙完了，有些之前想做的东西做了，今天写了一天的代码，实现后推送上线，叉会腰，写点文章记录一下吧。

要做什么

之前有个需求，希望统计用户设备固件版本升级变迁过程。

每日设备固件版本记录快照可以统计版本的分布，如昨天有10台设备固件在版本A上，5台设备固件在版本B上，今天再看报表时有8台设备固件在版本A上，12台设备固件在版本B上。

但无法记录设备固件版本变动过程，如有多少设备固件从版本A升级到了版本B，迁移的这些设备中是否有特定的一台设备。

数据设计

假定每次检查设备时，将设备信息传送至消息队列，附带如下消息：

{"sn":"S01","pid":"P01","v":"01.01"}

其中，sn为设备序列号，pid为设备产品型号，v为设备固件版本。视消息到达数据中心时刻为数据时间，围绕这些数据设计Clickhouse数据表：

CREATE TABLE device_info
(
    RecordDate      Date32,
    EventTime       DateTime64,
    Version         Int32,
    SN              String,
    PID             LowCardinality(String),
    FirmwareVersion LowCardinality(String)
) ENGINE = ReplacingMergeTree(Version)
      PARTITION BY toYYYYMM(RecordDate)
      ORDER BY (RecordDate, SN);

假定设备固件为01.01，当天升级到01.02，上报了两条消息，服务器只承认当天第一条消息有效，那么服务器可以日期与SN组成当天数据的唯一键，约定数据版本为 (86400 - 当天时间的第N秒)，以版本最大的数据为有效数据，数据库表会自动帮我们把多余的数据全滤掉，查询有效数据只需在SQL语句最后添加FINAL关键字即可。

SELECT * FROM device_info FINAL

设计快照表

在原始数据上做的手脚完毕，接着我们需要设计存储和检索这些数据的数据表。

假定有如下数据：

RecordDate	SN	PID	FirmwareVersion
2025-11-20	S01	P01	01.01
2025-11-20	S02	P01	01.01
2025-11-21	S01	P01	01.02
2025-11-21	S02	P01	01.01

如上数据表明，设备S01经历了一次升级，固件版本从01.01升级到01.02，设备S02仍然是01.01。

根据数据差异，这里可以定义三个快照，快照的数据近似于：

- 2025-11-20 PID为P01 固件版本为01.01的设备有：S01, S02
- 2025-11-21 PID为P01 固件版本为01.01的设备有：S02
- 2025-11-21 PID为P01 固件版本为01.02的设备有：S01

按照这个维度，设计表：

CREATE TABLE device_snapshot_bitmap
(
    RecordDate      Date32,
    PID             LowCardinality(String),
    FirmwareVersion LowCardinality(String),
    DeviceBitmap    AggregateFunction(groupBitmap, UInt64)
) ENGINE = MergeTree
    PARTITION BY toYYYYMM(RecordDate)
    ORDER BY (RecordDate, PID);

则可以通过如下SQL从device_info表中抽出数据放入该表：

INSERT INTO device_snapshot_bitmap(RecordDate, PID, FirmwareVersion, DeviceBitmap)
SELECT RecordDate, PID, FirmwareVersion, bitmapBuild(groupArray(toUInt64(SN)))
FROM (SELECT * FROM device_info WHERE RecordDate = toDate32(#{date}))
GROUP BY RecordDate, PID, FirmwareVersion

表数据量仅为日期 + PID + 固件版本的笛卡尔积，可以根据PID和固件版本来推算出每年该表数据上限。

查找变迁

当设备仅存在两个固件版本时，要比较起来挺简单的。

比如类似上述的情况，P01只有01.01和01.02这两个版本，升级路径只有这一条：01.01 -> 01.02，只需要比较今天与昨天的设备差异，那么只需要取昨天01.01版本的PID设备与今天01.02版本的PID设备两个集合的交集则立即得到结果：S01升级了固件版本，从01.01升级到01.02。

固件版本非常多的时候，就会有很多升级情况，如有01.01、01.02、01.03、01.04、01.05这5个版本，则会有可能存在的升级路径就非常多：

01.01 -> 01.02
01.01 -> 01.03
01.01 -> 01.04
01.01 -> 01.05
01.02 -> 01.03
01.02 -> 01.04
01.02 -> 01.05
01.03 -> 01.04
01.03 -> 01.05
01.04 -> 01.05

在查找版本变迁时，先找出所有的产品与固件版本的组合：

SELECT DISTINCT ON (PID, FirmwareVersion) PID, FirmwareVersion
FROM device_snapshot_bitmap
WHERE RecordDate = toDate32(#{date}) GROUP BY PID, FirmwareVersion

用代码找出所有的固件升级组合：

val versionComparator = object : Comparator<String> {
    override fun compare(o1: String, o2: String): Int = s2v(o1) - s2v(o2)
    private fun s2v(s: String): Int = StringUtils.split(s, ".").let { it[0]!!.toInt() * 100 + it[1]!!.toInt()
}

val pidMap = hashMapOf<String, ArrayList<String>>()
    deviceInfoMapper.findPidAndFv(date).forEach { i ->
        pidMap.getOrElse(i.pid) { arrayListOf() }
            .apply { add(i.firmwareVersion) }
            .also { pidMap[i.pid] = it }
}

pidMap.forEach { (pid, fvList) ->
    val vPair = mutableListOf<Pair<String, String>>()
    fvList.sortedWith(versionComparator).let {
        (0..<it.size).forEach { i ->
            (i + 1..<it.size).forEach { j ->
                vPair.add(Pair(it[i], it[j]))
            }
        }
    }
    // process vPair
}

最后，我们能在vPair中，拿到当前PID中的所有固件升级路径。

对vPair中的每一种固件升级路径都尝试进行处理，则就可以将所有的变迁全部查询出来，此时再定义一张变迁结果表，用于存储每日每种产品每次版本变迁的记录：

CREATE TABLE device_snapshot_diff
(
    RecordDate   Date32,
    PID          LowCardinality(String),
    FromVersion  LowCardinality(String),
    ToVersion    LowCardinality(String),
    DeviceBitmap AggregateFunction(groupBitmap, UInt64)
) ENGINE = MergeTree
    PARTITION BY toYYYYMM(RecordDate)
    ORDER BY (RecordDate, PID);

FromVersion + ToVersion表明设备升级记录为FromVersion -> ToVersion，查询变迁时需要拿前一天的FromVersion快照与今天的ToVersion快照对比。

写入变迁的SQL则为：

INSERT INTO device_snapshot_diff(RecordDate, PID, FromVersion, ToVersion, DeviceBitmap)
SELECT * FROM (SELECT toDate32(#{today}), #{pid}, #{fromVersion}, #{toVersion},
                 bitmapAnd((SELECT DeviceBitmap
                            FROM device_snapshot_bitmap
                            WHERE RecordDate = toDate32(#{yesterday})
                                AND FirmwareVersion = #{fromVersion}),
                           (SELECT DeviceBitmap
                            FROM device_snapshot_bitmap
                            WHERE RecordDate = toDate32(#{today})
                                AND FirmwareVersion = #{toVersion})))

图表展示

总不能每次要看结果的时候就写个SQL查询，用点主流的工具设计个Flow Diagram会更直观一些。

此时，device_stat_diff表中的数据大概为这样：

RecordDate	PID	FromVersion	ToVersion	DeviceBitmap
2025-11-21	P01	01.01	01.05	bitmap(S01)
2025-11-21	P01	01.02	01.05	bitmap(S02, S03)
2025-11-21	P01	01.03	01.05	bitmap(S04, S05)

以FromVersion与ToVersion为关键点，查询SQL语句为：

SELECT FromVersion, ToVersion, length(bitmapToArray(DeviceBitmap))
FROM device_snapshot_diff
WHERE PID = 'P01' AND RecordDate = toDate32('2025-11-21');

扔到Flow Diagram里展示如图：

flow diagram

结语

今天顺手把框架版本升级到了Spring Boot 4，总共写了6小时的代码，其中含40分钟的单元测试，非常简单，你也可以试一试。

Timbo Site