ElasticSearch 7 SQL 詳解

平時使用Elasticsearch的時候,會在Kibana中使用Query DSL來查詢數據.每次要用到Query DSL時都基本忘光了,需要重新在回顧一遍,最近發現Elasticsearch已經支持SQL查詢了(6.3版本以后),整理了下一些用法.

簡介

Elasticsearch SQL是一個X-Pack組件,它允許針對Elasticsearch實時執行類似SQL的查詢.無論使用REST接口,命令行還是JDBC,任何客戶端都可以使用SQL對Elasticsearch中的數據進行原生搜索和聚合數據.可以將Elasticsearch SQL看作是一種翻譯器,它可以將SQL翻譯成Query DSL.

Elasticsearch SQL具有如下特性:

  • 原生支持:Elasticsearch SQL是專門為Elasticsearch打造的.
  • 沒有額外的零件:無需其他硬件,處理器,運行環境或依賴庫即可查詢Elasticsearch,Elasticsearch SQL直接在Elasticsearch內部運行.
  • 輕巧高效:Elasticsearch SQL并未抽象化其搜索功能,相反的它擁抱并接受了SQL來實現全文搜索,以簡潔的方式實時運行全文搜索.

準備

先安裝好Elasticsearch和Kibana,這里安裝的是7.17.0版本

安裝完成后在Kibana中 http://127.0.0.1:5601/app/dev_tools#/console{target=“_blank”}
導入測試數據,數據地址: https://github.com/macrozheng/mall-learning/blob/master/document/json/accounts.json{target=“_blank”}

直接在Kibana的Dev Tools中運行如下命令即可:

POST /account/_bulk
{"index":{"_id":"1"}}
{"account_number":1,"balance":39225,"firstname":"Amber","lastname":"Duke","age":32,"gender":"M","address":"880 Holmes Lane","employer":"Pyrami","email":"amberduke@pyrami.com","city":"Brogan","state":"IL"}
{"index":{"_id":"6"}}
{"account_number":6,"balance":5686,"firstname":"Hattie","lastname":"Bond","age":36,"gender":"M","address":"671 Bristol Street","employer":"Netagy","email":"hattiebond@netagy.com","city":"Dante","state":"TN"}
{"index":{"_id":"13"}}
{"account_number":13,"balance":32838,"firstname":"Nanette","lastname":"Bates","age":28,"gender":"F","address":"789 Madison Street","employer":"Quility","email":"nanettebates@quility.com","city":"Nogal","state":"VA"}
{"index":{"_id":"18"}}
{"account_number":18,"balance":4180,"firstname":"Dale","lastname":"Adams","age":33,"gender":"M","address":"467 Hutchinson Court","employer":"Boink","email":"daleadams@boink.com","city":"Orick","state":"MD"}
{"index":{"_id":"20"}}
{"account_number":20,"balance":16418,"firstname":"Elinor","lastname":"Ratliff","age":36,"gender":"M","address":"282 Kings Place","employer":"Scentric","email":"elinorratliff@scentric.com","city":"Ribera","state":"WA"}
{"index":{"_id":"25"}}
{"account_number":25,"balance":40540,"firstname":"Virginia","lastname":"Ayala","age":39,"gender":"F","address":"171 Putnam Avenue","employer":"Filodyne","email":"virginiaayala@filodyne.com","city":"Nicholson","state":"PA"}
{"index":{"_id":"32"}}
{"account_number":32,"balance":48086,"firstname":"Dillard","lastname":"Mcpherson","age":34,"gender":"F","address":"702 Quentin Street","employer":"Quailcom","email":"dillardmcpherson@quailcom.com","city":"Veguita","state":"IN"}
{"index":{"_id":"37"}}
{"account_number":37,"balance":18612,"firstname":"Mcgee","lastname":"Mooney","age":39,"gender":"M","address":"826 Fillmore Place","employer":"Reversus","email":"mcgeemooney@reversus.com","city":"Tooleville","state":"OK"}
{"index":{"_id":"44"}}
{"account_number":44,"balance":34487,"firstname":"Aurelia","lastname":"Harding","age":37,"gender":"M","address":"502 Baycliff Terrace","employer":"Orbalix","email":"aureliaharding@orbalix.com","city":"Yardville","state":"DE"}
{"index":{"_id":"49"}}
{"account_number":49,"balance":29104,"firstname":"Fulton","lastname":"Holt","age":23,"gender":"F","address":"451 Humboldt Street","employer":"Anocha","email":"fultonholt@anocha.com","city":"Sunriver","state":"RI"}

第一個SQL查詢

我們使用SQL來查詢下前10條記錄,可以通過format參數控制返回結果的格式,txt表示文本格式,看起來更直觀點,默認為json格式.

在Kibana的Dev Tools中輸入如下命令:

POST /_sql?format=txt
{"query": "SELECT account_number,address,age,balance FROM account LIMIT 10"
}

查詢結果顯示如下.

