I have a table where I'm currently aggregating ~200 rows per day of stats. These stats include things like # of users, how many people using X feature, etc.
What I'd like to do is query this table so summarize the data in this way:
- Daily over the past X days
- Weekly over the past year - Take the stats from each Sunday and pulling the current day (assuming today isn't Sunday)
- Monthly over the past X years - Take the stats from the last day of each month and pulling the current day (assuming today isn't the last day of the month)
Columns:
date
is the day the data was aggregatedvalue
is the numeric value I need to show (e.g. - # of users)
I want empty records/dates to be filled with zeros so I need to use mysql to generate the dates and then join a matching record where available. How can I use mysql to determine the Sundays for the last year?
Unfortunately I can't provide an example of what I have working at the moment, I'm still investigating potential solutions. Most queries I come across are querying records by dates, not generating the dates themselves.
My table structure:
CREATE TABLE `daily_stats` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`date` date NOT NULL,
`stat` int(11) NOT NULL,
`value` bigint(20) unsigned NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `daily_stats_date_stat_unique` (`date`,`stat`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
mysql
tag. The calendar table is an interesting concept, but how would using that approach help me generate the dates I'm looking for?I want empty records/dates to be filled with zeros so I need to use mysql to generate the dates and then join a matching record where available. How can I use mysql to determine the Sundays for the last year?
But I can try to use MySql...