首页 新闻 搜索 专区 学院

求教如何写SQL

0
[待解决问题]

近期面试遇到这样一个问题:

有一张用户呼叫表,表中有两列,用户ID和呼叫时间,现在需要找出一分钟内呼叫次数超过10次的用户,标记为黑名单。该如何写SQL得到这种数据。

要求:SQL中不能出现排序操作。


已找到解决方法:
WITH CTE_BASE AS(
SELECT A.USER_ID, A.CALL_TIME
, CASE WHEN TIME_DIFF(A.CALL_TIME, B.CALL_TIME) BEWTEEN 1 AND 60 SECONDS THEN 1 ELSE 0 END AS CONTI_CNT
FROM CALL_RECORD A
FULL JOIN CALL_RECORD B
ON A.USER_ID = B.USER_ID
)
, CTE_ABNORMAL_CALLS AS(
SELECT USER_ID, CALL_TIME
FROM CTE_BASE
GROUP BY USER_ID, CALL_TIME
HAVING SUM(CONTI_CNT) > 10
)
SELECT DISTINCT USER_ID
FROM CTE_ABNORMAL_CALLS


但是该方法出现表自关联,现在基表数据量很大,不知道性能会如何?

SQL
davawang的主页 davawang | 菜鸟二级 | 园豆:221
提问于:2019-02-22 18:58
< >
分享
所有回答(6)
0

SELECT 用户ID
FROM dbo.Schedules
GROUP BY 用户ID, DATEPART(MINUTE, 呼叫时间)
HAVING count(*) >10
好像是这样吧。。。

Jeffcky | 园豆:2563 (老鸟四级) | 2019-02-22 20:45

这样跨分钟貌似有问题,比如:2019-02-22 20:49:20 和 2019-02-22 20:50:10

支持(0) 反对(0) jello chen | 园豆:7096 (大侠五级) | 2019-02-22 20:51

@jello chen: 那好办啦,你在以分分组前再加上以年、月、日、小时分组啦

支持(0) 反对(0) Jeffcky | 园豆:2563 (老鸟四级) | 2019-02-22 20:53

@jello chen:
SELECT 用户ID
FROM dbo.呼叫表
GROUP BY 用户ID,
DATEPART(YEAR, 呼叫时间),
DATEPART(MONTH, 呼叫时间),
DATEPART(DAY, 呼叫时间),
DATEPART(HOUR, DT.[Date]),
DATEPART(MINUTE, 呼叫时间)
HAVING count(*) >10

支持(0) 反对(0) Jeffcky | 园豆:2563 (老鸟四级) | 2019-02-22 20:55

@Jeffcky: 不是,我的问题在“一分钟内”这个点上

支持(0) 反对(0) jello chen | 园豆:7096 (大侠五级) | 2019-02-22 20:57

@jello chen:
你没根本理解意思,你看如下数据,你运行。
declare @calls table
(
userId VARCHAR(36),
callTime datetime
)
INSERT INTO @calls (callTime,userId) values ('2019-02-22 12:23:01','1FA3F63F-1F5A-4825-AB68-ED70365F85DA')
INSERT INTO @calls (callTime,userId) values ('2017-02-22 12:23:01','1FA3F63F-1F5A-4825-AB68-ED70365F85DA')
INSERT INTO @calls (callTime,userId) values ('2019-02-22 12:35:01','1FA3F63F-1F5A-4825-AB68-ED70365F85DA')
INSERT INTO @calls (callTime,userId) values ('2015-02-22 12:25:01','1FA3F63F-1F5A-4825-AB68-ED70365F85DA')
INSERT INTO @calls (callTime,userId) values ('2013-02-22 12:02:01','1FA3F63F-1F5A-4825-AB68-ED70365F85DA')
INSERT INTO @calls (callTime,userId) values ('2019-02-22 12:09:01','1FA3F63F-1F5A-4825-AB68-ED70365F85DA')
INSERT INTO @calls (callTime,userId) values ('2019-02-22 12:35:01','1FA3F63F-1F5A-4825-AB68-ED70365F85DA')

SELECT userId,DATEPART(MINUTE, callTime),COUNT()
FROM @calls
GROUP BY userId,
DATEPART(YEAR, callTime),
DATEPART(MONTH, callTime),
DATEPART(DAY,callTime),
DATEPART(HOUR, callTime),
DATEPART(MINUTE, callTime)
HAVING count(
) >1

支持(0) 反对(0) Jeffcky | 园豆:2563 (老鸟四级) | 2019-02-22 21:03

