Mysql验证数据的常用程序

Mysql验证数据的常用SQL

问题一

1.1问题描述

如果有一个设备，其变动关系呈之字形，也就是说，其上一条记录的start_date = 该设备的下一条记录的end_date。但是如何验证这个start_date与end_date直接的变化是连续的呢？请看下面一条数据：

mysql> select pit_key,device_key,device_id,start_date,end_date from pit_device_shop_temp where end_date = '9999-12-31' limit 1;
+---------+------------+--------------+---------------------+---------------------+
| pit_key | device_key | device_id    | start_date          | end_date            |
+---------+------------+--------------+---------------------+---------------------+
|       1 |     7      | 00S92HEA2SD1 | 2018-05-01 00:00:00 | 9999-12-31 00:00:00 |
+---------+------------+--------------+---------------------+---------------------+
1 row in set (0.00 sec)

查询这个device_key所对应的所有device的记录 ，如下所示：

mysql>  select pit_key,device_key,device_id,start_date,end_date from pit_device_shop_temp where device_key = 7;
+---------+------------+--------------+---------------------+---------------------+
| pit_key | device_key | device_id    | start_date          | end_date            |
+---------+------------+--------------+---------------------+---------------------+
|  586454 |          7 | 74A34AA1D5A7 | 2018-07-04 00:00:00 | 2018-08-28 00:00:00 |
|  713795 |          7 | 74A34AA1D5A7 | 2018-08-28 00:00:00 | 9999-12-31 00:00:00 |
+---------+------------+--------------+---------------------+---------------------+
2 rows in set (0.66 sec)

现在我们需要检验的就是这个2018-07-04 00:00:00 -> 2018-08-28 00:00:00 以及2018-08-28 00:00:00 -> 9999-12-31 00:00:00这个过程是否是连续的？

1.2 解决方案

测试思路大致如下：

• step 1：求出某设备最小日期和最大日期之间的差距
• step 2：求出设备每行的日期变化差之和
• 比较step 1和step 2是否相等
  得到如下sql：

select device_id
from pit_device_shop_temp 
group by device_id
having timestampdiff(day,min(start_date),max(end_date)) <> (sum(timestampdiff(day,start_date,end_date)));

1.3 核对结果

如果根据上述的SQL得到的值是Empty Set，那么表示变动的过程是正确的。