我有两张 table

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        ║ 
╚══════════╩═══════════════╩═════════════════════╩══════════════╝ 

查询

对于给定的 code_model，我需要为 code_scenario 列表获取最接近的 previ_val

我现在拥有的:

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 

这给了我

╔══════════╦════════════╦═══════════════╦═════════════════════╦════════════════════╗ 
║ 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  ║ 
╚══════════╩════════════╩═══════════════╩═════════════════════╩════════════════════╝ 

想要的结果

╔══════════╦════════════╦═══════════════╦═════════════════════╦════════════════════╗ 
║ 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  ║ 
╚══════════╩════════════╩═══════════════╩═════════════════════╩════════════════════╝ 

这给了我下一个日期的行。如果是 30/01/2017 12:45，我需要 30/01/2017 13:00:00 而不是 10:00:00 的行。 这些代码来自列表。目前只有 0 和 -1。

如何改进我的查询以使其有效？

请您参考如下方法:

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

您可以使用ROW_NUMBER 分析函数。目前还不清楚你是如何划分数据的，但这样的事情应该可行:

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; 

更新:

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

这将连接 temps 值在 SYSDATE 之后的表，然后按时间顺序获取第一个:

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;