0

In MySQl I have a table positions and I would like to find out if position of certain app for certain feed in a certain country type has been increasing/decreasing and by how much from the last reading.

This is the table structure:

CREATE TABLE `positions` (
  `application_id` int(11) unsigned NOT NULL,
  `country` varchar(2) NOT NULL,
  `feed_id` int(2) NOT NULL,
  `position` int(4) NOT NULL,
  `created` datetime NOT NULL,
  KEY `application_id` (`application_id`,`store`,`feed_id`,`position`),
  KEY `created` (`created`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

country is a two character ISO country code, every row belongs to one application through application_id and feed_id is another table that is helping to sort out the large amount of data.

What the query should look like? I have gotten to a pont where I group by the country code where application and feed id's are in WHERE clause but can't figure out the values :(

3
  • Maybe I'm confused, but you don't appear to have any field for "feed_volume" or similar in your table - what field are you using to measure your increase/decrease? Also, some DML (INSERT INTO TABLE VALUES(...) would be good. – Vérace Jul 26 '14 at 11:31
  • it's the position ... imagine position in a list where you want to track if the app is heading up or down ... Each country has feeds, feed has number of applications and each app on the feed/country gets a new value every day so this tracks the history of the ups and downs. – Ondrej Rafaj Jul 26 '14 at 12:22
  • That's clearer - although the field name "position" is not very intuitive. I'm still a bit unclear about whether you have one record per day or possibly many per day for a given app, feed and country type (which is what I think is happening). It would make life a lot easier if you could post some sample DML and give us the result you want and the logic you used to get that result. You'll need GROUP BYs and SUM()s, but I'm still a wee bit unclear as to exactly what you require. – Vérace Jul 26 '14 at 12:51
1

Join positions to itself. In pseudo-sql:

select
    o.position,
    n.position
from positions as o        -- old
inner join positions as n  -- new
    on o.application_id = n.application_id
    and o.country = n.country
    and o.feed_id = n.feed_id
    and o.created = <max value for this app, country and feed that's less than n.created>

There's a discussion on this SO question for other options.

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.