1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
|
WITH AGREGAT_TICKET_VENTE AS
(
SELECT to_date('22/12/2007', 'dd/mm/yyyy') AS JourDeVente, 16 AS CodeMagasin FROM dual union ALL
SELECT to_date('27/01/2007', 'dd/mm/yyyy') , 16 FROM dual union ALL
SELECT to_date('24/01/2007', 'dd/mm/yyyy') , 17 FROM dual union ALL
SELECT to_date('16/12/2006', 'dd/mm/yyyy') , 17 FROM dual union ALL
SELECT to_date('12/12/2006', 'dd/mm/yyyy') , 17 FROM dual union ALL
SELECT to_date('14/07/2006', 'dd/mm/yyyy') , 16 FROM dual union ALL
SELECT to_date('13/07/2006', 'dd/mm/yyyy') , 18 FROM dual
),
t as (
SELECT JourDeVente,CodeMagasin,grp
FROM agregat_ticket_vente
model
dimension by ( row_number()over(partition by 1 order by JourDeVente desc) rn)
measures(CodeMagasin,JourDeVente, 0 grp)ignore nav
(grp[rn]=case when nvl(CodeMagasin[cv()-1],CodeMagasin[cv()])=CodeMagasin[cv()] then
grp[cv()-1]
else grp[cv()-1]+1 end ))
SELECT jourdevente, codemagasin, COUNT (grp) OVER (PARTITION BY grp)cpt
FROM t |
Partager