Время прочтения: 6 мин.
Модели кругом проникли в нашу жизнь, даже простейший выход в сеть подробно логируется и анализируется всеми начиная с софтовых гигантов и заканчивая мелким сайтом небольшой компании. Все хотят заработать на данных, все хотят делать целевые предложения и не засыпать нецелевыми тоннами спама людей, а дать предложения именно тому, кому это надо чтобы он наконец купил ваш продукт.
Как строятся модели мы уже рассказывали, но при этом обошли казалось самый популярный инструмент после excel — это SQL. Да, на нем тоже можно писать модели. И это даже очень удобно, так как в одном флаконе и доступ к данным (не нужны обвязки или прокладки типа hibernate), которые позволят обратиться и выбрать именно то, что надо.
Вот так, например, мы реализовали в последнем проекте простейшее дерево, сразу на лету используя данные из БД.
SELECT
[ID],
[AMOUNT],
CASE
WHEN AMOUNT >= 0.99 AND CODE IN (1, 22)
THEN 8
WHEN AMOUNT >= 0.99
AND (CODE NOT IN (1, 22) OR CODE IS NULL)
AND DEBT_RUB >= 666.0
THEN 10
……………………………………………………………………….
WHEN AMOUNT < 0.99
AND (DEL_DEBT < 0 OR DEL_DEBT >= 10.0)
AND (MAX_DEBT < 0.01)
AND ARG < 20.3
THEN 5
END _L_
FROM [MOD].[FACTORS] AS L;
Но не следует забывать, что для каждого дела свой инструмент. Всё-таки SQL сиквел — это не совсем то, что нужно. Почему это так? Для примера возьмем недавний наш проект. Нужно было перенести в код простенькую модель дерева решений.
С помощью case when это сделать очень просто. Что мы и доказали.
Но!
Существует сложность — SQL не может на лету считать и тут же использовать столбцы. Конечно можно поработать с временными таблицами (что собственно мы и сделали) или подзапросами.
SELECT
[B_ID],
[AMOUNT_RUR],
_L_,
CASE
WHEN _L_ = 1 and AMOUNT <= 1000.0 THEN 0.30
………………………………………………………………………………………….
WHEN _L_ = 10 and AMOUNT > 20000.00 THEN 0.60
END P_DATA
FROM
(SELECT
[ID],
……………………………………………………………………….
FROM [MOD].[FACTORS] AS L) AS DATA;
Но основная проблема – у SQL существует предел и можно потерпеть фиаско, написав case when на сто строк , да еще и три раза и получить в итоге сообщение об ошибке.
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them
В итоге — это все не оптимально.
«Юзаем» java!
Стандартный модуль для подключения к базе (рассматривался в статье Работа с БД с помощью Java и JDBC ). Чем он хорош? Он универсальный — написал раз и «юзаешь» вечно.
Потом задумываем структуру, где все это будет храниться.
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Vector;
public class Example {
/**
* Основная таблица для работы с расчётом
*/
public static String IND = ".[ind]";
/**
* Справочник по данным
*/
public static String RDE = ".[DATA_IND]";
………………………………………………………………………………………………………….
/**
* Схема, в которой находятся все рабочие таблицы
*/
public static String SCHEME = "[DATA]";
/**
* Статический класс представляющий словарь/справочник RDE/DELAY_IND.
* Хранит информацию о данных
*/
public static class DelayInd {
// Таблица коэффициентов для сегмента 1
private static HashMap<Integer, HashMap<String, Double>> segFirst = new HashMap<Integer, HashMap<String, Double>>();
// Таблица коэффициентов для сегмента 2
private static HashMap<Integer, HashMap<String, Double>> segSecond = new HashMap<Integer, HashMap<String, Double>>();
/**
* Функция для заполнения словаря/справочника RDE
* @param data данные для словаря (результат работы DBC.executeQuerySelect())
* @throws SQLException при невозможности получить данные
*/
public static void fillDict(ResultSet data) throws SQLException {
if (data == null) {
// Если не удалось загрузить данные
System.out.println("Can't init DELAY_IND: data is null");
throw new SQLException();
} else {
while (data.next()) {
// Получаем коэффициенты и записываем в соответствующую таблицу
HashMap<String, Double> row = new HashMap<String, Double>();
row.put("KOEF", data.getDouble("KOEF"));
row.put("ALPHA", data.getDouble("ALPHA"));
row.put("BETA", data.getDouble("BETA"));
if (data.getString("IND_NAME").equals("ПЕРВЫЙ")) {
segFirst.put(data.getInt("REST"), (HashMap<String, Double>) row.clone());
} else {
segSecond.put(data.getInt("REST"), (HashMap<String, Double>) row.clone());
}
}
System.out.println("DELAY_IND: loaded");
}
}
}
Далее культурно, построчно заполняем базу – это стильно, модно и молодёжно, а еще безопасно. И никакой админ не покарает тебя за выборку 100500 строк в таблицу, так как ты загружаешь их последовательно и можно грузить хоть миллион, хоть два (нам надо было около 540 млн срок).
Ну и потом, вуаля, и из структуры обращаемся к тому, что надо – производим нужные действия (в случае дерева — это сравнение и выбор через простой if else).
/**
* Инициализация переменных значениями из БД.
* Производится перед каждым новым расчётом
* @param data данные для расчёта (результат работы DBC.executeQuerySelect())
* @throws SQLException при невозможности получить данные
*/
public void init(ResultSet data) throws SQLException {
// Получение данных из таблицы
CALC = data.getDouble("CALC");
REST = data.getInt("REST");
KIND = data.getInt("KIND");
STAGE = data.getInt("STAGE");
REPDATE = data.getString("REPDATE");
// Расчёт количества месяцев
months = (int) Math.ceil(DAYS / daysInMonth);
// Определение типа продукта
switch (KIND) {
case segFirst:
KIND = "ПЕРВЫЙ";
break;
case segSecond:
KIND = "ВТОРОЙ";
break;
}
// Очистка всего, что нужно очистить
byMonth.clear();
byMonthwoMR.clear();
// Сброс флага вычислений
isCalculated = false;
}
/**
* Выполнение расчёта
*/
public void makeCalculation() {
// Если данные не подходят по типу, то сворачиваем расчёт
if (KIND == null) {
calcFail = true;
return;
}
/* Общие расчёты*/
/**/
CALC_DATA = STAGE * (1 - DELAY_IND.getKoef(KIND));
/**/
SCRN =
Math.min(STAGE * (1 + Example.KIND_MR_ADD_IND.getMRAdd(CALC, REST)), 1.);
/* Начало расчёта */
if (STAGE == 1) {
/* Расчёт MR и дисконтированной доли */
for (int i = 1; i <= months; i++) {
HashMap<String, Double> calcs = new HashMap<String, Double>();
if (i <= 12) {
calcs.put(
"MR",
(i * CALC) / 12
);
} else {
calcs.put(
"MR",
CALC
);
}
calcs.put(
"discountPart",
CALC_1 / CALC_2 / Math.pow(1 + DISC_RATE / 12, i)
);
byMonth.add((HashMap<String, Double>) calcs.clone());
}
byMonthwo.add((HashMap<String, Double>)calcs.clone());
}
/**/
CALC_FINAL = 0.;
for (HashMap<String, Double> stringDoubleHashMap : byMonth) {
CALC_FINAL += stringDoubleHashMap.get("MR") * stringDoubleHashMap.get("discountPart");
}
/**/
CALC = 0.;
for (HashMap<String, Double> stringDoubleHashMap : byMonthwo) {
CALC += stringDoubleHashMap.get("MR") * stringDoubleHashMap.get("discountPart");
}
/* Конец расчёта */
}
// Установка флага завершения расчёта
isCalculated = true;
}
}
Выдаем итог, тут ничего сложного, но если интересно, то примерно вот так:
/**
* Получение результатов расчёта
* @return результаты расчётов в виде отформатированной строки
*/
public String getResults() {
if (calcFail) {
return "fail";
}
if (!isCalculated) {
return "not calculated";
}
String ret = "\n/******** STAGE: " + STAGE + " | " + REPDATE +" ********/";
ret += "\nxxx: calc/db\n\n";
ret += "\n";
for (int i = 0; i < byMonth.size(); i++) {
ret = ret.concat("k " + (i+1) + " | " + byMonth.get(i) + "\n");
}
ret += "CALC: " + CALC_FINAL.toString() + " / " + CALC.toString()
+ " | equal " + (Math.abs(CALC_FINAL - CALC) < 0.1)
+ " (" + Math.abs(CALC_FINAL - CALC) + ")\n";
ret += "\n";
return ret;
}
Таким образом, мы рассмотрели два варианта: через SQL и с помощью Java. И вывод простой – не увеличивайте свои страдания, не делайте модели через SQL.