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)
dateis the day the data was aggregated
valueis 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