34,590
社区成员
发帖
与我相关
我的任务
分享求group by 后满足求差条件后的最小值对应的SQL
表名T1
数据格式如下.we
C1 C2 C3
A1 ATIME1 112
A1 ATIME2 321
A1 ATIME3 4324
B1 BTIME1 213
B2 BTIME2 434
D1 DTIME1 55
想拉出数据要求如下
以C1 列分组。
在A1 中
如果ATIME1，ATIME2 ， ATIME3 时间差小于 5分钟只取最小的那条。如果大于5分钟则几条都要
数据格式如下.we
C1 C2 C3
A1 ATIME1 112
A1 ATIME2 321
A1 ATIME3 4324
B1 BTIME1 213
B2 BTIME2 434
D1 DTIME1 55
想拉出数据要求如下
以C1 列分组。
在A1 中
如果ATIME1，ATIME2 ， ATIME3 时间差小于 5分钟只取最小的那条。如果大于5分钟则几条都要
...全文
请发表友善的回复…
发表回复
以学习为目的 2014-07-01
- 打赏
- 举报
都是和最小时间比较的,其实最小时间再变得
如分钟时 1,3,7,8,9, 11,12
希望结果1,7,9,12
其实在8被 过滤后就是9开始了,应该算的.
逻辑伪码大概是这样
DO(string C1 ,string C2,string C3) //记录的一行,C1类型,C2 开始时间,C3 结束时间
{
//一条条读记录X,Y,Z
没下条记录跳出
if (X==C1) ,
if (Y- C2)< 5,
{
C3 = Z 覆盖原来结束时间
DO(C1.C2.C3)
}
else
{
SAVE()保存记录
DO(C1.Y.Z)
}
}
[/quote]
9开始了，后面为什么又是12呢，不是要差5啊
看不懂了～～
能不能上20条实际数据和预期结果[/quote]
我46楼就说了，楼主估计给出的数据不全面，所以有些规律肯定没体现出来，所以写出来的不符合要求
测试230 2014-07-01
- 打赏
- 举报
都是和最小时间比较的,其实最小时间再变得
如分钟时 1,3,7,8,9, 11,12
希望结果1,7,9,12
其实在8被 过滤后就是9开始了,应该算的.
逻辑伪码大概是这样
DO(string C1 ,string C2,string C3) //记录的一行,C1类型,C2 开始时间,C3 结束时间
{
//一条条读记录X,Y,Z
没下条记录跳出
if (X==C1) ,
if (Y- C2)< 5,
{
C3 = Z 覆盖原来结束时间
DO(C1.C2.C3)
}
else
{
SAVE()保存记录
DO(C1.Y.Z)
}
}
[/quote]
9开始了，后面为什么又是12呢，不是要差5啊
看不懂了～～
能不能上20条实际数据和预期结果[/quote]
你的语句应该没问题，我看错了，谢谢你。
测试230 2014-07-01
- 打赏
- 举报
都是和最小时间比较的,其实最小时间再变得
如分钟时 1,3,7,8,9, 11,12
希望结果1,7,9,12
其实在8被 过滤后就是9开始了,应该算的.
逻辑伪码大概是这样
DO(string C1 ,string C2,string C3) //记录的一行,C1类型,C2 开始时间,C3 结束时间
{
//一条条读记录X,Y,Z
没下条记录跳出
if (X==C1) ,
if (Y- C2)< 5,
{
C3 = Z 覆盖原来结束时间
DO(C1.C2.C3)
}
else
{
SAVE()保存记录
DO(C1.Y.Z)
}
}
不好意思,我举例错了.
是这种情况,
[/quote]
9开始了，后面为什么又是12呢，不是要差5啊
看不懂了～～
能不能上20条实际数据和预期结果[/quote]
不好意思,我打 错了
期望结果1，7，12
向东流 2014-06-30
- 打赏
- 举报
都是和最小时间比较的,其实最小时间再变得
如分钟时 1,3,7,8,9, 11,12
希望结果1,7,9,12
其实在8被 过滤后就是9开始了,应该算的.
逻辑伪码大概是这样
DO(string C1 ,string C2,string C3) //记录的一行,C1类型,C2 开始时间,C3 结束时间
{
//一条条读记录X,Y,Z
没下条记录跳出
if (X==C1) ,
if (Y- C2)< 5,
{
C3 = Z 覆盖原来结束时间
DO(C1.C2.C3)
}
else
{
SAVE()保存记录
DO(C1.Y.Z)
}
}
[/quote]
9开始了，后面为什么又是12呢，不是要差5啊
看不懂了～～
能不能上20条实际数据和预期结果
chen357313771 2014-06-30
- 打赏
- 举报
介个不行么？
测试230 2014-06-30
- 打赏
- 举报
都是和最小时间比较的,其实最小时间再变得
如分钟时 1,3,7,8,9, 11,12
希望结果1,7,9,12
其实在8被 过滤后就是9开始了,应该算的.
逻辑伪码大概是这样
DO(string C1 ,string C2,string C3) //记录的一行,C1类型,C2 开始时间,C3 结束时间
{
//一条条读记录X,Y,Z
没下条记录跳出
if (X==C1) ,
if (Y- C2)< 5,
{
C3 = Z 覆盖原来结束时间
DO(C1.C2.C3)
}
else
{
SAVE()保存记录
DO(C1.Y.Z)
}
}
向东流 2014-06-27
- 打赏
- 举报
早上过来，又写了个，用CTE作了个递归，应该可以了吧
with t1 as(
select 'A1' c1,'2014-6-26 18:00:00' c2
union all select 'A1','2014-6-26 18:02:00'
union all select 'A1','2014-6-26 18:05:00'
union all select 'A1','2014-6-26 18:07:00'
union all select 'A1','2014-6-26 18:11:00'
union all select 'A1','2014-6-26 18:12:00'
union all select 'A1','2014-6-26 18:14:00'
union all select 'B1','2014-6-26 18:02:00'
union all select 'B1','2014-6-26 18:06:00'
union all select 'B1','2014-6-26 18:08:00'
union all select 'B1','2014-6-26 18:12:00'
union all select 'B1','2014-6-26 18:14:00'
)
,t2 as(
select (row_number() over (partition by c1 order by c2)) id,*,min(c2) over(partition by c1) c3
from t1
)
,t3 as(
select *
from t2
where id=1
union all
select t2.id,t2.c1,t2.c2,
case when datediff(mi,t3.c3,t2.c2)>=5 then t2.c2 else t3.c3 end
from t2 inner join t3
on t2.c1=t3.c1 and t2.id=t3.id+1
)
select * from t3 where c2=c3 order by c1,c2
以学习为目的 2014-06-27
- 打赏
- 举报
用我这租数据
1 A1 2014-6-26 18:00:00
2 A1 2014-6-26 18:04:00
3 A1 2014-6-26 18:08:00
要1,3
[/quote]
declare @galenkeny table(C1 VARCHAR(10),C2 DATETIME)
insert into @galenkeny
SELECT 'A1', '2014-6-26 18:00:00' union all
select 'A1', '2014-6-26 18:04:00' union all
select 'A1', '2014-6-26 18:08:00'
;WITH cte AS
(SELECT *,rn=ROW_NUMBER()OVER(PARTITION BY C1 ORDER BY C2 DESC ) FROM @galenkeny)
SELECT C1,C2 FROM cte
WHERE rn IN(1,3) ORDER BY C2
chen357313771 2014-06-27
- 打赏
- 举报
也写了个，应该能满足吧
WITH T1(C1,C2) AS(
SELECT 'A1' ,'2014-6-26 18:00:00'
UNION ALL SELECT 'A1','2014-6-26 18:02:00'
UNION ALL SELECT 'A1','2014-6-26 18:06:00'
UNION ALL SELECT 'A1','2014-6-26 18:08:00'
UNION ALL SELECT 'A1','2014-6-26 18:12:00'
UNION ALL SELECT 'A1','2014-6-26 18:14:00'
UNION ALL SELECT 'B1','2014-6-23 17:00:00'
UNION ALL SELECT 'B1','2014-6-23 17:01:00'
UNION ALL SELECT 'B1','2014-6-23 17:08:00'
UNION ALL SELECT 'B1','2014-6-23 17:18:00'
UNION ALL SELECT 'C1','2014-6-24 19:00:00'
UNION ALL SELECT 'C1','2014-6-24 19:07:00'
UNION ALL SELECT 'C1','2014-6-24 19:08:00'
UNION ALL SELECT 'C1','2014-6-24 19:18:00'
)
,t2 AS (
SELECT *
,ROW_NUMBER() OVER(ORDER BY c1,c2) AS ID
,ROW_NUMBER() OVER(PARTITION BY c1 ORDER BY c2) AS ID1
,MIN(c2) OVER(PARTITION BY c1) AS mintime
FROM t1
)
,t3 AS (
SELECT *,CAST(mintime AS DATETIME ) AS c3 FROM t2 WHERE ID=1 AND ID1=1
UNION ALL
SELECT A.*,CASE WHEN A.c1=B.c1 THEN (CASE WHEN DATEADD(MINUTE,5,CAST(B.c3 AS DATETIME))>=CAST(A.c2 AS datetime)
THEN CAST(B.c3 AS datetime) ELSE CAST(A.c2 AS DATETIME) END ) ELSE CAST(A.c2 AS datetime) END
FROM t2 AS A,t3 AS B
WHERE A.ID=B.ID+1
)
SELECT ID1, c1,c2
FROM t3
WHERE c2=c3
/*
ID1 c1 c2
1 A1 2014-6-26 18:00:00
3 A1 2014-6-26 18:06:00
5 A1 2014-6-26 18:12:00
1 B1 2014-6-23 17:00:00
3 B1 2014-6-23 17:08:00
4 B1 2014-6-23 17:18:00
1 C1 2014-6-24 19:00:00
2 C1 2014-6-24 19:07:00
4 C1 2014-6-24 19:18:00
*/
东家来了 2014-06-27
- 打赏
- 举报
create table loginlog(UserID bigint, LoginTime datetime)
insert into loginlog(UserID, LoginTime)
values
(201401, '2014-06-27 08:00:43'),
(201401, '2014-06-27 08:03:43'),
(201401, '2014-06-27 08:05:43'),
(201401, '2014-06-27 08:08:43'),
(201402, '2014-06-27 08:01:43'),
(201402, '2014-06-27 08:02:43'),
(201402, '2014-06-27 08:07:43'),
(201402, '2014-06-27 08:09:43'),
(201403, '2014-06-27 08:01:43'),
(201403, '2014-06-27 08:04:43'),
(201403, '2014-06-27 08:06:43'),
(201403, '2014-06-27 08:09:43'),
(201401, '2014-06-27 09:00:43'),
(201401, '2014-06-27 09:03:43'),
(201401, '2014-06-27 09:05:43'),
(201401, '2014-06-27 09:08:43'),
(201402, '2014-06-27 09:01:43'),
(201402, '2014-06-27 09:02:43'),
(201402, '2014-06-27 09:07:43'),
(201402, '2014-06-27 09:09:43'),
(201403, '2014-06-27 09:01:43'),
(201403, '2014-06-27 09:04:43'),
(201403, '2014-06-27 09:06:43'),
(201403, '2014-06-27 09:09:43')
with cte as (
select UserID,
LoginTime,
(DATEPART(MINUTE,LoginTime)/5)*5 'GroupID',
ROW_NUMBER() over(partition by UserID, (DATEPART(MINUTE,LoginTime)/5)*5, CONVERT(varchar(14), LoginTime) order by LoginTime ) 'Num'
from loginlog)
select UserID, LoginTime from cte where Num = 1
twtiqfn 2014-06-27
- 打赏
- 举报
有点难啊，我写不了来，水平还不行，哈
测试230 2014-06-26
- 打赏
- 举报
你要逐条比较差5分钟？因为你需求没写要逐条比较[/quote]
就是要每次都比,
打个比方.
对于用户登录数的统计.
同一用户在5分钟内的重复登录只算时间最早的那次
xdashewan 2014-06-26
- 打赏
- 举报
你要逐条比较差5分钟？因为你需求没写要逐条比较
测试230 2014-06-26
- 打赏
- 举报
有点问题没明白,
max(c2) - min(c2) > 5分钟,你只比了最大和最小值,还有中间的忽略了
xdashewan 2014-06-26
- 打赏
- 举报
先做group by ，然后用T1和group by后的子集联，条件是 C1相等 and （max(c3) - min(c3) > 5分钟）or （C3 = min(c3)）
xdashewan 2014-06-26
- 打赏
- 举报
WITH cte as
(
SELECT top 1 C1,C2,C3 FROM T1 WHERE C1 = 'A1'
UNION ALL
SELECT top 1 a.C1,a.C2,a.C3 FROM T1 a,cte b
where a.C1 = b.C1 and a.C3 - b.C3 > 5分钟 //时间比较自己写
)
SELECT * from cte
测试230 2014-06-26
- 打赏
- 举报
用我这租数据
1 A1 2014-6-26 18:00:00
2 A1 2014-6-26 18:04:00
3 A1 2014-6-26 18:08:00
要1,3
[/quote]
declare @galenkeny table(C1 VARCHAR(10),C2 DATETIME)
insert into @galenkeny
SELECT 'A1', '2014-6-26 18:00:00' union all
select 'A1', '2014-6-26 18:04:00' union all
select 'A1', '2014-6-26 18:08:00'
;WITH cte AS
(SELECT *,rn=ROW_NUMBER()OVER(PARTITION BY C1 ORDER BY C2 DESC ) FROM @galenkeny)
SELECT C1,C2 FROM cte
WHERE rn IN(1,3) ORDER BY C2
以学习为目的 2014-06-26
- 打赏
- 举报
思路我早给你了，可惜你只顾看现成sql，完全不理会[/quote]
我看了阿,问题真不知道要怎么写,
你这种做法是不是也要逐个比较?
A 18:00:00
A 18:01:00
A 18:07:00
A 18:08:00
2,3比较大于5,是不是第2条也被记了呢?
[/quote]
当然按照你的需求，是不记第二条的
测试230 2014-06-26
- 打赏
- 举报
需求就是5分钟内登录数只算一次,并且是时间最早的那次.
从你这个数据就三要1,3,5.
逻辑上应该是两个逐行比较,如果小于5记第1条,(算1次登录)
同时跳过第2条从第三条开始,(第三条算新登录)
如果大于5两条都记(算两次次登录),
测试230 2014-06-26
- 打赏
- 举报
思路我早给你了，可惜你只顾看现成sql，完全不理会[/quote]
我看了阿,问题真不知道要怎么写,
你这种做法是不是也要逐个比较?
A 18:00:00
A 18:01:00
A 18:07:00
A 18:08:00
2,3比较大于5,是不是第2条也被记了呢?
加载更多回复（33）
