在线时间统计

2006-06-25 04:09:25 608 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

避坑指南：Docker部署PaddleOCR-VL服务时，关于flash-attn安装、端口映射和模型加载的那些坑本文详细记录了Docker部署PaddleOCR-VL服务时遇到的常见问题及解决方案，包括flash-attn安装、GPU兼容性检查、容器生命周期管理、模型加载优化等关键环节。特别针对不同GPU架构提供了flash-attn的安装策略，并分享了端口映射、配置文件处理等实用技巧，帮助开发者高效避坑。阅读详情

表TABLE1 结构如下:
users time flag
A君 2006-6-24 8:14:06 0
B君 2006-6-24 8:14:06 0
A君 2006-6-24 9:22:52 1
C君 2006-6-24 7:13:23 0
A君 2006-6-24 10:25:03 0
C君 2006-6-24 9:28:32 1
A君 2006-6-24 11:46:27 1

flag 为0 表示用户登入时间, 为1表示用户登出时间. 现在要求算出每个人的在线总时间. 表的结构是定死的. 不能动了, 请教如何写存储过程?

...全文

深度学习【46】pix2pixHD 英伟达的pix2pixHD，能够合成高清的2048*1024图片，简直振奋人心。pix2pixHD是我之前介绍过的pix2pix的改进版本，使用多由粗到精的G网络和多尺度D网络（每个D网络都是用了pix2pix中同样的patch技术）。论文一开始用pix2pix尝试合成更高分辨率的图片，但发现效果不好，训练也很不稳定。所以就对pix2pix进行了一顿魔改。我们来看看他们是怎么改进的。由粗到精的G网络阅读详情

MJJ_Hua

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

MJJ_Hua 2006-06-25

打赏
举报

多谢各位指教. 散分.

LouisXIV 2006-06-25

打赏
举报

以秒为统计单位，如超过24小时需要改动一下写法

LouisXIV 2006-06-25

打赏
举报

if object_id('Table1') is not null drop table table1
go
create table table1
(
users varchar(10),
time datetime,
flag bit
)
go
insert into table1
select 'A君','2006-6-24 8:14:06',0 union all
select 'B君','2006-6-24 8:14:06',0 union all
select 'A君','2006-6-24 9:22:52',1 union all
select 'C君','2006-6-24 7:13:23',0 union all
select 'A君','2006-6-24 10:25:03',0 union all
select 'C君','2006-6-24 9:28:32',1 union all
select 'A君','2006-6-24 11:46:27',1
go
select * from table1
go
select id=identity(int,1,1),users,time,flag into # from (select top 100 percent * from table1 order by time)aa
go
select * from #
go

