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

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

- StackOne

---

SQL FIDDLE在这里：http://sqlfiddle.com/#!4/01217/33 - StackOne

兄弟，我不想打击你，但是没有人会读上面的查询语句。太疯狂了。最好抽象出你想要做什么以及使用哪些数据。 - rAndom69

有4个查询彼此连接，唯一的区别是where子句选择了不同的日期。我想知道是否可以减少行数和提高性能。 - StackOne

我会使用单个CTE，也按季度分组（函数TO_CHAR(date_field, 'Q')）。 - The Impaler

只是提醒一下，您的查询会返回价格达到峰值的最后日期。如果有多个日期，它只会显示最后一个。只是说一下。 - The Impaler

是的@The Impaler，这就是计划。获取最新日期。修复了表格上的多个日期问题。 - StackOne

Answer 1

也许你可以用更简洁的方式重写它，例如 SQL Fiddle。

select a.key, qtr, a.max_price, max(t.dat) as qo_dat 
from ABC t
join (
  select key, to_char(dat, 'Q') as qtr, max(cost) as max_price 
  from ABC
  where dat >= to_date('01-01-2017', 'mm-dd-yyyy') 
    and dat < to_date('01-01-2018', 'mm-dd-yyyy')
  group by key, to_char(dat, 'Q')
) a on a.key = t.key and a.max_price = t.cost and a.qtr = to_char(t.dat, 'Q')
group by a.key, a.qtr, a.max_price
order by a.key, a.qtr, a.max_price

输出有点不同，但它展示了你想要的。是吗？

- The Impaler

---

很好的回答，但我需要问题中所示的日期和格式，因为我将向此结果集添加其他列。 - StackOne

Answer 2

不要使用JOIN或交叉连接，考虑使用分析函数NTH_VALUE（请参见文档）来显示4个季度所需的值。

NTH_VALUE返回由analytic_clause定义的窗口中第n行的measure_expr值。

第一步：找到所有键（和季度）的“最大成本”及其相应日期。

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. 

最终查询：将第一个查询用作内联视图，并调用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 

- stefan

---

根据文档（例如https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Analytic-Functions.html#GUID-527832F7-63C0-4445-8C16-307FA5084056），OVER（）是“分析子句”，用于“分析函数”。PARTITION BY创建所谓的滑动窗口。与GROUP BY的一个不同之处是：对于每个组，您将获得多个值 - 这就是为什么我们使用SELECT UNIQUE…来获取最终结果的原因。 - stefan

你在每月处理这个时，该怎么做呢？我尝试过使用 to_char(dat, 'MM') 并且使用了 nth_value(max_cost,1) ... to ... nth_value(max_cost,12)。 - StackOne

我可能首先会使用PARTITION BY外连接来DENSIFY数据，然后再应用N_TH值。我已经为您编写了一个示例，请参见 https://dbfiddle.uk/?rdbms=oracle_18&fiddle=7b8917caeff3fea123b38cf6f1b31938 - stefan

谢谢，Stefan。这是一种快速的技术。 - StackOne

为此，我建议：找到所有月份/年份组合（始终使用4位数字表示年份！），然后使用PARTITION BY ... RIGHT JOIN。这将为每个月/年份提供行。请参见（最后一个查询@）https://dbfiddle.uk/?rdbms=oracle_18&fiddle=78f62687846351ab8e5a99bf54c6a201 - stefan

Answer 3

select a.key, a.q1tot, b.dat, a.q2tot, c.dat, a.q3tot, d.dat, a.q4tot, e.dat from (
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) a
    join 
 ( select key, cost, dat from ABC
  where dat < to_date('04-01-2017', 'mm-dd-yyyy')) b
  on a.key = b.key and a.Q1tot = b.cost
  join
  ( select key, cost, dat from ABC
   where dat >= to_date('04-01-2017', 'mm-dd-yyyy') and dat < to_date('07-01-2017', 
'mm-dd-yyyy')) c
 on a.key = c.key and a.Q1tot = c.cost
 join
 ( select key, cost, dat  from ABC
  where dat >= to_date('07-01-2017', 'mm-dd-yyyy') and dat < to_date('10-01-2017', 
'mm-dd-yyyy')) d
 on a.key = d.key and a.Q1tot = d.cost
    join
 ( select key, cost, dat from ABC
  where dat >= to_date('10-01-2017', 'mm-dd-yyyy') and dat < to_date('01-01-2018', 'mm-dd-yyyy')) e
  on a.key = e.key and a.Q1tot = e.cost

这是我的代码，但上面的两个查询执行得更快。

- StackOne