sql 统计一段时间内巡检次数 7 天内的不重复计算

[bwd1991]

这个可以通过 sql 实现吗？感觉好困难
现在巡检记录是个单表结构
ID DeviceID DeviceName CreateUser CreateTime Remark CreateUserCode Dept_ID

19 4 电气箱 1 管理员 2019-03-18 09:18:34.997 admin 8
20 4 电气箱 1 管理员 2019-03-18 09:20:02.430 admin 8
21 4 电气箱 1 管理员 2019-03-18 10:13:19.313 admin 8
22 4 电气箱 1 管理员 2019-03-18 11:19:11.097 admin 8

[awanganddong]

另外建一张表，专门存不重复数据。

可以实时同步写，也可以走队列定时刷数据到这张新表。

[c6h6benzene]

是要哪个字段不重复…？

[wmwgijol28]

需求不是很明确
如果是要统计不同设备每天巡检 可以把 createtime 格式化成年月日 再 group by deviceID，年月日

[bwd1991]

@c6h6benzene 需要 DeviceID 和对应的次数 CreateTime 在七天内的算 1 次

[bwd1991]

@wmwgijol28 设备需要 7 天巡检一次 设定一个周期 比如两个月 就是需要 60/7 次 然后需要计算实际巡检次数 7 天内重复的只计算一次。。

[ccoming]

@awanganddong 感觉楼主表达不够清晰。需求应该是统计某段时间内的巡检次数，但两次间隔 7 天内的巡检，只算一次？

[bwd1991]

@awanganddong 想用 sql 实现一下统计 。。 没办法的话只能这么搞了

[bwd1991]

@ccoming 对 所以是。。统计一段时间内巡检次数，7 天内的不重复计算

[c6h6benzene]

@bwd1991 所以针对上面的示例数据，结果就是 DeviceID/DeviceName/CheckedTimes：4/电器箱 /1 ？（假设今天 19/3/20 ）

那基本上就是只要 7 天内有没有检查过的 flag 嘛。

[bwd1991]

@c6h6benzene 对啊。。问题是 sql 怎么写
一条一条数据遍历简单 不知道怎么用 sql 处理

[c6h6benzene]

@bwd1991 我的思路是这样，不一定对：

还得有一个日期的维度表 dimDate 里面就是所有的日期，关联这张检查表（ on CreateTime 在 dimDate.Date 7 天内），然后 GROUP BY Date, DeviceID 来 COUNT(ID)得到对于每一天 7 天内的检查次数。之后再处理这个汇总表。

[djj510620510]

-- 把 1970-01-01 换成你想要的日期，把 your_table_name 换成你的表名
select
week_no
,DeviceID
,DeviceName
,CreateUserCode
,Dept_ID
,count(1) as times
from(
select
*
,cast(datediff(CreateTime, date_format('1970-01-01', '%Y%m%d')) / 7 as SIGNED int) as week_no
from your_table_name
)
group by
week_no
,DeviceID
, DeviceName
, CreateUserCode
, Dept_ID
;

[Marstin]

7 天是指每 7 天，还是任意两个相同 DeviceID 的数据时间间隔需要超过 7 天呢
比如 14 天内
1 2021/1/1
2 2021/1/3
1 2021/1/7
2 2021/1/9

应该取
1 2021/1/1
2 2021/1/3
2 2021/1/9
还是
1 2021/1/1
2 2021/1/3

[bwd1991]

@Marstin 取第一个 7 天
比如 1.1 1.2 1.8 1.10
取 1.1 1.8

[bwd1991]

@djj510620510 这个是取单个的吗。。我是汇总计算

[djj510620510]

@bwd1991 你改一下 group 的字段，然后 count(distinct DeviceID)就行了，按 week_no 来 group

[Marstin]

@bwd1991 那就很好办啊，先做一次查询，把时间减掉开始时间后除以七天然后取整，再根据这个字段和设备 ID 一起分组统计就完事了

[bwd1991]

@Marstin 不行的。。。 时间间隔是需要和上一个时间去判断 不能按周来 按周来可能是这周天 下周一

[dswyzx]

每七天跑一个定时任务,将不同 DeviceID 的最后一次巡检时间写入另一张单独表,与表上此 DeviceID 的巡检时间对比,不存在的写入,七天内的标志位 1,间隔超过七天或此时间与当前时间超过七天标志位 0,然后推送 0 的报警
select deviceid,max(createtime) as lastWacth from t group by deviceid
用业务逻辑解决问题呗,如果报警要及时,定时任务改成一天跑一次也不是不可以

[TimePPT]

提供个思路看行不行
窗口函数，拿 deviceid 开窗, 日期排序，取 row_number(), 最大值地板除以 7，如果结果为 0 则记录为 1

row_number() over (patition by deviceid order by dt)