这里是表格:
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
TO_CHAR(date_field, 'Q')
)。 - The Impaler