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

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

这是一个创建于 1389 天前的主题，其中的信息可能已经有所发展或是发生改变。

这个可以通过 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

第 1 条附言 · 2021-03-09 10:53:58 +08:00

决定建个新表了遍历下现有数据插入新表，以后数据增加时判断完插入新表

第 2 条附言 · 2021-03-09 10:54:08 +08:00

谢谢大家

第 3 条附言 · 2021-03-10 10:48:03 +08:00

最后解决方案是计算每个设备巡检的间隔天数小于 7 天不作处理，大于 7 天除以 7 取整，最后汇总起来就是缺少的次数

DECLARE
@date1 DATETIME
,@date2 DATETIME
,@days INT
SET @date1='2021-01-01'
SET @date2='2021-03-01'
SET @days=7
SELECT b.*,c.DeviceClassID,SUM(a.qscount) qsdays,DATEDIFF(DAY,@date1,@date2)/@days alldays FROM (

SELECT *,CASE WHEN DATEDIFF(DAY,tt.checkTime,tt.CreateTime)<=@days THEN 0 ELSE DATEDIFF(DAY,tt.checkTime,tt.CreateTime)/@days END qscount FROM
(SELECT t1.*
,CASE WHEN ISNULL((SELECT TOP 1 t2.CreateTime FROM dbo.xj_Check t2 WHERE t1.DeviceID=t2.DeviceID AND t2.ID<t1.ID ORDER BY t2.ID desc),t3.CreateTime)<@date1 THEN @date1
ELSE ISNULL((SELECT TOP 1 t2.CreateTime FROM dbo.xj_Check t2 WHERE t1.DeviceID=t2.DeviceID AND t2.ID<t1.ID ORDER BY t2.ID desc),t3.CreateTime) END checkTime
FROM xj_Check t1
INNER JOIN dbo.tb_DeviceList t3 ON t1.DeviceID=t3.DeviceID
WHERE t1.CreateTime BETWEEN @date1 AND @date2) tt
) a
INNER JOIN tb_DeviceList b ON a.DeviceID=b.DeviceID
INNER JOIN dbo.tb_DeviceType c ON b.DeviceType=c.ID
WHERE c.DeviceClassID=5
GROUP BY b.DeviceID,
b.DeviceName,
b.DeviceType,
b.DeviceTypeName,
b.Loaction,
b.CreateTime,
b.CreateUser,
b.MakerID,
b.MakerName,
b.IsEnable,
b.Admin,c.DeviceClassID
ORDER BY b.DeviceType,b.DeviceName

2019-03-18

admin

管理员

电气

26 条回复 • 2021-03-10 10:41:34 +08:00

awanganddong

2021-03-09 09:08:30 +08:00

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

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

c6h6benzene

2021-03-09 09:12:03 +08:00 via iPhone

是要哪个字段不重复…？

wmwgijol28

2021-03-09 09:33:17 +08:00 via iPhone

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

bwd1991

2021-03-09 09:33:48 +08:00

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

bwd1991

2021-03-09 09:35:00 +08:00

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

ccoming

2021-03-09 09:35:43 +08:00

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

bwd1991

2021-03-09 09:36:14 +08:00

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

bwd1991

2021-03-09 09:36:49 +08:00

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

c6h6benzene

2021-03-09 09:42:09 +08:00 via iPhone

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

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

bwd1991

2021-03-09 09:48:19 +08:00

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

c6h6benzene

2021-03-09 09:57:36 +08:00 via iPhone

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

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

djj510620510

2021-03-09 10:06:40 +08:00

-- 把 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

2021-03-09 10:07:55 +08:00

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

2021-03-09 10:11:33 +08:00

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

bwd1991

2021-03-09 10:12:54 +08:00

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

djj510620510

2021-03-09 10:15:11 +08:00

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

Marstin

2021-03-09 10:18:31 +08:00

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

bwd1991

2021-03-09 10:26:37 +08:00

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

dswyzx

2021-03-09 10:50:23 +08:00

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

TimePPT

2021-03-09 11:15:46 +08:00

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

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

TimePPT

2021-03-09 11:16:38 +08:00

@TimePPT row_number() over (partition by deviceid order by dt)

TimePPT

2021-03-09 11:19:12 +08:00

@TimePPT 这个思路的前提是先按天为周期去重

TimePPT

2021-03-09 12:57:37 +08:00 via Android

