sql - 在 SQL 中组合类似的行并聚合一些值

Question

我有一些无意义数据的示例表，如下所示:

+--------+---------+---------+--------+---------+
| Vendor |   Day   |  Item   | Amount | Revenue |
+--------+---------+---------+--------+---------+
| Bob    | Monday  | Apple   |      1 |    1.00 |
| Bob    | Monday  | Orange  |      1 |    1.00 |
| Bob    | Monday  | Peach   |      2 |    2.00 |
| Bob    | Monday  | Lettuce |      3 |    3.00 |
| Bob    | Monday  | Spinach |      5 |    5.00 |
| Bob    | Monday  | Cabbage |      3 |    3.00 |
| Bob    | Tuesday | Apple   |      2 |    2.00 |
| Bob    | Tuesday | Orange  |      2 |    2.00 |
| Bob    | Tuesday | Peach   |      0 |    0.00 |
| Bob    | Tuesday | Lettuce |      3 |    3.00 |
| Bob    | Tuesday | Spinach |      5 |    5.00 |
| Bob    | Tuesday | Cabbage |      5 |    5.00 |
| Cindy  | Monday  | Apple   |      1 |    1.00 |
| Cindy  | Monday  | Orange  |      3 |    3.00 |
| Cindy  | Monday  | Peach   |      3 |    3.00 |
| Cindy  | Monday  | Lettuce |      6 |    6.00 |
| Cindy  | Monday  | Spinach |      8 |    8.00 |
| Cindy  | Monday  | Cabbage |      2 |    2.00 |
| Cindy  | Tuesday | Apple   |      1 |    1.00 |
| Cindy  | Tuesday | Orange  |      3 |    3.00 |
| Cindy  | Tuesday | Peach   |      0 |    0.00 |
| Cindy  | Tuesday | Lettuce |      2 |    2.00 |
| Cindy  | Tuesday | Spinach |      3 |    3.00 |
| Cindy  | Tuesday | Cabbage |      4 |    4.00 |
+--------+---------+---------+--------+---------+

我想将 Item 中的值合并到类似的类别中，并将信息压缩到更少的行中，因为不需要这么多细节。例如，我想把 Apple、Orange 和 Peach 变成“Fruit”，然后把 Lettuce、Spinach 和 Cabbage 变成“Vegetable”，所有这些都不会丢失存储在 中的值金额 和收入。

我要实现的表格应该如下所示:

+--------+---------+-----------+--------+---------+
| Vendor |   Day   |   Item    | Amount | Revenue |
+--------+---------+-----------+--------+---------+
| Bob    | Monday  | Fruit     |      4 |    4.00 |
| Bob    | Monday  | Vegetable |     11 |   11.00 |
| Bob    | Tuesday | Fruit     |      4 |    4.00 |
| Bob    | Tuesday | Vegetable |     13 |   13.00 |
| Cindy  | Monday  | Fruit     |      7 |    7.00 |
| Cindy  | Monday  | Vegetable |     16 |   16.00 |
| Cindy  | Tuesday | Fruit     |      4 |    4.00 |
| Cindy  | Tuesday | Vegetable |      9 |    9.00 |
+--------+---------+-----------+--------+---------+

这是我当前的 SQL 查询:

SELECT vendor, 
       day, 
       item_category, 
       Sum(amount), 
       Sum(revenue) 
FROM   (SELECT mytable.*, 
               CASE item 
                 WHEN 'Apple' THEN 'Fruit' 
                 WHEN 'Orange' THEN 'Fruit' 
                 WHEN 'Peach' THEN 'Fruit' 
                 WHEN 'Lettuce' THEN 'Vegetable' 
                 WHEN 'Spinach' THEN 'Vegetable' 
                 WHEN 'Cabbage' THEN 'Vegetable' 
               END AS item_category 
        FROM   mytable) AS x 
GROUP  BY vendor, 
          day, 
          item, 
          item_category;

此查询仍返回完整的行列表，而不是聚合类似的行。我将如何修改它以实现我想做的事情？

Answer 1

您需要通过语句从组中删除项目。以下是更多详细信息。

由于您正在抓取项目(苹果、橙子等)，在您从顶部选择和分组依据中删除该项目之前，它仍然不会显示它们仅按 item_category 分组。

Group by 按语句的每个唯一组合进行分组，因此必须删除项目。

见下文。注意:我将 item_category 重命名为 item，这样看起来就像您所说的那样输出

SELECT vendor, 
   day, 
   item_category as item, 
   Sum(amount), 
   Sum(revenue) 
FROM   (SELECT mytable.*, 
           CASE item 
             WHEN 'Apple' THEN 'Fruit' 
             WHEN 'Orange' THEN 'Fruit' 
             WHEN 'Peach' THEN 'Fruit' 
             WHEN 'Lettuce' THEN 'Vegetable' 
             WHEN 'Spinach' THEN 'Vegetable' 
             WHEN 'Cabbage' THEN 'Vegetable' 
           END AS item_category 
    FROM   mytable) AS x 
GROUP  BY vendor, 
      day, 
      item_category;