时间戳

首页 > 解决方案 > 在 SQL 中组合类似的行并聚合一些值

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

问题描述

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

+--------+---------+---------+--------+---------+
| 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 转化为“水果”，将生菜、菠菜和卷心菜转化为“蔬菜”，所有这些都不会丢失存储在Amountand中的值Revenue。

我试图实现的表应该如下所示：

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

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

标签： sqlpostgresqlaggregate-functions

解决方案

您需要通过语句从您的组中删除项目。下面是更多细节。

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

通过按语句的每个唯一组合进行分组来分组工作，因此必须删除项目。

见下文。注意：我将 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;

推荐阅读