How to add calculated column with LAG in SQL?

Question

I have a MySQL table (version 8.0.26) with stock prices and want to calculate the log price change for future analysis. Here's my table and data.

CREATE TABLE `prices` (
  `ticker` varchar(7) NOT NULL,
  `date` datetime NOT NULL,
  `price` double DEFAULT NULL,
  PRIMARY KEY (`ticker`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci


INSERT INTO `sandbox`.`prices` (`ticker`, `date`, `price`) VALUES ('A', '2021-01-01', '10');
INSERT INTO `sandbox`.`prices` (`ticker`, `date`, `price`) VALUES ('A', '2021-01-02', '10.1');
INSERT INTO `sandbox`.`prices` (`ticker`, `date`, `price`) VALUES ('A', '2021-01-03', '11');
INSERT INTO `sandbox`.`prices` (`ticker`, `date`, `price`) VALUES ('B', '2021-01-01', '50');
INSERT INTO `sandbox`.`prices` (`ticker`, `date`, `price`) VALUES ('B', '2021-01-02', '51.5');
INSERT INTO `sandbox`.`prices` (`ticker`, `date`, `price`) VALUES ('B', '2021-01-03', '49');

I can write this query but the column isn't saved.

SELECT *, LN(price / lag(price, 1) OVER (PARTITION BY ticker)) AS ln_open_return FROM sandbox.prices;

I put together this code from these answers but I'm still getting a "1064 syntax error: 'WITH' is not valid at this position. Expecting an expression."

ALTER TABLE sandbox.prices
ADD COLUMN ln_change DOUBLE AS (
WITH temp AS (
 SELECT
  *,
  LAG(price, 1) OVER(PARTITION BY ticker ORDER BY date) AS prior
  FROM sandbox.prices
)
SELECT
    *,
   COALESCE(LN(price / prior)) AS ln_change
FROM temp) PERSISTED;

@radocaw My SQL query doesn't run because it has a syntax error and I can't figure it out. Also, what parameters are typically used for DECIMAL(size, d) to represent stock prices? — cona, Jul 26 '21 at 22:30
I think you should use a `VIEW` for this, not a calculated column. I don't believe it's possible to use windowed-functions in a calculated column, but I might be wrong. — Dai, Jul 26 '21 at 23:39
Please edit your question to specify which version of MySQL you're using. — MatBailie, Jul 26 '21 at 23:43
DOUBLE can't accurately represent all decimal values, you should read up of floating point rounding errors before using it. Or, more appropriately, use something like DECIMAL(9,2),which can hold values up to 9,999,999.99 in 4bytes. — MatBailie, Jul 26 '21 at 23:49

Dai · Accepted Answer · 2021-07-26T23:55:21.857

1

Calculated columns (aka computed columns, aka generated columns) in a TABLE (as in CREATE TABLE or ALTER TABLE) cannot contain queries, they can only be expressions derived from other columns in the same row.

https://dev.mysql.com/doc/refman/8.0/en/create-table-generated-columns.html

Values of a generated column are computed from an expression included in the column definition.

Generated column expressions must adhere to the following rules

[...]

Subqueries are not permitted.

Instead, you can do this using a VIEW. Your application code or reports would then query the view (prices_with_delta), not the base table (prices):

CREATE VIEW sandbox.prices_with_delta AS

SELECT
    p2.*,
    COALESCE( LN( p2.price / p2.prior ) ) AS ln_change
FROM
    (
        SELECT
            p.*,
            LAG( p.price, 1 ) OVER( PARTITION BY p.ticker ORDER BY p.date ) AS prior
        FROM
            sandbox.prices AS p
    ) AS p2

edited Jul 26 '21 at 23:55

answered Jul 26 '21 at 23:44

Dai

141,631
28
261
374

Answer looks good except for the repeated "As prior" statement. – cona Jul 26 '21 at 23:54
Is there a way to avoid creating a `prior` column which I believe violates normality? I'm not sure if normality rules apply to VIEWs or not. – cona Jul 27 '21 at 02:38
1

@cona "violates normality" isn't really a thing (I know you mean "normal form", but "violating" it is not an error or anything _illegal_ or causes a database crash)? Anyway, Normal Form only applies to storage, not views. – Dai Jul 27 '21 at 03:07

How to add calculated column with LAG in SQL?

1 Answers1