@TimePPT 囧，又想了下不太对，这个如果日期不连续还不能直接求

zhuangjia

2021-03-09 13:44:49 +08:00

借鉴了 MySQL 统计连续登录天数的思路和 sql，主要是这篇： https://zhuanlan.zhihu.com/p/32613190

生成测试数据

```
# 创建测试表
CREATE TABLE `tmp_test_lianxu_3` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`DeviceID` int(11) DEFAULT NULL,
`CreateTime` datetime DEFAULT NULL COMMENT '登录时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

# 生成测试数据

INSERT INTO `test`.`tmp_test_lianxu_3` (`id`, `DeviceID`, `CreateTime`) VALUES ('1', '1', '2014-01-01 21:00:00');
INSERT INTO `test`.`tmp_test_lianxu_3` (`id`, `DeviceID`, `CreateTime`) VALUES ('2', '1', '2014-01-02 15:37:57');
INSERT INTO `test`.`tmp_test_lianxu_3` (`id`, `DeviceID`, `CreateTime`) VALUES ('3', '2', '2014-01-01 09:00:00');
INSERT INTO `test`.`tmp_test_lianxu_3` (`id`, `DeviceID`, `CreateTime`) VALUES ('4', '2', '2014-01-02 09:00:00');
INSERT INTO `test`.`tmp_test_lianxu_3` (`id`, `DeviceID`, `CreateTime`) VALUES ('5', '1', '2014-01-04 10:00:00');
INSERT INTO `test`.`tmp_test_lianxu_3` (`id`, `DeviceID`, `CreateTime`) VALUES ('6', '1', '2014-01-05 12:00:00');
INSERT INTO `test`.`tmp_test_lianxu_3` (`id`, `DeviceID`, `CreateTime`) VALUES ('7', '2', '2014-01-10 00:00:00');
INSERT INTO `test`.`tmp_test_lianxu_3` (`id`, `DeviceID`, `CreateTime`) VALUES ('8', '2', '2014-01-11 13:00:00');
INSERT INTO `test`.`tmp_test_lianxu_3` (`id`, `DeviceID`, `CreateTime`) VALUES ('10', '2', '2014-01-12 12:00:00');
INSERT INTO `test`.`tmp_test_lianxu_3` (`id`, `DeviceID`, `CreateTime`) VALUES ('11', '1', '2014-01-08 06:00:00');
INSERT INTO `test`.`tmp_test_lianxu_3` (`id`, `DeviceID`, `CreateTime`) VALUES ('12', '2', '2014-01-11 21:00:00');
INSERT INTO `test`.`tmp_test_lianxu_3` (`id`, `DeviceID`, `CreateTime`) VALUES ('13', '2', '2014-01-15 21:00:00');
INSERT INTO `test`.`tmp_test_lianxu_3` (`id`, `DeviceID`, `CreateTime`) VALUES ('14', '2', '2014-01-17 21:00:00');
INSERT INTO `test`.`tmp_test_lianxu_3` (`id`, `DeviceID`, `CreateTime`) VALUES ('15', '2', '2014-01-19 21:00:00');
INSERT INTO `test`.`tmp_test_lianxu_3` (`id`, `DeviceID`, `CreateTime`) VALUES ('16', '2', '2014-01-21 21:00:00');
INSERT INTO `test`.`tmp_test_lianxu_3` (`id`, `DeviceID`, `CreateTime`) VALUES ('17', '2', '2014-01-26 21:00:00');
INSERT INTO `test`.`tmp_test_lianxu_3` (`id`, `DeviceID`, `CreateTime`) VALUES ('18', '2', '2014-01-28 21:00:00');
INSERT INTO `test`.`tmp_test_lianxu_3` (`id`, `DeviceID`, `CreateTime`) VALUES ('19', '2', '2014-01-30 21:00:00');
INSERT INTO `test`.`tmp_test_lianxu_3` (`id`, `DeviceID`, `CreateTime`) VALUES ('20', '2', '2014-02-16 21:00:00');
INSERT INTO `test`.`tmp_test_lianxu_3` (`id`, `DeviceID`, `CreateTime`) VALUES ('21', '2', '2014-02-13 21:00:00');

```

sql 语句如下

