es 聚合操作_es set filter-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_43326384/article/details/127107249

本文展示了如何使用Elasticsearch SQL查询语言和聚合操作来获取数据库中各部门员工数量、女性平均薪资、特定部门入职人数以及今年各月入职人数。这些示例涵盖了基本的聚合查询和时间序列分析，揭示了Elasticsearch在数据分析中的强大功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

前期准备

CREATE TABLE `staff` (
  `no` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL COMMENT '名称',
  `salary` double DEFAULT NULL COMMENT '薪水',
  `dept` varchar(10) DEFAULT NULL COMMENT '部门',
  `sex` int(11) DEFAULT NULL,
  `comeDate` datetime DEFAULT NULL COMMENT '入职时间',
  PRIMARY KEY (`no`)
) ENGINE=InnoDB AUTO_INCREMENT=113 DEFAULT CHARSET=utf8mb4;
INSERT INTO `staff`(`no`, `name`, `salary`, `dept`, `sex`, `comeDate`) VALUES (100, 'zhangsan', 3000, 'sale', 1, '2022-08-17 09:51:50');
INSERT INTO `staff`(`no`, `name`, `salary`, `dept`, `sex`, `comeDate`) VALUES (101, 'lisi', 8500, 'it', 1, '2020-09-29 10:04:17');
INSERT INTO `staff`(`no`, `name`, `salary`, `dept`, `sex`, `comeDate`) VALUES (102, 'wangwu', 5000, 'adm', 1, '2022-05-25 10:05:19');
INSERT INTO `staff`(`no`, `name`, `salary`, `dept`, `sex`, `comeDate`) VALUES (103, 'zhaoli', 12000, 'it', 0, '2020-10-29 10:05:53');
INSERT INTO `staff`(`no`, `name`, `salary`, `dept`, `sex`, `comeDate`) VALUES (104, 'wangyan', 19000, 'it', 0, '2018-09-29 10:06:28');
INSERT INTO `staff`(`no`, `name`, `salary`, `dept`, `sex`, `comeDate`) VALUES (105, 'liujun', 2000, 'sale', 1, '2011-09-29 10:07:01');
INSERT INTO `staff`(`no`, `name`, `salary`, `dept`, `sex`, `comeDate`) VALUES (106, 'litao', 5000, 'sale', 0, '2022-05-25 10:07:34');
INSERT INTO `staff`(`no`, `name`, `salary`, `dept`, `sex`, `comeDate`) VALUES (107, 'zhaokai', 20000, 'adm', 0, '2020-12-29 10:08:15');
INSERT INTO `staff`(`no`, `name`, `salary`, `dept`, `sex`, `comeDate`) VALUES (108, 'lifang', 6000, 'cust', 0, '2021-01-01 10:09:03');
INSERT INTO `staff`(`no`, `name`, `salary`, `dept`, `sex`, `comeDate`) VALUES (109, 'zhouxiaoli', 10000, 'cust', 0, '2022-09-01 10:10:23');
INSERT INTO `staff`(`no`, `name`, `salary`, `dept`, `sex`, `comeDate`) VALUES (110, 'liuhang', 15000, 'it', 1, '2015-10-29 10:11:16');
INSERT INTO `staff`(`no`, `name`, `salary`, `dept`, `sex`, `comeDate`) VALUES (111, 'zhengfang', 20000, 'adm', 0, '2022-09-01 10:40:43');
INSERT INTO `staff`(`no`, `name`, `salary`, `dept`, `sex`, `comeDate`) VALUES (112, 'wuhua', 25000, 'sale', 0, '2021-02-04 10:42:09');

es 相关操作

GET /_sql?format=json
{
  "query":"select dept,count(*) as num from staff group by dept "
}

# SELECT dept, count(*) num FROM `staff` GROUP BY dept ORDER BY COUNT(*) DESC
#  查询各个部门的总人数 并由大到小序
GET /staff/_search?filter_path=aggregations
{
  "aggs": {
    "dept_bucket": {
      "terms": {
        "field": "dept.keyword",
        "size": 10,
        "order": {
          "_key": "desc"
        }
      }
    }
  }
}

# SELECT dept, count(salary) num FROM `staff` where sex = 0 GROUP BY dept 
GET /staff/_search?filter_path=aggregations
{
  "aggs": {
    "dept_bucket": {
      "terms": {
        "field": "dept.keyword",
        "size": 10,
        "order": {
          "_key": "desc"
        }
      }
    }
  },
  "query": {
    "term": {
      "sex": {
        "value": "0"
      }
    }
  }
}

# 查询各个部门的女生的平均薪水
# SELECT dept, avg(salary) num FROM `staff` where sex = 0 GROUP BY dept
GET /staff/_search?filter_path=aggregations
{
  "aggs": {
    "dept_bucket": {
      "terms": {
        "field": "dept.keyword",
        "size": 10,
        "order": {
          "_key": "desc"
        }
      },
      "aggs": {
        "salary_avg": {
          "avg": {
            "field": "salary"
          }
        }
      }
    }
  },
  "query": {
    "term": {
      "sex": {
        "value": "0"
      }
    }
  }
}

# SELECT dept, count(`no`) from staff where comeDate > '2022-01-01 00:00:00' GROUP BY dept  
# 查询it 部门今年入职的总人数
GET /staff/_search?filter_path=aggregations
{
  "aggs": {
    "dept_bucket": {
      "terms": {
        "field": "dept.keyword",
        "size": 10,
        "order": {
          "_key": "desc"
        }
      }
    }
  },
  "query": {
    "range": {
      "comeDate": {
        "gte": "01/01/2022",
        "format": "dd/MM/yyyy||yyyy"
      }
    }
  }
}


# 查询 今年以来每个月入职的人数
# SELECT str_to_date(comeDate,'%Y-%m')   ,count(`NO`) from staff where comeDate > '2022-01-01' GROUP BY str_to_date(comeDate,'%Y-%m') 
GET /staff/_search?filter_path=aggregations
{
  "aggs": {
    "come_date_bucket": {
      "date_histogram": {
        "field": "comeDate",
        "format": "yyyy-MM-dd",
        "interval": "month"
      }
    }
  },
  "query": {
    "range": {
      "comeDate": {
        "gte": "01/01/2022",
        "format": "dd/MM/yyyy||yyyy"
      }
    }
  }
}