Задача. Поиск баланса счета на заданную дату.

Дана таблица ACCOUNT_BALANCES с балансами счетов, указанными для тех дат, когда балансы менялись:

create table account_balances(newaccnum char(20), balance_date date, balance number);

insert into account_balances(newaccnum, balance_date, balance) values('40702840700000003465', to_date('28.06.2012', 'dd.mm.yyyy'), 300);

insert into account_balances(newaccnum, balance_date, balance) values('40702840700000003465', to_date('29.06.2012', 'dd.mm.yyyy'), 310);

insert into account_balances(newaccnum, balance_date, balance) values('40702840700000003465', to_date('01.07.2012', 'dd.mm.yyyy'), 8890);

insert into account_balances(newaccnum, balance_date, balance) values('40702280000007003168', to_date('26.06.2012', 'dd.mm.yyyy'), 123);

insert into account_balances(newaccnum, balance_date, balance) values('40702280000007003168', to_date('27.06.2012', 'dd.mm.yyyy'), 19034);

insert into account_balances(newaccnum, balance_date, balance) values('40702280000007003168', to_date('29.06.2012', 'dd.mm.yyyy'), 19035);

Написать функцию, которая по заданному номеру счета и заданной дате выдает баланс. Следует брать последний известный баланс по счету на последнюю дату, не превосходящую заданную.

Воспользуемся конструкцией max() keep(dense_rank) при написании требуемой функции:

create or replace function fn_account_balance(in_newaccnum in char,

in_balance_date in date)

return number is

v_result number;

begin

select max(balance) keep(dense_rank last order by balance_date)

into v_result

from account_balances

where newaccnum = in_newaccnum

and balance_date <= in_balance_date;

return v_result;

end fn_account_balance;

Проверим результат:

select fn_account_balance('40702840700000003465', to_date('27.06.2012','dd.mm.yyyy')) f_465_27_06,

fn_account_balance('40702840700000003465', to_date('28.06.2012','dd.mm.yyyy')) f_465_28_06,

fn_account_balance('40702840700000003465', to_date('29.06.2012','dd.mm.yyyy')) f_465_29_06,

fn_account_balance('40702840700000003465', to_date('30.06.2012','dd.mm.yyyy')) f_465_30_06,

fn_account_balance('40702840700000003465', to_date('01.07.2012','dd.mm.yyyy')) f_465_01_07,

fn_account_balance('40702840700000003465', to_date('02.07.2012','dd.mm.yyyy')) f_465_02_07,

fn_account_balance('40702280000007003168', to_date('25.06.2012','dd.mm.yyyy')) f_168_25_06,

fn_account_balance('40702280000007003168', to_date('26.06.2012','dd.mm.yyyy')) f_168_26_06,

fn_account_balance('40702280000007003168', to_date('27.06.2012','dd.mm.yyyy')) f_168_27_06,

fn_account_balance('40702280000007003168', to_date('28.06.2012','dd.mm.yyyy')) f_168_28_06,

fn_account_balance('40702280000007003168', to_date('29.06.2012','dd.mm.yyyy')) f_168_29_06,

fn_account_balance('40702280000007003168', to_date('30.06.2012','dd.mm.yyyy')) f_168_30_06

from dual;

Задача. Удаление исторических данных о рейтинге.

Дана таблица истории изменения рейтинга клиентов в скоринговых баллах kk_client_rating(clientname varchar2(100), dt date, rating integer).

Написать оператор delete, который уберет для каждого клиентов все рейтинги, кроме последнего по дате.

Если для клиента на последнюю дату существует более одного значения рейтинга, то следует оставить только максимальное значение рейтинга.

Создадим таблицу и заполним ее данными:

create table kk_client_rating(clientname varchar2(100), dt date, rating integer);

insert into kk_client_rating(clientname, dt, rating) values('Сыроедов', to_date('28.06.2012','dd.mm.yyyy'), 100);

insert into kk_client_rating(clientname, dt, rating) values('Сыроедов', to_date('29.06.2012','dd.mm.yyyy'), 90);

insert into kk_client_rating(clientname, dt, rating) values('Сыроедов', to_date('30.06.2012','dd.mm.yyyy'), 85);

insert into kk_client_rating(clientname, dt, rating) values('Сыроедов', to_date('30.06.2012','dd.mm.yyyy'), 100);

insert into kk_client_rating(clientname, dt, rating) values('Жирнов', to_date('25.06.2012','dd.mm.yyyy'), 100);

insert into kk_client_rating(clientname, dt, rating) values('Жирнов', to_date('27.06.2012','dd.mm.yyyy'), 85);

insert into kk_client_rating(clientname, dt, rating) values('Жирнов', to_date('28.06.2012','dd.mm.yyyy'), 85);

insert into kk_client_rating(clientname, dt, rating) values('Булочкин', to_date('25.06.2012','dd.mm.yyyy'), 85);

insert into kk_client_rating(clientname, dt, rating) values('Булочкин', to_date('29.06.2012','dd.mm.yyyy'), 80);

insert into kk_client_rating(clientname, dt, rating) values('Булочкин', to_date('29.06.2012','dd.mm.yyyy'), 85);

Напишем требуемый оператор с использованием max() keep(dense_rank):

delete from kk_client_rating

where (clientname, dt, rating) not in

(select clientname,

max(dt),

max(rating) keep(dense_rank last order by dt)

from kk_client_rating

group by clientname);

Проверим результат удаления:

select * from kk_client_rating;

Напишем другой вариант через rowid и row_number():

delete from kk_client_rating

where rowid not in (select rowid

from (select rowid,

row_number() over(partition by clientname order by dt desc, rating desc) rn

from kk_client_rating)

where rn = 1);

Проверим результат удаления:

select * from kk_client_rating;