如何在MongoDB或SQL中用正则创建分组并计算平均蛋白质含量？

没问题！我来帮你分别实现MongoDB和SQL版本的需求——先从Product字段用正则提取分类标签，再基于分类计算每组的平均蛋白质含量。

MongoDB 实现 #

1. 提取Category字段（匹配你的期望结果） #

利用MongoDB聚合管道的$regexFind操作符，我们可以从嵌套的my_id.Product中提取匹配正则(pork|chicken|tofu)的内容，同时处理大小写差异（比如示例里的Pork、Chicken），匹配不到的内容设为null（对应你要的NA）。

完整的聚合管道如下：

db.test.aggregate([
  {
    $addFields: {
      "my_id.Category": {
        $cond: {
          if: { $regexFind: { input: "$my_id.Product", regex: /(pork|chicken|tofu)/i } },
          then: { $getField: { field: "str", input: { $regexFind: { input: "$my_id.Product", regex: /(pork|chicken|tofu)/i } } } },
          else: null
        }
      }
    }
  },
  // 展开嵌套结构，让输出和你的期望格式完全一致
  {
    $replaceRoot: {
      newRoot: {
        Category: "$my_id.Category",
        Product: "$my_id.Product",
        Protein: "$my_id.Protein"
      }
    }
  }
])

2. 基于Category计算平均蛋白质含量 #

在提取分类的基础上，添加$group阶段即可完成聚合统计，为了让结果更清晰，我们可以把null替换为明确的Uncategorized：

db.test.aggregate([
  {
    $addFields: {
      "my_id.Category": {
        $cond: {
          if: { $regexFind: { input: "$my_id.Product", regex: /(pork|chicken|tofu)/i } },
          then: { $getField: { field: "str", input: { $regexFind: { input: "$my_id.Product", regex: /(pork|chicken|tofu)/i } } } },
          else: "Uncategorized"
        }
      }
    }
  },
  {
    $group: {
      _id: "$my_id.Category",
      averageProtein: { $avg: "$my_id.Protein" },
      productCount: { $sum: 1 } // 可选：统计每组的产品数量
    }
  },
  {
    $project: {
      Category: "$_id",
      averageProtein: { $round: ["$averageProtein", 2] }, // 保留两位小数
      productCount: 1,
      _id: 0
    }
  }
])

SQL 实现 #

不同SQL方言的正则函数略有差异，下面以常用的三种方言为例：

1. 提取Category字段 #

MySQL

使用REGEXP_SUBSTR函数，支持忽略大小写匹配，无匹配结果时返回null：

SELECT
  REGEXP_SUBSTR(Product, '(pork|chicken|tofu)', 1, 1, 'i') AS Category,
  Product,
  Protein
FROM test;

（注：SQL中一般不会用嵌套字段存储，所以表结构需将Product和Protein设为直接字段）

PostgreSQL

使用SUBSTRING结合正则匹配，通过(?i)标记忽略大小写：

SELECT
  SUBSTRING(Product FROM '(?i)pork|chicken|tofu') AS Category,
  Product,
  Protein
FROM test;

SQL Server（2017+）

支持REGEXP_SUBSTR函数，写法类似MySQL：

SELECT
  REGEXP_SUBSTR(Product, 'pork|chicken|tofu', 1, 1, 'i') AS Category,
  Product,
  Protein
FROM test;

2. 基于Category计算平均蛋白质含量 #

以MySQL为例，用COALESCE将null替换为Uncategorized，再分组聚合：

SELECT
  COALESCE(REGEXP_SUBSTR(Product, '(pork|chicken|tofu)', 1, 1, 'i'), 'Uncategorized') AS Category,
  ROUND(AVG(Protein), 2) AS averageProtein,
  COUNT(*) AS productCount
FROM test
GROUP BY Category;

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