1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
| WITH MYDATA_LVL1_N0 as ( SELECT IDFICHEANNONCEUR,AA.IDFICHEPASSAGE1,AA.IDFICHEPASSAGE2,sum(PRIX) as PRIX
FROM (SELECT IDFICHEANNONCEUR,AA.IDFICHEPASSAGE as IDFICHEPASSAGE1,AA.IDFICHEPASSAGE as IDFICHEPASSAGE2,sum(PRIX) as PRIX
FROM AW3_AGG_3_2010_3 AA
WHERE (AA.IDCLASSEMEDIA in ( 3,142) ) AND ( DATEACTIVITE between to_date('2010-04-01 00:00:00','YYYY-MM-DD HH24:MI:SS') AND to_date('2010-04-30 23:59:59','YYYY-MM-DD HH24:MI:SS') )
GROUP BY IDFICHEANNONCEUR,AA.IDFICHEPASSAGE
UNION ALL
SELECT IDFICHEANNONCEUR,AA.IDFICHEPASSAGE as IDFICHEPASSAGE1,AA.IDFICHEPASSAGE as IDFICHEPASSAGE2,sum(PRIX) as PRIX
FROM AW3_AGG_3_2010_142 AA
WHERE (AA.IDCLASSEMEDIA in ( 3,142) ) AND ( DATEACTIVITE between to_date('2010-04-01 00:00:00','YYYY-MM-DD HH24:MI:SS') AND to_date('2010-04-30 23:59:59','YYYY-MM-DD HH24:MI:SS') )
GROUP BY IDFICHEANNONCEUR,AA.IDFICHEPASSAGE) AA
GROUP BY IDFICHEANNONCEUR,AA.IDFICHEPASSAGE1,AA.IDFICHEPASSAGE2),
MYDATA_LVL2_N0 as ( SELECt * FROM (
SELECT AA.*,
dense_rank() OVER(ORDER BY PART_SCORE DESC,IDFICHEPASSAGE1,IDFICHEPASSAGE2) AS RN ,
1 as RN_H
FROM ( SELECT AA.*, 0 as PART_SCORE FROM MYDATA_LVL1_N0 AA ) AA
)
WHERE RN > 0 AND RN <= 100 AND RN_H <= 100
)
SELECT PRIX,IDFICHEANNONCEUR,AA.IDFICHEPASSAGE1,AA.IDFICHEPASSAGE2,RN,RN_H,L0.POSITIONSPOT FROM MYDATA_LVL2_N0 AA
LEFT JOIN AW3_FICHEPASSAGE L0 ON L0.IDFICHEPASSAGE = AA.IDFICHEPASSAGE1 |
Partager