0

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 aggregated
  • value 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
  • The best option is by using a Calendar table. mssqltips.com/sqlservertip/4054/… – McNets Jan 19 at 15:58
  • Ah, thanks for adding that mysql tag. The calendar table is an interesting concept, but how would using that approach help me generate the dates I'm looking for? – Webnet Jan 19 at 17:22
  • I've used SQL Server for the example: dbfiddle.uk/… – McNets Jan 19 at 17:28
  • I hear ya, but that doesn't help answer the question. This question is about using SQL to generate the correct date range, then I can join in rows where dates match. – Webnet Jan 19 at 17:52
  • I just showed you how to: 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... – McNets Jan 19 at 17:55
1
+50

I don't have enough reputation to comment, however I think this is what you're looking for. Check out "Date Series Generation" in this document. https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive-date-series

Common Table Expressions, which enable Date Series Generation, are available as of MySQL 8.0, or Mariadb 10.2.2. If you're on an earlier version and can't upgrade, I think @McNets answer is the way to go.

1

You can use a Calendar table for this and other purposes. A calendar table is always useful.

I've set up a minimal example, but usually a Calendar table has more detailed fields like, hours, minutes, ect.

CREATE TABLE Calendar (
    cDate datetime, 
    cDay int, 
    cDayOfWeek int, 
    cDayName varchar(20)
);

CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)
BEGIN
  WHILE dateStart <= dateEnd DO
    INSERT INTO Calendar (cDate, cDayOfWeek, cDayName) 
    VALUES (dateStart, DAYOFWEEK(dateStart), DAYNAME(dateStart));
    SET dateStart = date_add(dateStart, INTERVAL 1 DAY);
  END WHILE;
END;

CALL filldates('2018-01-01','2018-12-31');

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?

You can use the calendar table to build this empty table in this way:

INSERT INTO daily_stats (`date`, `stat`, `value`, `created_at`)
SELECT
    cDate, 0, 0, now()
FROM 
    Calendar 
WHERE 
    cDayOfWeek = 7;

NOTE: cDayOfWeek could be different depending on first day of week in your system.

SELECT * FROM daily_stats LIMIT 10;
SELECT * FROM daily_stats LIMIT 10;
id | date       | stat | value | created_at          | updated_at
-: | :--------- | ---: | ----: | :------------------ | :---------
 1 | 2018-01-06 |    0 |     0 | 2019-01-19 18:16:49 | null      
 2 | 2018-01-13 |    0 |     0 | 2019-01-19 18:16:49 | null      
 3 | 2018-01-20 |    0 |     0 | 2019-01-19 18:16:49 | null      
 4 | 2018-01-27 |    0 |     0 | 2019-01-19 18:16:49 | null      
 5 | 2018-02-03 |    0 |     0 | 2019-01-19 18:16:49 | null      
 6 | 2018-02-10 |    0 |     0 | 2019-01-19 18:16:49 | null      
 7 | 2018-02-17 |    0 |     0 | 2019-01-19 18:16:49 | null      
 8 | 2018-02-24 |    0 |     0 | 2019-01-19 18:16:49 | null      
 9 | 2018-03-03 |    0 |     0 | 2019-01-19 18:16:49 | null      
10 | 2018-03-10 |    0 |     0 | 2019-01-19 18:16:49 | null      

db<>fiddle here

  • This is good to know, thanks. This doesn't really answer the question though because this is essentially the same data store I have today except the date column is broken out into other columns and suggests entering zeros for data missing. If I'm missing data for a day, it means the cron didn't run or failed in some way so filling the table with empty values would need another cron in place. – Webnet Jan 19 at 20:55
  • For my specific needs, a solution that does this at runtime without a stored procedure would be ideal. – Webnet Jan 19 at 21:00
  • You only need to generate the calendar table once. You can generate a calendar table for the next 50 years – McNets Jan 19 at 21:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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