account_number |      address       |      age      |    balance    
---------------+--------------------+---------------+---------------
1              |880 Holmes Lane     |32             |39225          
6              |671 Bristol Street  |36             |5686           
13             |789 Madison Street  |28             |32838          
18             |467 Hutchinson Court|33             |4180           
20             |282 Kings Place     |36             |16418          
25             |171 Putnam Avenue   |39             |40540          
32             |702 Quentin Street  |34             |48086          
37             |826 Fillmore Place  |39             |18612          
44             |502 Baycliff Terrace|37             |34487          
49             |451 Humboldt Street |23             |29104

如上實例,使用 _sql 指明使用SQL模塊,在 query 字段中指定要執行的SQL語句.使用 format 指定返回數據的格式,數據格式可選項有以下幾個,它們都是見名識意的:

formatAccept Http header說明
csvtext/csv逗號分隔
jsonapplication/jsonJson 格式
tsvtext/tab-separated-valuestab 分隔
txttext/plain文本格式
yamlapplication/yamlyaml
cborapplication/cbor簡潔的二進制對象表示格式
smileapplication/smile類似于 cbor 的另一種二進制格式

將SQL轉化為DSL

當我們需要使用Query DSL時,也可以先使用SQL來查詢,然后通過Translate API轉換即可.

例如我們翻譯以下查詢語句:

POST /_sql/translate
{"query": "SELECT account_number,address,age,balance FROM account WHERE age>32 LIMIT 10"
}

最終獲取到Query DSL結果如下.

{"size" : 10,"query" : {"range" : {"age" : {"from" : 32,"to" : null,"include_lower" : false,"include_upper" : false,"boost" : 1.0}}},"_source" : false,"fields" : [{"field" : "account_number"},{"field" : "address"},{"field" : "age"},{"field" : "balance"}],"sort" : [{"_doc" : {"order" : "asc"}}]
}

然后可以用Query DSL 語法來查詢:

GET /account/_search
{"size": 10,"query": {"range": {"age": {"from": 32,"to": null,"include_lower": false,"include_upper": false,"boost": 1}}},"_source": false,"fields": [{"field": "account_number"},{"field": "address"},{"field": "age"},{"field": "balance"}],"sort": [{"_doc": {"order": "asc"}}]
}

SQL和DSL混合使用

我們還可以將SQL和Query DSL混合使用,比如使用Query DSL來設置過濾條件.

例如查詢 age在30-35 之間的記錄,可以使用如下查詢語句:

POST /_sql?format=txt
{"query": "SELECT account_number,address,age,balance FROM account","filter": {"range": {"age": {"gte": 30,"lte": 35}}},"fetch_size": 10
}

SQL和ES對應關系

雖然 SQL 和 Elasticsearch 對于數據的組織方式(以及不同的語義)有不同的術語,但本質上它們的用途是相同的.下面是它們的映射關系表:

SQLElasticsearch說明
columnfield在 Elasticsearch 字段時,SQL 將這樣的條目調用為 column.注意,在 Elasticsearch,一個字段可以包含同一類型的多個值(本質上是一個列表) ,而在 SQL 中,一個列可以只包含一個表示類型的值.Elasticsearch SQL 將盡最大努力保留 SQL 語義,并根據查詢的不同,拒絕那些返回多個值的字段.
rowdocument列和字段本身不存在; 它們是行或文檔的一部分.兩者的語義略有不同: 行row往往是嚴格的(并且有更多的強制執行),而文檔往往更靈活或更松散(同時仍然具有結構).
tableindex在 SQL 還是 Elasticsearch 中查詢針對的目標
schemaimplicit在關系型數據庫中,schema 主要是表的名稱空間,通常用作安全邊界.Elasticsearch沒有為它提供一個等價的概念.

雖然這些概念之間的映射在語義上有些不同,但它們間更多的是有共同點,而不是不同點.

詞法結構

ES SQL 的詞法結構很大程度上類似于 ANSI SQL 本身.ES SQL 當前一次只能接受一個命令,這里的命令是由輸入流結尾結束的 token 序列.這些 token 可以是關鍵字,標識符(帶引號或者不帶引號),文本(或者常量),特殊字符符號(通常是分隔符).

關鍵字

關鍵詞這個其實跟我們寫 SQL 語句那種關鍵字的定義是一樣的,例如 SELECT,FROM 等都是關鍵字,需要注意的是,關鍵字不區分大小寫.

SELECT * FROM my_table

如上示例,共有 4 個 token:SELECT, * ,FROM ,my_table,其中 SELECT,* ,FROM 是關鍵詞,表示在 SQL 具有固定含義的詞.而 my_table 是一個標識符,其表示了 SQL 中實體,如表,列等

標識符

標識符有兩種類型:帶引號的和不帶引號的,示例如下:

SELECT ip_address FROM "hosts-*"

如上示例,查詢中有兩個標識符分別為不帶引號的 ip_address 和帶引號的 hosts-*(通配符模式).
因為 ip_address 不與任何關鍵字沖突,所以可以不帶引號.而 hosts-* 與 - (減號操作)和 * 沖突,所以要加引號.

