CODEWITHSUNDEEP
mysql
Ray S.
Ray S.

Reputation: 1200

Time Frequency of Inserts

I have data being inserted into a mysql table of the form

id     companyid     date

how would I find the time average frequency of inserts by companyid.

Some companies send data daily, some weekly, some every 10 days, etc.

would like a result of the form

companyid     average frequency of inserts
2       every 5 days
3       every 10 days
4       every 2 days

Upvotes: 1

Views: 58

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

One definition of average would be the difference between the maximum and minimum values divided by one less than the count. Something like this might be what you are looking for:

select companyid,
       (case when max(date) <> min(date())
             then datediff(max(date), min(date)) / (count(*) - 1)
        end) as average_frequency
from table t
group by companyid;

Upvotes: 2

Related Questions