如何在CnosDB中构建数据透视表？GROUP BY CUBE使用异常求助

我希望在单个结果集中获取更多聚合数据，尝试使用GROUP BY CUBE函数，但结果未达预期。

数据集 #

CREATE TABLE tbl(f_1 DOUBLE, TAGS(room, device));

INSERT INTO tbl(time, room, device, f_1) VALUES
('2023-12-01T00:00:00', 'A', 'mt', 57),
('2023-12-01T00:00:00', 'A', 'f', 70),
('2023-12-01T00:00:00', 'B', 'mt', 58),
('2023-12-01T00:00:00', 'B', 'f', 71),
('2023-12-01T00:01:00', 'A', 'mt', 57),
('2023-12-01T00:01:00', 'A', 'f', 70),
('2023-12-01T00:01:00', 'B', 'mt', 71);

SELECT * FROM tbl ORDER BY time, room, device;

查询结果：

+---------------------+------+--------+------+
| time                | room | device | f_1  |
+---------------------+------+--------+------+
| 2023-12-01T00:00:00 | A    | f      | 70.0 |
| 2023-12-01T00:00:00 | A    | mt     | 57.0 |
| 2023-12-01T00:00:00 | B    | f      | 71.0 |
| 2023-12-01T00:00:00 | B    | mt     | 58.0 |
| 2023-12-01T00:01:00 | A    | f      | 70.0 |
| 2023-12-01T00:01:00 | A    | mt     | 57.0 |
| 2023-12-01T00:01:00 | B    | mt     | 71.0 |
+---------------------+------+--------+------+

执行的SQL及异常结果 #

SELECT COALESCE(room, 'all_room') AS t#room, COALESCE(device, 'all_device') as t#device, COUNT(*) as t#count FROM tbl GROUP BY CUBE(room, device) ORDER BY room, device;

返回结果：

+----------+------------+---------+
| t#room   | t#device   | t#count |
+----------+------------+---------+
| A        | f          | 1       |
| A        | mt         | 1       |
| A        | all_device | 2       |
| B        | f          | 1       |
| B        | mt         | 1       |
| B        | all_device | 1       |
| B        | all_device | 1       |
| all_room | f          | 1       |
| all_room | f          | 1       |
| all_room | mt         | 1       |
| all_room | mt         | 1       |
| all_room | all_device | 2       |
| all_room | all_device | 1       |
| all_room | all_device | 1       |
+----------+------------+---------+

期望结果 #

+----------+------------+---------+
| t#room   | t#device   | t#count |
+----------+------------+---------+
| A        | f          | 2       |
| A        | mt         | 2       |
| A        | all_device | 4       |
| B        | f          | 1       |
| B        | mt         | 2       |
| B        | all_device | 3       |
| all_room | f          | 3       |
| all_room | mt         | 4       |
| all_room | all_device | 7       |
+----------+------------+---------+

内容的提问来源于stack exchange，提问作者Darts