Sql语句实现不同记录同一属性列的差值计算

我不是女神ヾ 2021-06-26 16:06 365阅读 0赞

所使用的表的具体结构如下图所示  
![在这里插入图片描述][2018110810134693.png]

Table中主键是（`plateNumber+currentTime`）

要实现的查询是：

给定车牌号和查询的时间区间，查询给定的时间区间内所包含记录的`currentTime`的差值，并计算`AverageSpeed`和该差值的乘积，求这段时间内的最高速度（`HighestSpeed`）,并按照`type`值得不同进行划分。–>(`type`值只有0和1两个值)

主要思路是，首先能够得出的是相同type类型下同一个车牌号（也即同一车辆）在给定的时间区间内的`currentTime`的差值，比如按照`currentTime`排序号，相邻两条记录`currentTime`的差值，得出这个以后，其余的都可以通过聚合函数得出。

我们以车牌号为京A111111为例，设计如下图所示的测试用例。  
![在这里插入图片描述][2018110810143737.png]

可以看到车牌号为`京A111111`的车辆总共有6条记录，其中`type`为0的有两条，`type`为1的有4条,

我们首先计算时间的差值,sql语句书写如下：

SELECT a.platenumber, 
           a.currenttime, 
           a.type, 
           a.averagespeed, 
           a.highestspeed, 
           currenttime - (SELECT currenttime 
                          FROM   carmultispeedinfo 
                          WHERE  platenumber = a.platenumber 
                                 AND type = a.type 
                                 AND currenttime < a.currenttime 
                          ORDER  BY currenttime DESC 
                          LIMIT  1)AS timediff 
    FROM   carmultispeedinfo a

通过`navicat`可以看到如下图所示的查询结果：

通过核查`timediff`的值是正确的，这样之后就可以在这个基础上添加内容了。

完整的sql语句如下：

SELECT Sum(aa.averagespeed * aa.timediff) AS milesdiff, 
           Max(aa.highestspeed)               AS HighestSpeed, 
           Sum(aa.timediff)                   AS timediff, 
           aa.type 
    FROM   (SELECT a.platenumber, 
                   a.currenttime, 
                   a.type, 
                   a.averagespeed, 
                   a.highestspeed, 
                   currenttime - (SELECT currenttime 
                                  FROM   carmultispeedinfo 
                                  WHERE  platenumber = a.platenumber 
                                         AND type = a.type 
                                         AND currenttime < a.currenttime 
                                  ORDER  BY currenttime DESC 
                                  LIMIT  1) AS timediff 
            FROM   carmultispeedinfo a)aa 
    WHERE  aa.platenumber = '京A111111' 
           AND aa.currenttime >= 1521790124670 
           AND aa.currenttime <= 1521790125685 
    GROUP  BY aa.type

显示结果如下：  
![在这里插入图片描述][20181108101751307.png]

经过核对，是我们想要得出的结果。之后将`sql`对应替换到`mybatis`的`mapper`文件中就可以了。<记录一下，备忘>将来有更深入的理解之后会继续更新，谢谢大家。

[2018110810134693.png]: /images/20210626/f66c5d47a7a841689174e7ea79b7ed4e.png
[2018110810143737.png]: /images/20210626/f02712a2b85b4ce3ab980af073a8b72f.png
[20181108101751307.png]: /images/20210626/cbdee46d900b417dabd270f55de0570f.png