CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-group
Weedoze
Weedoze

Reputation: 13943

ORACLE min date row for each code

I have 2 tables

PREVI_INFO

╔══════════╦══════════════╦═════════════╦══════════════════╦════════════╗
║ previ_id ║ code_station ║ code_modele ║    date_previ    ║ type_previ ║
╠══════════╬══════════════╬═════════════╬══════════════════╬════════════╣
║   501201 ║         2952 ║          48 ║ 29/01/2017 15:00 ║ AUTO       ║
║   501156 ║         2952 ║          48 ║ 30/01/2017 07:00 ║ AUTO       ║
╚══════════╩══════════════╩═════════════╩══════════════════╩════════════╝

PREVI_VAL

╔══════════╦═══════════════╦═════════════════════╦══════════════╗
║ previ_id ║ code_scenario ║        temps        ║ valeur_debit ║
╠══════════╬═══════════════╬═════════════════════╬══════════════╣
║   501201 ║            -1 ║ 30/01/2017 10:00:00 ║ 2,024        ║
║   501201 ║            -1 ║ 30/01/2017 13:00:00 ║ 2,215        ║
║   501201 ║             0 ║ 30/01/2017 10:00:00 ║ 1,456        ║
║   501201 ║             0 ║ 30/01/2017 13:00:00 ║ 1.687        ║
╚══════════╩═══════════════╩═════════════════════╩══════════════╝

QUERY

For a given code_model I need to get the nearest previ_val for a list of code_scenario

What I have now :

SELECT *
FROM (
  SELECT previ_id, type_previ
  FROM (
    SELECT previ_id, type_previ
    FROM PREVI.previ_info
    WHERE code_modele = '48'
    ORDER BY date_previ DESC
  ) pi
  WHERE ROWNUM < 2
) pi
JOIN PREVI.previ_val pv
ON pv.previ_id = pi.previ_id

which gives me

╔══════════╦════════════╦═══════════════╦═════════════════════╦════════════════════╗
║ previ_id ║ type_previ ║ code_scenario ║        temps        ║    valeur_debit    ║
╠══════════╬════════════╬═══════════════╬═════════════════════╬════════════════════╣
║   501201 ║ AUTO       ║ -1            ║ 30/01/2017 10:00:00 ║ 2,027503327181698  ║
║   501201 ║ AUTO       ║ -1            ║ 30/01/2017 13:00:00 ║ 2,289291759560228  ║
║   501201 ║ AUTO       ║ -1            ║ 30/01/2017 16:00:00 ║ 2,488605471829943  ║
║   501201 ║ AUTO       ║ -1            ║ 30/01/2017 19:00:00 ║ 2,5768532759013274 ║
║   501201 ║ AUTO       ║ -1            ║ 30/01/2017 22:00:00 ║ 2,5567552515698297 ║
║   501201 ║ AUTO       ║ -1            ║ 31/01/2017 01:00:00 ║ 2,4847510721331894 ║
║   501201 ║ AUTO       ║ -1            ║ 31/01/2017 04:00:00 ║ 2,371160558216584  ║
║   501201 ║ AUTO       ║ 0             ║ 30/01/2017 10:00:00 ║ 2,027503327181698  ║
║   501201 ║ AUTO       ║ 0             ║ 30/01/2017 13:00:00 ║ 2,281620351009415  ║
║   501201 ║ AUTO       ║ 0             ║ 30/01/2017 16:00:00 ║ 2,4679642018714993 ║
║   501201 ║ AUTO       ║ 0             ║ 30/01/2017 19:00:00 ║ 2,5426531265028185 ║
║   501201 ║ AUTO       ║ 0             ║ 30/01/2017 22:00:00 ║ 2,510706411016839  ║
║   501201 ║ AUTO       ║ 0             ║ 31/01/2017 01:00:00 ║ 2,4287719529773804 ║
║   501201 ║ AUTO       ║ 0             ║ 31/01/2017 04:00:00 ║ 2,308307979316664  ║
║   501201 ║ AUTO       ║ 0,25          ║ 30/01/2017 10:00:00 ║ 2,027503327181698  ║
║   501201 ║ AUTO       ║ 0,25          ║ 30/01/2017 13:00:00 ║ 2,2950103323648503 ║
║   501201 ║ AUTO       ║ 0,25          ║ 30/01/2017 16:00:00 ║ 2,506610245355028  ║
║   501201 ║ AUTO       ║ 0,25          ║ 30/01/2017 19:00:00 ║ 2,6144186413564663 ║
║   501201 ║ AUTO       ║ 0,25          ║ 30/01/2017 22:00:00 ║ 2,63992592676027   ║
║   501201 ║ AUTO       ║ 0,25          ║ 31/01/2017 01:00:00 ║ 2,6660032204209982 ║
║   501201 ║ AUTO       ║ 0,25          ║ 31/01/2017 04:00:00 ║ 2,689414559791597  ║
╚══════════╩════════════╩═══════════════╩═════════════════════╩════════════════════╝

Desired result

╔══════════╦════════════╦═══════════════╦═════════════════════╦════════════════════╗
║ previ_id ║ type_previ ║ code_scenario ║        temps        ║    valeur_debit    ║
╠══════════╬════════════╬═══════════════╬═════════════════════╬════════════════════╣
║   501201 ║ AUTO       ║ -1            ║ 30/01/2017 13:00:00 ║ 2,289291759560228  ║
║   501201 ║ AUTO       ║ 0             ║ 30/01/2017 13:00:00 ║ 2,281620351009415  ║
╚══════════╩════════════╩═══════════════╩═════════════════════╩════════════════════╝

This gives me the row with the next date. if it is 30/01/2017 12:45, I need the row with 30/01/2017 13:00:00 not 10:00:00. The codes are from a list. For the moment it is 0 and -1 only.

How can I improve my query to make it works ?

Upvotes: 3

Views: 74

Answers (1)

MT0
MT0

Reputation: 167962

For a given code_model I need to get the nearest previ_val for a list of code_scenario

You can use the ROW_NUMBER analytic function. It is unclear how you are partitioning the data but something like this should work:

SELECT *
FROM   (
  SELECT i.*,
         v.*,
         ROW_NUMBER() OVER ( PARTITION BY i.previ_id, v.code_scenario
                             ORDER BY ABS( i.date_previ - v.temps ) ) AS rn
  FROM   ( SELECT *
           FROM   ( SELECT *
                    FROM   PREVI_INFO
                    WHERE  i.code_modele = 48
                    ORDER BY date_previ DESC )
           WHERE ROWNUM = 1
         ) i
         INNER JOIN PREVI_VAL v
         ON ( i.previ_id = v.previ_id )
)
WHERE rn = 1;

Update:

I need to get the date that is the next compared to now.

This will join the tables where the temps value is after the SYSDATE and then get the first chronologically:

SELECT *
FROM   (
  SELECT i.*,
         v.*,
         ROW_NUMBER() OVER ( PARTITION BY i.previ_id, v.code_scenario
                             ORDER BY v.temps ) AS rn
  FROM   ( SELECT *
           FROM   ( SELECT *
                    FROM   PREVI_INFO
                    WHERE  i.code_modele = 48
                    ORDER BY date_previ DESC )
           WHERE ROWNUM = 1
         ) i
         INNER JOIN PREVI_VAL v
         ON (   i.previ_id = v.previ_id 
            AND v.temps >= SYSDATE )
)
WHERE rn = 1;

Upvotes: 2

Related Questions