1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
| WITH MaTable AS
(
SELECT 1 AS nm, 'CLT1' as cl, to_date('01/02/2010', 'dd/mm/yyyy') AS dt FROM dual union ALL
SELECT 2 , 'CLT1' , to_date('03/04/2010', 'dd/mm/yyyy') FROM dual union ALL
SELECT 3 , 'CLT1' , to_date('15/04/2010', 'dd/mm/yyyy') FROM dual union ALL
SELECT 4 , 'CLT1' , to_date('08/05/2010', 'dd/mm/yyyy') FROM dual union ALL
SELECT 5 , 'CLT2' , to_date('01/06/2005', 'dd/mm/yyyy') FROM dual union ALL
SELECT 6 , 'CLT2' , to_date('25/07/2005', 'dd/mm/yyyy') FROM dual union ALL
SELECT 7 , 'CLT2' , to_date('09/10/2007', 'dd/mm/yyyy') FROM dual
)
SELECT nm, dt,
dt - lag(dt, 1, dt) over(partition by cl ORDER BY nm ASC) AS dt_diff,
case count(*) over(partition by cl order by nm asc)
when 1 then 0
else (dt - first_value(dt) over(partition by cl order by nm asc))
/ (count(*) over(partition by cl order by nm asc) -1)
end as moy
FROM MaTable;
NM DT DT_DIFF MOY
1 01/02/2010 0 0
2 03/04/2010 61 61
3 15/04/2010 12 36.5
4 08/05/2010 23 32
5 01/06/2005 0 0
6 25/07/2005 54 54
7 09/10/2007 806 430 |
Partager