0

Friends, I would like help from you to find a solution to the following case:

A table with 2 fields: datetime, record (the record being any number that can be repeated a few times)

I need to count how many times each record appears in 12h, 24h, and 72h intervals.

Example, supposing record number 1: It appears 2 times in a 12h interval, plus 1 within 24h and another 3 in the 72h interval.

Thus, for the first record 1, in the 12-hour column there would be a value of 2, a 24-hour value would be 3, a 72-hour value would be 6. In the second row of record 1 the results would be: for 12h value 1, 24h value 2, 72h value 5 and so on.

Any suggestion?

Improve this question
5
  • 1
    How do you come up with "the second row of record 1"? Just do not get you. Better, write a table and populate it with some values so we can understand you.
    – jyao
    Commented Mar 27, 2018 at 4:05
  • throw sample data
    – KumarHarsh
    Commented Mar 27, 2018 at 6:23
  • Define interval. 12 horus from each other or from the current date time? If it's the first, then is this dynamic across each record meaning a 12hr window for one record isn't the same for another?
    – S3S
    Commented Mar 27, 2018 at 19:44
  • DATE;REGISTRO;2H;6H;12H;24H 27/03/2018 00:00:02;A;0;1;1;1 27/03/2018 00:14:26;B;0;1;1;1 27/03/2018 02:24:02;A;1;1;1;1 27/03/2018 02:52:50;A;1;1;1;1 27/03/2018 04:19:14;A;0;1;1;1 27/03/2018 04:48:02;B;0;1;1;1 27/03/2018 06:43:14;A;1;1;1;1 27/03/2018 07:40:50;B;1;1;1;1 27/03/2018 08:09:38;B;0;1;1;1 27/03/2018 08:38:26;A;0;1;1;1 27/03/2018 10:33:38;B;1;1;1;1 27/03/2018 11:16:50;A;0;0;1;1 27/03/2018 11:45:38;B;1;1;1;1 27/03/2018 12:57:38;B;1;1;1;1 27/03/2018 14:24:02;B;1;1;1;1 27/03/2018 14:38:26;B;1;1;1;1 27/03/2018 16:19:14;B;0;0;0;0 27/03/2018 18:00:02;A;0;0;0;0
    – Carlos Eduardo Nogueira Gondim
    Commented Mar 28, 2018 at 1:45
  • The calculation needs to iterate between all the records in the table to check not only the record but also the time interval of that time + 2h forwards or + 6h, + 12h, + 24h or + 72h
    – Carlos Eduardo Nogueira Gondim
    Commented Mar 28, 2018 at 2:16

1 Answer 1

Reset to default
1

I can share the conceptual answer to this problem, you will have to fill in correct syntax for query as per your database engine.

select record, count(record)
from table
where datetime is interval of 12 hours from now
group by record

This query can give you record occurrences in one single interval of time.

select record, 
   SUM(case when datetime is in interval 12h then 1 else 0 end) 12_hour_occurrence,
   SUM(case when datetime is in interval 24h then 1 else 0 end) 24_hour_occurrence,
   SUM(case when datetime is in interval 72h then 1 else 0 end) 72_hour_occurrence
from table
where datetime > now() - 72 hours # don't pass thorugh extra records
group by record

For running on single query on multiple instances, you can use this concept to find occurrences in multiple intervals.

Improve this answer
4
  • Hassan, thank you for the help. The way is almost this, but it's not exactly that. I could not explain correctly what I need. This calculation needs to check in the time column all intervals for each record. It is not -72h from now, but + 72h from the time of registration in the table.
    – Carlos Eduardo Nogueira Gondim
    Commented Mar 28, 2018 at 2:14
  • @CarlosEduardoNogueiraGondim - do you mean interval since the first time any record appeared in the table? If yes, then now() can be replace with min() datetime for each record and - with +. It will be helpful to see actual data for example records: {1, 2, 3} you have shared in the problem statement.
    – Hassan Farid
    Commented Mar 28, 2018 at 11:51
  • see this exemple: DATE;REGISTRO;2H;6H;12H;24H 27/03/2018 00:00:02;A;0;1;1;1 27/03/2018 00:14:26;B;0;1;1;1 27/03/2018 02:24:02;A;1;1;1;1 27/03/2018 02:52:50;A;1;1;1;1 27/03/2018 04:19:14;A;0;1;1;1 27/03/2018 04:48:02;B;0;1;1;1 27/03/2018 06:43:14;A;1;1;1;1 27/03/2018 07:40:50;B;1;1;1;1 27/03/2018 08:09:38;B;0;1;1;1 27/03/2018 08:38:26;A;0;1;1;1 27/03/2018 10:33:38;B;1;1;1;1 27/03/2018 11:16:50;A;0;0;1;1 27/03/2018 11:45:38;B;1;1;1;1 27/03/2018 12:57:38;B;1;1;1;1 27/03/2018 14:24:02;B;1;1;1;1 27/03/2018 14:38:26;B;1;1;1;1 27/03/2018 16:19:14;B;0;0;0;0 27/03/2018 18:00:02;A;0;0;0;
    – Carlos Eduardo Nogueira Gondim
    Commented Mar 28, 2018 at 17:59
  • I'm using sql server, not MySql.
    – Carlos Eduardo Nogueira Gondim
    Commented Mar 28, 2018 at 18:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.