Помогите с SQL

[green_fr]

У меня есть таблица с курсами ценных бумаг. Скажем, колонки POS_DARR, POS_COD и POS_COURS для даты, кода бумаги и курса.
В программе, заполняющей эту таблицу могут быть баги, когда курс застывает на некотором значении, и перестаёт обновляться, т.е. начиная со дня баги курс будет равен одному и тому же значению.
Мне нужно вывести все подозрительные линии, т.е. для некоторой даты вывести все коды, курсы которых не изменились по крайней мере со вчерашнего дня (халява), а затем дату, с которой этот курс оставался неизменным.

Я написал следующего монстра (POSITION - таблица курсов, код не в одной колонке POS_COD, а в двух POS_PTF_COD и POS_VAL_COD, INTEGRATION - список всех дат базы данных):

select pos_darr, min(int_darr), pos_ptf_cod, pos_val_cod, pos_cours
from (
select pos1.pos_darr, int_darr, pos1.pos_ptf_cod, pos1.pos_val_cod, pos1.pos_cours
from position pos1, position pos2, integration
where pos1.pos_darr = '20/07/2006'
and pos1.pos_val_cod = 'FR0000108664'
and pos1.pos_ptf_cod = '1000101'
and pos1.pos_ptf_cod = pos2.pos_ptf_cod
and pos1.pos_val_cod = pos2.pos_val_cod
and int_darr < pos1.pos_darr
and pos1.pos_darr > pos2.pos_darr
and int_darr <= pos2.pos_darr
group by pos1.pos_darr, pos1.pos_ptf_cod, pos1.pos_val_cod, pos1.pos_cours, int_darr
having min(pos2.pos_cours) = max(pos2.pos_cours)
)
group by pos_darr, pos_ptf_cod, pos_val_cod, pos_cours

Монстра работает, конечно, но только когда я запрашиваю один код (как это сдалено в примере). Когда прошу много - думает тормозит. Неудивительно, впрочем. Есть какие-то идеи, как вообще такие вещи пишутся?

Можно, конечно, писать не запрос, а цикл, который пробегает по курсору... Но хочется красиво как-то.

Comments

[aguti.livejournal.com]

Сопровождай такие задачки скриптом создания таблиц(ы). А то ниче не разобрать :)

[aguti.livejournal.com]

Желательно с группой соотвествующих инсертов :)

[green-fr.livejournal.com]

Вот:
create table POSITION
(
POS_DARR DATE not null,
POS_PTF_COD VARCHAR2(10) not null,
POS_VAL_COD VARCHAR2(15) not null,
POS_COURS NUMBER(17,6)
);

create table INTEGRATION
(
INT_DARR DATE not null
);

insert into INTEGRATION (INT_DARR)
values (to_date('20-07-2006', 'dd-mm-yyyy'));
insert into INTEGRATION (INT_DARR)
values (to_date('19-07-2006', 'dd-mm-yyyy'));
insert into INTEGRATION (INT_DARR)
values (to_date('18-07-2006', 'dd-mm-yyyy'));
insert into INTEGRATION (INT_DARR)
values (to_date('17-07-2006', 'dd-mm-yyyy'));

insert into POSITION (POS_DARR, POS_PTF_COD, POS_VAL_COD, POS_COURS)
values (to_date('20-07-2006', 'dd-mm-yyyy'), '1', '1', 10);
insert into POSITION (POS_DARR, POS_PTF_COD, POS_VAL_COD, POS_COURS)
values (to_date('19-07-2006', 'dd-mm-yyyy'), '1', '1', 10);
insert into POSITION (POS_DARR, POS_PTF_COD, POS_VAL_COD, POS_COURS)
values (to_date('18-07-2006', 'dd-mm-yyyy'), '1', '1', 12);
insert into POSITION (POS_DARR, POS_PTF_COD, POS_VAL_COD, POS_COURS)
values (to_date('17-07-2006', 'dd-mm-yyyy'), '1', '1', 10);
insert into POSITION (POS_DARR, POS_PTF_COD, POS_VAL_COD, POS_COURS)
values (to_date('20-07-2006', 'dd-mm-yyyy'), '1', '2', 10);
insert into POSITION (POS_DARR, POS_PTF_COD, POS_VAL_COD, POS_COURS)
values (to_date('19-07-2006', 'dd-mm-yyyy'), '1', '2', 10);
insert into POSITION (POS_DARR, POS_PTF_COD, POS_VAL_COD, POS_COURS)
values (to_date('18-07-2006', 'dd-mm-yyyy'), '1', '2', 10);
insert into POSITION (POS_DARR, POS_PTF_COD, POS_VAL_COD, POS_COURS)
values (to_date('17-07-2006', 'dd-mm-yyyy'), '1', '2', 10);
insert into POSITION (POS_DARR, POS_PTF_COD, POS_VAL_COD, POS_COURS)
values (to_date('20-07-2006', 'dd-mm-yyyy'), '1', '3', 10);
insert into POSITION (POS_DARR, POS_PTF_COD, POS_VAL_COD, POS_COURS)
values (to_date('19-07-2006', 'dd-mm-yyyy'), '1', '3', 12);
insert into POSITION (POS_DARR, POS_PTF_COD, POS_VAL_COD, POS_COURS)
values (to_date('18-07-2006', 'dd-mm-yyyy'), '1', '3', 12);
insert into POSITION (POS_DARR, POS_PTF_COD, POS_VAL_COD, POS_COURS)
values (to_date('17-07-2006', 'dd-mm-yyyy'), '1', '3', 10);

