0

I am trying for several days to solve the following problem: let's say we have

table users

user_id, credit

table properties

property_id, user_id, check_interval

tables states_(year) (i.e. multiple tables - for each year one starting 2010 - i.e. states_2010 - when the application was born)

property_id, timestamp

The logic behind is that each user has one or more properties. Each property has one or more states. Each state has been obtained as of the timestamp. States are obtained once per certain time, which is check_interval (for each property specific). Obtaining each state costs each user one credit which is deducted from the user account (table users).

My aim is to get an overview with property_ids and time of next check (each property_id only once) starting from the checks planned for the immediate future. Time of the next check is the timestamp of the last state (i.e. max(timestamp) from all of the states_(year) tables) plus check_interval. That overview should contain only property_id of properties belonging to users that have some credit (i.e. credit > 0).

I have found very close question (Get max row per group from a related table) but it only relates to three tables and is solving my issue only partially. In my case, there is also complication with users' credit and multiple states_(year) tables which is something I cannot break.

Community
  • 1
  • 1
John Peeterson
  • 71
  • 1
  • 12

1 Answers1

0

You may want to start by trying to overcome the state_[year] difficulty using a view.

CREATE VIEW super_states AS
SELECT property_id, `timestamp` FROM states_2013
UNION
SELECT property_id, `timestamp` FROM states_2012
UNION
SELECT property_id, `timestamp` FROM states_2011;

{Continue as needed}

Then try this SQL

SELECT properties.property_id
  , DATE_ADD(states.most_recent, properties.check_interval SECONDS) AS Next_Time
FROM properties
JOIN users
  ON properties.user_id = users.user_id
LEFT JOIN (SELECT property_id, MAX(timestamp) AS most_recent
           FROM super_states
           GROUP BY property_id) AS states
  ON states.property_id = properties.property_id
WHERE users.credit > 0;

Edit - after feedback

Step 1. Check the view - Does this look ok?

SELECT * FROM super_states LIMIT 20;

Step 2. Check the subquery which shows most recent timestamp per property

SELECT property_id, MAX(`timestamp`) AS most_recent
FROM super_states
GROUP BY property_id;

Step 3. Try this variation of the solution

SELECT properties.property_id
  , states.most_recent AS most_recent
  , properties.check_interval AS seconds_to_next_check
  , DATE_ADD(states.most_recent, properties.check_interval SECONDS) AS Next_Time
FROM properties
JOIN users
  ON properties.user_id = users.user_id
LEFT JOIN (SELECT property_id, MAX(`timestamp`) AS most_recent
           FROM super_states
           GROUP BY property_id) AS states
  ON states.property_id = properties.property_id
WHERE users.credit > 0;
AgRizzo
  • 5,261
  • 1
  • 13
  • 28
  • Thanks for the tip! However it seems that Next_Time is blank. I do not know why. I have little experience with such complicated queries but can it be because most_recent is defined later (row 6 of your code) than used (row 2 of your code)? – John Peeterson Mar 27 '14 at 20:41
  • Not really (I'm not implying I am perfect, but that code is considered valid SQL and if it was undefined, the query would throw some nasty error). I will break the query down into its components, which you can run and see if I made some poor assumptions. It could also be a backticks problem – AgRizzo Mar 27 '14 at 20:49
  • Thanks a lot! I have gone through the tests and found that a problem is somehow connected with function DATE_ADD which was neither working nor throwing any error. I do not know if the performance is better using DATE_ADD but for our aim simple summation of two number suffices. Working solution added above. – John Peeterson Mar 27 '14 at 21:57