elasticsearch 多重分组并取组内最小值两个方案

最新推荐文章于 2023-12-25 14:42:50 发布

1001.2014.3001

最新推荐文章于 2023-12-25 14:42:50 发布

阅读量1.3k

点赞数

分类专栏： mysql elasticsearch

本文链接：https://blog.csdn.net/wangh92/article/details/102695496

版权

mysql 同时被 2 个专栏收录

19 篇文章 0 订阅

订阅专栏

elasticsearch

12 篇文章 0 订阅

订阅专栏

//sql 版本
SELECT id,estate_name,house_type,price 
FROM 
( SELECT id,estate_name,house_type,price,
 ROW_NUMBER () OVER ( PARTITION BY estate_name,house_type ORDER BY price ASC) rownum 
 FROM property 
 ) t WHERE rownum = 1 AND estate_name IS NOT NULL

//需要查出对应文档id  
 GET property_index/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "univalent": {
              "gte": 6000,
              "lte": 20000
            }
          }
        },
        {
          "term": {
            "status": {
              "value": "有效"
            }
          }
        },
        {
          "term": {
            "demand": {
              "value": "售"
            }
          }
        },
        {
          "exists": {
            "field": "estateName"
          }
        },
        {
          "range": {
            "activationDate": {
              "gte": 1530288000000
            }
          }
        },
        {
          "range": {
            "room": {
              "gte": 0,
              "lte": 7
            }
          }
        },
        {
          "range": {
            "hall": {
              "gte": 0,
              "lte": 5
            }
          }
        }
      ],
      "must_not": [],
      "should": []
    }
  },
  "size": 0,
  "aggs": {
    "estateName": {
      "terms": {
        "field": "estateName.keyword",
        "size": 9000
      },
      "aggs": {
        "rooms": {
          "terms": {
            "field": "room",
            "size": 10
          },
          "aggs": {
            "hall": {
              "terms": {
                "field": "hall",
                "size": 10
              },
              "aggs": {
                "price": {
                  "top_hits": {
                    "size": 1,
                    "_source": [
                      "id",
                      "price"
                    ],
                    "sort": [
                      {
                        "price": {
                          "order": "asc"
                        },
                        "activationDate": {
                          "order": "desc"
                        }
                      }
                    ]
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

//只需要查出对应组内最低价即可
GET property_index/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "univalent": {
              "gte": 6000,
              "lte": 20000
            }
          }
        },
        {
          "term": {
            "status": {
              "value": "有效"
            }
          }
        },
        {
          "term": {
            "demand": {
              "value": "售"
            }
          }
        },
        {
          "exists": {
            "field": "estateName"
          }
        },
        {
          "range": {
            "activationDate": {
              "gte": 1530288000000
            }
          }
        },
        {
          "range": {
            "room": {
              "gte": 0,
              "lte": 7
            }
          }
        },
        {
          "range": {
            "hall": {
              "gte": 0,
              "lte": 5
            }
          }
        }
      ],
      "must_not": [],
      "should": []
    }
  },
  "size": 0,
  "aggs": {
    "estateName": {
      "terms": {
        "field": "estateName.keyword",
        "size": 9000
      },
      "aggs": {
        "rooms": {
          "terms": {
            "field": "room",
            "size": 10
          },
          "aggs": {
            "hall": {
              "terms": {
                "field": "hall",
                "size": 10
              },
              "aggs": {
                "price": {
                  "min": {
                    "field": "price"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}