Elasticsearch 聚合統計與SQL聚合統計文法對比（一）

　　Es相比關系型資料庫在資料檢索方面有着極大的優勢，在處理億級資料時，可謂是毫秒級響應，我們在使用Es時不僅僅進行簡單的查詢，有時候會做一些資料統計與分析，如果你以前是使用的關系型資料庫，那麼Es的資料統計跟關系型資料庫還是有很大的差別的，是以，這篇内容，為了更好的了解，我簡單對比了Es中統計的寫法與關系型資料庫的寫法。

　　首先，先了解一下Es中關于聚合的概念：

　　　　1：桶（Buckets）滿足特定條件的文檔的集合；

　　　　2：名額（Metrics）對桶内的文檔進行統計計算

這兩個概念是什麼意思？先看下面一段T-SQL統計代碼：

SELECT Color,SUM(1) as Nums【2】
FROM #Cars
GROUP BY Color 【1】

　　桶：滿足特定條件的集合，這個很好了解，比如可以把藍色的放到藍色的桶裡，綠色的放到綠色的桶裡，桶是用來存放不同類型的集合。SQL代碼中【1】就可以了解對桶進行分組，有多少種顔色，就會有幾種不同的桶。桶類似于SQL中GROUP BY；

　　名額：對桶内的資料進行統計計算。SQL代碼中【2】就可以了解為名額，每個桶裡有多少條記錄。名額類似于SQL中各種彙總，如Count(),Sum(),Max(),Min()；

概念了解之後，對比來了，我們來做一組資料:

1. 建立表結構并填充資料

1.1建立SQLSERVER結構與資料

CREATE TABLE #Cars
(
	ID int IDENTITY(1,1) NOT NULL,	　　　　　　　　　 --建立自增序列
	Price int,					--價格			
	Color varchar(50),				--顔色
	Make varchar(50),				--品牌
	Sold datetime,					--銷售日期
	Primary key(ID)				        --定義ID為臨時表#Cars的主鍵
);
INSERT INTO #Cars(Price,Color,Make,Sold) VALUES (10000,'紅色','漢蘭達','2014-10-28');
INSERT INTO #Cars(Price,Color,Make,Sold) VALUES (20000,'紅色','漢蘭達','2014-11-05');
INSERT INTO #Cars(Price,Color,Make,Sold) VALUES (30000,'綠色','福特','2014-05-18');
INSERT INTO #Cars(Price,Color,Make,Sold) VALUES (15000,'藍色','豐田','2014-11-05');
INSERT INTO #Cars(Price,Color,Make,Sold) VALUES (12000,'綠色','豐田','2014-07-02');
INSERT INTO #Cars(Price,Color,Make,Sold) VALUES (20000,'紅色','漢蘭達','2014-11-05');
INSERT INTO #Cars(Price,Color,Make,Sold) VALUES (80000,'紅色','寶馬','2014-01-01');
INSERT INTO #Cars(Price,Color,Make,Sold) VALUES (25000,'藍色','福特','2014-02-12');

1.2建立Elastsearch 結構與資料

POST /testindex/cars/_bulk
{ "index": {}}
{ "price" : 10000, "color" : "紅色", "make" : "漢蘭達", "sold" : "2014-10-28" }
{ "index": {}}
{ "price" : 20000, "color" : "紅色", "make" : "漢蘭達", "sold" : "2014-11-05" }
{ "index": {}}
{ "price" : 30000, "color" : "綠色", "make" : "福特", "sold" : "2014-05-18" }
{ "index": {}}
{ "price" : 15000, "color" : "藍色", "make" : "豐田", "sold" : "2014-07-02" }
{ "index": {}}
{ "price" : 12000, "color" : "綠色", "make" : "豐田", "sold" : "2014-08-19" }
{ "index": {}}
{ "price" : 20000, "color" : "紅色", "make" : "漢蘭達", "sold" : "2014-11-05" }
{ "index": {}}
{ "price" : 80000, "color" : "紅色", "make" : "寶馬", "sold" : "2014-01-01" }
{ "index": {}}
{ "price" : 25000, "color" : "藍色", "make" : "福特", "sold" : "2014-02-12" }

2. 統計查詢對比

上面的代碼中，分别建立了Es與SQLSERVER的資料結構，并且填充了一些資料。接下來，我們來舉幾個統計的例子，來看看他們兩個之間的統計代碼分别怎麼寫。

2.1 統計哪個顔色的銷量最好？

【SQLSERVER實作】

SELECT Color,SUM(1) as SalesNum 
FROM #Cars
GROUP BY Color

結果如下圖：

【Elasticsearch 實作】

GET  testindex/cars/_search
{
    "size": 0, 【3】
    "aggs": {【1】
      "SalesNum": { 【2】
        "terms": {【4】
          "field": "color.keyword",
          "size": 10
        }
      }
    }
}

【1】：如果想要進行統計分析，統計代碼需要寫在aggs中，aggs是aggregations 的簡稱，也可以寫作 aggregations。

【2】：是指定的列的名稱，作用同SQLSERVER統計中as 重命名。

【3】：這裡設定了傳回值為0，因為這個查詢不僅僅傳回了我們的統計的内容，還傳回了搜尋結果的内容，這裡我們并不需要搜尋結果的内容，是以設定為0.

【4】：這裡定義了桶的類型，如果需要不同的統計内容，這些需要使用不同的統計類型。

2.2 按顔色統計出平均價格？

SELECT Color,AVG(Price) as '平均價格'
FROM #Cars
GROUP BY Color

GET testindex/cars/_search
{
  "size": 0,
  "aggs": {
    "s": {
      "terms": {
        "field": "color.keyword",
        "size": 10
      },
      "aggs": {
        "avg_price": {
          "avg": {
            "field": "price"
          }
        }
      }
    }
  }
}

2.3 按照顔色統計出平均價格、最高價格、最低價格？

SELECT Color,AVG(Price) as '平均價格',MIN(Price) as MinPrice,MAX(Price) as MaxPrice
FROM #Cars
GROUP BY Color

參考：https://elasticsearch.cn/question/4799

2.4 統計每一個企業品牌的最低價格和最高價格？

SELECT Make,MIN(Price) as MinPrice,MAX(Price) as MaxPrice
FROM #Cars
GROUP BY Make

GET testindex/cars/_search
{
  "size": 0
  ,"aggs": {
    "make": {
      "terms": {
        "field": "make.keyword"
      }
      ,"aggs": {
        "price_age": {
          "avg": {
            "field": "price"
          }
        },
        "min_price": {
          "min": {
            "field": "price"
          }
        }
        ,"max_price":{
          "max": {
            "field": "price"
          }
        }
      }
    }
  }
}

通過上面的幾個示例，我簡單總結了幾個SQLSever 中彙總函數與Es 的對比，看下面的表格：

SQLSERVER函數	Agg_Type	功能說明
GROUP BY 字段名稱	Terms （避免使用分詞字段用來分組）	分組、Es劃分桶
Max（）函數	Max	求最大值
Min（）函數	Min	求最小值
Avg（）函數	Avg	求平均值

今天就先對比下簡單的聚合彙總、求平均值統計，明天再對比下其他的，比如日期的聚合以及聚合的排序等。