现在，我在配置单元中使用下面的查询将一行拆分为多行，但我还希望根据“category”列对“product”列进行分组，每组将按组的顺序进行匹配，并具有“；”对每组进行分类，并在组中单独列出项目。

SELECT id, customer, prodcut_split  
FROM orders lateral view explode(split(product,';')) products AS prodcut_split

这是我现在的数据

| id |  Customer| Category                  | Product                           |
+----+----------+---------------------------+-----------------------------------+
| 1  |  John    | Furniture; Technology     | Bookcases, Chairs; Phones, Laptop |
| 2  |  Bob     | Office supplies; Furniture| Paper, Blinders; Tables           |
| 3  |  Dylan   | Furniture                 | Tables, Chairs, Bookcases         |

我想要的结果如下：

| id |  Customer| Category       | Product   |
+----+----------+----------------+-----------+
| 1  |  John    | Furniture      | Bookcases |
| 1  |  John    | Furniture      | Chairs    |
| 1  |  John    | Technology     | Phones    |
| 1  |  John    | Technology     | Laptop    |
| 2  |  Bob     | Office supplies| Paper     |
| 2  |  Bob     | Office supplies| Blinders  |
| 2  |  Bob     | Furniture      | Tables    |
| 3  |  Dylan   | Furniture      | Tables    |
| 3  |  Dylan   | Furniture      | Chairs    |
| 3  |  Dylan   | Furniture      | Bookcases |