oracle case when then elas,【06】把 Elasticsearch 当数据库使：CASE WHEN 聚合

最新推荐文章于 2022-02-03 18:46:33 发布
最新推荐文章于 2022-02-03 18:46:33 发布
阅读量222 收藏
点赞数
文章标签： oracle case when then elas

使用 https://github.com/taowen/es-monitor 可以用 SQL 进行 elasticsearch 的查询。前面histogram的聚合要求范围是固定的interval，而有的时候我们希望自己定义的range来做group by。这个时候就需要用CASE WHEN语句来把字段人为地分为几类。也就是相当于把这个字段先做了一个转换，然后再用转换过的字段进行聚合。

CASE WHEN ipo_year >= 2000

SQL

$ cat << EOF | ./es_query.py http://127.0.0.1:9200

SELECT ipo_year_range, COUNT(*) FROM symbol GROUP BY

CASE WHEN ipo_year >= 2000 THEN 'post_2000'

WHEN ipo_year < 2000 THEN 'pre_2000' END AS ipo_year_range

EOF

有多少WHEN THEN就有多少个bucket

{"ipo_year_range": "pre_2000", "COUNT(*)": 758}

{"ipo_year_range": "post_2000", "COUNT(*)": 2140}

Elasticsearch

{

"aggs": {

"ipo_year_range": {

"range": {

"ranges": [

{

"from": 2000.0,

"key": "post_2000"

},

{

"to": 2000.0,

"key": "pre_2000"

}

],

"field": "ipo_year"

},

"aggs": {}

}

},

"size": 0

}

这里特别要注意from是包含这个值的，也就是>=，而to是不包含的，也就是

{

"hits": {

"hits": [],

"total": 6714,

"max_score": 0.0

},

"_shards": {

"successful": 1,

"failed": 0,

"total": 1

},

"took": 32,

"aggregations": {

"ipo_year_range": {

"buckets": [

{

"to": 2000.0,

"to_as_string": "2000.0",

"key": "pre_2000",

"doc_count": 758

},

{

"from_as_string": "2000.0",

"from": 2000.0,

"key": "post_2000",

"doc_count": 2140

}

]

}

},

"timed_out": false

}

Profile

[

{

"query": [

{

"query_type": "MatchAllDocsQuery",

"lucene": "*:*",

"time": "1.318725000ms",

"breakdown": {

"score": 0,

"create_weight": 8817,

"next_doc": 1219528,

"match": 0,

"build_scorer": 90380,

"advance": 0

}

}

],

"rewrite_time": 2954,

"collector": [

{

"name": "MultiCollector",

"reason": "search_multi",

"time": "4.342172000ms",

"children": [

{

"name": "TotalHitCountCollector",

"reason": "search_count",

"time": "0.7725600000ms"

},

{

"name": "RangeAggregator: [ipo_year_range]",

"reason": "aggregation",

"time": "2.091621000ms"

}

]

}

]

}

]

CASE WHEN ipo_year > 2000

SQL

$ cat << EOF | ./es_query.py http://127.0.0.1:9200

SELECT ipo_year_range, COUNT(*) FROM symbol GROUP BY

CASE WHEN ipo_year > 2000 THEN 'post_2000'

WHEN ipo_year < 2000 THEN 'pre_2000'

ELSE '2000' END AS ipo_year_range

EOF

{"ipo_year_range": "pre_2000", "COUNT(*)": 758}

{"ipo_year_range": "2000", "COUNT(*)": 3874}

{"ipo_year_range": "post_2000", "COUNT(*)": 2082}

Elasticsearch

{

"aggs": {

"ipo_year_range": {

"filters": {

"filters": {

"pre_2000": {

"range": {

"ipo_year": {

"lt": 2000

}

}

},

"post_2000": {

"range": {

"ipo_year": {

"gt": 2000

}

}

}

},

"other_bucket_key": "2000"

},

"aggs": {}

}

},

"size": 0

}

因为 range aggregation 只能表达 >=，所以如果条件是 > 则只能用 filters aggregation来表达任意复杂的范围。

{

"hits": {

"hits": [],

"total": 6714,

"max_score": 0.0

},

"_shards": {

"successful": 1,

"failed": 0,

"total": 1

},

"took": 29,

"aggregations": {

"ipo_year_range": {

"buckets": {

"pre_2000": {

"doc_count": 758

},

"2000": {

"doc_count": 3874

},

"post_2000": {

"doc_count": 2082

}

}

}

},

"timed_out": false

}

Profile