```
SELECT
DeviceID,
# 检查每次巡检记录，如果最近一次巡检时间是 7 天前，那么有效巡检天数+1 ；如果是 7 天内，有效巡检天数+0 ；否则设置为 1
@cont_day := ( CASE
WHEN ( DATEDIFF(login_dt, @real_last_dt) > 7 ) THEN (@cont_day + 1)
WHEN ( DATEDIFF(login_dt, @real_last_dt) <= 7 ) THEN (@cont_day + 0)
ELSE 1
END
) AS days,
@last_did := DeviceID,
# 判断当前巡检是否为有效巡检（间隔 7 天），是则更新最后有效巡检时间；
# 如果不是则判断是否存在最后有效巡检时间，存在则不变，不存在则设置未当前巡检时间
@real_last_dt := ( CASE
WHEN ( DATEDIFF(login_dt, @real_last_dt) > 7 ) THEN login_dt
WHEN ( @real_last_dt > 0 ) THEN @real_last_dt
ELSE @last_dt
END
) as last_dt,
@last_dt := login_dt
FROM
( SELECT DeviceID, DATE(CreateTime) AS login_dt FROM tmp_test_lianxu_3
WHERE DeviceID=2 AND CreateTime BETWEEN "2014-01-01" AND "2014-01-31"
ORDER BY DeviceID, CreateTime ) AS t,
( SELECT @last_did := '', @last_dt := '', @real_last_dt := '', @cont_day := 0 ) AS t1

```

执行后结果如下：（其中 days 即为有效巡检天数）

DeviceID|days|@last_did := DeviceID|last_dt|@last_dt := login_dt
---|---|---|---|---
2|1|2||2014/1/1
2|1|2|2014/1/1|2014/1/2
2|2|2|2014/1/10|2014/1/10
2|2|2|2014/1/10|2014/1/11
2|2|2|2014/1/10|2014/1/11
2|2|2|2014/1/10|2014/1/12
2|2|2|2014/1/10|2014/1/15
2|2|2|2014/1/10|2014/1/17
2|3|2|2014/1/19|2014/1/19
2|3|2|2014/1/19|2014/1/21
2|3|2|2014/1/19|2014/1/26
2|4|2|2014/1/28|2014/1/28
2|4|2|2014/1/28|2014/1/30

zhuangjia

2021-03-09 13:47:46 +08:00

@zhuangjia 尴尬，忘了回复不支持 markdown，这个格式看着愁人

bwd1991

2021-03-10 10:41:34 +08:00

@zhuangjia 哈哈哈辛苦了最后解决方案是计算每个设备巡检的间隔天数

DECLARE
@date1 DATETIME
,@date2 DATETIME
,@days INT
SET @date1='2021-01-01'
SET @date2='2021-03-01'
SET @days=7
SELECT b.*,c.DeviceClassID,SUM(a.qscount) qsdays,DATEDIFF(DAY,@date1,@date2)/@days alldays FROM (

SELECT *,CASE WHEN DATEDIFF(DAY,tt.checkTime,tt.CreateTime)<=@days THEN 0 ELSE DATEDIFF(DAY,tt.checkTime,tt.CreateTime)/@days END qscount FROM
(SELECT t1.*
,CASE WHEN ISNULL((SELECT TOP 1 t2.CreateTime FROM dbo.xj_Check t2 WHERE t1.DeviceID=t2.DeviceID AND t2.ID<t1.ID ORDER BY t2.ID desc),t3.CreateTime)<@date1 THEN @date1
ELSE ISNULL((SELECT TOP 1 t2.CreateTime FROM dbo.xj_Check t2 WHERE t1.DeviceID=t2.DeviceID AND t2.ID<t1.ID ORDER BY t2.ID desc),t3.CreateTime) END checkTime
FROM xj_Check t1
INNER JOIN dbo.tb_DeviceList t3 ON t1.DeviceID=t3.DeviceID
WHERE t1.CreateTime BETWEEN @date1 AND @date2) tt
) a
INNER JOIN tb_DeviceList b ON a.DeviceID=b.DeviceID
INNER JOIN dbo.tb_DeviceType c ON b.DeviceType=c.ID
WHERE c.DeviceClassID=5
GROUP BY b.DeviceID,
b.DeviceName,
b.DeviceType,
b.DeviceTypeName,
b.Loaction,
b.CreateTime,
b.CreateUser,
b.MakerID,
b.MakerName,
b.IsEnable,
b.Admin,c.DeviceClassID
ORDER BY b.DeviceType,b.DeviceName