Elasticsearch实现过滤-聚合-聚合过滤-排序的查询语法问询

索引映射示例 #

假设你的索引team_members映射结构如下：

{
  "mappings": {
    "properties": {
      "member_id": {"type": "keyword"},
      "member_experience_years": {"type": "integer"}, // 成员经验年限
      "member_goals": {"type": "integer"}, // 成员进球数
      "team": {
        "properties": {
          "team_id": {"type": "keyword"},
          "team_name": {"type": "keyword"},
          "team_wins": {"type": "integer"} // 团队胜场数
        }
      }
    }
  }
}

示例数据 #

[
  {"member_id": "m1", "member_experience_years": 5, "member_goals": 20, "team": {"team_id": "t1", "team_name": "闪电队", "team_wins": 15}},
  {"member_id": "m2", "member_experience_years": 3, "member_goals": 18, "team": {"team_id": "t1", "team_name": "闪电队", "team_wins": 15}},
  {"member_id": "m3", "member_experience_years": 2, "member_goals": 12, "team": {"team_id": "t2", "team_name": "风暴队", "team_wins": 12}},
  {"member_id": "m4", "member_experience_years": 6, "member_goals": 22, "team": {"team_id": "t3", "team_name": "巨浪队", "team_wins": 18}}
]

动态生成的查询DSL #

以下是完全匹配需求的Elasticsearch查询语句，可根据用户输入的动态参数替换对应条件：

{
  "size": 0, // 无需返回原始文档，只取聚合结果
  "query": {
    "bool": {
      "filter": [
        // 步骤1：按用户条件过滤成员（等价SQL WHERE）
        {"range": {"member_experience_years": {"gte": 3}}} // 示例：经验年限≥3年
      ]
    }
  },
  "aggs": {
    // 步骤2：按团队维度分组（等价SQL GROUP BY team_id）
    "group_by_team": {
      "terms": {
        "field": "team.team_id",
        // 步骤4：按团队胜场数降序排序（等价SQL ORDER BY team_wins DESC）
        "order": [{"team_wins_avg": "desc"}] // 同团队成员的team_wins一致，avg结果即为团队真实胜场数
      },
      "aggs": {
        // 聚合团队下过滤后成员的平均进球数（等价SQL AVG(member_goals)）
        "avg_goals": {
          "avg": {"field": "member_goals"}
        },
        // 聚合团队胜场数（用于排序）
        "team_wins_avg": {
          "avg": {"field": "team.team_wins"}
        },
        // 步骤3：过滤聚合结果（等价SQL HAVING AVG(member_goals) ≥17）
        "filter_valid_teams": {
          "bucket_selector": {
            "buckets_path": {
              "avgGoals": "avg_goals"
            },
            "script": "params.avgGoals >= 17"
          }
        }
      }
    }
  }
}

各部分对应SQL逻辑的解释 #

• 步骤1（WHERE）：query.bool.filter中的条件负责过滤符合要求的成员，可根据用户输入动态替换为match、term等其他查询类型。
• 步骤2（GROUP BY）：group_by_team这个terms聚合按团队ID分组，将过滤后的成员聚合到所属团队维度。
• 步骤3（HAVING）：bucket_selector子聚合负责筛选出平均进球数达标团队，只有满足脚本条件的团队桶会被保留。
• 步骤4（ORDER BY）：terms聚合的order参数指定按团队胜场数降序排列，若需按其他团队属性排序，替换对应的聚合字段即可。

动态生成注意事项 #

• 成员过滤条件：可根据用户输入动态拼接query.bool.filter内的子条件。
• 聚合字段：如需聚合其他成员属性，添加对应的sum/max等聚合即可。
• 过滤阈值：bucket_selector里的脚本阈值可动态替换为用户输入的值。

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