Form a query

[orion_rus]

Hello world, i need to analize logs.
That i have:
i have logs about poisitions in a search engines:
fields:
word
googleposition
yahooposition

and others
i need to analize any moving in this positions ;
for example
word - concrete
googleposition - 24
yahooposition - 23
date - 2.02.06

word - concrete
googleposition - 21
yahooposition - 23
date - 3.02.06

word - concrete
googleposition - 21
yahooposition - 22
date - 4.02.06

For today i need to analize in a 1 query what happend from the previous position:
output should be follows:
googleposition: 21 Up
yahooposition - 22 Up
(it means what it find a difference from the previous changing position, not from the previous record)
Can u help me to find out it?

[Demaestro]

Do these logs live in a database or in a raw file somehwere?

[orion_rus]

in a MySQL database!

[Demaestro]

So what I have here is not exactly what you want but it is close. For what I wrote you would need an arg for "word". What this statement will do is return today and the day before today in 2 rows. You can then look at the dif or program something that will do the evaluation for you
****************************

select
..word,
..googleposition,
..yahooposition,
..date
from
..table
where
..date = $today
..and word = 'keyword'

union all

select
..word,
..googleposition,
..yahooposition,
..max(date)
from
..table
where
..date!= $today
..and word = 'keyword'
group by
..date

[edited by: Demaestro at 4:01 pm (utc) on Aug. 25, 2006]

[FalseDawn]

Demaestro - I think there is a problem with the second part of the union - it appears that it will return more than a single row.

If you are going to go this route, a simpler approach might be something like:

SELECT googlepos,yahoopos FROM log WHERE word='concrete' and mydate<=DATEPOINT ORDER BY mydate DESC LIMIT 2

Which would return the 2 rows relating to DATEPOINT, which can then be examined in code.

[Demaestro]

FD I guess mine assumes that there is only 1 entry per keyword.... per day... then it shouldn't return mutiple rows for the second query, yours does look cleaner though.

Orion, let us know if you need more help. I am sure someone here will help you get around it all.

[FalseDawn]

Yeah, it's one of those things that is relatively easy to solve using a simple bit of SQL and some code, but rather more difficult using pure SQL.
I think if it is really absolutely necessary to use pure SQL (is it for a report field, or something?), then the following will work, but it's not pretty.

SELECT 'googleposition', L.googlepos, CASE WHEN L.googlepos>L2.googlepos THEN 'up' ELSE CASE WHEN L.googlepos<L2.googlepos THEN 'down' ELSE 'nochange' END END FROM log L JOIN log L2 ON L.word=L2.word WHERE L.word='concrete' AND L2.word='concrete' AND L.mydate=DATEPOINT AND L2.mydate=(SELECT MAX(mydate) FROM log L3 WHERE word='concrete' AND mydate<DATEPOINT)
UNION
SELECT 'yahooposition', L.yahoopos, CASE WHEN L.yahoopos>L2.yahoopos THEN 'up' ELSE CASE WHEN L.yahoopos<L2.yahoopos THEN 'down' ELSE 'nochange' END END FROM log L JOIN log L2 ON L.word=L2.word WHERE L.word='concrete' AND L2.word='concrete' AND L.mydate=DATEPOINT AND L2.mydate=(SELECT MAX(mydate) FROM log L3 WHERE word='concrete' AND mydate<DATEPOINT)

[edited by: FalseDawn at 5:51 pm (utc) on Aug. 25, 2006]

[Demaestro]

Looks pretty to me FD ;)

But I am a nerd so....