CC BY
35
БИБЛИОГРАФИЧЕСКИЙ СПИСОК
1. Артемьев В.И. Разработка т^апе^приложений. Центр Информационных Технологий, www.citforum.ru, 1998.
2. Агнер Р. "Лучевая терапия" для интернет-навигации // Мир Intemet. 2002. №11-12.
3. Свечкарев В.П., Штепа И.В., Корохова Е.В. Исследование ситуаций при моделировании состояний системы// Новые технологии управления движением технических объектов. Материалы 4-й Международной научно-технической конференции. 2001.
4. Гришин В.Г. Образный анализ экспериментальных данных. -Москва, 1982.
ЗАМЕНА ВРЕМЕННЫХ ТАБЛИЦ В SQL-СЕРВЕРЕ FIREBIRD
При переходе с файловых БД или других SQL-серверов на SQL-сервер FIREBIRD часто возникает ряд проблем, связанных с отсутствием временных таблиц в FIREBIRD. Однако, несмотря на отсутствие временных таблиц в этом SQL-сервере, ряд механизмов FIREBIRD позволяет их успешно заменять. Ниже описываются три подхода для решения задач без использования временных таблиц.
Первый подход. Чаще всего достаточно использовать структуру FOR SELECT <ЗАПРОС> INTO <ПЕРЕМЕННЫЕ> DO <ДЕЙСТВИЕ>. Оператор FOR SELECT предназначен для выполнения операций (DO) над переменными (INTO), значение которых устанавливается равным значениям возвращаемых полей запроса (SELECT). При этом интерпретатором FIREBIRD:
1) выполнится запрос, фактически создав в ОЗУ ЭВМ (во временном файле, если вся таблица не умещается в ОЗУ) временную таблицу;
2) затем будет осуществлен переход на первую строку выборки запроса;
3) значения полей первой строки запроса присвоятся переменным хранимой процедуры, перечисленным в into по порядку их перечисления: <ПЕРЕМЕН-
НАЯ1>=<ПОЛЕ1>, <ПЕРЕМЕННАЯ2>=<ПОЛЕ2> и т.д.;
4) выполнятся операции, указанные в блоке DO;
5) если в выборке запроса еще остались строки, то будет осуществлен переход ко второй строке и повторены операции с п. 3.
Полный синтаксис этого оператора приведен в документации SQL-сервера. В некотором роде FOR SELECT и есть временная таблица, над которой проводятся операции. Разберем это на примере таблицы:
В.Л. Каратаев
// домашняя бухгалтерия CREATE TABLE CASH ( ID INTEGER,
// идентификатор записи в таблице // статья доходов/расходов
// сумма в рублях: с "плюсом" доходы (например, зар^
NAME VARCHAR(30), DENGI INTEGER,
плата), с "минусом"- расходы (например, покупки)
RDATE TIMESTAMP )
// дата внесения записи
D NAME D ENGI RDA TE
зарплата 3000 10.04.2003
ком. платежи -500 05.04.2003
интернет - 5 о 22.04.2003
дивиденды 100 02.04.2003
зарплата 3000 24.04.2003
ком. платежи -400 29.04.2003
Необходимо составить годовой отчет о полученных и израсходованных суммах в
виде
NAME DOHOD RASHOD
зарплата 3 000 0
ком.платежи 0 -500
интернет 0 -150
дивиденды 100 0
зарплата 3 000 0
ком.платежи 0 -400
ИТОГО б 100 -1 050
В данном случае проблема состоит в том, что колонку DENGI необходимо разбить на две колонки, к тому же подсчитать итоговую сумму. Использование временных таблиц здесь является наиболее эффективным решением. Для FIREBIRD решение будет таким:
CREATE PROCEDURE ITOG (
NGOD TIMESTAMP,
EGOD TIMESTAMP)
RETURNS (
NAME VARCHAR(30),
DOHOD INTEGER,
RASHOD INTEGER)
AS
DECLARE VARIABLE SDOHOD INTEGER;
DECLARE VARIABLE SRASHOD INTEGER;
DECLARE VARIABLE TDENGI INTEGER;
BEGIN
/* ИНИЦИАЛИЗАЦИЯ ПЕРЕМЕННЫХ */
SDOHOD=0;
SRASHOD=0;
/* AUAIBfiA */
FOR SELECT NAME, DENGI FROM CASH WHERE RDATE BETWEEN :NGOD AND :EGOD
INTO :NAME, :TDENGI DO
BEGIN
/* РАЗБИТИЕ ПОЛЯ DENGI НА ДВЕ КОЛОНКИ- DOHOD (ДОХОД) И RASHOD (РАСХОД) */
IF (TDENGI>=0) THEN BEGIN
DOHOD=TDENGI;
RASHOD=0;
/* ПОДСЧЕТ ИТОГОВОЙ СУММЫ ПО ДОХОДАМ */
SDOHOD=SDOHOD+TDENGI;
END
ELSE
BEGIN
RASHOD=TDENGI;
DOHOD=0;
/* ПОДСЧЕТ ИТОГОВОЙ СУММЫ ПО РАСХОДАМ */ SRASHOD=SRASHOD+TDENGI;
END
SUSPEND;
END
/* ВЫВОД ИТОГОВОЙ СУММЫ */
NAME='EOlAl';
DOHOD=SDOHOD;
RASHOD=SRASHOD;
SUSPEND;
END
Второй подход заключается в использовании комбинации FOR SELECT и хранения в переменных процедуры предыдущих значений полей таблицы.
В предыдущем примере пункт “зарплата” будет выведен столько раз, сколько он встречается в таблице CASH, то есть, если получали в течение года зарплату 12 раз, то 12 раз она и выведется, притом список будет не сортированным. Таблица станет более читабельной, если будут сгруппированы все одинаковые статьи с соответствующим суммированием по столбцам. Тогда для подсчета сумм еще и по статьям необходимо ввести сортировку с суммированием строк, следовательно, требуется распознавать “переход” к другой статье (то есть сравнивать названия статьи в текущей записи с названием статьи в предыдущей записи) и хранить промежуточные результаты подсчета по статье:
NAME DOHOD RASHOD
дивиденды 100 0
зарплата б 000 0
интернет 0 -150
ком. платежи 0 -900
ИТОГО б 100 -1 050
Соответствующая процедура будет выглядеть следующим образом: CREATE PROCEDURE ITOG2 (
NGOD TIMESTAMP,
EGOD TIMESTAMP)
RETURNS (
NAME VARCHAR(30),
DOHOD INTEGER,
RASHOD INTEGER)
AS
DECLARE VARIABLE SDOHOD INTEGER;
DECLARE VARIABLE SRASHOD INTEGER;
DECLARE VARIABLE TDENGI INTEGER;
DECLARE VARIABLE STDOH INTEGER;
DECLARE VARIABLE STRAS INTEGER;
DECLARE VARIABLE STOLD VARCHAR(30) CHARACTER SET WIN1251; DECLARE VARIABLE STNEW VARCHAR(30) CHARACTER SET WIN1251;
BEGIN
/* ИНИЦИАЛИЗАЦИЯ ПЕРЕМЕННЫХ */
SDOHOD=0;
SRASHOD=0;
STDOH=0;
STRAS=0;
STOLD='';
STNEW='';
/* ВЫБОРКА */
FOR SELECT NAME, DENGI FROM CASH WHERE RDATE BETWEEN :NGOD AND :EGOD ORDER BY NAME INTO :STNEW, :TDENGI DO
BEGIN
/* ДЕТЕКТИРУЕТСЯ НАЧАЛО НОВОЙ СТАТЬИ */
IF (:STOLD<>:STNEW) THEN BEGIN
/* ПО ПРЕДЫДУЩЕЙ СТАТЬЕ НЕОБХОДИМО ВЫВЕСТИ РЕЗУЛЬТАТ */ NAME=STOLD;
DOHOD=STDOH;
RASHOD=STRAS;
IF (:NAME<>'') THEN SUSPEND;
/* ОБНУЛИТЬ ПЕРЕМЕННЫЕ ДЛЯ ПРОВЕДЕНИЯ СУММИРОВАНИЯ ПО СЛЕДУЮЩЕЙ СТАТЬЕ */
STOLD=STNEW;
STDOH=0;
STRAS=0;
END
/* ПРОВЕРКА- ИДЕТ ЛИ СУММИРОВАНИЕ ПО ОДНОЙ И ТОЙ ЖЕ СТАТЬЕ */
IF (:STOLD=:STNEW) THEN BEGIN
/* РАЗБИТИЕ ДОХОДОВ И РАСХОДОВ ПО КОЛОНКАМ С СУММИРОВАНИЕМ */ IF (:TDENGI>=0) THEN BEGIN
/* СЧИТАЕТСЯ СУММА ДОХОДОВ ПО ТЕКУЩЕЙ СТАТЬЕ */ STDOH=STDOH+TDENGI;
/* СЧИТАЕТСЯ ОБЩАЯ СУММА ДОХОДОВ */
SDOHOD=SDOHOD+TDENGI;
END
ELSE
BEGIN
/* СЧИТАЕТСЯ СУММА РАСХОДОВ ПО ТЕКУЩЕЙ СТАТЬЕ */ STRAS=STRAS+TDENGI;
/* СЧИТАЕТСЯ ОБЩАЯ СУММА РАСХОДОВ */
SRASHOD=SRASHOD+TDENGI;
END
END
END
/* НЕОБХОДИМО УЧЕСТЬ ЗАМЫКАЮЩУЮ СПИСОК СТАТЬЮ */
NAME=STNEW;
DOHOD=STDOH;
RASHOD=STRAS;
SUSPEND;
/* ВЫВОД ИТОГОВОЙ СУММЫ */
NAME='EOlAl';
DOHOD=SDOHOD;
RASHOD=SRASHOD;
SUSPEND;
END
Как частный случай первых двух подходов, следует отметить возможность использования вложенного оператора FOR SELECT <ЗАПРОС> INTO <ПЕРЕМЕННЫЕ> DO <ДЕЙСТВИЕ>:
FOR SELECT <ЗАПРОС1> INTO <ПЕРЕМЕННЫЕ 1 > DO
FOR SELECT <ЗАПРОС2> INTO <ПЕРЕМЕННЫЕ2> DO <ДЕЙСТВИЕ>
Или
FOR SELECT <ЗАПРОС1> INTO <ПЕРЕМЕННЫЕ 1 > DO BEGIN
SELECT <ЗАПРОС2> INTO <ПЕРЕМЕННЫЕ2>
<ДЕЙСТВИЕ>
END
При этом если интерпретировать FOR SELECT как своеобразную “временную” таблицу, то такое вложение предоставляет возможность работать с несколькими “временными” таблицами. Например, если ведется в отдельной таблице подробный учет по другому члену семьи в такой же по структуре таблице, как и CASH, но с именем CASH2:
CREATE TABLE CASH2 (
ID INTEGER,
NAME VARCHAR(30),
DENGI INTEGER,
RDATE TIMESTAMP);
то чтобы подвести общий итог по двум членам семьи в виде
NAME SUMMA
дивиденды 100
зарплата 8 000
интернет -150
ком. платежи -900
проезд -200
ИТОГО 6 850
можно реализовать следующую процедуру:
CREATE PROCEDURE ITOG3 (
NGOD TIMESTAMP,
EGOD TIMESTAMP)
RETURNS (
NAME VARCHAR(30),
SUMMA INTEGER)
AS
DECLARE VARIABLE S INTEGER;
DECLARE VARIABLE TDENGI INTEGER;
DECLARE VARIABLE TDENGI2 INTEGER;
BEGIN
/* ИНИЦИАЛИЗАЦИЯ */
S=0;
/* ВЫБОРКА */
FOR SELECT NAME, SUM(DENGI) FROM CASH WHERE RDATE BETWEEN :NGOD AND :EGOD GROUP BY NAME INTO :NAME, :TDENGI DO
BEGIN
/* ВЫБОРКА ИЗ ВТОРОЙ ТАБЛИЦЫ */
TDENGI2=0;
SELECT SUM(DENGI) FROM CASH2 WHERE RDATE BETWEEN :NGOD AND :EGOD AND
NAME=:NAME GROUP BY NAME
INTO :TDENGI2;
/* ОБРАБОТКА ВОЗВРАЩЕНИЯ ПУСТЫХ ЗАПИСЕЙ */
IF (TDENGI IS NULL) THEN TDENGI=0;
IF (TDENGI2 IS NULL) THEN TDENGI2=0;
/* ПОДСЧЕТ СУММЫ И ВЫВОД РЕЗУЛЬТАТОВ */
SUMMA=TDENGI+TDENGI2;
S=S+SUMMA;
SUSPEND;
END
/* НЕОБХОДИМО УЧЕСТЬ ЗАПИСИ, КОТОРЫЕ ЕСТЬ ВО ВТОРОЙ ТАБЛИЦЕ И НЕТ В ПЕРВОЙ */
FOR SELECT NAME, SUM(DENGI) FROM CASH2 WHERE RDATE BETWEEN :NGOD AND :EGOD AND
NAME NOT IN (SELECT DISTINCT NAME FROM CASH)
GROUP BY NAME INTO :NAME, :SUMMA DO
BEGIN
S=S+SUMMA;
SUSPEND;
END
/* ВЫВОД ИТОГОВОЙ СУММЫ */ NAME-EOlAl';
SUMMA=S;
SUSPEND;
END
Третий подход применяется в тех случаях, когда невозможно по условиям задачи отсортировать предварительно выборку. Тогда организуется псевдовременная таблица с необходимыми полями, в процедуре производится сначала ее наполнение, а затем делается требуемая обработка записей в такой таблице. Вот как выглядит решение задачи,
поставленной при рассмотрении второго подхода, при использовании псевдовременных таблиц:
/* НЕОБХОДИМО СОЗДАТЬ ПСЕВДОВРЕМЕННУЮ ТАБЛИЦУ */
CREATE TABLE TEMP (
NAME VARCHAR(30), // поле для отчета DOHOD INTEGER, // поле для отчета RASHOD INTEGER, // поле для отчета
TUSER VARCHAR(8), // так как возможен многопользовательский доступ к таблице, то необходимо указывать, какому пользователю принадлежит запись, иначе, в случае, разбития процедуры формирования отчета на несколько транзакций, записи разных пользователей смешаются
TDATE TIMESTAMP // дата внесения записи - нужна для контроля за отслужившими записями и по каким-либо причинам не удаленными )
/* ПРОЦЕДУРА ФОРМИРОВАНИЯ ОТЧЕТА */ CREATE PROCEDURE ITOG4 (
NGOD TIMESTAMP,
EGOD TIMESTAMP)
RETURNS (
NAME VARCHAR(30),
DOHOD INTEGER,
RASHOD INTEGER)
AS
DECLARE VARIABLE TDENGI INTEGER; DECLARE VARIABLE ST VARCHAR(30); DECLARE VARIABLE SDOHOD INTEGER; DECLARE VARIABLE SRASHOD INTEGER; BEGIN
/* ИНИЦИАЛИЗАЦИЯ ПЕРЕМЕННЫХ */ SDOHOD=0;
SRASHOD=0;
/* УДАЛЕНИЕ СТАРЫХ ЗАПИСЕЙ, ВОЗМОЖНО ОСТАВШИХСЯ ОТ ПРЕДЫДУЩИХ ЗАПРОСОВ ПОЛЬЗОВАТЕЛЯ */
DELETE FROM TEMP WHERE TUSER=USER;
/* ЗАПОЛНЕНИЕ ПСЕВДОВРЕМЕННОЙ ТАБЛИЦЫ СПИСКОМ СТАТЕЙ */
INSERT INTO TEMP SELECT DISTINCT NAME, 0, 0, USER, CAST('NOW' AS TIMESTAMP) FROM CASH
WHERE RDATE BETWEEN :NGOD AND :EGOD;
/* ФОРМИРОВАНИЕ ОТЧЕТА */
FOR SELECT NAME, DENGI FROM CASH INTO :ST, :TDENGI DO BEGIN
IF (:TDENGI>=0) THEN
UPDATE TEMP SET DOHOD=DOHOD+: TDENGI WHERE NAME=:ST AND TUSER=USER;
ELSE
UPDATE TEMP SET RASHOD=RASHOD+:TDENGI WHERE NAME=:ST AND TUSER=USER;
END
/* ВЫВОД РЕЗУЛЬТАТА */
FOR SELECT NAME, DOHOD, RASHOD FROM TEMP WHERE TUSER=USER ORDER BY NAME
INTO :NAME, :DOHOD, :RASHOD DO
BEGIN
SDOHOD=SDOHOD+DOHOD;
SRASHOD=SRASHOD+RASHOD;
SUSPEND;
END
/* ВЫВОД ИТОГОВОЙ СУММЫ */
NAME='EOlAl';
DOHOD=SDOHOD;
RASHOD=SRASHOD;
SUSPEND;
END
Результат выполнения процедуры:
NAME DOHOD RASHOD
дивиденды 100 0
зарплата 6 000 0
интернет 0 -150
ком. платежи 0 -900
ИТОГО 6 100 -1 050
Т ак как по какой-либо причине в таблице TEMP могут остаться “бесхозные” старые записи, то необходимо ее периодически чистить, например, следующим образом:
DELETE FROM TEMP WHERE TDATE<CAST('TODAY' AS TIMESTAMP);
Понятно, что третий подход самый медленный из всех, так как проход по таблице CASH осуществляется два раза, к тому же делаются вставки и обновления записей в псевдовременной таблице и только затем выводится результат. Сюда же добавляется задача обеспечения регулярной чистки таблицы TEMP. Первый и второй вариант будут выполняться значительно быстрее - практически со скоростью выборки записей. Как показывает опыт, индексирование таблицы CASH по сортируемому полю NAME позволит добиться получения первых результатов выполнения запроса через несколько секунд даже на таблицах с количеством записей равным примерно 10 млн. строк. В то же время при третьем подходе время получения первых записей составит несколько секунд уже на таблицах с количеством записей равным примерно 10 тыс. строк. Например, при выполнении процедур ITOG2 и ITOG4 на приведенных выше тестовых примерах были получены следующие результаты (данные программы IBExpert):
ПАРАМЕТР ITOG2 ITOG4
Query Time
Prepare 0,00 ms 0,00 ms
Execute 15,00 ms 16,00 ms
Avg fetch time 3,00 ms 3,20 ms
Operations
Fetches | 15 570
Поэтому рекомендуется в первую очередь пытаться реализовать при решении поставленных задач первый (использование FOR SELECT) или второй подход (комбинация FOR SELECT и хранение предыдущих значений) и только в крайнем случае использовать третий подход (организация псевдовременных таблиц).
Е.В. Попова
ЭКСПЕРТНАЯ СИСТЕМА ДИАГНОСТИКИ ВЕРТЕБРОГЕННЫХ ПОЯСНИЧНЫХ БОЛЕЙ
В настоящее время продолжает оставаться актуальным вопрос дифференциальной диагностики поясничных болей, имеющих различные причины происхождения. В частности, диагностика позвоночных болей, вызванных остеохондрозом позвоночника от поясничных болей, вызванных другими причинами [1-3].
Использование диагностических возможностей неврологическо-клинического метода в определённых ситуациях при обследовании больных бывает недостаточным, тогда приходится использовать дополнительные небезопасные аппаратурные дорогостоящие методы нейровизуализации, такие как компьютерная томография и магнитнорезонансная томография.
Предлагается для диагностики вертеброгенных поясничных болей использовать разработанную машинно-обучаемую экспертную систему на основе байесовской модели логического вывода с несколькими узлами. Система ставит соответствующий диагноз, исходя из представленных ей симптомов, и рекомендует курс медикаментозного и физиотерапевтического лечения любого из диагностированных заболеваний.
В модели определены взаимосвязи между симптомами и заболеваниями, система правил и критериев (более 500), причём степень сложности при этом не ограничивается.