📝注意: 對于標識符來說,應該盡量避免使用復雜的命名和與關鍵字沖突的命名,并且在輸入的時候使用引號作為標識符,這樣可以消除歧義.

直接常量

ES SQL 支持兩種隱式的類型常量:字符串 和 數字.

  • 字符串,字符串可以用單引號進行限定,例如: 'mysql' .如果在字符串中包含了單引號,則需要使用另一個單引號進行轉義,例如: 'Captain EO''s Voyage' .
  • 數值常量,數值常量可以使用十進制和科學計數法進行表示,其示例如下:
1969    -- integer notation
3.14    -- decimal notation
.1234   -- decimal notation starting with decimal point
4E5     -- scientific notation (with exponent marker)
1.2e-3  -- scientific notation with decimal point

一個包含小數點的數值常量會被解析為 Double 類型.如果適合解析為整型,則解析為 Integer,否則解析為長整型(Long).

單引號,雙引號

在 SQL 中,單引號和雙引號具有不同的含義,不能互換使用.單引號用于聲明字符串,而雙引號用于表示標識符.示例如下:

SELECT "first_name" FROM "musicians"  WHERE "last_name"  = 'Carroll'

如上示例,first_name,musicians,last_name 都是標識符,用雙引號.而 Carroll 是字符串,用單引號.

特殊字符

一些非數字和字母的字符具有不同于運算符的專用含義,特殊字符有:

字符描述
*在一些上下文中表示數據表的所有字段,也可以表示某些聚合函數的參數.
,用于列舉列表的元素
.用于數字常量或者分隔標識符限定符(表,列等)
()用于特定的 SQL 命令,函數聲明,或者強制優先級.

運算符

ES SQL 中大多數的運算符它們的優先級都是相同的,并且是左關聯.如果需要修改優先級,則要用括號來強制改變其優先級.下表是 ES SQL 支持的運算符和其優先級:

運算符結合性說明
.左結合限定符或者分割符
::左結合PostgreSQL-style 風格的類型轉換符
+ -右結合一元加減符
* / %左結合乘法,除法,取模
+ -左結合加法,減法運算
BETWEEN IN LIKE范圍包含,字符匹配
< > <= >= = <=> <> !=比較運算
NOT右結合邏輯非
AND左結合邏輯與
OR 左結合邏輯或

注釋

ES SQL 支持兩種注釋:單行和多行注釋,其示例如下:

-- single line comment,單行注釋/* multilinecommentthat supports /* nested comments */多行注釋*/

常用SQL操作

語法介紹

在ES中使用SQL查詢的語法與在數據庫中使用基本一致,具體格式如下:

SELECT select_expr [, ...]
[ FROM table_name ]
[ WHERE condition ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition]
[ ORDER BY expression [ ASC | DESC ] [, ...] ]
[ LIMIT [ count ] ]
[ PIVOT ( aggregation_expr FOR column IN ( value [ [ AS ] alias ] [, ...] ) ) ]

WHERE

可以使用WHERE語句設置查詢條件,比如查詢state字段為VA的記錄,查詢語句如下.

POST /_sql?format=txt
{"query": "SELECT account_number,address,age,balance,state FROM account WHERE state='VA' LIMIT 10"
}

查詢結果如下:

account_number |      address       |      age      |    balance    |     state     
---------------+--------------------+---------------+---------------+---------------
13             |789 Madison Street  |28             |32838          |VA             
486            |991 Applegate Court |22             |35902          |VA             
703            |489 Flatlands Avenue|29             |27443          |VA             
835            |641 Royce Street    |25             |46558          |VA             
897            |731 Poplar Street   |25             |45973          |VA             
564            |842 Congress Street |22             |43631          |VA             
588            |301 Anna Court      |31             |43531          |VA             
660            |916 Amersfort Place |33             |46427          |VA             
797            |919 Quay Street     |26             |6854           |VA             
836            |953 Dinsmore Place  |25             |20797          |VA

GROUP BY

我們可以使用 GROUP BY 語句對數據進行分組,統計出分組記錄數量,最大age和平均balance等信息,查詢語句如下.

POST /_sql?format=txt
{"query": "SELECT state,COUNT(*),MAX(age),AVG(balance) FROM account GROUP BY state LIMIT 10"
}

HAVING

我們可以使用 HAVING 語句對分組數據進行二次篩選,比如篩選分組記錄數量大于15的信息,查詢語句如下.

POST /_sql?format=txt
{"query": "SELECT state,COUNT(*),MAX(age),AVG(balance) FROM account GROUP BY state HAVING COUNT(*)>15 LIMIT 10"
}

查詢結果如下:

     state     |   COUNT(*)    |   MAX(age)    |   AVG(balance)   
---------------+---------------+---------------+------------------
AK             |22             |40             |26131.863636363636
AL             |25             |40             |25739.56          
AR             |18             |39             |27238.166666666668
CA             |17             |40             |22517.882352941175
CT             |16             |39             |28278.4375        
DC             |24             |40             |23180.583333333332
FL             |18             |38             |20443.444444444445