[

{

"query": [

{

"query_type": "MultiTermQueryConstantScoreWrapper",

"lucene": "ipo_year:[* TO 2000}",

"time": "0.9414620000ms",

"breakdown": {

"score": 0,

"create_weight": 7987,

"next_doc": 0,

"match": 0,

"build_scorer": 741140,

"advance": 192335

}

},

{

"query_type": "MultiTermQueryConstantScoreWrapper",

"lucene": "ipo_year:{2000 TO *]",

"time": "5.790531000ms",

"breakdown": {

"score": 0,

"create_weight": 2424,

"next_doc": 0,

"match": 0,

"build_scorer": 1505935,

"advance": 4282172

}

},

{

"query_type": "MatchAllDocsQuery",

"lucene": "*:*",

"time": "0.2945730000ms",

"breakdown": {

"score": 0,

"create_weight": 3720,

"next_doc": 277440,

"match": 0,

"build_scorer": 13413,

"advance": 0

}

},

{

"query_type": "BooleanQuery",

"lucene": "ipo_year:`Q ipo_year:`R ipo_year:`S ipo_year:`T ipo_year:`U ipo_year:`V ipo_year:`W ipo_year:`X ipo_year:`Y ipo_year:`Z ipo_year:`[ ipo_year:`\ ipo_year:`] ipo_year:`^ ipo_year:`_ ipo_year:``",

"time": "3.399705000ms",

"breakdown": {

"score": 0,

"create_weight": 290588,

"next_doc": 0,

"match": 0,

"build_scorer": 706965,

"advance": 1191896

},

"children": [

{

"query_type": "TermQuery",

"lucene": "ipo_year:`Q",

"time": "0.08700500000ms",

"breakdown": {

"score": 0,

"create_weight": 10244,

"next_doc": 0,

"match": 0,

"build_scorer": 35010,

"advance": 41751

}

},

{

"query_type": "TermQuery",

"lucene": "ipo_year:`R",

"time": "0.06047300000ms",

"breakdown": {

"score": 0,

"create_weight": 4474,

"next_doc": 0,

"match": 0,

"build_scorer": 30064,

"advance": 25935

}

},

{

"query_type": "TermQuery",

"lucene": "ipo_year:`S",

"time": "0.04610500000ms",

"breakdown": {

"score": 0,

"create_weight": 5833,

"next_doc": 0,

"match": 0,

"build_scorer": 28901,

"advance": 11371

}

},

{

"query_type": "TermQuery",

"lucene": "ipo_year:`T",

"time": "0.05955900000ms",

"breakdown": {

"score": 0,

"create_weight": 4388,

"next_doc": 0,

"match": 0,

"build_scorer": 35718,

"advance": 19453

}

},

{

"query_type": "TermQuery",

"lucene": "ipo_year:`U",

"time": "0.05100400000ms",

"breakdown": {

"score": 0,

"create_weight": 4305,

"next_doc": 0,

"match": 0,

"build_scorer": 30884,

"advance": 15815

}

},

{

"query_type": "TermQuery",

"lucene": "ipo_year:`V",

"time": "0.05104100000ms",

"breakdown": {

"score": 0,

"create_weight": 4312,

"next_doc": 0,

"match": 0,

"build_scorer": 29454,

"advance": 17275

}

},

{

"query_type": "TermQuery",

"lucene": "ipo_year:`W",

"time": "0.05296000000ms",

"breakdown": {

"score": 0,

"create_weight": 5352,

"next_doc": 0,

"match": 0,

"build_scorer": 28870,

"advance": 18738

}

},

{

"query_type": "TermQuery",

"lucene": "ipo_year:`X",

"time": "0.03956500000ms",

"breakdown": {

"score": 0,

"create_weight": 4370,

"next_doc": 0,

"match": 0,

"build_scorer": 28989,

"advance": 6206

}

},

{

"query_type": "TermQuery",

"lucene": "ipo_year:`Y",

"time": "0.04285000000ms",

"breakdown": {

"score": 0,

"create_weight": 4276,

"next_doc": 0,

"match": 0,

"build_scorer": 28735,

"advance": 9839

}

},

{

"query_type": "TermQuery",

"lucene": "ipo_year:`Z",

"time": "0.1016550000ms",

"breakdown": {

"score": 0,

"create_weight": 4278,

"next_doc": 0,

"match": 0,

"build_scorer": 78106,

"advance": 19271

}

},

{

"query_type": "TermQuery",

"lucene": "ipo_year:`[",

"time": "0.08262300000ms",

"breakdown": {

"score": 0,

"create_weight": 4309,

"next_doc": 0,

"match": 0,

"build_scorer": 35134,

"advance": 43180

}

},

{

"query_type": "TermQuery",

"lucene": "ipo_year:`\",

"time": "0.1197220000ms",

"breakdown": {

"score": 0,

"create_weight": 4332,

"next_doc": 0,

"match": 0,

"build_scorer": 31697,

"advance": 83693

}

},

{

"query_type": "TermQuery",

"lucene": "ipo_year:`]",

"time": "0.1254620000ms",

"breakdown": {

"score": 0,

"create_weight": 4215,

"next_doc": 0,

"match": 0,

"build_scorer": 63276,

"advance": 57971

}

},

{

"query_type": "TermQuery",

"lucene": "ipo_year:`^",

"time": "0.1436460000ms",

"breakdown": {

"score": 0,

"create_weight": 4270,

"next_doc": 0,

"match": 0,

"build_scorer": 36163,

"advance": 103213

}

},

{

"query_type": "TermQuery",

"lucene": "ipo_year:`_",

"time": "0.1091380000ms",

"breakdown": {

"score": 0,

"create_weight": 4260,

"next_doc": 0,

"match": 0,

"build_scorer": 33857,

"advance": 71021

}

},

{

"query_type": "TermQuery",

"lucene": "ipo_year:``",

"time": "0.03744800000ms",

"breakdown": {

"score": 0,

"create_weight": 4231,

"next_doc": 0,

"match": 0,

"build_scorer": 29966,

"advance": 3251

}

}

]

}

],

"rewrite_time": 26423,

"collector": [

{

"name": "MultiCollector",

"reason": "search_multi",

"time": "26.31135900ms",

"children": [

{

"name": "TotalHitCountCollector",

"reason": "search_count",

"time": "0.3411380000ms"

},

{

"name": "FiltersAggregator: [ipo_year_range]",

"reason": "aggregation",

"time": "24.97556700ms"

}

]

}

]

}

]

}

]

