How do I ensure that categories with no data are still represented in SQL outputs

user_1718097 0
Viewed 76

I have some data in a series of tables in a relational database. For the most part, the data is in a SQL Server database although I have an identical problem with data in a MySQL database and so solution should ideally but applicable to both. The data consists of a series of categorical variables (e.g. month, group, etc.) and a numerical value representing an output. For simplicity, the data can be considered as a single table such as:

DROP TABLE IF EXISTS `tableData`;

CREATE TABLE `tableData` (
  `id` int(3) unsigned NOT NULL AUTO_INCREMENT,
  `myDate` date NOT NULL,
  `myGroup` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
  `myValue` int(3) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Add data:

insert into tableData (myDate,myGroup,myValue) VALUES   ('2020-01-01','A',34),
                                                        ('2020-02-01','A',19),
                                                        ('2020-03-01','A',65),
                                                        ('2020-04-01','B',38),
                                                        ('2020-05-01','A',37),
                                                        ('2020-06-01','A',23),
                                                        ('2020-07-01','B',82),
                                                        ('2020-09-01','A',83),
                                                        ('2020-10-01','A',16),
                                                        ('2020-10-01','B',32),
                                                        ('2020-11-01','A',84),
                                                        ('2020-12-01','A',21);

The table data looks like:

SELECT      myGroup AS 'group',
            MONTH(myDate) AS 'month',
            myValue AS 'value'
FROM
            tableData
ORDER BY
            myGroup,MONTH(myDate);


group month value
A         1    34
A         2    19
A         3    65
A         5    37
A         6    23
A         9    83
A        10    16
A        11    84
A        12    21
B         4    38
B         7    82
B        10    32

Clearly the query only returns the data in the table. However, I need to have all months and all groups represented even if there is no data for those groups explicitly held in the database.

My solution was to create a dummy table containing all combinations of categorical variables (in this case month and group) and LEFT JOIN the data to the dummy table.

DROP TABLE IF EXISTS `tableData`;

CREATE TABLE `tableDummy` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `myGroup` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
  `myMonth` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO tableDummy (myGroup,myMonth) VALUES ('A',1),('A',2),('A',3),('A',4),('A',5),('A',6),
                                                ('A',7),('A',8),('A',9),('A',10),('A',11),('A',12),
                                                ('B',1),('B',2),('B',3),('B',4),('B',5),('B',6),
                                                ('B',7),('B',8),('B',9),('B',10),('B',11),('B',12);

This dummy table looks like:

SELECT      myMonth,
            myGroup
FROM
            tableDummy
ORDER BY
            myMonth,
            myGroup;

myMonth myGroup
      1 A
      1 B
      2 A
      2 B
      3 A
      3 B
      4 A
      4 B
      5 A
      5 B
      6 A
      6 B
      7 A
      7 B
      8 A
      8 B
      9 A
      9 B
     10 A
     10 B
     11 A
     11 B
     12 A
     12 B

I can then LEFT JOIN the actual data to the dummy table catetories (replacing NULL values with zero) to produce the desired output:

SELECT      dumTbl.myMonth AS 'month',
            dumTbl.myGroup AS 'group',
            IFNULL(datTbl.value,0) AS 'value'  -- may be ISNULL() in SQL Server
FROM

   (SELECT  myMonth,
            myGroup
    FROM
            tableDummy
    ORDER BY
            myMonth,
            myGroup) dumTbl
        
LEFT JOIN

   (SELECT  myGroup as 'group',
            MONTH(myDate) as 'month',
            myValue as 'value'
    FROM
            tableData
    ORDER BY
            myGroup,MONTH(myDate) ) datTbl
            
ON dumTbl.myGroup = datTbl.group AND
   dumTbl.myMonth = datTbl.month

ORDER BY
            dumTbl.myMonth,
            dumTbl.myGroup;

This produces the following desired output:

month   group   value
    1   A          34
    1   B           0
    2   A          19
    2   B           0
    3   A          65
    3   B           0
    4   A           0
    4   B          38
    5   A          37
    5   B           0
    6   A          23
    6   B           0
    7   A           0
    7   B          82
    8   A           0
    8   B           0
    9   A          83
    9   B           0
   10   A          16
   10   B          32
   11   A          84
   11   B           0
   12   A          21
   12   B           0

In the real-world, the situation is a little more complicated. There are several categorical variables, each of which could have 100+ possible options (even though any SELECT statement will only require data from 2 or 3 categories at a time). Generating a dummy variable in advance that covers all options produces a table with multiple millions of rows and is impractical to manage with current tech, especially over a home broadband connection.

My question is, rather than download the dummy data from a table in the database, can the data contained in the dummy table be created in memory on-the-fly for only the small number of required values in the categorical variables using pure SQL (i.e. no additional Python, PHP or other programming language)? It is not possible to create a reduced table containing only the necessary options because not all users of the query will have permission to create tables in the database.

left-join sql-server mysql sql
3 Answers
SELECT dates.myDate, 
       groups.myGroup,
       COALESCE(myValue, 0) myValue 
FROM (SELECT DISTINCT myDate FROM tableData) dates
CROSS JOIN (SELECT DISTINCT myGroup FROM tableData) groups
LEFT JOIN tableData ON tableData.myDate = dates.myDate 
                   AND tableData.myGroup = groups.myGroup

The solution assumes that there is no gaps (for example, in dates list) which must be filled.

If server version allows then dates and groups may be CTEs.

user_101387340

Sadly you don't really have too many options when approaching this kind of problem. At least with some recursive cte we do can create dummy table dynamically which I can provide some simple pseudo example.

with recursive cte as(
select 1 as id ,1 as month, 1 as grp
  union all
  select id+1 as id,
  case when (month+1) % 12 = 0 then 12 else (month+1) % 12 end as month,
  case when (month+1) % 12 = 1 then grp + 1 else grp end as grp
  from cte
  where id<100 -- how many rows you need
)
select grp,month from cte

this will generate a series of grp and month which similar to op dummy table but with int as groups.( can easily modify with case )

db<>fiddle

there is also a lot of different way to achieve same result but imo cte is really easy to insert into the query which need this kind of dummy data.

also I'll like to know does MySql have any function similar to generate_series() from postgresql, cause in my memory this thing never happen.

user_144751840 
select g, mm, td.myValue 
from 
(select g, mm from 
    (select 1 mm union select 2 union select 3 
     union select 4 union select 5 union select 6 
     union select 7 union select 8 union select 9 
     union select 10 union select 11 union select 12 ) as Months, 
    (select distinct myGroup g from tableData) as Groups) as Mixer
left join tableData td on Month(td.myDate) = mm and td.myGroup = g
order by g,mm
user_59411770
Related