ORDER BY

我們可以使用ORDER BY語句對數據進行排序,比如按照balance字段從高到低排序,查詢語句如下.

POST /_sql?format=txt
{"query": "SELECT account_number,address,age,balance,state FROM account ORDER BY balance DESC LIMIT 10 "
}

查詢結果如下:

account_number |       address        |      age      |    balance    |     state     
---------------+----------------------+---------------+---------------+---------------
248            |717 Hendrickson Place |36             |49989          |WA             
854            |603 Cooper Street     |25             |49795          |AL             
240            |659 Highland Boulevard|35             |49741          |NH             
97             |512 Cumberland Walk   |40             |49671          |MO             
842            |833 Bushwick Court    |23             |49587          |TX             
168            |975 Flatbush Avenue   |20             |49568          |IL             
803            |963 Highland Avenue   |25             |49567          |MS             
926            |833 Quincy Street     |21             |49433          |VT             
954            |688 Hart Street       |22             |49404          |MD             
572            |994 Chester Court     |20             |49355          |UT

DESCRIBE

我們可以使用 DESCRIBE 語句查看表(ES中為索引)中有哪些字段,比如查看account表的字段,查詢語句如下.

POST /_sql?format=txt
{"query": "DESCRIBE account"
}

查詢結果如下:

     column      |     type      |    mapping    
-----------------+---------------+---------------
account_number   |BIGINT         |long           
address          |VARCHAR        |text           
address.keyword  |VARCHAR        |keyword        
age              |BIGINT         |long           
balance          |BIGINT         |long           
city             |VARCHAR        |text           
city.keyword     |VARCHAR        |keyword        
email            |VARCHAR        |text           
email.keyword    |VARCHAR        |keyword        
employer         |VARCHAR        |text           
employer.keyword |VARCHAR        |keyword        
firstname        |VARCHAR        |text           
firstname.keyword|VARCHAR        |keyword        
gender           |VARCHAR        |text           
gender.keyword   |VARCHAR        |keyword        
lastname         |VARCHAR        |text           
lastname.keyword |VARCHAR        |keyword        
state            |VARCHAR        |text           
state.keyword    |VARCHAR        |keyword

SHOW TABLES

我們可以使用 SHOW TABLES 查看所有的表(ES中為索引).

POST /_sql?format=txt
{"query": "SHOW TABLES"
}

查詢結果如下:

#! this request accesses system indices: [.kibana_7.17.0_001, .kibana_task_manager_7.17.0_001], but in a future major version, direct access to system indices will be prevented by default
#! this request accesses system indices: [.apm-agent-configuration, .apm-custom-link, .async-search, .kibana_7.17.0_001, .kibana_task_manager_7.17.0_001, .tasks], but in a future major version, direct access to system indices will be prevented by defaultcatalog    |             name              |     type      |     kind      
---------------+-------------------------------+---------------+---------------
my-application |.apm-agent-configuration       |TABLE          |INDEX          
my-application |.apm-custom-link               |TABLE          |INDEX          
my-application |.async-search                  |TABLE          |INDEX          
my-application |.kibana                        |VIEW           |ALIAS          
my-application |.kibana_7.17.0                 |VIEW           |ALIAS          
my-application |.kibana_7.17.0_001             |TABLE          |INDEX          
my-application |.kibana_task_manager           |VIEW           |ALIAS          
my-application |.kibana_task_manager_7.17.0    |VIEW           |ALIAS          
my-application |.kibana_task_manager_7.17.0_001|TABLE          |INDEX          
my-application |.tasks                         |TABLE          |INDEX          
my-application |account                        |TABLE          |INDEX          
my-application |kibana_sample_data_flights     |TABLE          |INDEX

支持的函數

使用SQL查詢ES中的數據,不僅可以使用一些SQL中的函數,還可以使用一些ES中特有的函數.

查詢支持的函數

我們可以使用 SHOW FUNCTIONS 語句查看所有支持的函數,比如搜索所有帶有 DATE 字段的函數可以使用如下語句.

POST /_sql?format=txt
{"query": "SHOW FUNCTIONS LIKE '%DATE%'"
}

查詢結果如下:

     name      |     type      
---------------+---------------
CURDATE        |SCALAR         
CURRENT_DATE   |SCALAR         
DATEADD        |SCALAR         
DATEDIFF       |SCALAR         
DATEPART       |SCALAR         
DATETIME_FORMAT|SCALAR         
DATETIME_PARSE |SCALAR         
DATETRUNC      |SCALAR         
DATE_ADD       |SCALAR         
DATE_DIFF      |SCALAR         
DATE_PARSE     |SCALAR         
DATE_PART      |SCALAR         
DATE_TRUNC     |SCALAR

全文搜索函數

全文搜索函數是ES中特有的,當使用 MATCH 或 QUERY 函數時,會啟用全文搜索功能,SCORE 函數可以用來統計搜索評分.

MATCH()

使用MATCH函數查詢address中包含Street的記錄.

