如何提高查询中的Oracle row_number [英] How to improve oracle row_number in query

问题描述

Table_A
-------------------
A_id          
-------------------
1     

Table_B
-------------------
B_id   |     A_id
-------------------
1            1
2            1
3            1

Table_C
-----------------------------
B_id  |   Process_date
-----------------------------
1         20130101 12:20:01
2         20130101 12:10:01
3         20130101 13:00:01

如何基于Table_C计时窗口从带有引用Table_A.A_id的Table_C中检索最大的process_date.如果我想在计时窗口20130101 12:09:00到12:21:00中检索Table_C.b_id和max(process_date)，则它应该返回id为1和process_date为12:20:01

How to retrieve the maximum process_date from Table_C with references of Table_A.A_id based on Table_C timing window. If I want to retrieve Table_C.b_id and max(process_date) in timing window 20130101 12:09:00 to 12:21:00 then it should return id as 1 and process_date as 12:20:01

以下查询我正在使用:

select b_id, 
       process_date
  from (select c1.b_id,
               c1.process_date,
               row_number() over(partition by a.a_id 
                                 order by c1.process_date desc) rn
          from table_a a
               inner join 
               table_b b
                 on a.a_id = b.a_id
               inner join 
               table_c c1
                 on b.b_id = c1.b_id
       ) 
 where rn = 1;

推荐答案

如果遵循，您需要给定a_id的最大处理日期和相应的b_id，其中处理日期在设置的日期范围内.这是不需要任何分析函数或行号的解决方案.我正在使用with子句和double来复制您的示例表.如果您有table_a，table_b，table_c，则可以将其删除.

If I follow, you want the maximum process date and corresponding b_id for a given a_id where the process date is within a set date range. Here is a solution not requiring any analytic functions or row number. I'm using a with clause and dual just to replicate your sample tables. You can remove that if you have table_a, table_b, table_c.

with table_a as 
(
 select 1 a_id from dual
),
table_b as 
(
 select 1 b_id, 1 a_id from dual union all
 select 2 b_id, 1 a_id from dual union all
 select 3 b_id, 1 a_id from dual
), table_c as
(
 select 1 b_id, to_date('20130101 12:20:01', 'yyyymmdd hh24:mi:ss') process_date from dual union all
 select 2 b_id, to_date('20130101 12:10:01', 'yyyymmdd hh24:mi:ss') process_date from dual union all
 select 3 b_id, to_date('20130101 13:00:01', 'yyyymmdd hh24:mi:ss') process_date from dual
) 
select table_c.b_id, 
       table_c.process_date
  from table_b,
       table_c
 where table_b.b_id = table_c.b_id
   and table_b.a_id = 1
   and table_c.process_date = (select max(process_date)
                                 from table_b b2,
                                      table_c c2
                                where table_b.a_id = b2.a_id
                                  and b2.b_id = c2.b_id
                                  and c2.process_date between to_date('20130101 12:09:00', 'yyyymmdd hh24:mi:ss')  and 
                                                              to_date('20130101 12:21:00', 'yyyymmdd hh24:mi:ss')
                              )

返回:

----------------------------
b_id  |  process_date
----------------------------
1       1/1/2013 12:20:01

这篇关于如何提高查询中的Oracle row_number的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！