es中没法使用子查询，所以很多复杂操作都没办法实现。因一个需求，想到的另类思路。欢迎提出建议和其他好的解决方法。

1、需求背景

需求：从两个时间区间中，根据号码和颜色进行聚合，sum（pass_count），并将两个聚合结果进行join，取交集。

2、mysql的写法

select t1.no,t1.color,t1.cc1,t2.cc2 from 
(select 
	no,color,sum(pass_count) cc1 
	from tt1 
	where pass_time > '2022-04-01' 
	and pass_time < '2022-04-10' 
	group by no,color) t1 
left join 
(select 
	no,color,sum(pass_count) cc2 
	from tt1 where pass_time > '2022-04-15' 
	and pass_time < '2022-04-30' 
	group by no,color) t2 
	on t1.no = t2.no and t1.color = t2.color

3、资料查询

3.1、思路1

先根据条件查出一个聚合结果，再根据结果进行第二个条件的查询与聚合。

结果：很显然，根据一个条件查询并聚合操作很简单。不管是用filter聚合，还是先query，再agg都可以实现。但是，在聚合的结果上再进行聚合就难了。

官网上提到的管道聚合。只能对已经聚合的结果(我用的是桶聚合)，根据已有的标量，进行结果操作。比如说，取聚合后的最大，最小…或者having。

这个只能pass

3.2、思路2

使用filter聚合，用两个过滤桶，然后在各种的过滤桶中，进行各种的条件聚合。然后后端再根据返回的两个桶，进行过滤去交集操作

3.2.1、DSL写法

{
  "size": 0,
  "query": {
    "bool": {
      "must": [
        {
          "terms": {
            "device_Id": [
              "32050501041321000000",
              "32050500011120000000"
            ]
          }
        }
      ]
    }
  },
  "aggs": {
    "before_filter": {
      "filter": {
        "range": {
          "pass_time": {
            "gte": "2022-04-06 00:00:00",
            "lte": "2022-04-10 23:00:00"
          }
        }
      },
      "aggs": {
        "group_by_plate_no_color": {
          "terms": {
            "script": {
              "source": "doc['plate_no'].value+','+doc['plate_color'].value",
              "lang": "painless"
            },
            "order": {
              "pass_count_total": "desc"
            },
            "size": 10000
          },
          "aggs": {
            "pass_count_total": {
              "sum": {
                "field": "pass_count"
              }
            },
            "having": {
              "bucket_selector": {
                "buckets_path": {
                  "cnt": "pass_count_total"
                },
                "script": {
                  "source": "params.cnt >= params.before_cnt",
                  "lang": "painless",
                  "params": {
                    "before_cnt": 10
                  }
                },
                "gap_policy": "skip"
              }
            }
          }
        }
      }
    },
    "after_filter": {
      "filter": {
        "range": {
          "pass_time": {
            "gte": "2022-04-11 00:00:00",
            "lte": "2022-04-16 23:00:00"
          }
        }
      },
      "aggs": {
        "group_by_plate_no_color": {
          "terms": {
            "script": {
              "source": "doc['plate_no'].value+','+doc['plate_color'].value",
              "lang": "painless"
            },
            "order": {
              "pass_count_total": "desc"
            },
            "size": 10000
          },
          "aggs": {
            "pass_count_total": {
              "sum": {
                "field": "pass_count"
              }
            },
            "having": {
              "bucket_selector": {
                "buckets_path": {
                  "cnt": "pass_count_total"
                },
                "script": {
                  "source": "params.cnt <= params.after_cnt",
                  "lang": "painless",
                  "params": {
                    "after_cnt": 1
                  }
                },
                "gap_policy": "skip"
              }
            }
          }
        }
      }
    }
  }
}

结果：后端根据api接口的返回数据，进行下一步操作。

4、弊端。

4.1、结果不全

es中的桶数是有限制的，虽然可以调整配置添加桶的数量，但治标不治本。还好，业务中的数据量不是很大，目前可以作为一个解决方案。

4.2、 易报错

也是因为es中的桶数有限制，一旦数据超过，就会报错，然后就后果很严重。

4.3、后端做数据处理，非常影响性能

因为将两个过滤桶数据放到后端做处理，要取交集。如果数据量大，或者业务操作麻烦时，会非常耗性能。慎重啊！

5、其他的解决思路。

目前涉及到的很多业务都是类似的，聚合根据结果做操作，不是多个聚合取交集、差集，就是将聚合后的结果作条件，放到另一个查询中做条件。

查看了很多资料，得出的结论是， es不支持子查询 。

如果小伙伴有什么其他的解决方案，欢迎指点。

6、第二种

另一种思路，可以参考下。第二种思路