I am writing a system for counting and redistributing traffic.

In the database (mysql) there are tables sites , ins , outs , clicks .

Table sites have id and domain fields.

Tables ins outs clicks have fields site_id created

Is it possible to get all the data from the table sites + in one query to get the number of ins , outs , clicks created in the last 3600 and 86400 seconds belonging to each of the sites?

  • one
    Maybe. So what is your question, what can you not do from what you tried? - Kromster

1 answer 1

It seems to me that it will be easier to use subqueries with sampling

Main query:

 SELECT `sites`.*, (<SUB_SELECT>) AS `ins_count`, (<SUB_SELECT>) AS `outs_count`, (<SUB_SELECT>) AS `click_count` FROM `sites` WHERE 1;

The subquery <SUB_SELECT> , for example, for the number of clicks in the last 3600 seconds:

 SELECT COUNT(*) FROM `clicks` WHERE `site_id` = `sites`.`id` AND `created` BETWEEN UNIX_TIMESTAMP()-3600 AND UNIX_TIMESTAMP()

The specifics of checking the time "for the last 3600 seconds" depends on the format of the created field, it can be TIMESTAMP or INT or something else, see for yourself how you

The full request looks awful and not understandable, but I would risk bringing it:

 SELECT `sites`.*, ( SELECT COUNT(*) FROM `ins` WHERE `site_id` = `sites`.`id` AND `created` BETWEEN UNIX_TIMESTAMP()-3600 AND UNIX_TIMESTAMP() ) AS `ins_count`, ( SELECT COUNT(*) FROM `outs` WHERE `site_id` = `sites`.`id` AND `created` BETWEEN UNIX_TIMESTAMP()-3600 AND UNIX_TIMESTAMP() ) AS `outs_count`, ( SELECT COUNT(*) FROM `clicks` WHERE `site_id` = `sites`.`id` AND `created` BETWEEN UNIX_TIMESTAMP()-3600 AND UNIX_TIMESTAMP() ) AS `click_count` FROM `sites` WHERE 1;

As a result, we have as a result all the sites and 3 additional fields from the subqueries with the number. If you need three more fields with the number for the last 86400, you know what to do.

  • Thanks, that is necessary! - AntonIvanov