|
Cold bringerУгу, не стояло. Бдительный INFORMIX не даёт делать UNION и использовать FIRST в SELECT выражениях. Также не даёт использовать FIRST в table(multiset()). Я просто в шоке.
Я даже попытался сырые данные о продажах в ORACLE перетянуть, чтобы там уже обрабатывать, но это слишком долго тянется.
У меня подозрение, что для достижения искомой цели мне придётся забирать данные отдельными запросами, для каждого склада, и для каждой даты - в рамках одного коннекта - обалдеть.
Два дня уже убил.
Во-первых, не знаю какой у вас сервер, а у меня лаптоп вернул результаты для 400,000 записей за 10 минут :
---------
create table s_total (
t_from char(30),
t_date date,
t_kod int,
t_sum money(10,2)
);
create procedure ins_total (n int);
define i int;
define j int;
define k int;
define l int;
for i = 1 to n
LET l= mod(i, 20);
LET j = mod(i,30);
LET k = mod(i, 100);
insert into s_total values (l, today - j UNITS DAY, k, i);
end for;
end procedure;
call ins_total (400000)
create view mySUM (mystore, mydate, tov, mysum)
as
SELECT t_from,
t_date,
t_kod,
Sum(t_sum)
FROM s_total
WHERE t_date BETWEEN (TODAY - 31 UNITS DAY) AND (TODAY)
GROUP BY t_from, t_date, t_kod
--------- sel.sql ------
database dbase;
select a.myStore, a.myDate, a.TOV, a.mySum
from mySum A
where
(select count(*) from mySum B where
a.myStore = b.myStore
and a.myDate = b.myDate
and a.mySum < b.mySum) < 10
--------
/home/xxx>time dbaccess - sel.sql > /dev/null
Database selected.
300 row(s) retrieved.
Database closed.
real 10m22.572s
user 0m0.015s
sys 0m0.031s
-----------------
Во-вторых, есть обходной вариант для очень быстрого выполнения, которым я пользовался в подобной ситуации :
--------------- Variant 2 ------------
create temp table temp_t1 (
mystore char(30),
mydate date,
tov int,
mysum money(15,2)
);
create temp table temp_res (
mystore char(30),
mydate date,
tov int,
mysum money(15,2)
);
/* create a copy of summarized data */
insert into temp_t1
SELECT t_from as mystore,
t_date as mydate,
t_kod as tov,
Sum(t_sum) as mysum
FROM s_total
WHERE t_date BETWEEN (TODAY - 31 UNITS DAY) AND (TODAY)
GROUP BY t_from, t_date, t_kod;
/* find tovar with max mysum for each day, each store */
select mystore, mydate, max(mysum) mysum
from temp_t1
group by mystore, mydate
into temp temp_t3;
/* 1st place */
insert into temp_res
select a.mystore, a.mydate, a.tov, a.mysum
from temp_t1 a, temp_t3 b
where a.mystore= b.mystore
and a.mydate = b.mydate
and a.mysum = b.mysum;
delete from temp_t1
where exists (select 1 from temp_res
where temp_t1.mystore= temp_res.mystore
and temp_t1.mydate = temp_res.mydate
and temp_t1.mysum = temp_res.mysum);
/* 2nd place */
drop table temp_t3;
select mystore, mydate, max(mysum) mysum
from temp_t1
group by mystore, mydate
into temp temp_t3;
insert into temp_res
select a.mystore, a.mydate, a.tov, a.mysum
from temp_t1 a, temp_t3 b
where a.mystore= b.mystore
and a.mydate = b.mydate
and a.mysum = b.mysum;
delete from temp_t1
where exists (select 1 from temp_res
where temp_t1.mystore= temp_res.mystore
and temp_t1.mydate = temp_res.mydate
and temp_t1.mysum = temp_res.mysum);
/* 3rd place */
drop table temp_t3;
select mystore, mydate, max(mysum) mysum
from temp_t1
group by mystore, mydate
into temp temp_t3;
insert into temp_res
select a.mystore, a.mydate, a.tov, a.mysum
from temp_t1 a, temp_t3 b
where a.mystore= b.mystore
and a.mydate = b.mydate
and a.mysum = b.mysum;
delete from temp_t1
where exists (select 1 from temp_res
where temp_t1.mystore= temp_res.mystore
and temp_t1.mydate = temp_res.mydate
and temp_t1.mysum = temp_res.mysum);
/* 4 place */
drop table temp_t3;
select mystore, mydate, max(mysum) mysum
from temp_t1
group by mystore, mydate
into temp temp_t3;
insert into temp_res
select a.mystore, a.mydate, a.tov, a.mysum
from temp_t1 a, temp_t3 b
where a.mystore= b.mystore
and a.mydate = b.mydate
and a.mysum = b.mysum;
delete from temp_t1
where exists (select 1 from temp_res
where temp_t1.mystore= temp_res.mystore
and temp_t1.mydate = temp_res.mydate
and temp_t1.mysum = temp_res.mysum);
/* 5 place */
drop table temp_t3;
select mystore, mydate, max(mysum) mysum
from temp_t1
group by mystore, mydate
into temp temp_t3;
insert into temp_res
select a.mystore, a.mydate, a.tov, a.mysum
from temp_t1 a, temp_t3 b
where a.mystore= b.mystore
and a.mydate = b.mydate
and a.mysum = b.mysum;
delete from temp_t1
where exists (select 1 from temp_res
where temp_t1.mystore= temp_res.mystore
and temp_t1.mydate = temp_res.mydate
and temp_t1.mysum = temp_res.mysum);
/* 6 place */
drop table temp_t3;
select mystore, mydate, max(mysum) mysum
from temp_t1
group by mystore, mydate
into temp temp_t3;
insert into temp_res
select a.mystore, a.mydate, a.tov, a.mysum
from temp_t1 a, temp_t3 b
where a.mystore= b.mystore
and a.mydate = b.mydate
and a.mysum = b.mysum;
delete from temp_t1
where exists (select 1 from temp_res
where temp_t1.mystore= temp_res.mystore
and temp_t1.mydate = temp_res.mydate
and temp_t1.mysum = temp_res.mysum);
/* 7 place */
drop table temp_t3;
select mystore, mydate, max(mysum) mysum
from temp_t1
group by mystore, mydate
into temp temp_t3;
insert into temp_res
select a.mystore, a.mydate, a.tov, a.mysum
from temp_t1 a, temp_t3 b
where a.mystore= b.mystore
and a.mydate = b.mydate
and a.mysum = b.mysum;
delete from temp_t1
where exists (select 1 from temp_res
where temp_t1.mystore= temp_res.mystore
and temp_t1.mydate = temp_res.mydate
and temp_t1.mysum = temp_res.mysum);
/* 8 place */
drop table temp_t3;
select mystore, mydate, max(mysum) mysum
from temp_t1
group by mystore, mydate
into temp temp_t3;
insert into temp_res
select a.mystore, a.mydate, a.tov, a.mysum
from temp_t1 a, temp_t3 b
where a.mystore= b.mystore
and a.mydate = b.mydate
and a.mysum = b.mysum;
delete from temp_t1
where exists (select 1 from temp_res
where temp_t1.mystore= temp_res.mystore
and temp_t1.mydate = temp_res.mydate
and temp_t1.mysum = temp_res.mysum);
/* 9 place */
drop table temp_t3;
select mystore, mydate, max(mysum) mysum
from temp_t1
group by mystore, mydate
into temp temp_t3;
insert into temp_res
select a.mystore, a.mydate, a.tov, a.mysum
from temp_t1 a, temp_t3 b
where a.mystore= b.mystore
and a.mydate = b.mydate
and a.mysum = b.mysum;
delete from temp_t1
where exists (select 1 from temp_res
where temp_t1.mystore= temp_res.mystore
and temp_t1.mydate = temp_res.mydate
and temp_t1.mysum = temp_res.mysum);
/* 10 place */
drop table temp_t3;
select mystore, mydate, max(mysum) mysum
from temp_t1
group by mystore, mydate
into temp temp_t3;
insert into temp_res
select a.mystore, a.mydate, a.tov, a.mysum
from temp_t1 a, temp_t3 b
where a.mystore= b.mystore
and a.mydate = b.mydate
and a.mysum = b.mysum;
delete from temp_t1
where exists (select 1 from temp_res
where temp_t1.mystore= temp_res.mystore
and temp_t1.mydate = temp_res.mydate
and temp_t1.mysum = temp_res.mysum);
select * from temp_res;
----------
300 row(s) retrieved.
Database closed.
real 0m3.591s
user 0m0.031s
sys 0m0.015s
Инджой
|
