Последние - значит одна покупка для каждого покупателя за максимальную дату?

Опять я не понял, что значит последние? Последние за какой период? Самый последний? Почему именно три последних, а не два?

Почему, напрмер andrey__Майки_____03.09.02__30р
не входят, а andrey__Вода______05.09.02__20р входит?


Можно как-то по конкретней

Я кажется понял, имеется ввиду выбрать последние покупки, общая сумма которых не более 150р, да?

А это задача чисто теоретическая, т.е. нельзя создавать индексы, нельзя использовать хинты, причём сделать всё одним запросом, т.е в целом именно к Oracle не имеет отношения?

Щас подумаем

Получайте, только есть замечания:

1. Вариант не самы красивый
2. Используются хинты, надо быть аккуратным в их написании. Малейшая ошибка - запрос работать не будет
3. Можно написать другой более красивый с использованием CAST И обьектов. Если надо напишу.

-- создадим таблицу
create table orders
(
client VARCHAR2(20),
product VARCHAR2(20),
date_pay DATE,
sum_pay NUMBER(36)
)
;

-- Заполним данными
insert into orders values('andrey','Сигареты',TO_DATE('01.09.2002','dd.mm.yyyy'),30);
insert into orders values('andrey','Трусы',TO_DATE('02.09.2002','dd.mm.yyyy'),10);
insert into orders values('andrey','Майки',TO_DATE('03.09.2002','dd.mm.yyyy'),30);
insert into orders values('andrey','Конфеты',TO_DATE('04.09.2002','dd.mm.yyyy'),90);
insert into orders values('andrey','Вода',TO_DATE('05.09.2002','dd.mm.yyyy'),20);
insert into orders values('andrey','Сок',TO_DATE('06.09.2002','dd.mm.yyyy'),30);

insert into orders values('serg','Сигареты',TO_DATE('01.09.2002','dd.mm.yyyy'),40);
insert into orders values('serg','Трусы',TO_DATE('02.09.2002','dd.mm.yyyy'),15);
insert into orders values('serg','Майки',TO_DATE('03.09.2002','dd.mm.yyyy'),33);
insert into orders values('serg','Конфеты',TO_DATE('04.09.2002','dd.mm.yyyy'),60);
insert into orders values('serg','Вода',TO_DATE('05.09.2002','dd.mm.yyyy'),10);
insert into orders values('serg','Сок',TO_DATE('06.09.2002','dd.mm.yyyy'),20);

commit;

-- индекс
create index i1_orders on orders(client, date_pay);

CREATE OR REPLACE FUNCTION get_count_orders(p_client orders.client%TYPE, p_sum orders.sum_pay%TYPE)
RETURN NUMBER IS
CURSOR c_orders (p_client orders.client%TYPE) IS
select /*+ INDEX_DESC(orders i1_orders) */
sum_pay
from
orders
where
client = p_client and
date_pay is not null;

v_all_sum orders.sum_pay%TYPE:=0;
v_cnt NUMBER(16):=0;
BEGIN
FOR v_orders IN c_orders(p_client) LOOP
v_all_sum:=v_all_sum+v_orders.sum_pay;

IF v_all_sum<p_sum THEN
v_cnt:=v_cnt+1;
ELSE
RETURN v_cnt+1;
END IF;
END LOOP;
END;

-- Запрос
select /*+ INDEX_DESC(orders i1_orders) */
*
from
orders
where
client = 'andrey' and
date_pay is not null and
rownum < get_count_orders('andrey');

SQLWKS> -- Запрос
SQLWKS> select /*+ INDEX_DESC(orders i1_orders) */
2> *
3> from
4> orders
5> where
6> client = 'andrey' and
7> date_pay is not null and
8> rownum < get_count_orders('andrey',150)
9> order by date_pay asc
10>
CLIENT PRODUCT DATE_PAY SUM_PAY
-------------------- -------------------- -------------------- ----------
andrey Конфеты 04.09.02 90
andrey Вода 05.09.02 20
andrey Сок 06.09.02 30
Выбрано 3 строк.


-- Запрос
select /*+ INDEX_DESC(orders i1_orders) */
*
from
orders
where
client = 'serg' and
date_pay is not null and
rownum < get_count_orders('serg',150)
order by date_pay asc;


SQLWKS> select /*+ INDEX_DESC(orders i1_orders) */
2> *
3> from
4> orders
5> where
6> client = 'serg' and
7> date_pay is not null and
8> rownum < get_count_orders('serg',150)
9> order by date_pay asc
10>
CLIENT PRODUCT DATE_PAY SUM_PAY
-------------------- -------------------- -------------------- ----------
serg Трусы 02.09.02 15
serg Майки 03.09.02 33
serg Конфеты 04.09.02 60
serg Вода 05.09.02 10
serg Сок 06.09.02 20
Выбрано 5 строк.


Если найдешь ошибки - поправлю.

Пардон в
-- Запрос
select /*+ INDEX_DESC(orders i1_orders) */
*
from
orders
where
client = 'andrey' and
date_pay is not null and
rownum < get_count_orders('andrey');

надо rownum < get_count_orders('andrey',150);
Не досмотрел просто

А теперь получайте через использование обьектов и CAST.
Замечания
1. Что касается хинтов - тоже самое
2. Что касается обьектов - база должна быть с поддержкой
обьектов

