V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
bwd1991
V2EX  ›  数据库

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

  •  
  •   bwd1991 · 2021-03-09 09:02:32 +08:00 · 2059 次点击
    这是一个创建于 1116 天前的主题,其中的信息可能已经有所发展或是发生改变。
    这个可以通过 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
    26 条回复    2021-03-10 10:41:34 +08:00
    awanganddong
        1
    awanganddong  
       2021-03-09 09:08:30 +08:00
    另外建一张表,专门存不重复数据。

    可以实时同步写,也可以走队列定时刷数据到这张新表。
    c6h6benzene
        2
    c6h6benzene  
       2021-03-09 09:12:03 +08:00 via iPhone
    是要哪个字段不重复…?
    wmwgijol28
        3
    wmwgijol28  
       2021-03-09 09:33:17 +08:00 via iPhone
    需求不是很明确
    如果是要统计不同设备每天巡检 可以把 createtime 格式化成年月日 再 group by deviceID,年月日
    bwd1991
        4
    bwd1991  
    OP
       2021-03-09 09:33:48 +08:00
    @c6h6benzene 需要 DeviceID 和对应的次数 CreateTime 在七天内的算 1 次
    bwd1991
        5
    bwd1991  
    OP
       2021-03-09 09:35:00 +08:00
    @wmwgijol28 设备需要 7 天巡检一次 设定一个周期 比如两个月 就是需要 60/7 次 然后需要计算实际巡检次数 7 天内重复的只计算一次。。
    ccoming
        6
    ccoming  
       2021-03-09 09:35:43 +08:00
    @awanganddong 感觉楼主表达不够清晰。需求应该是统计某段时间内的巡检次数,但两次间隔 7 天内的巡检,只算一次?
    bwd1991
        7
    bwd1991  
    OP
       2021-03-09 09:36:14 +08:00
    @awanganddong 想用 sql 实现一下统计 。。 没办法的话只能这么搞了
    bwd1991
        8
    bwd1991  
    OP
       2021-03-09 09:36:49 +08:00
    @ccoming 对 所以是。。统计一段时间内巡检次数,7 天内的不重复计算
    c6h6benzene
        9
    c6h6benzene  
       2021-03-09 09:42:09 +08:00 via iPhone
    @bwd1991 所以针对上面的示例数据,结果就是 DeviceID/DeviceName/CheckedTimes:4/电器箱 /1 ?(假设今天 19/3/20 )

    那基本上就是只要 7 天内有没有检查过的 flag 嘛。
    bwd1991
        10
    bwd1991  
    OP
       2021-03-09 09:48:19 +08:00
    @c6h6benzene 对啊。。问题是 sql 怎么写
    一条一条数据遍历简单 不知道怎么用 sql 处理
    c6h6benzene
        11
    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
        12
    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
        13
    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
        14
    bwd1991  
    OP
       2021-03-09 10:11:33 +08:00
    @Marstin 取第一个 7 天
    比如 1.1 1.2 1.8 1.10
    取 1.1 1.8
    bwd1991
        15
    bwd1991  
    OP
       2021-03-09 10:12:54 +08:00
    @djj510620510 这个是取单个的吗。。我是汇总计算
    djj510620510
        16
    djj510620510  
       2021-03-09 10:15:11 +08:00
    @bwd1991 你改一下 group 的字段,然后 count(distinct DeviceID)就行了,按 week_no 来 group
    Marstin
        17
    Marstin  
       2021-03-09 10:18:31 +08:00
    @bwd1991 那就很好办啊,先做一次查询,把时间减掉开始时间后除以七天然后取整,再根据这个字段和设备 ID 一起分组统计就完事了
    bwd1991
        18
    bwd1991  
    OP
       2021-03-09 10:26:37 +08:00
    @Marstin 不行的。。。 时间间隔是需要和上一个时间去判断 不能按周来 按周来可能是这周天 下周一
    dswyzx
        19
    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
        20
    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
        21
    TimePPT  
       2021-03-09 11:16:38 +08:00
    @TimePPT row_number() over (partition by deviceid order by dt)
    TimePPT
        22
    TimePPT  
       2021-03-09 11:19:12 +08:00
    @TimePPT 这个思路的前提是先按天为周期去重
    TimePPT
        23
    TimePPT  
       2021-03-09 12:57:37 +08:00 via Android
    @TimePPT 囧,又想了下不太对,这个如果日期不连续还不能直接求
    zhuangjia
        24
    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
        25
    zhuangjia  
       2021-03-09 13:47:46 +08:00
    @zhuangjia 尴尬,忘了回复不支持 markdown,这个格式看着愁人
    bwd1991
        26
    bwd1991  
    OP
       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
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   2873 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 29ms · UTC 13:09 · PVG 21:09 · LAX 06:09 · JFK 09:09
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.