统计语法

1.统计总数count(id)

{
    "index": "index",
    "type": "type",
    "from": 0,
    "size": 0,
    "body": {
        "sort": {
            "created_at": "desc"
        },
        "query": {
            "bool": {
                "filter": [],
                "must": [{
                    "match": {
                        "shop_id": 13299420
                    }
                }],
                "must_not": []
            }
        },
        "_source": {
            "includes": ["COUNT"],
            "excludes": []
        },
        "aggregations": {
            "count": {
                "value_count": {
                    "field": "id"
                }
            }
        }
    }
}
// php语句
$searchParams = [
    'index' => INDEX,
    'type' => TYPE,
    'from' => $offset,          // 分頁
    "size" => $limit,           // 條目
    'body' => [
        'sort' => [
            $column => $sort
        ],
        'query' => [
            'bool' => [
                'filter' => [],
                'must' => $masts,
                'must_not' => $must_not,
            ]
        ],
        '_source'=>[
            'includes' => [
                0 => 'COUNT',
            ],
            'excludes' => [
            ],
        ],
        'aggregations'=>[
            'count' => [
                'value_count' => [
                    'field' => 'id',
                ],
            ],
        ]
    ]
];

2.sum group by统计

// sql语法
SELECT 
  SUM(order_amount) 
FROM
  table
WHERE shop_id = 13299420 
  AND guider_id IN (
    53507531,
    53507529,
    53507527,
    53507519,
    53507509,
    53507507,
    53507505,
    53507431,
    53507424,
    53507419
  ) 
GROUP BY guider_id ;
// ES语法
{
    "index": "index",
    "type": "type",
    "from": 0,
    "size": 0,
    "body": {
        "query": {//query是where条件
            "bool": {
                "filter": {
                    "terms": {
                        "guider_id": [53507531, 53507529, 53507527, 53507519, 53507509, 53507507, 53507505, 53507431, 53507424, 53507419]
                    }
                },
                "must": {
                    "match": {
                        "shop_id": 13299420
                    }
                }
            }
        },
        "aggregations": {
            "guider_id": {
                "terms": {
                    "field": "guider_id",// 相当于group by guider_id
                    "size": 200
                },
                "aggregations": {
                    "order_sum": {
                        "sum": {
                            "field": "order_amount"// 相当于 sum(order_amount) as order_sum
                        }
                    }
                }
            }
        }
    }
}

PHP语句

$orderSumSearchParams = [
    'index' => ES_GUIDER_ORDER_LIST_INDEX,
    'type' => ES_GUIDER_ORDER_LIST_TYPE,
    'from' => 0,          // 分頁
    "size" => 0,           // 條目
    'body' => [
        'query' => [
            'bool' => [
                'filter' => [
                    "terms"=>[
                        "guider_id"=>$guiderIdArr
                    ]
                ],
                'must' => [
                    "match"=>[
                        "shop_id"=>$shop_id
                    ]
                ],
            ]
        ],
        "aggregations"=> [
            "guider_id"=> [
                "terms"=> [
                    "field"=> "guider_id",
                    "size"=> 200
                ],
                "aggregations"=> [
                    "order_sum"=> [
                        "sum"=> [
                            "field"=> "order_amount"
                        ]
                    ]
                ]
            ]
        ]
    ]
];

3.查询0<余额<50的会员人数和余额总数

//sql
SELECT 
  SUM(balance),
  COUNT(id) 
FROM
  merchant_member 
WHERE merchant_id = 10001460 
  AND balance > 0 
  AND balance < 50 ;
// ES
merchant_member/_search?merchant 【POST】
{
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "balance": {
              "gt": "0",
              "lt": "50"
            }
          }
        }
      ],
      "must": {
        "match": {
          "merchant_id": 100
        }
      }
    }
  },
  "aggregations": {
    "intraday_return": {
      "extended_stats": {
        "field": "balance"
      }
    }
  }
}