@jello chen: 一分钟无非就是比如上述例子35分钟时,就有两条,应该懂我意思了把

支持(0) 反对(0) Jeffcky | 园豆:2563 (老鸟四级) | 2019-02-22 21:05

@Jeffcky: 我不这么认为,我觉得2019-02-22 20:49:20 和 2019-02-22 20:50:10虽然分钟不一致,但是是在一分钟内的

支持(0) 反对(0) jello chen | 园豆:7096 (大侠五级) | 2019-02-22 21:07

@jello chen: oh,是的,这个确实不行,这个该如何解决?有点意思。我再想想,哈哈

支持(0) 反对(0) Jeffcky | 园豆:2563 (老鸟四级) | 2019-02-22 21:09

@jello chen: 对的,你的理解是对的。我想出了一种SQL写法,但是用到了排序操作,现在要求不能使用排序,就不知道怎么写了。

支持(0) 反对(0) davawang | 园豆:221 (菜鸟二级) | 2019-02-22 21:12

@davawang: 有点难度。。

支持(0) 反对(0) Jeffcky | 园豆:2563 (老鸟四级) | 2019-02-22 21:13
1

select * from (
select a.UserId,
(select COUNT() from T_Booking b where DateDiff(MINUTE,a.CreateTime,b.CreateTime)=0 and a.UserId=b.UserId ) as bnum
from T_Booking a
) t where bnum>10
----------------------------------------------------------------------------------------
select
from (
select a.UserId,
(select COUNT(*) from T_Booking b where DateDiff(SECOND,a.CreateTime,b.CreateTime) between 0 and 60 and a.UserId=b.UserId ) as bnum
from T_Booking a
) t where bnum>10

quanquan99 | 园豆:202 (菜鸟二级) | 2019-02-23 08:22
0

确实很刁钻,但是现实中在表中多加一两个字段也没啥事啊,为何要绕大湾子啊

学会乐观 | 园豆:737 (小虾三级) | 2019-02-23 09:36
0

问题漏洞,一分钟内是连续一分钟还是自然一分钟?
如果不强调连续,那就当是自然一分钟,但这个在实际业务中没有意义.
但如果是连续一分钟,就要求数据记录是时序排列好的,否则就必须用到排序.
不连续的话就直接用时间戳除以60取整进行分组就好了.

首席吐槽官秦寿 | 园豆:321 (菜鸟二级) | 2019-02-23 12:58

是连续一分钟

支持(0) 反对(0) davawang | 园豆:221 (菜鸟二级) | 2019-02-25 16:53
1

借用楼上@Jeffcky的数据,稍加改动,因为数据行数有限,故把题目改为“找出一分钟内呼叫次数超过2次的用户”,尝试如下:
declare @calls table
(
userId VARCHAR(36),
callTime datetime
)
INSERT INTO @calls (callTime,userId) values ('2019-02-22 12:33:01','1FA3F63F')
INSERT INTO @calls (callTime,userId) values ('2019-02-22 12:33:21','1FA3F63F')
INSERT INTO @calls (callTime,userId) values ('2019-02-22 12:34:30','1FA3F63F')
INSERT INTO @calls (callTime,userId) values ('2019-02-22 12:35:01','1FA3F63F')
INSERT INTO @calls (callTime,userId) values ('2019-02-22 12:35:21','1FA3F63F')
INSERT INTO @calls (callTime,userId) values ('2019-02-22 12:42:01','1FA3F63F')
INSERT INTO @calls (callTime,userId) values ('2019-02-22 12:49:01','1FA3F63F')

select distinct userId
from (
select a.userId, b.callTime_begin, b.callTime_end, COUNT() cishu
from @calls a,
( select userId, callTime as callTime_begin, dateadd(ss,60,callTime) as callTime_end
from @calls
) b
where a.userId = b.userId
and a.callTime between b.callTime_begin and b.callTime_end
group by a.userId, b.callTime_begin, b.callTime_end
having COUNT(
) > 2
) t

阿_修 | 园豆:204 (菜鸟二级) | 2019-02-24 11:35
0

SELECT DISTINCT
userId
FROM
(
SELECT COUNT(1) AS cou,
a.userId,
a.callTime
FROM #tb a
INNER JOIN #tb b
ON a.userId = b.userId
AND DATEDIFF(SECOND, a.callTime, b.callTime)
BETWEEN 0 AND 60
GROUP BY a.userId,
a.callTime
HAVING COUNT(1) > 2
) t;

Script丶 | 园豆:170 (初学一级) | 2020-07-21 12:07
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册