项目中，有一个小时获取一个数据的任务，获取具体的值后，需要存在数据库中。但是长期累计后，需要检查有没有做到一个小时记录一次。

比如这样的数据：

我们需要统计，有没有遗漏的遗留，是不是有遗漏了一个小时的，

select julianday(b.UsageStartDate)-julianday(a.UsageStartDate),a.UsageStartDate,a.UsageEndDate from (

select * from (select Row_Number() over ( order by UsageStartDate ) as RN , * from  "north1-nx-billing" where   LinkedAccountId = '621933488636' and ResourceId = 'i-0f2bf6bb9eab43dd5' and UsageType like '%HeavyUsage:m5.large' ORDER BY UsageStartDate

) where rn%2=0 ) a

inner join

(

select * from (select Row_Number() over ( order by UsageStartDate ) as RN , * from  "north1-nx-billing" where   LinkedAccountId = '621933488636' and ResourceId = 'i-0f2bf6bb9eab43dd5' and UsageType like '%HeavyUsage:m5.large'  ORDER BY UsageStartDate

) where rn%2<>0 ) b on a.rn=b.rn-1

查询结果：

这样就和容易查询出来，那些地方不是一个小时一次了。

知识点： 使用sqlite的窗口函数

select 自己要查询的字段 from (

select * from (select Row_Number() over ( order by UsageStartDate ) as RN , + 自己的查询sql

) where rn%2=0 ) a

inner join

(

select * from (select Row_Number() over ( order by UsageStartDate ) as RN , + 和上面一样的查询SQL

) where rn%2<>0 ) b on a.rn=b.rn-1

高性能云服务器

精品线路独享带宽，毫秒延迟，年中盛惠 1 折起