MySQL 8.0 – Fill Missing Data Points in GROUP_CONCAT

group-concatenationMySQLmysql-8.0

I need to generate data to build some charts.
The current results have missing data points and I'd like them to fill them with 0's.

Data is stored in MySQL 8. Simplified data sample & query fiddle here.

The query I currently have is

SELECT
    currency,
    GROUP_CONCAT(volume) AS volume
FROM (
    SELECT
        DATE(t.created_at) AS created_at,
        t.currency AS currency,
        SUM(t.amount) AS volume
    FROM
        transactions AS t
    WHERE (t.created_at BETWEEN @start AND @end)
    GROUP BY
        created_at,
        currency
    ORDER BY
        created_at,
        currency) r
GROUP BY
    currency

which creates this result set:

currency	volume
AUD	27553.52,13395.20,18349.51,3773.29,…
BRL	272.45,…
CAD	14738.08,7372.58,5926.08,7877.14,…
CHF	320.00,27.00,47.00,27.00,…
EUR	888.62,2806.27,4445.30,805.93,…
GBP	48588.64,37266.79,27275.01,13981.08,…
MXN	10.00,16298.00,1900.00,…
SEK	497.00,497.00,1491.00,…
USD	374660.85,347793.84,523608.81,839710.22,…

Where I need help:

How can I fill the mising data points with 0?
Let's assume the worst and at some point there are no transactions for any of the currencies for a day (or multiple days). How can I fill those missing data points?

I've read quite a few posts about WITH RECURSIVE and calendar tables but I can't wrap my head around it.

I'd appreciate any help/pointers. Thank you!

Update 1

@Akina's answer basically does what I asked for (thank you!) but: the query takes ages to complete.

The transactions table currently holds ~4m rows of data. A monthly resultset averages to roughly ~270k rows. Amogst others, there are indexes on currency, created_at and a compound index for created_at,currency.

Update 2

Something is off with my indexes. If I
LEFT JOIN transactions AS t FORCE INDEX(created_at) ...
then the query completes in ~15s, regardless if I set the date range to a month or 6 months.

Best Answer

WITH RECURSIVE  -- calendar generation needs in recuirsion

-- generate calendar, DATE() performs parameter checking additionally

calendar AS ( SELECT DATE(@start) created_at
              UNION ALL
              SELECT created_at + INTERVAL 1 DAY
              FROM calendar
              WHERE created_at < DATE(@end) ),

-- collect currencies list
              
currencies AS ( SELECT DISTINCT currency
                FROM transactions ),

-- gather daily data for all dates and all currencies
-- replace NULLs for the dates where there is no data with zeroz

daily AS ( SELECT ca.created_at AS created_at,  
                  cu.currency AS currency,  
                  COALESCE(SUM(t.amount), 0) AS volume
           FROM calendar ca
           CROSS JOIN currencies cu
           LEFT JOIN transactions AS t ON ca.created_at = DATE(t.created_at)
                                      AND cu.currency = t.currency
           GROUP BY ca.created_at, cu.currency )

-- get final data, aggregated data is sorted

SELECT currency, 
       GROUP_CONCAT(volume ORDER BY created_at) AS volume
FROM daily
GROUP BY currency;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=96b9434f665ea819c1fc2be225c403d4

Related Solutions

Mysql – Join two group_concat result

You can reverse the order of the two functions, so the concatenation of group_name and id is done first - using either CONCAT() or CONCAT_WS() - and then the group concatenation:

GROUP_CONCAT( CONCAT_WS(':', rtg.group_name, rtg.id) 
              ORDER BY rtg.id
              SEPARATOR ','                      -- the comma ',' is the default
            ) AS groupList

MySQL GROUP_CONCAT – Fix Duplicate Data Issue When DISTINCT Can’t Be Used

You have identified the source of the problem: that recipe is joined to two tables, recipe_detail and recipe_tagmap (and these to several other tables related to respectively "ingredients" and "tags"), and recipe is having one-to-many relationships with both of them.

One solution is to individually GROUP BY and aggregate first (one aggregation for the list of the tables related to ingredients and another for the group of tables related to tags, and then join back (again) to the main table (recipe):

SELECT recipe.*, 
       iid,  
       iname, 
       mabbr, 
       tag
FROM  recipe

  LEFT JOIN 
    ( SELECT recipe_detail.recipe_id,
             GROUP_CONCAT(recipe_detail.ingredient_id) AS iid,  
             GROUP_CONCAT(ingredient.name) AS iname, 
             GROUP_CONCAT(ingredient_mfr.abbr) AS mabbr
      FROM recipe
        JOIN recipe_detail
          ON recipe.id = recipe_detail.recipe_id
        LEFT JOIN ingredient
          ON recipe_detail.ingredient_id = ingredient.id
        LEFT JOIN ingredient_mfr
          ON ingredient.mfr_id = ingredient_mfr.id
      WHERE recipe.user_id = 1
      GROUP BY recipe_detail.recipe_id
    ) AS details
        ON recipe.id = details.recipe_id

  LEFT JOIN
    ( SELECT recipe_tagmap.recipe_id,
             GROUP_CONCAT(recipe_tag.name) AS tag 
      FROM recipe
        JOIN recipe_tagmap
          ON recipe.id = recipe_tagmap.recipe_id
        LEFT JOIN recipe_tag
         ON recipe_tagmap.tag_id = recipe_tag.id
      WHERE recipe.user_id = 1
      GROUP BY recipe_tagmap.recipe_id
    ) AS tags
      ON recipe.id = tags.recipe_id

WHERE recipe.user_id = 1 ;

Tested at: SQL-Fiddle

(Using the recipe table inside the 2 aggregations is not strictly needed but since you only want the recipes of one user, it will help for efficiency, restricting the number of rows retrieved from several tables and aggregated.)