Devtonight
  1. Home
  2. ›
  3. Articles

How to Calculate Exponential Moving Average (EMA) With MySQL

In mysql on • 4~7 minutes
How to Calculate Exponential Moving Average (EMA) With MySQL

MySQL can be used to calculate the “Exponential Moving Average” (EMA) easily and quickly. First, create a MySQL table with date and price columns. Feel free to use the below-given MySQL database table schema if you need it.

CREATE TABLE `ema` (
  `id` int NOT NULL,
  `date` date NOT NULL,
  `price` decimal(8,2) NOT NULL
);

ALTER TABLE `ema`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `ema`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

Insert some sample data into the table.

INSERT INTO `ema` (`id`, `date`, `price`) VALUES
(1, '2022-05-04', '39685.23'), (2, '2022-05-05', '36533.93'),
(3, '2022-05-06', '36019.22'), (4, '2022-05-07', '35463.46'),
(5, '2022-05-08', '34025.50'), (6, '2022-05-09', '30082.00'),
(7, '2022-05-10', '30999.97'), (8, '2022-05-11', '29000.00'),
(9, '2022-05-12', '28896.19'), (10, '2022-05-13', '29238.81'),
(11, '2022-05-14', '30033.96'), (12, '2022-05-15', '31295.03'),
(13, '2022-05-16', '29832.45'), (14, '2022-05-17', '30425.36'),
(15, '2022-05-18', '28681.26'), (16, '2022-05-19', '30290.33'),
(17, '2022-05-20', '29176.70'), (18, '2022-05-21', '29421.05'),
(19, '2022-05-22', '30264.66'), (20, '2022-05-23', '29081.76'),
(21, '2022-05-24', '29632.15'), (22, '2022-05-25', '29510.15'),
(23, '2022-05-26', '29183.06'), (24, '2022-05-27', '28589.48'),
(25, '2022-05-28', '29010.82'), (26, '2022-05-29', '29448.94'),
(27, '2022-05-30', '31731.22'), (28, '2022-05-31', '31762.76'),
(29, '2022-06-01', '29787.40'), (30, '2022-06-02', '30437.18'),
(31, '2022-06-03', '29669.39'), (32, '2022-06-04', '29853.51'),
(33, '2022-06-05', '29888.98'), (34, '2022-06-06', '31351.21'),
(35, '2022-06-07', '31115.82'), (36, '2022-06-08', '30185.58'),
(37, '2022-06-09', '30089.03'), (38, '2022-06-10', '29065.66'),
(39, '2022-06-11', '28390.94'), (40, '2022-06-12', '26585.10');

The Formula of Exponential Moving Average (EMA)

Before start creating the SQL query, we need to understand the formula of the Exponential Moving Average.

  • k = Multiplier
  • SMOOTHING = Smoothing controls the effectiveness of recent data and usually, it is 2
  • N = Period (Ex: 5, 13, 21, 34, 50, 100, 200..)
  • EMA = Current EMA
  • PRICE = Current price
  • PRE_EMA = Previous EMA

k = SMOOTHING ÷ (N + 1)​
EMA = PRICE × k + PRE_EMA × (1 − k)

Calculate Exponential Moving Average (EMA) With MySQL

Now let’s create the SQL query. We might need to change the period more often. So, we are using the @period variable to store it. Then we calculate the multiplier value and assign it to the @multiplier variable.

SET @period := 5;
SET @multiplier := 2 / (@period + 1);

Select date, price and ema fields. However, here we have a little problem. That means, according to the EMA formula, we need to provide a previous EMA value to the EMA formula. But we do not have such a value for the first iteration. So we have a few options.

  1. Since we do not have any previous data we can use 0 as the previous EMA.
  2. Calculate the average price and use it as the previous EMA.
  3. Use the current price as the previous EMA.

If we used the first option, due to the unrelated 0, it can take some additional iterations to start showing correct EMA values. If we used the second option, it will be a bit fine, but it also can generate unexpected results when the average price is too high or low from the current price. If we used the third option, that means the current price as the previous EMA, still it will produce false data for the first couple of iterations, however, it will always recover faster than other available options. We use 5 as the period value. However, if you are using higher period values like 50, 100, 200,.. it will take longer iterations to start showing correct EMA values. So make sure to add enough data for high period values.

So we check whether @ema is already assigned or not (null). If it is null, the current price will be used as the previous EMA. Otherwise, the actual calculated previous EMA will be used starting from the second iteration.

SELECT
    date,
    price,
    @ema := (price * @multiplier + (IF(@ema IS null, price, @ema)) * (1 - @multiplier)) AS ema
FROM ema
ORDER BY date ASC;

Finally, the complete SQL query should be something like the below.

SET @period := 5;
SET @multiplier := 2 / (@period + 1);

SELECT
    date,
    price,
    @ema := (price * @multiplier + (IF(@ema IS null, price, @ema)) * (1 - @multiplier)) AS ema
FROM ema
ORDER BY date ASC;
Share:
You May Also Like: