sql - 查询优化以减少多个连接语句

Question

这是表:

CREATE TABLE ABC
(
     key NUMBER(5), 
     orders NUMBER(5), 
     cost NUMBER(5), 
     dat DATE
);

insert into ABC (key, orders, cost, dat) values (1, 3, 5, to_date('10-11- 
2017', 'mm-dd-yyyy'));
insert into ABC (key, orders, cost,dat) values (1, 5, 2, to_date('02-10- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (1, 6, 1, to_date('03-10- 
2017', 'mm-dd-yyyy'));
insert into ABC (key, orders, cost,dat) values (1, 7, 2, to_date('05-10- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (1, 8, 3, to_date('07-10- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (1, 3, 4, to_date('08-10- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (2, 3, 6, to_date('02-10- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (2, 3, 9, to_date('01-10- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (2, 2 ,5, to_date('03-10- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (2, 3, 2, to_date('05-10- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (2, 1, 1, to_date('06-10- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (3, 4, 12, to_date('10-10- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (3, 3, 9, to_date('01-10- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (3, 2 ,5, to_date('05-10- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (3, 3, 2, to_date('06-10- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (3, 1, 1, to_date('07-10- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (3, 4, 12, to_date('11-10- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost, dat) values (1, 3, 5, to_date('10-01- 
2017', 'mm-dd-yyyy'));
insert into ABC (key, orders, cost,dat) values (1, 5, 2, to_date('02-17- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (1, 6, 1, to_date('03-18- 
2017', 'mm-dd-yyyy'));
insert into ABC (key, orders, cost,dat) values (1, 7, 2, to_date('05-14- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (1, 8, 3, to_date('07-13- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (1, 3, 4, to_date('08-12- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (2, 3, 6, to_date('02-11- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (2, 3, 9, to_date('01-15- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (2, 2 ,5, to_date('03-14- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (2, 3, 2, to_date('05-18- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (2, 1, 1, to_date('06-19- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (3, 4, 12, to_date('10-11- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (3, 3, 9, to_date('01-12- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (3, 2 ,5, to_date('05-16- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (3, 3, 2, to_date('06-17- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (3, 1, 1, to_date('07-12- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (3, 4, 12, to_date('12-21- 
2017', 'mm-dd-yyyy')); 

不知道为什么我的结果会重复。

这是我的查询:

with qone as
(select a.key, a.max_price, max(t.dat) as qo_dat from  ABC t
JOIN
(select key, max(cost) as max_price from ABC
where dat >= to_date('01-01-2017', 'mm-dd-yyyy') and dat < to_date('04-01- 
2017', 'mm-dd-yyyy')
group by key) a on a.key = t.key and a.max_price = t.cost
group by a.key, a.max_price),
qtwo as
(select a.key, a.max_price, max(t.dat) as qt_dat from  ABC t
JOIN
(select key, max(cost) as max_price from ABC
where dat >= to_date('04-01-2017', 'mm-dd-yyyy') and dat < to_date('07-01- 
2017', 'mm-dd-yyyy')
group by key) a on a.key = t.key and a.max_price = t.cost
group by a.key, a.max_price),
qthree as
(select a.key, a.max_price, max(t.dat) as qth_dat from  ABC t
JOIN
(select key, max(cost) as max_price from ABC
where dat >= to_date('07-01-2017', 'mm-dd-yyyy') and dat < to_date('10-01- 
2017', 'mm-dd-yyyy')
group by key) a on a.key = t.key and a.max_price = t.cost
group by a.key, a.max_price),
qfour as
(select a.key, a.max_price, max(t.dat) as qf_dat from  ABC t
JOIN
(select key, max(cost) as max_price from ABC
where dat >= to_date('10-01-2017', 'mm-dd-yyyy') and dat < to_date('01-01- 
2018', 'mm-dd-yyyy')
group by key) a on a.key = t.key and a.max_price = t.cost
group by a.key, a.max_price)
select qo.key, qo.max_price as max_q1, qo.qo_dat, qt.max_price as max_q2, 
qt.qt_dat, qth.max_price as max_q3, qth.qth_dat, qf.max_price as max_q4, 
qf.qf_dat from qone qo
join qtwo qt on qt.key = qo.key 
join qthree qth on qth.key = qth.key
join qfour qf on qf.key = qf.key
order by keyenter code here

我想知道是否有办法减少线条。

我是怎么做到的？我找到每个季度的最大价格和最大日期，我使用 where 语句定义季度。

我使用分而治之的技术，我找到了所有四个季度的最高价格和各自的日期，并在关键点上加入它们。下面一个自定义季度的示例。

`select a.key, a.max_price, max(t.dat) as qo_dat from  ABC t
JOIN
(select key, max(cost) as max_price from ABC
where dat >= to_date('01-01-2017', 'mm-dd-yyyy') and dat < to_date('04-01- 
2017', 'mm-dd-yyyy')
group by key) a on a.key = t.key and a.max_price = t.cost
group by a.key, a.max_price`

输出:



可能的优化解决方案:但我正在想办法在它旁边添加相应的日期

select 
    t.key, 
    max( case when t.dat >= Tmp.Q1From and t.dat < Tmp.Q1End then t.cost 
else 0 end ) as Q1Tot, 
    max( case when t.dat >= Tmp.Q1End and t.dat < Tmp.Q2End then t.cost else 
0 end ) as Q2Tot, 
    max( case when t.dat >= Tmp.Q2End and t.dat < Tmp.Q3End then t.cost else 
0 end ) as Q3Tot, 
    max( case when t.dat >= Tmp.Q3End and t.dat < Tmp.Q4End then t.cost else 
0 end ) as Q4Tot 
from 
    ABC t,
       ( select 
               to_date('01-01-2017', 'mm-dd-yyyy') Q1From,
               to_date('04-01-2017', 'mm-dd-yyyy') Q1End,
               to_date('07-01-2017', 'mm-dd-yyyy') Q2End,
               to_date('10-01-2017', 'mm-dd-yyyy') Q3End,
               to_date('01-01-2018', 'mm-dd-yyyy') Q4End
            from 
               dual ) Tmp
 where 
        t.dat >= to_date('01-01-2017', 'mm-dd-yyyy')
    and t.dat < to_date('01-01-2018', 'mm-dd-yyyy')
 group by 
    t.key

Answer 1

考虑使用分析函数 NTH_VALUE(参见 documentation)来并排显示 4 个季度所需的值，而不是使用 JOIN 或交叉连接。

NTH_VALUE returns the measure_expr value of the nth row in the window defined by the analytic_clause.

第一步:找到所有 key (和季度)的“最大成本”及其对应的日期。

select *
from (
  select key, dat, to_char( dat, 'Q' ) quarter 
  , max( cost ) over ( partition by key, to_char( dat, 'Q' ) order by cost desc ) maxcost_
  , max( dat ) over ( partition by key, to_char( dat, 'Q' ) order by cost desc ) maxdat_
  , row_number()  over ( partition by key, to_char( dat, 'Q' ) order by cost desc ) rownum_
    from abc
)
where rownum_ = 1 

-- result
KEY  DAT        QUARTER  MAXCOST_  MAXDAT_    ROWNUM_  
1    17-FEB-17  1        2         17-FEB-17  1        
1    14-MAY-17  2        2         14-MAY-17  1        
1    12-AUG-17  3        4         12-AUG-17  1        
1    01-OCT-17  4        5         11-OCT-17  1        
2    10-JAN-17  1        9         15-JAN-17  1        
2    10-MAY-17  2        2         18-MAY-17  1        
3    10-JAN-17  1        9         12-JAN-17  1        
3    10-MAY-17  2        5         16-MAY-17  1        
3    10-JUL-17  3        1         12-JUL-17  1        
3    10-NOV-17  4        12        21-DEC-17  1        

10 rows selected. 

最终查询:将第一个查询用作 INLINE VIEW，并调用 NTH_VALUE 来检索每个季度的值。

select unique key
,  nth_value( maxcost_, 1 ) from first over ( partition by key ) q1max
,  nth_value( maxdat_, 1 ) from first over ( partition by key ) q1date
,  nth_value( maxcost_, 2 ) from first over ( partition by key ) q2max
,  nth_value( maxdat_, 2 ) from first over ( partition by key ) q2date
,  nth_value( maxcost_, 3 ) from first over ( partition by key ) q3max
,  nth_value( maxdat_, 3 ) from first over ( partition by key ) q3date
,  nth_value( maxcost_, 4 ) from first over ( partition by key ) q4max
,  nth_value( maxdat_, 4 ) from first over ( partition by key ) q4date
from (
  select *
  from ( 
    select key, dat, to_char( dat, 'Q' ) quarter 
    , max( cost ) over ( partition by key, to_char( dat, 'Q' ) order by cost desc ) maxcost_
    , max( dat ) over ( partition by key, to_char( dat, 'Q' ) order by cost desc ) maxdat_
    , row_number()  over ( partition by key, to_char( dat, 'Q' ) order by cost desc ) rownum_
    from abc
  )
  where rownum_ = 1  
) -- inline view (no name required)
order by key
;

-- result
KEY  Q1MAX  Q1DATE     Q2MAX  Q2DATE     Q3MAX  Q3DATE     Q4MAX  Q4DATE     
1    2      17-FEB-17  2      14-MAY-17  4      12-AUG-17  5      11-OCT-17  
2    9      15-JAN-17  2      18-MAY-17  NULL   NULL       NULL   NULL       
3    9      12-JAN-17  5      16-MAY-17  1      12-JUL-17  12     21-DEC-17