-- Вариант через CAST и использование обьектов
CREATE TYPE orders_object AS OBJECT
(
client VARCHAR2(20),
product VARCHAR2(20),
date_pay DATE,
sum_pay NUMBER(36)
);

CREATE TYPE orders_table AS TABLE OF orders_object;

CREATE OR REPLACE FUNCTION get_count_orders2(p_client orders.client%TYPE, p_sum orders.sum_pay%TYPE)
RETURN orders_table IS

CURSOR c_orders (p_client orders.client%TYPE) IS
select /*+ INDEX_DESC(orders i1_orders) */
client,product,date_pay,sum_pay
from
orders
where
client = p_client and
date_pay is not null;

v_all_sum orders.sum_pay%TYPE:=0;
v_cnt NUMBER(16):=0;
v_orders orders%ROWTYPE;

t_orders orders_table := orders_table();
n INTEGER:=0;
BEGIN
FOR v_orders IN c_orders(p_client) LOOP
v_all_sum:=v_all_sum+v_orders.sum_pay;

IF v_all_sum<p_sum THEN
n:=n+1;
t_orders.EXTEND;
t_orders(n):=v_orders;
ELSE
RETURN t_orders;
END IF;

END LOOP;
END get_count_orders2;

-- Использование
select
*
from
table(cast(get_count_orders2('andrey',150) as orders_table))
order
by date_pay;

-- Проверка
-- Использование
SQLWKS> -- Использование
SQLWKS> select
2> *
3> from
4> table(cast(get_count_orders2('andrey',150) as orders_table))
5> order
6> by date_pay
7>
CLIENT PRODUCT DATE_PAY SUM_PAY
-------------------- -------------------- -------------------- ----------
andrey Конфеты 04.09.02 90
andrey Вода 05.09.02 20
andrey Сок 06.09.02 30
Выбрано 3 строк.

SQLWKS> -- Использование
SQLWKS> select
2> *
3> from
4> table(cast(get_count_orders2('serg',150) as orders_table))
5> order
6> by date_pay
7>
CLIENT PRODUCT DATE_PAY SUM_PAY
-------------------- -------------------- -------------------- ----------
serg Трусы 02.09.02 15
serg Майки 03.09.02 33
serg Конфеты 04.09.02 60
serg Вода 05.09.02 10
serg Сок 06.09.02 20
Выбрано 5 строк.


Я думаю этот вариант изящнее

Еще одно замечание: в обоих моих вариантах в процедурах необязательно использовать хинты. Достаточно использование фразы order by. Условии запроса date_pay is not null можно заменить на условие date_pay > <дате>, что-бы ограничить кол-во записей.

По поводу твоего варианта - как насчёт плана выполнения?
Твой:
ID OPERATION OPTIONS OBJECT_NAME OBJECT_TYPE OPTIMIZER
---------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ --------------------------------------------------------------------------------
0 SELECT STATEMENT RULE
1 SORT ORDER BY
2 FILTER
3 TABLE ACCESS BY INDEX ROWID ORDERS
4 INDEX RANGE SCAN I1_ORDERS NON-UNIQUE
5 SORT AGGREGATE
6 TABLE ACCESS BY INDEX ROWID ORDERS
7 INDEX RANGE SCAN I1_ORDERS NON-UNIQUE
Выбрано 8 строк.

Мой:
ID OPERATION OPTIONS OBJECT_NAME OBJECT_TYPE OPTIMIZER
---------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ --------------------------------------------------------------------------------
0 SELECT STATEMENT RULE
1 SORT ORDER BY
2 COLLECTION ITERATOR PICKLER FETCH

Это при установке Oracle задаётся:
Oracle8 Enterprise Edition
The Select Cartridges and Options dialog box displays the following Oracle database cartridges and options:

Oracle Parallel Server Option
Oracle8 ConText Cartridge
Oracle8 Image Cartridge
Oracle8 Objects Option
Oracle8 Partitioning Option
Oracle8 Spatial Cartridge
Oracle8 Time Series Cartridge
Oracle8 Visual Information Retrieval Cartridge
Select the cartridge(s)/option(s) for which you are licensed, then click OK.

Надо включить Oracle8 Objects Option.

И еще возможно это только в Oracle EE, в SE может и не быть? У тебя какая версия?

Дело твое. Если для твоей базы твой вариант работает приемлимо, можно и не дёргаться.
Но с точки зрения плана выполнения, запрос работает не очень оптимально. Как я понимаю, в твоём варианте, для каждой записи из алиаса "а" - производится запрос(определение суммы) для алиаса "b" согласно условию.
Кол-во таких вложенных запросов равно общему кол-ву строк для данного клиента. Если условие выполняется, то строка попадает в результат.

В моём же примере, в запросе процедуры в цикле считываются последние строки и как только условие выполняется, происходит выход.

Я всё-же советую тебе это дело добить. У тебя база наверняка рабочая есть, не 10 строк. И можно было бы реально сравнить разницу в скорости. Может мой запрос еще в 10 медленнее выполняется.

Это было бы интересно для всех.
Доставить поддержку обьектов не трудно.
Надо запустить инсталлятор из Programs->Oracle Nt.
В окне выбора продуктов Oracle кнопкой From выбрать файл NT.PRD с установочного диска Oracle .\win32\install\nt.prd.
Потом в опции {All products} раскрыть опцию Oracle8 EE Cartridges and Options выбрать Oracle8 Object options.
Нажать кнопку Install и он установит.

Если ошибка останется может быть нужно будет перезапустить экземпляр.