I have this query.
CREATE TABLE ip_logs ( `ip_address` VARCHAR(11), `start_date` VARCHAR(11), `end_date` VARCHAR(11), `time_stamp`, VARCHAR(20), `mappings`, INTEGER, `loc_id` INTEGER ); INSERT INTO ip_logs (`ip_address`,`start_date`,`end_date`,`time_stamp`,`mappings`, `loc_id`) VALUES ('120.2.53.21','2020-01-03','2020-01-09','2020-01-03 14:33','2', '5'), ('198.3.222.2','2020-01-03','2020-01-14','2020-01-03 7:53', '7','4'), ('108.4.213.3','2020-01-04','2020-01-07','2020-01-04 12:13','3', '4'), ('128.5.173.4','2020-01-07','2020-02-15','2020-01-07 8:29', '12','3'), ('110.6.432.5','2020-01-07','2020-03-01','2020-01-07 11:45','4', '2'), ('198.7.222.6','2020-01-10','2020-01-14', '2020-01-10 17:32','8', '1'), ('118.8.113.7','2020-01-10','2020-01-19','2020-01-10 20:52','10', '4'), ('106.1.212.9','2020-02-24','2020-03-30','2020-02-24 10:08','5', '1');
I want to generate the 7 day average of the mappings column. For example, I want to calculate the average mappings for each day (from 2020-01-03 – 2020-01-10).
Results:
time_stamp | avg_mapping 2020-01-03 | 4.5 (2 + 7) /2 2020-01-04 | 3 2020-01-07 | 8 (12 +4)/2 2020-01-10 | 9
Then return avg(avg_mapping)
Advertisement
Answer
I don’t see how your question relates to a rolling average. From your data and results, it seems like you just want aggregation and avg()
:
select date(time_stamp) as time_stamp_day, avg(mapping) as avg_mapping from ip_logs group by date(time_stamp)
If you want the average of column avg_mapping
in the resultset (which is not the same thing as an overall average of mapping
), then you can add another level of aggregation:
select avg(avg_mapping) as avg_avg_mapping from ( select avg(mapping) as avg_mapping from ip_logs group by date(time_stamp) ) l