weixin_39546661
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Elasticsearch数据库全套教程+安装部署+框架原理+开发调用+性能调优等
07-12
Elasticsearch数据库：Elasticsearch与Kubernetes集成技术教程.pdf Elasticsearch数据库：Elasticsearch在实时日志分析中的应用.pdf Elasticsearch数据库：Elasticsearch在电子商务搜索中的实践.pdf Elasticsearch...
docker-elasticsearch-alpine:基于Alpine Linux的Elasticsearch Docker映像
05-22
docker-elasticsearch-alpine 基于Alpine... 依存关系高山的：3.10 图片标签REPOSITORY TAG SIZEblacktop/elasticsearch latest 294MBblacktop/elasticsearch 7.10 294MBblacktop/elasticsearch 7.9 297MBblacktop/elas
【06】把 Elasticsearch 当数据库使：CASE WHEN 聚合
weixin_34082854的博客
02-19 4104
使用 https://github.com/taowen/es-monitor 可以用 SQL 进行 elasticsearch 的查询。前面histogram的聚合要求范围是固定的interval，而有的时候我们希望自己定义的range来做group by。这个时候就需要用CASE WHEN语句来把字段人为地分为几类。也就是相当于把这个...
oracle case when then elas,蓄意为负数
weixin_42365251的博客
04-13 255
IMPORTANTSince 7.5.0.0, the path /_sql is changed to /_nlpcn/sql, and the path /_sql/_explain is changed to /_nlpcn/sql/explain.DEPRECATEDPlease note, this project is no longer in active development, ...
elasticsearch语法讲解
Clozzz的博客
06-01 562
一、elasticsearch数据模型 Index：索引，由多个Document组成 Type：索引类型，6.x中仅支持一个，以后将逐渐被移除 Document：文档，由多个field组成， Field：字段，包括字段名与字段值 二、Document管理 PUT/POST/GET/DELETE http://ip:端口/索引名称/类型/主键 创建：PUT、POST请求 查询：GET请求 删除：DELETE请求 二、创建elasticsearch数据库 PUT 库名 { “settings”:{
Java微服务篇4——Elastic search
gyfghh的博客
08-17 1028
Java微服务篇4——Elastic search 1、Elastic search安装配置 Elastic search官方：https://www.elastic.co/cn/products/elasticsearch Elastic search6.2.4 百度云：https://pan.baidu.com/s/1JyQok8Nija4gYhcjh-HWcw提取码：isa2 解压即可 在config/elasticsearch.yml修改 # ----------------------------
oracle case when then elas,Understanding "Query Then Fetch" vs "DFS Query Then Fetch"
weixin_35615322的博客
04-13 69
In our last article on starts-with phrase matching, we ran into a situation where the returned scores were suspicious. As a refresher, here is the query in question:$ curl -XGET localhost:9200/starts...
springboot集成elasticSearch.zip
07-14
在应用启动时，可能需要初始化数据或者同步数据库数据到Elasticsearch。可以使用`@PostConstruct`注解的方法来实现。 ```java @Service public class UserService { @Autowired private UserRepository ...
springboot整合elasticsearch.md
04-07
- 与 Elasticsearch 版本无关，适用于所有版本的 ES。 - **缺点**： - 不具备高级功能，如批量操作等。 - **应用场景**： - 当需要与不同版本的 Elasticsearch 集群交互时非常有用。 - **Java High Level REST...
mysqlwithelasticsearch:一个小软件包，可使用PHP在Elasticsearch中同步MySQL数据
05-07
使用PHP将MySQL与Elasticsearch连接一个用于将MySQL与Elasticsearch连接的小型库。 用它来同步数据并进行全文搜索。 单击此处查找的单击此处查找的下载最新版本克隆图书馆git clone -b release2 ...
ElasticSearch学习笔记
silent1的专栏
03-24 4037
1 Elasticsearch中的概念与关系型数据库对比。 Relational DB  ⇒ Databases ⇒ Tables ⇒ Rows         ⇒ Columns 关系型数据库       数据库           表             行                    列  Elasticsearch  ⇒ Indices       ⇒ Typ
count与case when配合使用问题
baidu_19338587的博客
10-24 7711
SELECT COUNT(case when age>15 then scone ELSE 1 end) FROM `t_z_test1`; SELECT COUNT(case when age>15 then scone ELSE 1 end) FROM `t_z_test1`; MySQL执行这条sql时会每条记录的去执行count里的条件 如果满足age>15就对当前记...
mybatis使用case when实现模糊查询搜索相似度
滕青山博客
02-03 1963
模糊查询搜索相似度这类问题建议使用ElasticSearch来实现，用原生sql的case when来写效率很低。 话虽如此，但这里也记录一下吧。 代码 mapper.xml <select id="search" resultType="com.daanchakan.site.entity.Answer" parameterType="list"> select *, <foreach item="item" collection="chars"
聚合(CaseWhen(聚合)) 解决方案
shencb的专栏
01-23 1435
select UnsubmittedCount = (case when ApproveStatus in ('未提交', '已提交') then count(FormID) end), ApprovedCount = (case when ApproveStatus in ('已审核') then count(FormID) end) from formLogistic
ElasticSearch使用—多条件查询
午夜阳光
08-09 3352
ElasticSearch多条件查询java api例子程序。 public class EsMulConditionQuery { private static String index = "imgdata_index"; private static String type = "imgdata_type"; public static void main(String[]
Elasticsearch：sniffing 的最佳实践：What, when, why, how
Elastic 中国社区官方博客
07-08 2869
Elasticsearch 为当今使用的众多工具和应用程序提供强大的搜索体验，从运营分析仪表板到显示带有露台的最近餐馆的地图，你都可以出门在外。 在所有这些实现中，应用程序和集群之间的连接都是通过 Elasticsearch 客户端建立的。 优化客户端和 Elasticsearch 集群之间的连接对于最终用户的体验极为重要。 Elasticsearch 客户端的典型配置是你必须连接到的节点的 URL。 但是你还可以做更多的事情，一种优化此连接的方法是 sniffing。 以下是 sniffing 的工作
ElasticSearch ——（一）介绍与基本概念
CoffeJoy
04-03 205
ElasticSearch增删改查 PUT /lib/ { "settings": { "index":{ "number_of_shards":3, "number_of_replicas":0 } } } GET _search { "query": { "match_all": {} } } PUT /lib/ { ...
ElasticSearch进阶：一文全览各种ES查询在Java中的实现
良月柒
01-20 1660
程序员的成长之路互联网/程序员/技术/资料共享关注阅读本文大概需要 12 分钟。来自：blog.csdn.net/mu_wind/article/details/118423362这篇博...
Elasticsearch 基本操作
You and me~
11-04 4048
Elasticsearch之基础操作Elasticsearch之基础操作 存 指定id存储 使用自动生成id 取 返回所有字段 返回指定字段 只返回_source信息不返回元数据 查 更新 完全更新 局部更新 删除 DSL语句查询 全文搜索 短语搜索 高亮搜索 存指定id存储PUT /{index}/{type}/{id} { "field": "value", ... } 例： PUT /
ERROR: [1] bootstrap checks failed. You must address the points described in the following [1] lines before starting Elasticsearch. bootstrap check failure [1] of [1]: memory locking requested for elasticsearch process but memory is not locked ERROR: Elas
最新发布
06-13
ticsearch requires memory locking in order to ensure that the Java Virtual Machine (JVM) can manage memory effectively...This should resolve the memory locking issue and allow you to start Elasticsearch.
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值