【应用】如何判断客户是否短时间内频繁交易，查询5分钟内交易次数≥3次的交易记录

咏怡

求助求助，这个问题已经捣鼓了两天了，各种百度还是没有解决心中的疑问。求大神相助。

一张记录所有用户交易日期、时间、id的表格，想要查询5分钟内交易次数≥3次的交易记录，sql语言如何写呢？

6940.png

date        time        ID
2019/6/30        104739        A00001
2019/6/30        104740        A00001
2019/6/30        104744        A00001
2019/6/30        104745        A00001
2019/6/30        190727        A00002
2019/6/30        213819        A00003
2019/6/30        213830        A00004
2019/7/1        215106        A00007
2019/7/1        215109        A00009
2019/7/1        215109        A00005
2019/7/1        215109        A00010

卖水果的net

1. DECLARE@tTABLE(DDATENOTNULL,TINTNOTNULL,IDVARCHAR(10)NOTNULL);
2. INSERT@t(D,T,ID)
3. VALUES('2019/6/30',104739,'A00001'),('2019/6/30',104740,'A00001'),('2019/6/30',104744,'A00001'),
4. ('2019/6/30',104745,'A00001'),('2019/6/30',190727,'A00002'),('2019/6/30',213819,'A00003'),
5. ('2019/6/30',213830,'A00004'),('2019/7/1',215106,'A00007'),('2019/7/1',215109,'A00009'),
6. ('2019/7/1',215109,'A00005'),('2019/7/1',215109,'A00010');
8. WITHDAS(SELECT*,T/10000*3600+T/100*60+T%60SFROM@t),
9. D1AS(SELECT*,
10. LEAD(ID,2)OVER(ORDERBYD.ID,D.D,D.T)LeadID,
11. LEAD(S,2)OVER(ORDERBYD.ID,D.D,D.T)LeadS,
12. LAG(ID,2)OVER(ORDERBYD.ID,D.D,D.T)LagID,
13. LAG(S,2)OVER(ORDERBYD.ID,D.D,D.T)LagS
14. FROMD)
15. SELECTD1.D,D1.T,D1.ID
16. FROMD1
17. WHERE(D1.LeadID=D1.IDANDD1.LeadS-D1.S<=300)
18. OR(D1.LagID=D1.IDANDD1.S-D1.LagS<=300);

复制代码

(11行受影响) DTID ------------------------------- 2019-06-30104739A00001 2019-06-30104740A00001 2019-06-30104744A00001 2019-06-30104745A00001 (4行受影响)

咏怡

lead和lag这两个函数，楼主先了解一下。

咏怡

引用1楼HelloWorld,的回复:

1. DECLARE@tTABLE(DDATENOTNULL,TINTNOTNULL,IDVARCHAR(10)NOTNULL);
2. INSERT@t(D,T,ID)
3. VALUES('2019/6/30',104739,'A00001'),('2019/6/30',104740,'A00001'),('2019/6/30',104744,'A00001'),
4. ('2019/6/30',104745,'A00001'),('2019/6/30',190727,'A00002'),('2019/6/30',213819,'A00003'),
5. ('2019/6/30',213830,'A00004'),('2019/7/1',215106,'A00007'),('2019/7/1',215109,'A00009'),
6. ('2019/7/1',215109,'A00005'),('2019/7/1',215109,'A00010');
8. WITHDAS(SELECT*,T/10000*3600+T/100*60+T%60SFROM@t),
9. D1AS(SELECT*,
10. LEAD(ID,2)OVER(ORDERBYD.ID,D.D,D.T)LeadID,
11. LEAD(S,2)OVER(ORDERBYD.ID,D.D,D.T)LeadS,
12. LAG(ID,2)OVER(ORDERBYD.ID,D.D,D.T)LagID,
13. LAG(S,2)OVER(ORDERBYD.ID,D.D,D.T)LagS
14. FROMD)
15. SELECTD1.D,D1.T,D1.ID
16. FROMD1
17. WHERE(D1.LeadID=D1.IDANDD1.LeadS-D1.S<=300)
18. OR(D1.LagID=D1.IDANDD1.S-D1.LagS<=300);

复制代码

(11行受影响) DTID ------------------------------- 2019-06-30104739A00001 2019-06-30104740A00001 2019-06-30104744A00001 2019-06-30104745A00001 (4行受影响) 谢谢大神，已经成功了，感动

咏怡

引用2楼卖水果的net的回复:lead和lag这两个函数，楼主先了解一下。
好，2楼大神用了这两个函数，我消化理解下。谢谢