POST /_sql?format=txt
{"query": "SELECT account_number,address,age,balance,SCORE() FROM account WHERE MATCH(address,'Street') LIMIT 10"
}

查詢結果如下:

account_number |        address        |      age      |    balance    |    SCORE()    
---------------+-----------------------+---------------+---------------+---------------
6              |671 Bristol Street     |36             |5686           |0.95395315     
13             |789 Madison Street     |28             |32838          |0.95395315     
32             |702 Quentin Street     |34             |48086          |0.95395315     
49             |451 Humboldt Street    |23             |29104          |0.95395315     
51             |334 River Street       |31             |14097          |0.95395315     
63             |510 Sedgwick Street    |30             |6077           |0.95395315     
87             |446 Halleck Street     |22             |1133           |0.95395315     
107            |694 Jefferson Street   |28             |48844          |0.95395315     
138            |422 Malbone Street     |39             |9006           |0.95395315     
140            |878 Schermerhorn Street|32             |26696          |0.95395315
QUERY()

使用 QUERY 函數查詢address中包含Street的記錄.

POST /_sql?format=txt
{"query": "SELECT account_number,address,age,balance,SCORE() FROM account WHERE QUERY('address:Street') LIMIT 10"
}

查詢結果如下:

account_number |        address        |      age      |    balance    |    SCORE()    
---------------+-----------------------+---------------+---------------+---------------
6              |671 Bristol Street     |36             |5686           |0.95395315     
13             |789 Madison Street     |28             |32838          |0.95395315     
32             |702 Quentin Street     |34             |48086          |0.95395315     
49             |451 Humboldt Street    |23             |29104          |0.95395315     
51             |334 River Street       |31             |14097          |0.95395315     
63             |510 Sedgwick Street    |30             |6077           |0.95395315     
87             |446 Halleck Street     |22             |1133           |0.95395315     
107            |694 Jefferson Street   |28             |48844          |0.95395315     
138            |422 Malbone Street     |39             |9006           |0.95395315     
140            |878 Schermerhorn Street|32             |26696          |0.95395315

SQL CLI

如果你不想使用Kibana來使用ES SQL的話,也可以使用ES自帶的SQL CLI來查詢,該命令位于ES的bin目錄下.

使用如下命令啟動SQL CLI:

elasticsearch-sql-cli http://localhost:9200

然后直接輸入SQL命令即可查詢了,注意要加分號.

SELECT account_number,address,age,balance FROM account LIMIT 10;

ES SQL 的局限性

使用SQL查詢ES有一定的局限性,沒有原生的Query DSL那么強大,對于嵌套屬性和某些函數的支持并不怎么好,但是平時用來查詢下數據基本夠用了.

ES SQL 使用實戰

我們先準備數據,此處我們將使用 Kibana 提供的航班數據:

如下圖,在 Kibana 中點擊左邊欄的 Analytics 下的 Overview ,右邊的頁面中選擇 DashBoard 然后點擊 Install some sample data 鏈接,
再點擊 Sample flight data 即可加入航班的數據.
在這里插入圖片描述
可以使用以下語句查看航班數據:

POST /kibana_sample_data_flights/_search
{"query": {"match_all": {}}
}

下面來看看常用的 SQL 如何編寫.

1. WHERE

我們過濾出目的地為 US 的數據:

POST /_sql?format=txt
{"query": "SELECT FlightNum, OriginWeather, OriginCountry, Carrier FROM kibana_sample_data_flights WHERE DestCountry = 'US'"
}

查詢結果如下:

   FlightNum   |   OriginWeather   | OriginCountry |    Carrier     
---------------+-------------------+---------------+----------------
R43CELD        |Cloudy             |US             |JetBeats        
3YAQM9U        |Clear              |US             |JetBeats        
8SHQI41        |Cloudy             |US             |JetBeats        
HF9AP10        |Sunny              |US             |JetBeats        
ZTL6FPB        |Heavy Fog          |IT             |ES-Air          
TF9BTQL        |Clear              |JP             |Kibana Airlines 
T9QK7GX        |Clear              |IN             |Logstash Airways
4AHGESO        |Rain               |ZA             |Kibana Airlines 
J684XSR        |Sunny              |AR             |JetBeats        
T390OH4        |Cloudy             |IN             |ES-Air          
Q33SYKK        |Sunny              |KR             |JetBeats        
JBQ50Y2        |Clear              |IT             |Logstash Airways

2. GROUP BY

可以使用 GROUP BY 語句對數據進行分組聚合統計操作,例如查詢航班分組的平均飛行距離等.其示例如下:

POST /_sql?format=txt
{"query": "SELECT count(*),max(DistanceMiles), avg(DistanceMiles) FROM kibana_sample_data_flights GROUP BY DestCountry"
}

如上示例,我們以目的地國家進行分組,然后統計每個分組的數量,最大的飛行距離,平均飛行距離.其結果如下:

   count(*)    |max(DistanceMiles)|avg(DistanceMiles)