Запрос на 20/07/2006 должен выдать:

POS_PTF_COD POS_VAL_COD DATE_START
1 1 19/07/2006
1 2 17/07/2006

Пара (1, 3) в списке не должна фигурировать, поскольку курс в последний день изменился.

[green-fr.livejournal.com]

create table POSITION
(
POS_DARR DATE not null,
POS_PTF_COD VARCHAR2(10) not null,
POS_VAL_COD VARCHAR2(15) not null,
POS_COURS NUMBER(17,6)
);
create table INTEGRATION
(
INT_DARR DATE not null
);

insert могу тоже набрать, но это уже маразм будет :-)

В двух словах, есть некая величина (курс) для некоторого набора ID (бумаги). Как найти те ID, для которых курс не менялся со вчерашнего дня, понятно. А как при этом ещё и вывести дату, с которой курс постоянен?

[aguti.livejournal.com]

Нет, маразмом не будет. Я же не прошу всю таблицу, а 5 строк, чтобы понять, какой результат ожидается. Просто иначе эти инсерты придется набирать мне (иначе задачу не почувствовать).
В качестве наводки: кроме функций мин и макс (которые очень тяжелые, т.к. статистические) есть еще прием order by и взять первую строку.
А вот для попытки реализации этой идеи гони инсерты :)

[aguti.livejournal.com]

Я не очень поняла назначение даты INT_DARR. POS_DARR не достаточно?

[(Anonymous)]

Достаточно. INT_DARR - это в принципе то же самое, что distinct POS_DARR.

[green-fr.livejournal.com]

Я думаю, даже без подписи ты поняла, кто отвечал :-Р

[aguti.livejournal.com]

Еще вопрос: курс меняется ежедневно? Или нерегулярно?

[green-fr.livejournal.com]

Для простоты предположим, что курс есть на каждый день для каждой бумаги. И только один в день, т.е. все DARR - это date, а не datetime.

[aguti.livejournal.com]

Очень сложная задача и интересная, невозможна без рекурсии и, как следствие, курсора (ИМХО). Я еще подумаю на досуге, сейчас убегаю.
Но твое решение (твой монстр) неверно, т.к. не учитывает факта, что интересует последовательная смена (то есть не просто минимальное, а минимальное из непосредственной близости к текущему).
Выполни своего монстра на 1,3 и поймешь, о чем я.

[green-fr.livejournal.com]

Постой, я для того и сделал монстра, чтобы учесть этот случай (иначе совсем просто - минимум даты, на которой курс равен нынешнему). И вроде как проверил...

Без рекурсии тоже делается, но циклом: список бумаг, у которых курс сегодня равен вчерашнему, а потом по этому списку запрос типа минимум даты, на которой count(курсы между изначальной датой и этой) без фильтра равен count(того же самого) с фильтром "курс тот же, что вчера". Но я хочу ещё и без единого гвоздя цикла :-)

[aguti.livejournal.com]

А ты свой примерчик проверь. У меня сейчас нет под рукой базы, поэтому не могу тебе точно сказать, но выдает фигню. Но может это я ошиблась, завтра еще раз проверю.
Список - правильное решение, но сложное, потому что много count, будет тормозить.
Самое правильное, на мой взгляд, решение - это завести триггер на апдейт и каждый раз сохранять эту дату в какой-нибудь статистической табличке, откуда потом выдирать одним э-ле-мен-тар-ным селектом. Но мы ведь не ищем простых путей, как я поняла? :)

[green-fr.livejournal.com]

Ты будешь смеяться, но база вообще read-only, единственный update происходит по утрам, нашим скриптом, который закачивает новый день. Т.е. даже триггер не понадобится. Но ты правильно заметила про лёгкие пути. Лажа :-)

[aguti.livejournal.com]

Для меня эта задачка представляет скорее теоретический интерес для исследований возможностей SQL. В производство я даже твоего монстра не поставила бы - только триггер (ну или процедуру), и простые селекты, которые легко читаются. Иначе замучаешься дебаггить и оптимизировать индексы, а любое легкое изменение условий приведет к еще одной неделе творческой работы с SQL - три дня понять, что было написано и еще 4 чтобы правильно это исправить :).

