MySQL: Group timeseries conditionally

Valentoni
February 5, 2024
134 views
0 votes
2 Answers

I’m trying to bring a time series sum grouped by date, week or month, depending on the total number of dates. For instance, if there is up to 30 days, group the sum by date, if there is between 31 and 90 days, group it by week and if there is more than 90 days, group it by month.

I already got the results I need with the following queries:

Grouping by date:

SELECT
    dtCreated,
    FROM_DAYS(TO_DAYS(dtCreated) -MOD(TO_DAYS(dtCreated) -1, 7)) as week_beginning,
    DATE(DATE_FORMAT(dtCreated, '%Y-%m-01')) AS month_beginning,
    COALESCE(SUM(valToSum), 0) as soma
    FROM(   
        SELECT 
            DATE(dt_created) as dtCreated,
            valToSum
        FROM MQV_PDV_TICKET
    ) as derived_table
GROUP BY dtCreated

Grouping by week:

SELECT
    dtCreated,
    FROM_DAYS(TO_DAYS(dtCreated) -MOD(TO_DAYS(dtCreated) -1, 7)) as week_beginning,
    DATE(DATE_FORMAT(dtCreated, '%Y-%m-01')) AS month_beginning,
    COALESCE(SUM(valToSum), 0) as soma
    FROM(   
        SELECT 
            DATE(dt_created) as dtCreated,
            valToSum
        FROM MQV_PDV_TICKET
    ) as derived_table
GROUP BY week_beginning

Grouping by month:

SELECT
    dtCreated,
    FROM_DAYS(TO_DAYS(dtCreated) -MOD(TO_DAYS(dtCreated) -1, 7)) as week_beginning,
    DATE(DATE_FORMAT(dtCreated, '%Y-%m-01')) AS month_beginning,
    COALESCE(SUM(valToSum), 0) as soma
    FROM(   
        SELECT 
            DATE(dt_created) as dtCreated,
            valToSum
        FROM MQV_PDV_TICKET
    ) as derived_table
GROUP BY month_beginning

All three options work just fine and bring me the right sum results with proper grouping, but I couldn’t make it group dinamically, to make it depends on the number of total days.

I already tried to use CASE WHEN within the GROUP BY clause, in this manner:

GROUP BY (CASE 
             WHEN COUNT(*) <= 30 THEN dtCreated
             WHEN COUNT(*) BETWEEN 31 AND 90 THEN week_beginning
             WHEN COUNT(*) > 90 THEN month_beginning
         END)

but then it errors me: #1111 - invalid use of group function (GROUP)

How could I achieve this conditional grouping?

Thx in advance!

Tags: conditional-statements mysql

Answers

I would use a WITH statement, to check in a separated query the number of records, then group it accordingly the proposed conditions.

WITH DateRange AS (
    SELECT
        MIN(DATE(dt_created)) AS start_date,
        MAX(DATE(dt_created)) AS end_date,
        COUNT(DISTINCT DATE(dt_created)) AS total_days
    FROM MQV_PDV_TICKET
),
PreparedData AS (
    SELECT
        DATE(dt_created) AS dtCreated,
        FROM_DAYS(TO_DAYS(dt_created) - MOD(TO_DAYS(dt_created) - 1, 7)) AS week_beginning,
        DATE_FORMAT(dt_created, '%Y-%m-01') AS month_beginning,
        valToSum
    FROM MQV_PDV_TICKET
)
SELECT
    CASE
        WHEN (SELECT total_days FROM DateRange) <= 30 THEN dtCreated
        WHEN (SELECT total_days FROM DateRange) BETWEEN 31 AND 90 THEN week_beginning
        ELSE month_beginning
    END AS grouping_period,
    COALESCE(SUM(valToSum), 0) AS total_sum
FROM PreparedData
GROUP BY grouping_period;

Here is one option to do it – using sample data as:

Create Table MQV_PDV_TICKET AS 
SELECT 
  @rownum := @rownum + 1 AS ID,
  DATE_ADD(DATE_ADD(CURDATE(), INTERVAL -30 DAY), INTERVAL @rownum DAY) AS DT_CREATED, 
  ABS(@rownum + (1.5 - @rownum)) * @rownum as VALUE_TO_SUM
FROM 
  (SELECT 0 AS dummy UNION ALL 
   SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
   SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
   SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL
   SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL
   SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL
   SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL
   SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL
   SELECT 28 UNION ALL SELECT 30 UNION ALL SELECT 31 UNION ALL SELECT 32 
  ) AS t
CROSS JOIN 
  (SELECT @rownum := 0) AS r;
  -- Records: 33

… SQL – inner query generates groupings and dates

SELECT x.GROUPED_BY,  
       Case When x.GROUPED_BY = 'DAY' Then x.DT_CREATED
            When x.GROUPED_BY = 'WEEK' Then x.WEEK_START
            When x.GROUPED_BY = 'MONTH' Then x.MONTH_START
       End as GROUP_DATE_START,
       Sum(x.VALUE_TO_SUM) as VALUE_SUMMED
    FROM( SELECT 
            ID,
            DATE(DT_CREATED) as DT_CREATED,
            VALUE_TO_SUM,
            Case When Count(ID) OVER() < 31 Then 'DAY'
                 When Count(ID) OVER() Between 31 And 90 Then 'WEEK'
                 When Count(ID) OVER() > 90 Then 'MONTH'
            End as GROUPED_BY, 
            FROM_DAYS(TO_DAYS(DT_CREATED) -MOD(TO_DAYS(DT_CREATED) -1, 7)) as WEEK_START,
            DATE(DATE_FORMAT(DT_CREATED, '%Y-%m-01')) AS MONTH_START
          FROM MQV_PDV_TICKET
       )  x
GROUP BY x.GROUPED_BY,  
         Case When x.GROUPED_BY = 'DAY' Then x.DT_CREATED
              When x.GROUPED_BY = 'WEEK' Then x.WEEK_START
              When x.GROUPED_BY = 'MONTH' Then x.MONTH_START
         End

/*    R e s u l t :
GROUPED_BY  GROUP_DATE_START    VALUE_SUMMED
----------  ----------------  --------------
WEEK        2024-01-07                  42
WEEK        2024-01-14                 115.5
WEEK        2024-01-21                 189
WEEK        2024-01-28                 262.5
WEEK        2024-02-04                 232.5    */

… see it here –> https://dbfiddle.uk/tD-8peva
NOTE: you should adjust it to your context – this has been coded for sample data above

Please signup or login to give your own answer.

Click here to cancel reply.