select users,convert(varchar(10),dateadd(second,sum(logintime),0),108)as LoginTime
from
(
select
users,
logintime=(select datediff(second,time,a.time) from # where flag=0 and users=a.users and id=(select max(id) from # where id<a.id and users=a.users))
from # a
where flag=1
)aa
group by users

go
drop table #,table1
go

/*
id users time flag
----------- ---------- ------------------------------------------------------ ----
1 C君 2006-06-24 07:13:23.000 0
2 A君 2006-06-24 08:14:06.000 0
3 B君 2006-06-24 08:14:06.000 0
4 A君 2006-06-24 09:22:52.000 1
5 C君 2006-06-24 09:28:32.000 1
6 A君 2006-06-24 10:25:03.000 0
7 A君 2006-06-24 11:46:27.000 1

users LoginTime
---------- ----------
A君 02:30:10
C君 02:15:09
*/

--测试数据中B没有登出记录，不做统计
--如有连续登入而只有一次登出，一最后一次登入和登出之间的时间差为准

zlp321002 2006-06-25

打赏
举报

--如果按每天统计一次,用户登录信息.要加个条件,如下:

declare @t table(users varchar(20),time datetime,flag int)
insert into @t select 'A君','2006-6-24 8:14:06',0
union all select 'B君','2006-6-24 8:14:06',0
union all select 'A君','2006-6-24 9:22:52',1
union all select 'C君','2006-6-24 7:13:23',0
union all select 'A君','2006-6-24 10:25:03',0
union all select 'C君','2006-6-24 9:28:32',1
union all select 'A君','2006-6-24 11:46:27',1
union all select 'C君','2006-6-25 7:13:23',0
union all select 'A君','2006-6-25 10:25:03',0
union all select 'C君','2006-6-25 9:28:32',1
union all select 'A君','2006-6-25 11:46:27',1

SELECT users,LoginTime=MAX(LoginTime),LogoutTime=MAX(LogoutTime)
FROM
(
SELECT users,LoginTime=MIN(CASE WHEN FLAG=0 THEN TIME END),
LogoutTime=MAX(CASE WHEN FLAG=1 THEN TIME END),
flag,
times=convert(varchar(10),TIME,120)
FROM @t
GROUP BY users,FLAG,convert(varchar(10),TIME,120)
)T
GROUP BY USERS,times
ORDER BY USERS

/*
A君 2006-06-24 08:14:06.000 2006-06-24 11:46:27.000
A君 2006-06-25 10:25:03.000 2006-06-25 11:46:27.000
B君 2006-06-24 08:14:06.000 NULL
C君 2006-06-24 07:13:23.000 2006-06-24 09:28:32.000
C君 2006-06-25 07:13:23.000 2006-06-25 09:28:32.000

*/

LouisXIV 2006-06-25

打赏
举报

记录登陆时间的文件一般都存在不正常登出记录

你是不是考虑连续2，3次登入而只有1次登出的记录？

zlp321002 2006-06-25

打赏
举报

declare @t table(users varchar(20),time datetime,flag int)
insert into @t select 'A君','2006-6-24 8:14:06',0
union all select 'B君','2006-6-24 8:14:06',0
union all select 'A君','2006-6-24 9:22:52',1
union all select 'C君','2006-6-24 7:13:23',0
union all select 'A君','2006-6-24 10:25:03',0
union all select 'C君','2006-6-24 9:28:32',1
union all select 'A君','2006-6-24 11:46:27',1

SELECT users,LoginTime=MAX(LoginTime),LogoutTime=MAX(LogoutTime)
FROM
(
SELECT users,LoginTime=MIN(CASE WHEN FLAG=0 THEN TIME END),
LogoutTime=MAX(CASE WHEN FLAG=1 THEN TIME END),
flag
FROM @t
GROUP BY users,FLAG
)T
GROUP BY USERS
ORDER BY USERS

/*
A君 2006-06-24 08:14:06.000 2006-06-24 11:46:27.000
B君 2006-06-24 08:14:06.000 NULL
C君 2006-06-24 07:13:23.000 2006-06-24 09:28:32.000
*/

mschen 2006-06-25

打赏
举报

-- 这样可以对上边的结果对Users分组,sum(DiffTime)就可以得到总的时间了

-- 如果你的表非常大的话,最好在Users,和Time字段上建立符合索引

create index idx_users_time on #t(users,[time])

liangpei2008 2006-06-25

打赏
举报

--刚才写的是以小时为单位，改为分钟更合理！
select users,
时间=sum(case flag when 0 then datediff(mi,time,getdate()) else -datediff(mi,time,getdate()) end)
from @t
group by users

mschen 2006-06-25

打赏
举报

-- 测试表,测试数据
create table #t(users varchar(10),[time] datetime,flag bit)
Go
insert #t select 'A君','2006-6-24 8:14:06',0
insert #t select 'B君','2006-6-24 8:14:06',0
insert #t select 'A君','2006-6-24 9:22:52',1
insert #t select 'c君','2006-6-24 7:13:23',0
insert #t select 'A君','2006-6-24 10:25:03',0
insert #t select 'C君','2006-6-24 9:28:32',1
insert #t select 'A君','2006-6-24 11:46:27',1
Go
-- 查询语句
select users,
[time] as LoginTime,
isnull((select top 1 [time]
from #t
where users=a.users and [time]>a.[time] and flag=1
order by [time]),getdate()) as LogoutTime,
datediff(mi,[time],isnull((select top 1 [time]
from #t
where users=a.users and [time]>a.[time] and flag=1
order by [time]),getdate())) as DiffTime
from #t a

where flag=0
-- 结果

users LoginTime LogoutTime DiffTime
---------- ------------------------------------------------------ ------------------------------------------------------ -----------
A君 2006-06-24 08:14:06.000 2006-06-24 09:22:52.000 68
B君 2006-06-24 08:14:06.000 2006-06-25 16:24:25.340 1930
c君 2006-06-24 07:13:23.000 2006-06-24 09:28:32.000 135
A君 2006-06-24 10:25:03.000 2006-06-24 11:46:27.000 81

（所影响的行数为 4 行）

liangpei2008 2006-06-25

打赏
举报

--这样？
declare @t table(users varchar(10),time datetime,flag int)
insert @t select 'A','2006-6-24 8:14:06',0
union all select 'B','2006-6-24 8:14:06',0
union all select 'A','2006-6-24 9:22:52',1
union all select 'C','2006-6-24 7:13:23',0
union all select 'A','2006-6-24 10:25:03',0
union all select 'C','2006-6-24 9:28:32',1
union all select 'A','2006-6-24 11:46:27 ',1

select users,
时间=sum(case flag when 0 then datediff(hh,time,getdate()) else -datediff(hh,time,getdate()) end)
from @t
group by users

MJJ_Hua 2006-06-25

打赏
举报

不正常记录？
什么叫不正常记录啊, 记录的格式都是上面那样,没有问题的.

LouisXIV 2006-06-25

打赏
举报

是否考虑不正常记录？

在线时间统计

相关推荐