SQLクエリをElasticSearchスタイルに変換する方法は？

debugcn 投稿 Dev

ドイツのKhokhlov

毎日のユニーク訪問数をカウントする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, COUNT(*) as cnt
    FROM (SELECT DISTINCT stat_cookie_hash, DATE(DATE_ADD(created_at, 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クエリを書き直す方法を教えていただければ幸いです。

ドイツのKhokhlov

私は魂を見つけました。

まず、クエリからタイムゾーンと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;
    }

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]