如何确保没有数据的类别仍然在SQL输出中表示

Question

我在关系数据库的一系列表中有一些数据。在大多数情况下，数据在SQL Server数据库中，尽管我对MySQL数据库中的数据有相同的问题，因此解决方案应该是理想的，但适用于两者。数据由一系列分类变量(如月份、组等(和表示输出的数值组成。为了简单起见，可以将数据视为单个表，例如：

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;

添加数据：

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);

表格数据看起来像：

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

显然，查询只返回表中的数据。然而，我需要表示所有月份和所有组，即使数据库中没有明确保存这些组的数据。

我的解决方案是创建一个包含分类变量(在本例中为月份和组(所有组合的伪表，并将数据LEFT JOIN到伪表中。

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);

这个伪表看起来像：

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

然后，我可以将实际数据LEFT JOIN到伪表cateories(将NULL值替换为零(，以产生所需的输出：

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;

这会产生以下所需的输出：

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

在现实世界中，情况稍微复杂一些。有几个分类变量，每个变量都可以有100多个可能的选项(即使任何SELECT语句一次只需要2或3个类别的数据(。提前生成一个涵盖所有选项的伪变量会生成一个包含数百万行的表，用当前技术管理是不切实际的，尤其是在家庭宽带连接上。

我的问题是，与其从数据库中的表中下载伪数据，不如使用纯SQL(即没有额外的Python、PHP或其他编程语言(在内存中动态创建包含在伪表中的数据，仅用于类别变量中的少量所需值？无法创建只包含必要选项的精简表，因为并非所有查询用户都有权在数据库中创建表。

Answer 1

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 

该解决方案假定不存在必须填补的空白(例如，日期列表中(。

如果服务器版本允许，则dates和groups可以是CTE。

Answer 2

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

Answer 3

遗憾的是，在处理此类问题时，您并没有太多选择。至少通过一些递归cte，我们可以动态创建伪表，我可以提供一些简单的伪示例。

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

这将生成一系列grp和month，它们类似于op伪表，但使用int作为组。(可使用case轻松修改(

db<gt；小提琴

也有很多不同的方法可以实现相同的结果，但imo-cte确实很容易插入到需要这种伪数据的查询中。

我还想知道MySql是否有类似于postgresql中generate_series((的函数，因为在我的记忆中，这件事从未发生过。