MYSQL - select data from the second row in a large table

Question

I have an external third-party program exporting a database to mysql in real time and I want to show data for reporting. Thus, I cannot change the structure because it is synchronized in real time.

The table structure looks something like this:

ID | Date | Transaction ----------------------------- 12 | 2012-11-01 | 200 12 | 2012-11-02 | 250 12 | 2012-11-03 | 150 12 | 2012-11-04 | 1000 12 | 2012-11-05 | 225 .... 13 | 2012-11-01 | 175 13 | 2012-11-02 | 20 13 | 2012-11-03 | 50 13 | 2012-11-04 | 100 13 | 2012-11-05 | 180 13 | 2012-11-06 | 195 

The data is very large and continues to grow every day.

What I want to do is create a report (lookup table) based on something like this:

 ID | Date | Transaction | Prev Day Transaction ---------------------------------------------------- 12 | 2012-11-01 | 200 | 0 12 | 2012-11-02 | 250 | 200 12 | 2012-11-03 | 150 | 250 12 | 2012-11-04 | 1000 | 150 12 | 2012-11-05 | 225 | 1000 .... 13 | 2012-11-01 | 175 | 0 13 | 2012-11-02 | 20 | 175 13 | 2012-11-03 | 50 | 20 13 | 2012-11-04 | 100 | 50 13 | 2012-11-05 | 180 | 100 13 | 2012-11-06 | 195 | 180 

I just can't get quick pick instructions. Currently, the source data is already 283,120 rows. And it will grow like 500 rows per day.

I tried something like:

 SELECT *, (SELECT transaction FROM table as t2 WHERE t1.id=t2.id AND t1.date>t2.date ORDER BY t2.date DESC LIMIT 0,1) FROM table AS t1 

It works, but the select statement is very slow. Most of the time it is cut off in the middle of the operation.

I need help - this is a very fast SQL statement that I could later use to create a view table.

Answer 1

See this link: http://sqlfiddle.com/#!2/54a5e/12

 select t.id,t.cDate,t.cTrans ,(case when @pID=t.id then @pTran else 0 end) as preT ,(@pID :=t.id) as `tID`,(@pTran := t.cTrans) as `tTrans` from tb_test_1 as t,(select @pID = 0, @pTran = 0) as t2 order by id,cDate; 

Column

tID and tTrans must be saved and cannot be displayed on the page.

Please forgive me because I only know a little English!

Answer 2

Try this query -

 SELECT t1.*, COALESCE(t2.transaction, 0) Prev_Day_Transaction FROM trans t1 LEFT JOIN (SELECT * FROM trans ORDER BY id, date DESC) t2 ON t1.id = t2.id AND t1.date > t2.date GROUP BY t1.id, t1.date; +------+------------+-------------+----------------------+ | id | date | transaction | Prev_Day_Transaction | +------+------------+-------------+----------------------+ | 12 | 2012-11-01 | 200 | 0 | | 12 | 2012-11-02 | 250 | 200 | | 12 | 2012-11-03 | 150 | 250 | | 12 | 2012-11-04 | 1000 | 150 | | 12 | 2012-11-05 | 225 | 1000 | | 13 | 2012-11-01 | 175 | 0 | | 13 | 2012-11-02 | 20 | 175 | | 13 | 2012-11-03 | 50 | 20 | | 13 | 2012-11-04 | 100 | 50 | | 13 | 2012-11-05 | 180 | 100 | | 13 | 2012-11-06 | 195 | 180 | +------+------------+-------------+----------------------+ 

Add a composite index (id, date) to the table.

=============================

 ALTER TABLE mt4_daily ADD INDEX IX_mt4_daily_DATE (DATE); ALTER TABLE mt4_daily ADD INDEX IX_mt4_daily (ID, DATE); 

Answer 3

Divide the table into several pairs using selection operators and join them using the UNION Set operator. Since all the given operators are parallel, you will get the data very quickly. You can split the data using a unique numeric column in the table. eg.

 select * from tbl_x where col1%3=0 union select * from tbl_x where col1%3=1 union select * from tbl_x where col1%3=2 ... 

The above sql query splits data and samples in parallel

Answer 4

I would try to write a query as follows:

 SELECT tbl.ID, tbl.Date, tbl.Transaction, COALESCE(tbl1.Transaction,0) as PrevDay FROM tbl left join tbl tbl1 on tbl.ID = tbl1.ID and tbl.Date = tbl1.Date + INTERVAL 1 DAY 

(this will only work if you make sure the table contains all the days, if you miss one day, the next day PrevDay will always be displayed as 0, I'm not sure if this is what you need).

EDIT: I would try this solution that works even if some days are missing:

 SELECT tbl.id, tbl.date, tbl.Transaction, COALESCE(tbl1.Transaction,0) as PrevDay FROM (SELECT tbl.id, tbl.date as d1, max(tbl1.ddate) as d2 FROM tbl LEFT JOIN tbl tbl1 ON tbl.id = tbl1.id and tbl.date>tbl1.date GROUP BY tbl.id, tbl.date) t LEFT JOIN tbl on tbl.id = t.id and DATE(tbl.ddate) = DATE(t.d1) LEFT JOIN tbl tbl1 ON tbl1.id = t.id and DATE(tbl1.date) = DATE(t.d2)