22.10.2009 Поиск курса валюты
Необходимо написать SQL запрос, который выводит значение курса валюты на заданную дату. Такой вопрос может быть задан на собеседовании при приеме на работу в качестве разработчика баз данных. Вопрос не столь прост, как может показаться.
Постановка задачи:
Имеется таблица изменений курса валют T_CURRENCY:
|
CODE
|
varchar2(32)
|
Код валюты (USD, RUR, …)
|
|
DATE_C
|
date
|
Дата изменения курса
|
|
VALUE
|
number
|
Значение курса
|
Данные заносятся в эту таблицу каждый раз, когда изменяется значение курса какой либо валюты. Напишите SQL запрос, с помощью которого можно получить значение курса для заданной валюты на заданную дату.
Решение:
При решении этой задачи необходимо сообразить, что курсы валют изменяются не каждый день. Например, в выходные изменений нет, а запросить значение интересующей нас валюты мы можем на любую дату, в том числе и на любой выходной. Соответственно, простой запрос на равенство даты будет неправильным решением. Если это обстоятельство отметить при ответе, то задача может считаться наполовину решенной, так как этим вы продемонстрируете знание предметной области, что немаловажно. Если люди, проводящие собеседование, адекватные, то это уже будет большим плюсом.
Совсем будет неплохо, если мы в решении напишем DDL для создания нужной нам таблицы и определение первичного ключа, дополнительно продемонстрировав умение создавать таблицы скриптами, а не тупо при помощи какого-нибудь SQL Navigator-а:
CREATE TABLE T_CURRENCY
( CODE VARCHAR2(32) NOT NULL,
DATE_C DATE NOT NULL,
VALUE NUMBER NOT NULL
);
ALTER TABLE T_CURRENCY
ADD CONSTRAINT PK_T_CURRENCY
PRIMARY KEY (CODE,DATE_C)
USING INDEX PCTFREE 0;
( CODE VARCHAR2(32) NOT NULL,
DATE_C DATE NOT NULL,
VALUE NUMBER NOT NULL
);
ALTER TABLE T_CURRENCY
ADD CONSTRAINT PK_T_CURRENCY
PRIMARY KEY (CODE,DATE_C)
USING INDEX PCTFREE 0;
Данные для теста
INSERT INTO T_CURRENCY VALUES ('USD',TO_DATE('25.05.2009’,’DD.MM.YYYY'),31);
INSERT INTO T_CURRENCY VALUES ('USD',TO_DATE('26.06.2009’,’DD.MM.YYYY'),32);
INSERT INTO T_CURRENCY VALUES ('USD',TO_DATE('29.06.2009’,’DD.MM.YYYY'),30);
COMMIT;
INSERT INTO T_CURRENCY VALUES ('USD',TO_DATE('26.06.2009’,’DD.MM.YYYY'),32);
INSERT INTO T_CURRENCY VALUES ('USD',TO_DATE('29.06.2009’,’DD.MM.YYYY'),30);
COMMIT;
Далее следует немного подумать и реализовать запрос с учетом отмеченного выше обстоятельства. Классический вариант запроса с использованием подзапроса будет иметь вид:
SELECT CODE, VALUE
FROM T_CURRENCY
WHERE DATE_C = (SELECT MAX(DATE_C)
FROM T_CURRENCY
WHERE DATE_C <= TO_DATE('27.06.2009’,’DD.MM.YYYY') AND CODE='USD'
)
AND CODE='USD';
FROM T_CURRENCY
WHERE DATE_C = (SELECT MAX(DATE_C)
FROM T_CURRENCY
WHERE DATE_C <= TO_DATE('27.06.2009’,’DD.MM.YYYY') AND CODE='USD'
)
AND CODE='USD';
Этот запрос выведет курс доллара на заданную дату. В подзапросе мы ищем максимальную дату меньшую, чем заданную. Все же в ответе лучше будет написать запрос, используя переменные подстановки:
SELECT CODE, VALUE
FROM T_CURRENCY
WHERE DATE_C = (SELECT MAX(DATE_C)
FROM T_CURRENCY
WHERE DATE_C <= :DATE_C
)
AND CODE = :CODE;
FROM T_CURRENCY
WHERE DATE_C = (SELECT MAX(DATE_C)
FROM T_CURRENCY
WHERE DATE_C <= :DATE_C
)
AND CODE = :CODE;
Можно также дополнительно написать вариант решения этой задачи, используя PL/SQL функцию, демонстрируя собственные знания:
CREATE OR REPLACE
FUNCTION GET_CURRENCY(psCODE IN VARCHAR2, — Код валюты
pdDATE IN DATE — Дата актуальности
) RETURN NUMBER
IS
BEGIN
FOR i IN (SELECT VALUE
FROM T_CURRENCY
WHERE CODE = psCODE
AND DATE_C <= pdDATE
ORDER BY DATE_C DESC
)
LOOP
RETURN i.VALUE;
END LOOP;
RETURN NULL;
END GET_CURRENCY;
FUNCTION GET_CURRENCY(psCODE IN VARCHAR2, — Код валюты
pdDATE IN DATE — Дата актуальности
) RETURN NUMBER
IS
BEGIN
FOR i IN (SELECT VALUE
FROM T_CURRENCY
WHERE CODE = psCODE
AND DATE_C <= pdDATE
ORDER BY DATE_C DESC
)
LOOP
RETURN i.VALUE;
END LOOP;
RETURN NULL;
END GET_CURRENCY;
С точки зрения производительности использование PL/SQL функции в этом виде более предпочтительно, чем использование запроса с подзапросом, так как в PL/SQL функции не требуется двойного сканирования таблицы с данными.
Смотрите также:
Комментарии читателей
Оставьте свой комментарий
Вы должны быть авторизированны, чтобы оставить комментарий.
На практике представляет интерес также получение возможно более быстрым способом списка курсов валют по всем датам заданного периода, чтоб не вызывать вышеописанную процедуру сотни раз. Одним запросом получится? (Подразумевается, что тестовая таблица с курсами на случайные даты имеется, такая, чтобы были курсы на весь запрашиваемый период).
Интересный вопрос, подумаю на досуге, если что-то получится, оформлю ответ отдельным постом.
Реально на практике я стараюсь не решать подобные задачи написанием сложных SQL запросов. На мой взгляд правильнее решать такие задачи на PL/SQL, написав соответствующую функцию. Почему? Потому что в такой функции будут простейшие запросы, и, соответственно, всегда предсказуемое поведение оптимизатора сервера Oracle, что я считаю самым важным. Алгоритм может быть примерно такой:
– Считываем при помощи bulk collect список изменений курсов валют за заданный период в хэш-массив. Считается за один раз и очень быстро.
– Делаем один проход по массиву, и формируем на его основе второй массив, в который добавляем отсутствующие даты и значения курсов валют. Тоже выполнится очень быстро
– Формируем выдачу полученной таблицы из функции в зависимости от требований интерфейса
Прелесть Oracle в аналитических функциях, вот решение для Автоматизатора
select
q.value
, q.date_c
, q.date_n
from (
select
t.CODE
, t.VALUE
, t.date_c
, lead(t.date_c, 1, sysdate) over (order by t.date_c) date_n
from
T_CURRENCY t
where
CODE=’USD’
) q
where q.date_c < = to_date(’30.06.2009?,’DD.MM.YYYY’) and q.date_n >= to_date(’24.06.2009?,’DD.MM.YYYY’)
А вообще, я лично принял бы человека, который предложил бы ввести в базовую таблицу дополнительно поле «Дата По», т.е. хранить диапазон дат с первичным ключем по этому полю и CODE (с логикой заполнения, например, на тригерах)
ну да, дата с дата по – стандартная хранилищная задача с расчетом таблицы с остатками по всем дням ночью
Наверное эта задача стандартная не только для хранилищ и не только ночью
Позвольте весьма критичное замечание по теме:
чушь несусветная, ибо решение столь очевидно сколь необязательно разработчику знать «…что курсы валют изменяются не каждый день. Например, в выходные изменений нет…». Другими словами знание всех предметных областей невозможно да и ненужно. Горазно важнее способность к исследованию нужной предметной области, понимание заказчика/постановщика задач.
«…продемонстрировав умение создавать таблицы скриптами, а не тупо при помощи какого-нибудь SQL Navigator-а…» это вопрос удобства а никакое не умение.
Учитывая вышесказанное и сама статья и каменты ничто иное как проявление дилетантизма.
dreamer, вы сами написали, э… и проявили …, но я публикую Ваш коммент, он хотя бы по теме, а то спамеры достали
Вы бы еще написали, что разработчику не обязательно знать, что такое курс валюты. Ага, заказчик потом все растолкует.
Знание ВСЕХ предметных областей необязательно, это верно, но кому нужен разработчик, который не знает ни одной?
Насчет очевидности – ну, ну… Я десятками отворачивал претендентов, ни имеющих понятия о таких вот очевидностях, но гордо претендующих на звание разработчика. Наверное, я проявлял дилетантизм, может в их числе были и вы, dreamer? Старые обиды, бывает…
Насчет создания таблиц в SQLNavigator – это как раз вопрос не удобства, а умения. Разработчик, который предпочитает создавать визуально таблицы в SQLNavigator, видимо, никогда не работал в крупных проектах. Он может годами сидел на автоматизации чего-то в какой-то конторе в одиночку, тут можно и не заботиться о нормальной инсталляции серверной части приложений, хватит и SQLNavigator-а. В крупных проектах и больших командах это не прокатывает, и очень многие претенденты не умеют создать табличку скриптом, не говоря уже о грамотном патче для модификации таблицы и данных этой таблицы. А это часто довольно не просто.
Впрочем, кто понимает, ругаться не будет.
Уверен, кому-то этот материал будет полезным, а вставать в позу гуру не всегда уместно
Простите что задел ваше самолюбие.
Как же я вас, столичных снобов-дилетантов, ненавижу.
Это патология и я стараюсь с ней бороться, но постоянно находится тот кто бередит старые раны
Ну что вы, dreamer, не задели вы мое самолюбие, если бы задели, то я просто не пропустил бы ваши комменты. А так пожалуйста, мне даже интересны ваши замечания.
Может быть стоит вспомнить, что кроме борьбы есть еще и метод сотрудничества?
Но все же ненависть работает против вас, и нам, столичным э… дилетантам она как то параллельно
Если что есть еще по теме, кроме эмоций, то милости просим.
При чем здесь инсталляция серверной части? апдейты таблиц? С эмоциями может конечно и перестарался
Может конечно этот материал и будет кому-то полезен, но по вашим же словам: «… а то спамеры достали …»
). И такой подход применим практически к любым сущностям, меняющимися во времени с хранением истории изменений. И на мой взгляд – это лучшее и технически верное решение.
Касаемо решения:
Sergei Kuznetsov всё описал: лучше временной диапазон. и при вводе нового курса ограничивать «дату по» (если запись существует
Насчет спамеров – действительно достали
А адекватному обсуждению всегда рад.
Насчет столичных «снобов». Поверьте, в IT в столице истинно столичных специалистов не так много, там в немалой степени погоду делают приезжие, которые может и стали москвичами, но недавно. Или еще не стали. Или не станут. И нормальные люди встречаются гораздо чаще, как среди столичных, так и приезжих, чем какие либо «снобы».
Насчет серверной части. В статье имелось ввиду некоторое собеседование и один из типовых вопросов, который там может быть задан. В вопросе фигурировала только одна дата. Решение приведено соответственное, для одной даты.
Также даны некоторые рекомендации, как преподнести решение. Одна из них – продемонстрировать умение написать скрипт на создание таблиц. И был нелестный отзыв о создании таблиц инструментами типа SQLNavigator. Намек о том, что объекты БД надо создавать скриптами, гоняя их через SQL*Plus, а не визуальными средствами, поверьте, это профессионалы оценят. Так как создание скриптом таблицы – это создание одного из объектов БД. Это та часть, которую разработчик вносит в проект, в том числе в инсталляцию серверной части, куда войдет скрипт этой таблички в нашем примере. Если это человек продемонстрирует на собеседовании, пояснив, что и как, и для чего это нужно, это покажет его знания. Но это один из подходов. Знаю множество систем, которые инсталлируются заказчику целиком из дампа. В таких системах как создавать объекты и есть вопрос удобства. Все равно дамп переливают. Я лично считаю это плохим решением. Ну не нравится. И это мнение многие разделяют. Нам скрипты подавай и аккуратную инсталляцию серверной части. А многие не разделяют. Но мы друг друга дилетантами не считаем. Подход разный. Не более.
Очень часто вопросы задаются таким образом, чтобы вывести претендента на разговор и о других технологиях. И от этого зависит почти все.
Sergei Kuznetsov действительно дал хороший коммент, особенно с предложением ввести дополнительный столбец, чтобы получился диапазон дат, кто же с этим спорит? Просто в статье рассматривался конкретный вопрос. По моему опыту, претенденты в нем очень часто плавают, несмотря на его относительную простоту.
К тому же диапазон дат не всегда оптимальное решение, это тоже вопрос дискуссионный. Для небольших по размеру таблиц одна дата может быть более оптимальным решением. Почему? Потому что ввод в интерфейсе делается проще и быстрее, а накладные расходы на поиск могут считаться несущественными. Напротив, для больших по размеру таблиц это решение очень не оптимальное, так затраты на поиск будут очень велики. Выгоднее делать диапазон дат, для быстрого поиска с использованием BETWEEN. Но интерфейс делать сложнее, не мне вам это объяснять, так как требуется поддержка правильных диапазонов и предыдущих записей, верно?
А почему нет проверки типа валюты в подзапросе? Ведь тогда в нём выберется максимальная дата по ВСЕМ валютам. А это неправильно.
Михаил, спасибо за замечание, все верно, код подправил