SQLとElasticsearchとのクエリの比較

SQLとElasticsearchのクエリがそれぞれどう対応しているのか、よく使われると思うものについて大体まとめました。

# 環境
Elasticsearch 6.2.4 〜 7.5

## 前提条件
記載する内容は以下の条件を前提とします。
#### Elasticsearchで検索する際のURLの記載は省略
Elasticsearchでは検索を行う際に以下のようなURLへリクエストします。
`{ElasticsearchのURL}/{index名}/_search`
例えばSQLでproductというテーブルに対して索引を行っている場合、Elasticsearchでは以下のURLへリクエストを行っています。
`http://localhost:9200/product/_search`

以降Elasticsearchのリクエスト先URLは記載しませんが、上述のようなURLへリクエストしている前提です。

#### 文字列のデータタイプは全てKeyword（全文検索は使わない）
Elasticsearchは全文検索が可能であり、文字列データはフィールドのデータタイプによって全文検索対象かどうかが変わります。

* 通常の文字列 : [Keyword](https://www.elastic.co/guide/en/elasticsearch/reference/current/keyword.html)
* 全文検索対象 : [Text](https://www.elastic.co/guide/en/elasticsearch/reference/current/text.html)

RDBMSでも全文検索が可能ですが利用するRDBMSによって扱い方がかなり異なるので、全文検索を行う場合はSQLと比較するよりもElasticsearch専用の構文として検索方法を理解した方が良いかなと思います。
そのため、今回は文字列のデータは全て**Keyword**のデータタイプである前提とします。

# 条件なしで検索
SQLにおけるWHERE句なしの検索は、Elasticsearchでは[Match All Query](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-match-all-query.html)を利用します。

```sql:SQL
SELECT *
FROM product
```

```json:Elasticsearch
{
  "query": {
    "match_all": {}
  }
}
```

Elasticsearchの検索結果はhitsというフィールドに格納されて返ってきます。

# 取得件数や取得開始位置を指定
SQLにおけるLIMIT(その他OFFSETだったりROWNUMだったり)を利用した取得件数や取得開始位置の指定は、Elasticsearchでは[From / Size](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-body.html#request-body-search-from-size)を利用します。

Elasticsearchがfromとsizeで2つのパラメータを指定しているので、SQLの例はLIMITとOFFSETを指定した例を記載します。（RDBMSに合わせて適宜読み替えてください）
例えば先頭5件をスキップして10件だけ取得する場合は以下のようになります。

```sql:SQL
SELECT *
FROM product
LIMIT 10 OFFSET 5
```

```json:Elasticsearch
{
  "from": 5,
  "size": 10, 
  "query": {
    "match_all": {}
  }
}
```
Elasticsearchのデフォルトでは`"from": 0, "size": 10`となっています。

# 取得する列を指定
SQLにおけるSELECT句での取得カラムの指定は、Elasticsearchでは[Source filtering](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-body.html#request-body-search-source-filtering)の機能を利用します。

```sql:SQL
SELECT
  product_name,
  product_count 
FROM product
```

```json:Elasticsearch
{
  "_source": [
    "product_name",
    "product_count"
  ],
  "query": {
    "match_all": {}
  }
}
```

# 重複行の削除
SQLにおけるSELECT句でDISTINCTキーワードを指定した重複行の削除は、Elasticsearchでは[Field Collapsing](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-body.html#request-body-search-collapse)の機能を利用します。

```sql:SQL
SELECT DISTINCT
  product_name
FROM product
```

```json:Elasticsearch
{
  "collapse": {
    "field": "product_name"
  },
  "query": {
    "match_all": {}
  }
}
```

# 条件に一致するデータを検索（WHERE句）
以降の例では特定のデータタイプの場合を記載しますが、他のデータタイプを指定する場合も基本的には同様です。
## 指定した値に等しい
SQLにおける特定の値に等しいデータの検索は、Elasticsearchでは[Term Query](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-term-query.html)を利用します。

```sql:SQL
SELECT *
FROM product
WHERE product_name = 'りんご'
```

```json:Elasticsearch
{
  "query": {
    "term": {
      "product_name": {
        "value": "りんご"
      }
    }
  }
}
```

## 指定した複数の値のいずれかに等しい（IN句）
SQLにおけるIN句を利用した検索は、Elasticsearchでは[Terms Query](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-terms-query.html)を利用します。

```sql:SQL
SELECT *
FROM product
WHERE product_name IN ('りんご', 'にんじん')
```

```json:Elasticsearch
{
  "query": {
    "terms": {
      "product_name": [
        "りんご",
        "にんじん"
      ]
    }
  }
}
```

## 指定した値が含まれている（LIKE句）
SQLにおけるLIKE句を利用した検索は、Elasticsearchでは[Wildcard Query](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-wildcard-query.html)を利用します。

```sql:SQL
SELECT *
FROM product
WHERE product_name LIKE '%じ%'
```

```json:Elasticsearch
{
  "query": {
    "wildcard": {
      "product_name": {
        "value": "*じ*"
      }
    }
  }
}
```

## 指定した値より大きい（or 小さい）
SQLにおける指定した値に対する大小を条件にした検索は、Elasticsearchでは[Range Query](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-range-query.html)を利用します。
大小の条件は以下のように指定します。

|SQL|Elasticsearch|MEMO|
|:----|:----|:----|
|>=|gte|**G**reater-**T**han or **E**qual to|
|>|gt|**G**reater-**T**han|
|<=|lte|**L**ess-**T**han or **E**qual to|
|<|lt|**L**ess-**T**han|

```sql:SQL
SELECT *
FROM product
WHERE product_count >= 10
```

```json:Elasticsearch
{
  "query": {
    "range": {
      "product_count": {
        "gte" : 10
      }
    }
  }
}
```

## 複数の条件に一致
SQLにおける複数条件を指定した検索は、Elasticsearchでは[Bool Query](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-bool-query.html)の`must`と`should`を利用します。
複数の条件は以下のように指定します。

|SQL|Elasticsearch|
|:----|:----|
|AND|must|
|OR|should|

### AND条件の場合

```sql:SQL(AND)
SELECT *
FROM product
WHERE product_name = 'りんご'
  AND product_count >= 10
```

```json:Elasticsearch(must)
{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "product_name": "りんご"
          }
        },
        {
          "range": {
            "product_count": {
              "gte": 10
            }
          }
        }
      ]
    }
  }
}
```

### OR条件の場合

```sql:SQL
SELECT *
FROM product
WHERE product_name= 'りんご'
   OR product_count >= 10
```

```json:Elasticsearch
{
  "query": {
    "bool": {
      "should": [
        {
          "term": {
            "product_name": "りんご"
          }
        },
        {
          "range": {
            "product_count": {
              "gte": 10
            }
          }
        }
      ]
    }
  }
}
```

### ANDとORの組み合わせの場合
ANDとORを組み合わせる場合はBool Queryの中にBool Queryを書くことで実現できます。

```sql:SQL
SELECT *
FROM product
WHERE product_count >= 10
  AND (product_name = 'りんご' OR product_price = 100) 
```

```json:Elasticsearch
{
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "count": {
              "gte": 10
            }
          }
        },
        {
          "bool": {
            "should": [
              {
                "term": {
                  "product_name": "りんご"
                }
              },
              {
                "term": {
                  "product_price": 100
                }
              }
            ]
          }
        }
      ]
    }
  }
}
```
この書き方以外にも[minimum_should_match](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-minimum-should-match.html)というパラメータを利用して同じ検索結果を得る方法があるのですが、SQLと比べた場合には上述の方がわかりやすいかなと思います。

## 指定した条件に一致しない
SQLにおける指定した条件に一致しない場合の検索は、Elasticsearchでは[Bool Query](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-bool-query.html)の`must_not`を利用します。

```sql:SQL
SELECT *
FROM product
WHERE product_name <> 'りんご'
```

```json:Elasticsearch
{
  "query": {
    "bool": {
      "must_not": [
        {
          "term": {
            "product_name": "りんご"
          }
        }
      ]
    }
  }
}
```

# 取得するデータを並び変える（ORDER BY句）
SQLにおけるORDER BY句を利用した並び替えは、Elasticsearchでは[Sort](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-body.html#request-body-search-sort)の機能を利用します。

```sql:SQL
SELECT *
FROM product
ORDER BY product_name ASC
```

```json:Elasticsearch
{
  "query": {
    "match_all": {}
  },
  "sort": [
    {
      "product_name": {
        "order": "asc"
      }
    }
  ]
}
```

# データを集計する
Elasticsearchでの集計には[Aggregations](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations.html)を利用します。
検索に比べるとElasticsearch特有の書き方がやや強いので少しわかりにくいかもしれませんが、基本的な構文は以下の通りです。

```json
{
  "aggs": {
    "{Aggregation名(わかりやすければ何でもOK)}": {
        "{Aggregationの種類}": {}
  }
}
```

Elasticsearchの集計結果はhitsではなくaggregationsというフィールドに格納されて返ってきます。
検索結果は通常通りhitsの方に格納されて一緒に返ってくるので、検索結果が不要な場合は`size`を0に指定しておくと良いです。

## 指定した列の平均値や最大値を求める（集計関数）
SQLにおける集計関数を利用したデータ集計は、Elasticsearchでは[Metrics Aggregations](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics.html)を利用します。
SQLの集計関数とElasticsearchの対応一覧は以下の通りです。

|SQL|Elasticsearch|
|:----|:----|
|AVG|[Avg Aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-avg-aggregation.html)|
|MAX|[Max Aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-max-aggregation.html)|
|MIN|[Min Aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-min-aggregation.html)|
|SUM|[Sum Aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-sum-aggregation.html)|

```sql:SQL
SELECT
  AVG(product_count)
FROM product
```

```json:Elasticsearch
{
  "aggs": {
    "product_count_avg": {
      "avg": {
        "field": "product_count"
      }
    }
  }
}
```

## 同じ値を持つデータ毎に集計する（GROUP BY句）
SQLにおけるGROUP BY句を利用したデータ集計は、Elasticsearchでは[Terms Aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html)を利用します。

```sql:SQL
SELECT
  product_name
FROM product
GROUP BY product_name
```

```json:Elasticsearch
{
  "aggs": {
    "product_name_aggs": {
      "terms": {
        "field": "product_name",
        "size": 10
      }
    }
  }
}
```

Terms Aggregationを利用した集計は`size`に指定された上位N件までが集計されます。（例では10件）

集計関数と組み合わせたい場合はAggregationをネストさせます。

```sql:SQL
SELECT
  AVG(product_count)
FROM product
GROUP BY product_name
```

```json:Elasticsearch
{
  "aggs": {
    "product_name_aggs": {
      "terms": {
        "field": "product_name",
        "size": 10
      },
      "aggs": {
        "product_count_avg": {
          "avg": {
            "field": "product_count"
          }
        }
      }
    }
  }
}
```

## 指定した条件のデータを集計する（WHERE句との組み合わせ）
元となるデータを絞り込んだ上で集計したい場合、`query`と組み合わせることで実現できます。

```sql:SQL
SELECT
  AVG(product_count)
FROM product
WHERE product_name = 'りんご'
```

```json:Elasticsearch
{
  "size": 0,
  "query": {
    "term": {
      "product_name": {
        "value": "りんご"
      }
    }
  },
  "aggs": {
    "product_count_avg": {
      "avg": {
        "field": "product_count"
      }
    }
  }
}
```
ちょっとわかりにくいのですが、`query`で絞り込んだ結果が返却される`hits`と、集計結果が返却される`aggregations`はそれぞれ別の項目です。
集計結果のみが取得できれば良い場合、上述の例のように`size`に0を指定することで`hits`に何も返却されなくなるため、レスポンスデータが軽くなります。
（`aggregations`にはちゃんと`query`で指定した条件に一致するデータから集計した結果が返却されます）

# 集計結果に対して条件を指定する（HAVING句）
SQLにおけるHAVING句を利用した集計結果に対する検索は、Elasticsearchでは[Bucket Selector Aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline-bucket-selector-aggregation.html)を利用します。


```sql:SQL
SELECT
  product_name,
  SUM(product_count)
FROM product
GROUP BY product_name
HAVING SUM(product_count) > 10
```

```json:Elasticsearch
{
  "aggs": {
    "product_name_aggs": {
      "terms": {
        "field": "product_name",
        "size": 10
      },
      "aggs": {
        "product_count_sum": {
          "sum": {
            "field": "product_count"
          }
        },
        "product_count_sum_bucket": {
          "bucket_selector": {
            "buckets_path": {
              "count_sum": "product_count_sum"
            },
            "script": "params.count_sum > 10"
          }
        }
      }
    }
  }
}
```

`buckets_path`に絞り込み条件の対象となる項目を指定します。
例ではSUM(product_count)に相当する`product_count_sum`を条件指定の対象にしています。

`script`には絞り込み条件を記述します。
`buckets_path`で指定した値を利用したい場合は`params.count_sum`というように変数名を指定することで実現できます。
`params`は実行したクエリ内の変数を参照する場合に指定する値です。

# その他JOINとかサブクエリとか
ElasticsearchはRDBMSでは無いため、残念ながらJOINやサブクエリのような機能はありません。
登録するデータを非正規化したり、データをネストして持つことが出来る[Nested datatype](https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html)を利用したりして上手く検索できるようにデータを登録する必要があります。