前端之家

  1. 首页
  2. 问答

如何将SQL查询转换为ElasticSearch样式？

• 问答

我有一个针对 MySQL 的复杂查询，该查询计算每天的唯一访问量。我想使用 ElasticSearch 使其更快。

想象这样一个表：

  • id | site_id | created_at ________ | stat_cookie_hash
  • 10 | 12346 | 2019-11-11 15:42:24 | sf2f2eghasdh2nvr
  • 11 | 12346 | 2019-10-26 09:41:09 | jk67j97jkhjghklj
  • 12 | 12346 | 2019-10-26 14:25:01 | vnuoi96iyoui60yu
  • 13 | 12346 | 2019-09-01 18:12:54 | zxcqscgscv1vcdcv
查询的

结果如下：

  • 2019-11-11 | 1
  • 2019-10-26 | 2
  • 2019-09-01 | 1

SQL 查询本身：

    SELECT day,COUNT(*) as cnt
    FROM (SELECT DISTINCT stat_cookie_hash,DATE(DATE_ADD(created_at,INTERVAL :timezone MINUTE)) as day
        FROM stat_visitor
        WHERE site_id=:site_id) AS T
    GROUP BY day
    ORDER BY day DESC

：timezone是一个整数，例如180

：site_id是一个整数，例如40

同一个，但有替换： 

    SELECT day,INTERVAL 180 MINUTE)) as day
        FROM stat_visitor
        WHERE site_id=40) AS T
    GROUP BY day
    ORDER BY day DESC

ElasticSearch版本：

{
    "version" : {
        "number" : "7.4.1","build_flavor" : "default","build_type" : "rpm","build_hash" : "fc0eeb6e2c25915d63d871d344e3d0b45ea0ea1e","build_date" : "2019-10-22T17:16:35.176724Z","build_snapshot" : false,"lucene_version" : "8.2.0","minimum_wire_compatibility_version" : "6.8.0","minimum_index_compatibility_version" : "6.0.0-beta1"
      }
}

我是ElasticSearch的新手，所以我不知道如何管理它。请帮帮我！

如果您了解Yii2 Framework，并且能向我展示如何重写具有yii2-elasticsearch扩展的SQL查询，那将是非常不错的。

tianyuwei 回答：如何将SQL查询转换为ElasticSearch样式？

Yii2 elasticsearch guide将为您提供帮助。

但我认为这不会帮助您加快速度。您可以使用memcache并缓存sql结果。

我建议您将访问计数存储在缓存中并定期更新sql。

考虑elasticsearch没有'GROUP BY'

,

我找到了灵魂。

首先，我从查询中删除了时区和DATE，DATE_ADD函数。

然后我将其转换为ElasticSearch格式。它是集成的PHP版本：

    public static function elasticSearch($site_id,$page_id=null,$markup_id=null,$started_at=null,$group_by='created_day')
    {
        $index = 'stat_visitor';

        $client = ClientBuilder::create()
            ->setSelector('\Elasticsearch\ConnectionPool\Selectors\RoundRobinSelector')
            ->build();

        $params = [
            'index' => $index,'size' => 0,'body' => [
                'aggs' => [
                    'cnt' => [
                        'terms' => [
                            'field' => $group_by,'size' => 9999,'order' => ['_term' => 'desc'],],'aggs' => [
                            'unique_hashes' => [
                                'cardinality' => [
                                    'field' => 'stat_cookie_hash'
                                ]
                            ]
                        ]
                    ]
                ],'query' => [
                    'bool' => [
                        'must' => [
                            ['term' => ['site_id' => $site_id]],'sort' => ['created_day' => 'desc'],]
        ];

        if ($page_id) {
            $params['body']['query']['bool']['must'][] = ['term' => ['page_id' => $page_id]];
        }

        if ($markup_id) {
            $params['body']['query']['bool']['must'][] = ['term' => ['markup_id' => $markup_id]];
        }

        if ($started_at) {
            $params['body']['query']['bool']['filter'][] = ['range' => ['created_at' => [ 'gte' => $started_at]]];
        }

        $response = $client->search($params);

        $result = [];
        foreach ($response['aggregations']['cnt']['buckets'] as $bucket) {
            $result[$bucket['key_as_string']] = $bucket['unique_hashes']['value'];
        }

        return $result;
    }
elasticsearchmysqlyii2
本文链接：https://www.f2er.com/3121957.html

大家都在问