#mysql #percentage #calculation
#mysql #процент #вычисление
Вопрос:
Как добавить столбец процентного изменения (не процентных пунктов) в MySQL?
существует таблица со столбцом изменений в процентах:
---------
| percent |
---------
| -0.50 |
| 0.50 |
| 1.00 |
| -0.20 |
| 0.50 |
| -1.00 |
| -2.00 |
| 0.75 |
| 1.00 |
| 0.50 |
---------
Как написать запрос, который вычисляет общее процентное изменение значения для каждой строки, чтобы вычисляемая строка выражала свое процентное изменение и все предыдущие строки процентного изменения?.
ожидаемый результат:
--------- --------------- ---------------
| percent | nominal_value | total_percent |
--------- --------------- ---------------
| -0.50 | 0.50 | -0.50 |
| 0.50 | 0.75 | -0.25 |
| 1.00 | 1.50 | 0.50 |
| -0.20 | 1.20 | 0.20 |
| 0.50 | 1.80 | 0.80 |
| -1.00 | 0.00 | -1.00 |
| -2.00 | -2.00 | -3.00 |
| 0.75 | -0.50 | -1.50 |
| 1.00 | 0.00 | -1.00 |
| 0.50 | 0.50 | -0.50 |
--------- --------------- ---------------
Где nominal_value это произвольное значение, которое было изменено на percent so для первой строки, если номинальное значение было 1.0 (100%), но было изменено на -0.50 ( -50% ), это привело к номинальному значению 0.5 .
Затем во второй строке percent изменение было 0.50 ( 50% ), поэтому номинальное значение было увеличено вдвое 0.5 => 0.75 , но можно также сказать, что оно было просто уменьшено на -0.25 ( -25% ) от его первоначального значения, поскольку от 1.0 до 0.75 — это -0.25 ( -25% ) 1.0 .
Это именно то, что я после total_percent изменения, nominal_value было просто для пояснительной цели и не требуется.
Я использую MySQL 8, поэтому запрос может использовать оконные функции / диапазоны и т.д.
вот тестовая таблица для репликации:
CREATE TABLE IF NOT EXISTS test
(
percent DECIMAL(5,2) NOT NULL
)
ENGINE = InnoDB
;
INSERT INTO test (percent) VALUES
(-0.50)
,(0.50)
,(1.00)
,(-0.20)
,(0.50)
,(-1.0)
,(-2.0)
,(0.75)
,(1.0)
,(0.50)
;
Комментарии:
1. Вам нужен ПЕРВИЧНЫЙ КЛЮЧ
2. Я не понимаю, как вы можете перейти от номинального значения 0 к номинальному значению -2. Как только что-то равно 0, любое процентное изменение этого значения даст 0.
3. В первой и последней строках ваших данных
nominal_valueравно 0,5. Однако в первом случаеtotal_percentравно -0,5, а в последнем случае равно 0,5. Как это может быть?4. @Nick например, у вас есть баланс в размере 100 долларов на вашем банковском счете, и вы тратите 100% от него, поэтому результатом был баланс = 0 долларов, но поскольку в вашем аккаунте есть возможность получения кредита, вы тратите еще 100 долларов на покупки в тот же день, так что в итоге вы получаете баланс в размере 100 долларов, который отражается на -200% от первоначальной суммы баланса и -100%, поступающих в отдел с 0 долларов. Другим примером может быть изменение значения функции sinus, которая переходит от -1 через 0 к 1 и обратно к -1, пересекая 0. Если бы вы остановились на 0, то вы не смогли бы выразить его увеличение или падение в процентах с течением времени.
5. @jimmix если у вас нет какого-либо способа уникальной идентификации строк, то у вас на самом деле нет таблицы. Без него вы можете случайно получить результат, соответствующий вашим ожиданиям, но это просто слепая удача.
Ответ №1:
Этот запрос даст вам желаемые результаты. Он использует два CTE, первый, который просто добавляет номер строки к данным, и второй, рекурсивный CTE, который генерирует nominal_value значения из текущего percent и предыдущего nominal_value (где предыдущее определяется номером строки). Наконец, total_percent вычисляется из nominal_value .
Примечание
Чтобы этот (и любой подобный) запрос работал надежно, должен быть PRIMARY KEY , по которому первый CTE может упорядочивать свои результаты. В демо-версии я добавил AUTO_INCREMENT INT столбец id для этой цели.
WITH RECURSIVE cte AS (
SELECT percent, ROW_NUMBER() OVER () AS rn
FROM test
ORDER BY id),
cte2 AS (
SELECT 1 percent AS nominal_value, rn
FROM cte
WHERE rn = 1
UNION ALL
SELECT CASE WHEN nominal_value = 0 THEN percent
ELSE nominal_value percent * ABS(nominal_value)
END,
cte.rn
FROM cte
JOIN cte2 ON cte2.rn = cte.rn - 1
)
SELECT percent, nominal_value, (nominal_value - 1) AS total_percent
FROM cte2
JOIN cte ON cte.rn = cte2.rn
Вывод:
percent nominal_value total_percent
-0.5 0.5 -0.5
0.5 0.75 -0.25
1 1.5 0.5
-0.2 1.2 0.2
0.5 1.8 0.8
-1 0 -1
-2 -2 -3
0.75 -0.5 -1.5
1 0 -1
0.5 0.5 -0.5
Комментарии:
1. он хорошо работал с данными, предоставленными вместе с вопросом, однако при тестировании на большем наборе данных возникла эта ошибка
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value., что неизбежно при использовании рекурсии в запросе. Возможно, из-за отсутствия индекса?2. @Jimmix да, вам нужно использовать рекурсивный запрос из-за итеративного процесса создания
nominal_valueзначений. Но нет никакого вреда в том, чтобы установить эту переменную достаточно большой, чтобы охватить все строки в вашей таблице.3. Я хотел бы избежать установки этой переменной достаточно высокой, потому что, если я правильно понимаю причину, стоящую за этим, это защита от почти бесконечного выполнения плохо написанных запросов. Я пытался переписать ваш запрос, но не удалось. Может ли решением быть использование комбинации LAG () / Временная таблица / переменные?
4. @Jimmix вы всегда можете установить ограничение по времени выполнения. Смотрите dev.mysql.com/doc/refman/8.0/en /… . Проблема с использованием переменных заключается в том, что они устарели в MySQL 8.0 и будут удалены в более поздней версии.
5. Если бы в
testтаблице был дополнительный столбец с уникальными датами, которые являются ПЕРВИЧНЫМ КЛЮЧОМ, было бы возможно написать запрос, который дает тот же результат, но не использует переменные и не повторяется?
Ответ №2:
DROP TABLE IF EXISTS test;
CREATE TABLE test
( id SERIAL PRIMARY KEY
, percent DECIMAL(5,2) NOT NULL
);
INSERT INTO test (percent) VALUES
(-0.5)
,(0.5)
,(1)
,(-0.2)
,(0.5)
,(-1)
;
SELECT ROUND(@i:=(@i (@i*percent)),2)n
FROM test
, (SELECT @i:=1) vars
ORDER
BY id;
------
| n |
------
| 0.50 |
| 0.75 |
| 1.50 |
| 1.20 |
| 1.80 |
| 0.00 |
------
6 rows in set (0.00 sec)
mysql>
Ответ №3:
Альтернативный способ вычисления этих данных — использование хранимой процедуры. Преимущество этого подхода в том, что он не требует рекурсивного CTE или переменных, но недостатком является то, что может быть сложно использовать результаты (например, в JOIN ). Эта процедура создает временную таблицу для хранения результатов перед их возвратом; эту таблицу можно было бы сохранить вместо DROP удаления в конце процедуры, если бы потребовалась дальнейшая обработка. Как и в случае с другими ответами, этот подход требует, чтобы данные имели PRIMARY KEY , чтобы гарантировать согласованные результаты.
DELIMITER //
CREATE PROCEDURE total_percent()
BEGIN
DECLARE nominal_value DECIMAL(10,2) DEFAULT 1;
DECLARE this_percent DECIMAL(5,2);
DECLARE done INT DEFAULT 0;
DECLARE p_cursor CURSOR FOR SELECT percent FROM test ORDER BY id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
CREATE TEMPORARY TABLE p (percent DECIMAL(5, 2),
nominal_value DECIMAL(10, 2),
total_percent DECIMAL(10, 2));
OPEN p_cursor;
compute: LOOP
FETCH p_cursor INTO this_percent;
IF done THEN
LEAVE compute;
END IF;
IF nominal_value = 0 THEN
SET nominal_value = this_percent;
ELSE
SET nominal_value = nominal_value this_percent * ABS(nominal_value);
END IF;
INSERT INTO p VALUES (this_percent, nominal_value, nominal_value -1);
END loop;
SELECT * FROM p;
DROP TABLE p;
END //
DELIMITER ;
CALL total_percent();
Вывод:
percent nominal_value total_percent
-0.5 0.5 -0.5
0.5 0.75 -0.25
1 1.5 0.5
-0.2 1.2 0.2
0.5 1.8 0.8
-1 0 -1
-2 -2 -3
0.75 -0.5 -1.5
1 0 -1
0.5 0.5 -0.5
Комментарии:
1. Приятно видеть 🙂 Я проверю, как это работает, забавно, что вы упомянули проблемы с объединениями, потому что именно сейчас я работаю с вашим ans, используя CTE, и выполняю объединения с другими таблицами 🙂 Что такого сложного в использовании JOINs с подобной процедурой?
2. @Jimmix Если вы хотите использовать хранимую процедуру, дайте временной таблице более удобное имя и удалите инструкции
SELECT *иDROPв конце. Затем вы можете использовать временную таблицу в других запросах.3. @Jimmix Для
JOINредактирования с CTE, оберните это последнееSELECTкак другое CTE, т.е., cte3 AS (SELECT percent, nominal_value, (nominal_value - 1) AS total_percent FROM cte2 JOIN cte ON cte.rn = cte2.rn)и его будет достаточно легко использовать вJOINинструкции позже в вашем запросе.
Ответ №4:
Это небольшое изменение принятого ответа из-за того, что OP отредактировал сообщение и добавил дополнительные строки данных и желаемый результат после принятого ответа. был опубликован и принят:
Запрос:
DROP TABLE IF EXISTS test;
CREATE TABLE test
(
id SERIAL PRIMARY KEY
, percent DECIMAL(5,2) NOT NULL
);
INSERT INTO test (percent) VALUES
(-0.50)
,(0.50)
,(1.00)
,(-0.20)
,(0.50)
,(-1.0)
,(-2.0)
,(0.75)
,(1.0)
,(0.50)
;
SELECT
percent,
CASE @i
WHEN 0 THEN ROUND(@i:=(@i (percent * 1)),2) -1
ELSE ROUND(@i:=(@i (percent * ABS(@i))) ,2) -1
END total_percent
FROM
test
, (SELECT @i:=1) vars
ORDER
BY id;
Результат:
--------- ---------------
| percent | total_percent |
--------- ---------------
| -0.50 | -0.50 |
| 0.50 | -0.25 |
| 1.00 | 0.50 |
| -0.20 | 0.20 |
| 0.50 | 0.80 |
| -1.00 | -1.00 |
| -2.00 | -3.00 |
| 0.75 | -1.50 |
| 1.00 | -1.00 |
| 0.50 | -0.50 |
--------- ---------------
10 rows in set, 3 warnings (0.00 sec)
Обратите внимание, что принятый ответ останавливает вычисления после достижения нулевого номинального значения, и тогда независимо от того, процентное изменение не имеет значения, и номинальное значение остается тем же = 0. В некоторых случаях это может быть правильным подходом. Для других вот этот, который продолжает вычисление через ноль или ответ @Nick в случае, если вы используете MySQL 8.