[green-fr.livejournal.com]

Ачипятался, всесто "and pos1.pos_darr > pos2.pos_darr" читать "and pos1.pos_darr >= pos2.pos_darr", тогда всё работает :-)

Ладно, время не ждёт, начинаю писать процедуру (если что, так и скажу "les gens d'Oracle m'ont dit..."). Но если появятся какие идеи - говори, ага?

P.S. Я только что понял, в какую сторону смотри зверёк на твоём userpic :-))) Раньше мне там виделась часть морды, направленной влево вниз, т.е. вообще все пиксели - это мех, мех, мех %-)

[genem.livejournal.com]

Hi,

Я не уверен, что прочитал внимательно :)
Это оно? :

create table green (product int, date int, rate int)

insert into green select 1,5,10
insert into green select 1,4,10
insert into green select 1,3,10
insert into green select 1,2,10
insert into green select 1,1,11
insert into green select 2,5,10
insert into green select 2,4,10
insert into green select 2,3,12
insert into green select 2,2,10
insert into green select 2,1,11
insert into green select 3,5,12
insert into green select 3,4,11
insert into green select 3,3,11


declare @date int

set @date = 5

select product
,min(date) mdate
from green g1
where g1.date < @date
and not exists
(select 1
from green g2
where g2.product = g1.product
and g2.date >= g1.date
and g2.date <= @date
and g2.rate != g1.rate
)
group by product

[green-fr.livejournal.com]

Да, твой монстр гораздо ситабельнее моего, но тормозит точно так же...
Может ему индексов добавить? Или микрософту?

[aguti.livejournal.com]

not exists всегда тормозит, в оличие от exists - ведь ему надо перелопатить всю таблицу, прежде чем ответить на вопрос.
А какие индексы у тебя уже есть?

[genem.livejournal.com]

А если найти сперва все которые не поменялись со вчера, сложить их во временную таблицу - должно быть не так много же ж. Потом с ней джойнить и искать минимальную дату монстром?

[aguti.livejournal.com]

Даже еще проще: во время апдейта проверить, поменялось ли значение, и если да - обновить дату во вспомогательной таблице. Так в ней всегда самая старая дата для этого курса. Но, как сказал green_fr, мы не ищем легких путей :)

[green-fr.livejournal.com]

И это я тоже попробовал :-) Читабельность при этом возросла невломенно, в отличие от скорости.

[green-fr.livejournal.com]

DARR+PTF_COD
DARR+VAL_COD
PTF_COD+VAL_COD+DARR

Но мне же нужен только DARR, т.е. любой их первых двух прокатывает.
Я в итоге решил даже процедуру не писать, а сделать, как ты предложила - в момент смены курса просчитывать дату и писать её куда-нибудь уже готовенькую.

По поводу "микрософту" - это работали мы с genem в Москве, был там культовый запрос пользователей. Машина, типа, тормозит, просим добавить памяти и микрософту.

[aguti.livejournal.com]

Правильное решение. Но попробуй еще этот запрос, мне просто интересно:

select distinct pos_ptf_cod, pos_val_cod, first_value(pos_darr) over
(partition by pos_ptf_cod, pos_val_cod order by pos_darr) mindate
from position a
where pos_darr < '20/07/2006'
and not exists
(select 1
from position
where pos_ptf_cod = a.pos_ptf_cod
and pos_val_cod = a.pos_val_cod
and pos_darr >= a.pos_darr
and pos_darr <= '20/07/2006'
and pos_cours != a.pos_cours
)

[green-fr.livejournal.com]

Это же слово в слово то, что Женька парой комментов выше предложил. Красиво, читабельно, но тормозит тоже жутко.
Кстати, не знал, что можно в таком случае сделать alias одной instance таблицы position, а вторую оставить без alias. Век живи, век учись :-)

[aguti.livejournal.com]

Не слово в слово, а без group by :) Мне было интересно, будет ли first_value работать быстрее min (у меня нет под рукой достаточно большой базы, чтобы проверить).

[green-fr.livejournal.com]

110 секунд на твой вариант, столько же на Женькин.
Для сравнения, мой показывал что-то в районе 500 :-)

[zadiraka.livejournal.com]

Значит надо LEFT JOIN применять вместо not exists.

[green-fr.livejournal.com]

А почему ты думаешь, что left join будет работать как-то по-другому?

[alexnavfr.livejournal.com]

Как тесен мир. Я на прошлой неделе баг искал именно на этом инструменте облиг ABBEY NAT и именно потому что котировок на нем нет и цена не меняется! :))))

[green-fr.livejournal.com]

Наш называется Chorus, но баги - они же заразные.
И как, что сделал ты?