Java, SQL, Подготовка данных

Модели – SQL VS Java

Время прочтения: 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.

Советуем почитать