---------------+------------------+------------------
46             |7600.7158203125   |3233.800320625305 
305            |12140.8603515625  |6603.605808945953 
377            |9917.6455078125   |3128.910634331741 
416            |10832.3994140625  |7915.6610843951885
944            |10600.296875      |4077.664177652133 
691            |10293.208984375   |2775.8247816469493
45             |12075.3935546875  |7542.028591579861 
1096           |12353.7802734375  |5037.134736095902 
91             |10000.7255859375  |5683.497867123111 
278            |10030.87109375    |3448.2222546090325
48             |9670.9072265625   |3278.826272328695 
237            |10575.1279296875  |5419.154288118902 
15             |10346.84765625    |3214.9680114746093

3. HAVING

可以使用 HAVING 對分組的數據進行二次篩選,比如篩選分組中記錄數大于 100 的數據,其結果如下:

POST /_sql?format=txt
{"query": "SELECT count(*),max(DistanceMiles), avg(DistanceMiles) FROM kibana_sample_data_flights GROUP BY DestCountry HAVING COUNT(*) > 100"
}

我們過濾出了分組中記錄數大于 100 的數據,其結果如下:

   count(*)    |max(DistanceMiles)|avg(DistanceMiles)
---------------+------------------+------------------
305            |12140.8603515625  |6603.605808945953 
377            |9917.6455078125   |3128.910634331741 
416            |10832.3994140625  |7915.6610843951885
944            |10600.296875      |4077.664177652133 
691            |10293.208984375   |2775.8247816469493
1096           |12353.7802734375  |5037.134736095902 
278            |10030.87109375    |3448.2222546090325
237            |10575.1279296875  |5419.154288118902 
449            |10282.5048828125  |3213.2889483309536
373            |10774.0           |5064.675941446831

4. ORDER BY

我們可以使用 ORDER BY 進行排序,例如將平均飛行距離降序排序,其結果如下:

POST /_sql?format=txt
{"query": "SELECT count(*),max(DistanceMiles), avg(DistanceMiles) as avgDistance FROM kibana_sample_data_flights GROUP BY DestCountry HAVING COUNT(*) > 100 ORDER BY avgDistance desc"
}

如上示例,我們將數據用平均距離排序,其結果為

   count(*)    |max(DistanceMiles)|   avgDistance    
---------------+------------------+------------------
416            |10832.3994140625  |7915.6610843951885
305            |12140.8603515625  |6603.605808945953 
283            |10556.7587890625  |6030.0211101842015
237            |10575.1279296875  |5419.154288118902 
214            |11447.2265625     |5323.084783429297 
774            |11407.380859375   |5280.042444507589 
116            |10553.98828125    |5118.16688169282  
373            |10774.0           |5064.675941446831

5. 分頁

分頁有多種實現方式,可以使用 limit,top,fetch_size 來進行分頁.

1,使用limit 分頁操作

POST /_sql?format=txt
{"query": "SELECT FlightNum, OriginWeather, OriginCountry, Carrier FROM kibana_sample_data_flights WHERE DestCountry = 'US' limit 10"
}

2,使用 top 進行分頁

POST /_sql?format=txt
{"query": "SELECT top 10 FlightNum, OriginWeather, OriginCountry, Carrier FROM kibana_sample_data_flights WHERE DestCountry = 'US'"
}

3,使用 fetch_size 進行分頁

POST /_sql?format=txt
{"query": "SELECT FlightNum, OriginWeather, OriginCountry, Carrier FROM kibana_sample_data_flights WHERE DestCountry = 'US'","fetch_size": 10
}

其結果如下:

   FlightNum   | OriginWeather | OriginCountry |    Carrier     
---------------+---------------+---------------+----------------
R43CELD        |Cloudy         |US             |JetBeats        
3YAQM9U        |Clear          |US             |JetBeats        
8SHQI41        |Cloudy         |US             |JetBeats        
HF9AP10        |Sunny          |US             |JetBeats        
ZTL6FPB        |Heavy Fog      |IT             |ES-Air          
TF9BTQL        |Clear          |JP             |Kibana Airlines 
T9QK7GX        |Clear          |IN             |Logstash Airways
4AHGESO        |Rain           |ZA             |Kibana Airlines 
J684XSR        |Sunny          |AR             |JetBeats        
T390OH4        |Cloudy         |IN             |ES-Air

6. 子查詢

ES SQL 是可以支持類似于 SELECT X FROM (SELECT * FROM Y) 這樣簡單的子查詢的

POST /_sql?format=txt
{"query": "SELECT avg(data.DistanceMiles) from (SELECT FlightNum, OriginWeather, OriginCountry, Carrier, DistanceMiles FROM kibana_sample_data_flights WHERE DestCountry = 'US') as data"
}

其結果如下:

avg(data.DistanceMiles)
-----------------------
4714.944895442431

參考資料

官方文檔:xpack-sql{target=“_blank”}

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/news/165316.shtml
繁體地址，請注明出處：http://hk.pswp.cn/news/165316.shtml
英文地址，請注明出處：http://en.pswp.cn/news/165316.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

