1

Right now I have a table that looks like this but with around 1 million rows and over 100 company names.

Date  |  Company  | Price
10/08/16  Exxon      2.00
10/08/16  Shell      1.95
11/08/16  Exxon      2.01
11/08/16  Shell      1.97

What is the best way to go about calculating the percent difference per day per company so that I end up with a table or view that looks like this.

Date  | Company | % difference
10/08/16  Exxon     .56
10/08/16  Shell     .24
11/08/16  Exxon     1.005
11/08/16  Shell     1.01

would it be better to store each company as a column or keep it in row format, also how would I write the function so its not recalculating all million rows every day when I import new information.

  • See if MariaDB's Windowing functions will make that easy to write. – Rick James Aug 30 '16 at 0:31
0

I wrote out an answer that works in SQL Server before noticing you have this question tagged as MySQL. For context, if you weren't in MySQL you'd use the LAG function like this:

SELECT 
    s.P_date, 
    s.Company, 
    s.Price, 
    s.Price/s.Last_price as Percent_diff
FROM
(   SELECT 
        t.P_date, 
        t.Company, 
        t.Price,
        LAG(t.Price) OVER (PARTITION BY t.Company ORDER BY t.Company, t.P_Date)  Last_price
    FROM YourTable as t
) as s

The LAG line selects the Price value from the last record for the same company, allowing the outer query to do the percent calculation.

But MySQL doesn't support LAG. Luckily there's an answer on Stack Overflow that shows how to simulate LAG in MySQL. It even uses an example table almost identical to yours:

https://stackoverflow.com/questions/11303532/simulate-lag-function-in-mysql

I suggest you do this as a view, not a table. That way the calculation will only run when you select from the view, leaving you free to update your main table without worrying about those million rows bogging you down.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.