CREATE TABLE`tb_user` (
`id`bigint(20) NOTNULL AUTO_INCREMENT COMMENT'主鍵',
`user_no`varchar(255) NOTNULLCOMMENT'編號(hào)',
`name`varchar(255) DEFAULTNULLCOMMENT'昵稱',
`email`varchar(255) DEFAULTNULLCOMMENT'郵箱',
`phone`varchar(255) NOTNULLCOMMENT'手機(jī)號(hào)',
`gender`tinyint(4) NOTNULLDEFAULT'0'COMMENT'性別  0：男生   1：女生',
`birthday`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'出生日期',
`is_delete`tinyint(4) NOTNULLDEFAULT'0'COMMENT'刪除標(biāo)志 0：否  1：是',
`create_time` datetime DEFAULTNULLCOMMENT'創(chuàng)建時(shí)間',
`update_time` datetime DEFAULTNULLCOMMENT'更新時(shí)間',
`create_by`bigint(20) DEFAULTNULLCOMMENT'創(chuàng)建人',
`update_by`bigint(20) DEFAULTNULLCOMMENT'更新人',
`address`varchar(1024) DEFAULTNULLCOMMENT'地址',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5201011DEFAULTCHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

SELECT
 * 
FROM
 ( SELECTid, NAME, birthday, ROW_NUMBER () OVER ( PARTITIONBYNAMEORDERBYidDESC ) AS row_num FROM tb_user
   wherenamein('徐千云', '李億石')
 ) AS u 
WHERE
 u.row_num=1

SELECT id, name, birthday from tb_user 
where id in (SELECT MAX(id) from tb_user where name in('徐千云', '李億石') group by name)

SELECT id, name, birthday from tb_user u
INNER JOIN (SELECT MAX(id) max_id from tb_user where name in('徐千云', '李億石') group by name) as t
on u.id=t.max_id

ALTER TABLE `db_test`.`tb_user` 
ADD INDEX `idx_name`(`name`) USING BTREE;

SELECT id,name from tb_user 
where id in (SELECT MAX(id) from tb_user where name in('徐千云', '李億石') group by name)

SELECT
 id, name, birthday
FROM
 ( SELECTid, name,birthday, ROW_NUMBER () OVER ( PARTITIONBYNAMEORDERBY birthday DESC ) AS row_num FROM tb_user
   wherenamein('徐千云', '李億石')
 ) AS u 
WHERE
 u.row_num =1

SELECT id, name, birthday from tb_user u1
where birthday = (select max(birthday) from tb_user u2 where u2.name in('徐千云', '李億石') and u1.`name`=u2.`name`)

GET user_info/_search
{
"size": 0,
"query": {
    "bool": {
      "filter": [
        { "terms": { "name": ["徐千云", "李億石"] }},
      ]
    }
  },
"aggs": {
    "group_by_name": {
      "terms": {
        "field": "name",
        "size": 1000
      },
      "aggs": {
        "latest_user": {
          "top_hits": {
            "sort": [
              { "birthday": { "order": "desc" }}
            ],
            "_source": ["id", "name", "org_id", "birthday"],
            "size": 1
          }
        }
      }
    }
  }
}

public Integer aggregateResults(UserQueryDTO dto, Consumer<List<UserInfo>> consumer) {
        finalint pageSize = 500;
        Integer total = 0;
        Map<String, Object> afterKey = null;
        while (true) {
            // 創(chuàng)建 SearchRequest
            SearchRequest searchRequest = new SearchRequest(getAliasName());
            // 構(gòu)建查詢條件
            BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery();
            if (StringUtils.isNotBlank(dto.getTypes())) {
                boolQueryBuilder.filter(QueryBuilders.termsQuery("name", dto.getNames));
            }
            // Composite聚合實(shí)現(xiàn)并分頁(yè)
            CompositeAggregationBuilder compositeAggregation = AggregationBuilders
                    .composite("group_by_name",
                            Lists.newArrayList( new TermsValuesSourceBuilder("name").field("name")))
                    .size(pageSize);
            // 設(shè)置分頁(yè)的 afterKey
            compositeAggregation.aggregateAfter(afterKey);
            // 添加top_hits子聚合，size=1獲取每個(gè)分組中的最新文檔
            TopHitsAggregationBuilder topHitsAggregation = AggregationBuilders
                    .topHits("latest_user")
                    .sort("birthday", SortOrder.DESC)
                    .fetchSource(new String[]{"id", "name", "birthday"}, null)
                    .size(1);
            // 添加子聚合到 composite 聚合
            compositeAggregation.subAggregation(topHitsAggregation);
            // 構(gòu)建 SearchSourceBuilder size=0不返回文檔，只返回聚合結(jié)果
            SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder()
                    .size(0)
                    .query(boolQueryBuilder)
                    .aggregation(compositeAggregation);

            // 設(shè)置搜索請(qǐng)求，執(zhí)行搜索
            searchRequest.source(searchSourceBuilder);
            SearchResponse response = search(searchRequest);

            // 處理分頁(yè)聚合結(jié)果
            List<String> dataList = new ArrayList<>();
            ParsedComposite compositeAgg = response.getAggregations().get("group_by_name");
            // 遍歷當(dāng)前頁(yè)的分組結(jié)果
            compositeAgg.getBuckets().forEach(bucket -> {
                ParsedTopHits topHits = bucket.getAggregations().get("latest_user");
                SearchHit[] hits = topHits.getHits().getHits();
                for (SearchHit hit : hits) {
                   dataList.add(hit.getSourceAsString());
                }
            });
            List<UserInfo> results = convert(dataList, UserInfo.class);
            // 回調(diào)
            consumer.accept(results);
            total = total + results.size();
            // 更新 afterKey，為下一頁(yè)查詢準(zhǔn)備, 如果afterKey為空，說(shuō)明已經(jīng)查詢到最后一頁(yè)
            afterKey = compositeAgg.afterKey();
            if (afterKey == null) {
                break;
            }
        }
        return total;
    }