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
| SELECT
MFT_AR_DETAILR_YRES_ID,
max(MFT_AR_DETAILR_PRIXA) AS prix,
count(*) AS Nbr_jours,
min(MFT_AR_DETAILR_CURRENT_DATE) AS Du,
max(MFT_AR_DETAILR_CURRENT_DATE) AS Au
FROM
(
SELECT
MFT_AR_DETAILR_YRES_ID,
MFT_AR_DETAILR_PRIXA,
MFT_AR_DETAILR_CURRENT_DATE,
sum(dif) OVER (PARTITION BY MFT_AR_DETAILR_YRES_ID ORDER BY MFT_AR_DETAILR_CURRENT_DATE) AS plage
FROM (
SELECT
MFT_AR_DETAILR_YRES_ID,
MFT_AR_DETAILR_PRIXA,
MFT_AR_DETAILR_CURRENT_DATE,
coalesce(abs(MFT_AR_DETAILR_PRIXA-lag(MFT_AR_DETAILR_PRIXA) over (PARTITION BY MFT_AR_DETAILR_YRES_ID ORDER BY MFT_AR_DETAILR_CURRENT_DATE)),0) AS dif
FROM MFT_AR_DETAILR x where not exists (select * from MFT_AR_DETAILR y
where MFT_AR_DETAILR_PRIXA is null
and x.MFT_AR_DETAILR_YRES_ID=y.MFT_AR_DETAILR_YRES_ID)
)
)
GROUP BY MFT_AR_DETAILR_YRES_ID, plage
ORDER BY MFT_AR_DETAILR_YRES_ID, Du; |
Partager