请问怎么取表中两条连续记录之间的日期差异?例如表内容:
id customer date amount
1 1234 2010-10-1 1000
2 1234 2010-10-3 1500
3 1234 2010-10-10 2300希望找出来1234这个客户每两次下订单之间的日期差异的最大值。
如上表例子,第一次和第二次下单之间差2天,第三次和第二次之间差7天,希望的结果集是:
customer max_gap
1234 7
id customer date amount
1 1234 2010-10-1 1000
2 1234 2010-10-3 1500
3 1234 2010-10-10 2300希望找出来1234这个客户每两次下订单之间的日期差异的最大值。
如上表例子,第一次和第二次下单之间差2天,第三次和第二次之间差7天,希望的结果集是:
customer max_gap
1234 7
SELECT customer,Max(nvl(lag_date,date)-date) max_gap FROM(
SELECT id, customer, DATE,
Lag(date)over(PARTITION BY customer ORDER BY id) lag_date,amount
FROM table_name
)
GROUP BY customer;
--上面写错一个地方,相减后再取绝对值ABS!WITH tab AS(
SELECT 1 id, '1234' customer, To_Date('2010-10-1','yyyy-mm-dd') DaT, 1000 amount FROM dual UNION ALL
SELECT 2,'1234', To_Date('2010-10-3','yyyy-mm-dd'), 1500 FROM dual UNION ALL
SELECT 3, '1234', To_Date('2010-10-10','yyyy-mm-dd'), 2300 FROM dual
)
SELECT customer,Max(Abs(nvl(lag_date,dat)-dat)) max_gap FROM(
SELECT id, customer, DAT,
Lag(dat)over(PARTITION BY customer ORDER BY id) lag_date,amount
FROM tab
)
GROUP BY customer;customer max_gap
-----------------
1234 7
当前日志序列 123
SQL> with tb as
2 (
3 select 1 id,1234 customer,to_date('2010-10-1','yyyy-mm-dd') dt,1000 amount from dual union all
4 select 2,1234,to_date('2010-10-3','yyyy-mm-dd'),1500 from dual union all
5 select 3,1234,to_date('2010-10-10','yyyy-mm-dd'),2300 from dual
6 ),
7 tb2 as
8 (select customer,dt,row_number() over(partition by customer order by dt) rn
9 from tb)
10 select a.customer,max(a.dt-b.dt)
11 from tb2 a join tb2 b on a.rn-1=b.rn and a.customer=b.customer
12 group by a.customer
13 / CUSTOMER MAX(A.DT-B.DT)
---------- --------------
1234 7
已写入 file afiedt.buf 1 with tb as
2 (
3 select 1 id,1234 customer,to_date('2010-10-1','yyyy-mm-dd') dt,1000 amount from dual union all
4 select 2,1234,to_date('2010-10-3','yyyy-mm-dd'),1500 from dual union all
5 select 3,1234,to_date('2010-10-10','yyyy-mm-dd'),2300 from dual
6 ),
7 tb2 as
8 (select customer,dt,row_number() over(partition by customer order by id,dt) rn
9 from tb)
10 select a.customer,max(a.dt-b.dt)
11 from tb2 a join tb2 b on a.rn-1=b.rn and a.customer=b.customer
12* group by a.customer
SQL> / CUSTOMER MAX(A.DT-B.DT)
---------- --------------
1234 7