使用row_number即可
我用sql server写了一个 你看看ok不？
select A.[user],
DATEDIFF( Day,A.[date],B.[date]) as interval
from
( SELECT [user]
,[date]
, ROW_NUMBER() over(partition by [user] order by [date] asc) as num
FROM [test].[dbo].[Table1] ) A
left outer join
(SELECT [user]
,[date]
, ROW_NUMBER() over(partition by [user] order by [date] asc) as num
FROM [test].[dbo].[Table1] ) B
on A.[user]=B.[user]
where A.num=1
and B.num=2
create table card_PurchaseInterval as
select
a.card_num,
DATEDIFF(a.day,b.day) as interval
from
(select
card_num,day,
row_number() over(partition by (card_num) order by day asc) as num
from
user.Larage_Table
where length(card_num)=16 and card_num like '9%' ) a
left outer join
(select
card_num,day,
row_number() over(partition by (card_num) order by day asc) as num
from
user.Larage_Table
where length(card_num)=16 and card_num like '9%') b
on
a.card_num=b.card_num
where a.card_num=1 and b.card_num=2
各位帮忙看看，为什么去出来的数为0行呢
SELECT ACC_NBR ,LOGIN_TIME ,rn = ROW_NUMBER()OVER(PARTITION BY ACC_NBR ORDER BY LOGIN_TIME)
INTO #temp_behavior
FROM ECH_LLS.dbo.TM_MOBILE_LOGIN_201604
SELECT a.ACC_NBR,datediff(MINUTE,a.LOGIN_TIME,b.LOGIN_TIME)AS 时间间隔
FROM #temp_behavior a,#temp_behavior b
WHERE a.ACC_NBR = b.ACC_NBR AND a.rn + 1 = b.rn
select
v.user,
abs(datediff(v.dt1,v.dt2) as `购买间隔天数`
from
(select
z.user,
z.date as dt1
w.date as dt2
from
(select
y.user,
y.date
from
(select
b.user,
b.date,
rank()over(partition by b.user order by b.date ) as rk
from
(select
a.user,
count(disttinct a.date)
from 表名 a
group by
a.user
having count(disttinct a.date) >=2)x
inner join 表名 b
on x.user=b.user)y
where y.rk <=2
group by
y.user,
y.date)z
inner join (
select
y.user,
y.date
from
(select
b.user,
b.date,
rank()over(partition by b.user order by b.date ) as rk
from
(select
a.user,
count(disttinct a.date)
from 表名 a
group by
a.user
having count(disttinct a.date) >=2)x
inner join 表名 b
on x.user=b.user)y
where y.rk <=2
group by
y.user,
y.date)z
)w
on z.user=w.user)v
where v.dt1 is not null
and v.dt2 is not null
and v.dt1 <> v.dt2
WITH ranked_transactions AS (
SELECT
user,
date,
ROW_NUMBER() OVER (
PARTITION BY user
ORDER BY date
) AS rn
FROM transactions
)
SELECT
t1.user,
DATEDIFF(day, t1.date, t2.date) AS days_interval
FROM ranked_transactions t1
JOIN ranked_transactions t2
ON t1.user = t2.user
AND t1.rn = 1
AND t2.rn = 2;
扫码加好友，拉您进群
全部版块
我的主页
收藏