ESP32之避障

ESP32之避障

ESP32之避障 圖片 程序 int Led27;//定義LED 接口 int buttonpin4; //定義光遮斷傳感器接口 int val;//定義數字變量val void setup() { pinMode(Led,OUTPUT);//定義LED 為輸出接口 pinMode(buttonpin,INPUT);//定義避障傳感器為輸出接口 } void loop() {Serial.begin(9600);…
閱讀更多...
保姆級 Keras 實現 YOLO v3 一

保姆級 Keras 實現 YOLO v3 一

保姆級 Keras 實現 YOLO v3 一 一. YOLO v3 總覽二. 特征提取網絡特征提取網絡代碼實現 三. 特征融合特征融合代碼實現 四. 網絡輸出模型輸出代碼實現 五. 網絡模型代碼實現六. 代碼下載 如果要給 YOLO 目標檢測算法一個評價的話, 就是快和準, 現在已經到了 v8, 但是我為什么還…
閱讀更多...
如何開啟MySQL的慢查詢日志

如何開啟MySQL的慢查詢日志

說明&#xff1a;如果需要查看某一條SQL查詢速度慢&#xff0c;并對慢的SQL進行優化&#xff0c;那么開啟MySQL慢查詢日志是一定要做的事情&#xff0c;本文介紹如何開啟MySQL的慢查詢日志&#xff1b; 查看MySQL慢查詢是否開啟 首先&#xff0c;輸入下面的命令&#xff0c;查…
閱讀更多...
為什么 x86 操作系統從 0x7c00 處開始

為什么 x86 操作系統從 0x7c00 處開始

0x00&#xff1a;x86 架構 BIOS 引導加載程序中的"0x7C00"之謎 你知道 x86 操作系統中的"0x7C00"這個神奇數字嗎 ? "0x7C00" 是BIOS加載MBR&#xff08;主引導記錄&#xff0c;磁盤中的第一個扇區&#xff09;的內存地址。操作系統或引導加載…
閱讀更多...
2-Linux學習環境搭建

2-Linux學習環境搭建

1 Linux學習環境搭建 1.1 虛擬化介紹 # win 機器----》裝一個虛擬化軟件----》虛擬化出linux操作系統# kvm vmware openstack docker k8s # kvm vmware 虛擬化軟件 -運行在linux上&#xff0c;做虛擬化的軟件 -vmware運行在win&#xff0c;linux&#xff0c;商業軟件…
閱讀更多...
AMEYA360：瑞薩面向高端工業傳感器系統推出高精度模擬前端的32位RX MCU

AMEYA360：瑞薩面向高端工業傳感器系統推出高精度模擬前端的32位RX MCU

全球半導體解決方案供應商瑞薩電子&#xff08;TSE&#xff1a;6723&#xff09;宣布面向高端工業傳感器系統推出一款全新RX產品——RX23E-B&#xff0c;擴展32位微控制器&#xff08;MCU&#xff09;產品線。新產品作為廣受歡迎的RX產品家族的一員&#xff0c;具有高精度模擬前…
閱讀更多...
hadoop2.x linux集群部署

hadoop2.x linux集群部署

hadoop2.x 集群部署 下載hadoop需要提前準備好jdk1.8 和rsync 和ssl集群信息解壓安裝配置環境變量配置site配置文件(/hadoop/etc/hadoop目錄下)core-site.xmlhdfs-site.xmlyarn-site.xmlmapred-site.xmlhadoop-env.sh要追加java_home!配置節點slaves 配置免密ssh訪問沒有ssh-co…
閱讀更多...
【計算方法與科學建模】矩陣特征值與特征向量的計算（四）：乘冪法及其python實現

【計算方法與科學建模】矩陣特征值與特征向量的計算（四）：乘冪法及其python實現

文章目錄 一、Jacobi 旋轉法二、Jacobi 過關法三、Householder 方法四、乘冪法 矩陣的特征值&#xff08;eigenvalue&#xff09;和特征向量&#xff08;eigenvector&#xff09;在很多應用中都具有重要的數學和物理意義。 本文將詳細介紹乘冪法的基本原理和步驟&#xff0c;并…
閱讀更多...
【JavaSE】基礎筆記 - 異常（Exception）

【JavaSE】基礎筆記 - 異常（Exception）

目錄 1、異常的概念和體系結構 1.1、異常的概念 1.2、 異常的體系結構 1.3 異常的分類 2、異常的處理 2.1、防御式編程 2.2、異常的拋出 2.3、異常的捕獲 2.3.1、異常聲明throws 2.3.2、try-catch捕獲并處理 3、自定義異常類 1、異常的概念和體系結構 1.1、異常的…
閱讀更多...
datasets.Dataset.map方法學習筆記

datasets.Dataset.map方法學習筆記

Dataset.map 方法概要 可以將datasets中的Dataset實例看做是一張數據表。map方法會將輸入的function按照指定的方式應用在每一行&#xff08;每一行稱為一個example&#xff09;上。本文采用一下示例進行說明&#xff1a; from datasets import Dataset # datasets.__versi…
閱讀更多...
前端開發工具集合

