1

I'm using MySQL 8.0's date generation and am joining in data when/where available. This technique allows me to ensure this query returns zero values. This works perfectly when I'm pulling data for a single stat. Now I'm trying to adjust it to pull multiple stats at the same time so I can generate reports pretty easily.

Here's the current output:

[2019-01-24] 0
[2019-01-25] 0
[2019-01-26] 0
[2019-01-27] 62
[2019-01-28] 64,22,7
[2019-01-29] 65,21,7
[2019-01-30] 66,21

My objective would be to adjust this query so that any specific stat that doesn't have an entry gets filled with zeros so the ideal output would look like:

[2019-01-24] 0,0,0
[2019-01-25] 0,0,0
[2019-01-26] 0,0,0
[2019-01-27] 62,0,0
[2019-01-28] 64,0,7
[2019-01-29] 65,21,7
[2019-01-30] 66,21,0
        WITH RECURSIVE dates (date) AS 
        (
          SELECT :startingDate
          UNION ALL
          SELECT  date + INTERVAL 1 DAY FROM dates WHERE date <= DATE_SUB(:endingDate, INTERVAL 1 DAY)
        )
        SELECT
            COALESCE(daily_stats.date, dates.date) AS label,
            GROUP_CONCAT(COALESCE(daily_stats.value, 0) ORDER BY FIELD(stat, 132, 120, 111)) AS value
        FROM dates
        LEFT JOIN daily_stats ON stat IN(132, 120, 111) AND daily_stats.date = dates.date
        GROUP BY label;

I'm unsure how to accomplish this without doing a UNION on several select queries. Is there a more efficient approach?

My table:

CREATE TABLE `daily_stats` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `stat` int(11) NOT NULL,
  `value` varchar(255) COLLATE utf8mb4_unicode_ci 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 AUTO_INCREMENT=4412 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  • As daily_stats.value is a VARCHAR and not an integer type (like 0) perhaps GROUP_CONCAT(COALESCE(daily_stats.value, "0")) or GROUP_CONCAT(IFNULL(daily_stats.value, "0")) – danblack Feb 4 '19 at 3:43
2

Both date and stat may be absent. So you must generate not date array, but (date, stat) pairs array:

WITH RECURSIVE dates (date) AS 
(
  SELECT '2019-01-24'
  UNION ALL
  SELECT  date + INTERVAL 1 DAY FROM dates WHERE date <= DATE_SUB('2019-01-30', INTERVAL 1 DAY)
),
stats (stat) AS (
  SELECT DISTINCT stat 
  FROM daily_stats
  WHERE stat IN(132, 120, 111)
), 
dates_stats (date, stat) AS
(
  SELECT date, stat
  FROM dates, stats
)
SELECT
    dates_stats.date AS label,
    GROUP_CONCAT(COALESCE(daily_stats.value, 0) ORDER BY FIELD(dates_stats.stat, 132, 120, 111)) AS value
FROM dates_stats
LEFT JOIN daily_stats ON daily_stats.stat IN(132, 120, 111) 
                     AND daily_stats.date = dates_stats.date 
                     AND daily_stats.stat = dates_stats.stat
GROUP BY label;

fiddle

Or maybe ever

stats (stat) AS ( SELECT 132 stat
                  UNION ALL
                  SELECT 120
                  UNION ALL
                  SELECT 111
), 
1

Try this variation:

WITH RECURSIVE
dates (date) AS 
    (
      SELECT CAST('2019-01-24' AS date)
      UNION ALL
      SELECT  date + INTERVAL 1 DAY
      FROM dates
      WHERE date <= DATE_SUB('2019-01-30', INTERVAL 1 DAY)
    ),
s (ord, stat) AS
    ( SELECT 1, 132 UNION ALL
      SELECT 2, 120 UNION ALL
      SELECT 3, 111
    )
SELECT
    dates.date AS label,
    GROUP_CONCAT(COALESCE(ds.value, '0') ORDER BY s.ord) AS value
FROM dates
CROSS JOIN s
LEFT JOIN daily_stats AS ds
  ON  ds.stat = s.stat
  AND ds.date = dates.date
GROUP BY dates.date ;

Test at dbfiddle.uk

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.