By ET

Just wrote the most complicated SQL query I’ve ever encountered.� Since the table “clicks” has more than 5 million records, I’m going to wait till tomorrow to get the result.

The reason I have to write this is that I was trying to use a PERL program to do the same, and so far, after two days, it is still running with very slow progress.


create table sqlhistory as
SELECT a.inc, a.clicktime, (SELECT MAX(clicktime) FROM clicks b
where a.keyid=b.keyid and a.adid=b.adid and b.clicktime<a.clicktime
) as bctime, a.keyid, a.adid, a.bid, a.price, a.rank, a.reserve_price, a.reserve_price_new
FROM clicks a
where a.bid<>(select c.bid from clicks c
where a.keyid=c.keyid and a.adid=c.adid and c.clicktime=(SELECT MAX(clicktime) FROM clicks b
where a.keyid=b.keyid and a.adid=b.adid and b.clicktime<a.clicktime
)
limit 1
)

======Update====

Sun Wei suggested a new way to do it, and the productivity increased almost 1000 fold

1.

create table clickorder as
select * from clicks
order by keyid, adid, clicktime

2.

create an auto_increment field called ‘inc’, and a new field called lastbid

3.

update clickorder a, clickorder b
set a.lastbid=b.bid
where b.inc=a.inc-1 and a.keyid=b.keyid and a.adid=b.adid


By ET This entry was posted on Monday, August 17th, 2009 at 12:44 am and is filed under General, Information, Notes, Research, Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.