前端開發工具集合

文章目錄 Visual Studio Code (VS Code)安裝及配置一、安裝二、常用插件三、相關配置四、統一配置代碼 Visual Studio Code (VS Code)安裝及配置 一、安裝 下載地址&#xff1a;https://code.visualstudio.com/ ?> VS Code 下載慢&#xff0c;解決辦法請點擊 雙擊下載文件…
閱讀更多...
Java零基礎——SpringMVC篇

Java零基礎——SpringMVC篇

1.SpringMVC介紹 SpringMVC是Spring框架中的一個組件&#xff0c;是一個輕量級的web的MVC框架&#xff0c;充當controller,其本質就是一個Servlet。 1.1 傳統Servlet的不足 每個請求&#xff0c;都需要定義一個Servlet。雖然可以在service方法中&#xff0c;根據業務標識進行…
閱讀更多...
【Linux 帶寬限速】trickle，限制docker 上傳速度

【Linux 帶寬限速】trickle，限制docker 上傳速度

限制docker 上傳速度 然而&#xff0c;你可以使用第三方工具來實現這個目的。一個常用的工具是 trickle&#xff0c;它可以模擬網絡帶寬。 首先&#xff0c;你需要安裝 trickle。在 Ubuntu 上&#xff0c;可以使用以下命令安裝&#xff1a; sudo apt-get install trickle然后…
閱讀更多...
deepxde 源碼閱讀筆記（長期更新）

deepxde 源碼閱讀筆記（長期更新）

2023.11.23 閱讀的deepxde version: 1.9.0 1. train_aux_vars &#xff0c;即pde當中的第三個參數 這個變量的含義困惑很久。最后發現就是operator learning的PDEs方程parameters。 脈絡&#xff1a;def pde aux_vars-> deepxde目前支持tf1最多&#xff0c;但是對其他框架…
閱讀更多...
系列二、Spring整合單元測試

系列二、Spring整合單元測試

一、概述 Spring中獲取bean最常見的方式是通過ClassPathXmlApplicationContext 或者 AnnotationConfigApplicationContext的getBean()方式獲取bean&#xff0c;那么在Spring中如何像在SpringBoot中直接一個類上添加個SpringBootTest注解&#xff0c;即可在類中注入自己想要測試…
閱讀更多...
優秀的企業協同OA系統如何促進團隊協作？

優秀的企業協同OA系統如何促進團隊協作？

優秀的企業協同OA系統可以幫助團隊協同工作變得更簡單、更高效。 一、以下是一些優秀的企業協同OA系統可以幫助團隊協作的方法&#xff1a; 1、提高工作效率 優秀的企業協同OA系統可以提高工作效率&#xff0c;減少工作的重復性和低效率。例如&#xff0c;可以使用共享日歷和…
閱讀更多...
vue2生命周期

vue2生命周期

前言 vue的生命周期其實可以分為兩塊,一個是vue實例的生命周期,一個是組件的生命周期。 vue實例的生命周期方法共有4個:$mout,$forceUpdate,$nextTick,$destroy vue組件的生命周期鉤子共有8個:beforeCreate,created,beforeMount,mounted,beforeUpdate, updated,beforeDestr…
閱讀更多...
C/C++小寫字母的判斷 2022年3月電子學會中小學生軟件編程（C/C++）等級考試一級真題答案解析

C/C++小寫字母的判斷 2022年3月電子學會中小學生軟件編程（C/C++）等級考試一級真題答案解析

目錄 C/C小寫字母的判斷 一、題目要求 1、編程實現 2、輸入輸出 二、算法分析 三、程序編寫 四、程序說明 五、運行結果 六、考點分析 C/C小寫字母的判斷 2022年3月 C/C編程等級考試一級編程題 一、題目要求 1、編程實現 輸入一個字符&#xff0c;判斷是否是英文小…
閱讀更多...
iframe內部子頁面與外部主頁面通訊

iframe內部子頁面與外部主頁面通訊

文章目錄 一、問題二、解決2.1、子頁面2.2、主頁面 三、知識點3.1、[瀏覽器兼容性](https://developer.mozilla.org/zh-CN/docs/Web/API/Window/postMessage#%E6%B5%8F%E8%A7%88%E5%99%A8%E5%85%BC%E5%AE%B9%E6%80%A7)3.2、詳解3.2.1、發送方3.2.2、接收方 一、問題 如上所示&a…
閱讀更多...
被刪數據庫+勒索

被刪數據庫+勒索

被刪數據庫勒索 – WhiteNights Site 標簽&#xff1a;日志 快照在手&#xff0c;天下我有 服務器又給人黑了。這次是給人黑了數據庫&#xff0c;被人刪庫勒索&#xff0c;先放圖吧。我還花了今天一個下午的時間查修這個漏洞&#xff0c;導致網站有接近6個小時都沒法訪問。 …
閱讀更多...
最新文章

Copyright @ 2022~2023