Elasticsearch使用script实现时间差值计算统计数据量

elasticsearch duery dsl做时间差值计算

一、SQL

<!--sql转为dsl查询-->
SELECT * FROM news where pubtime>='2020-03-23 00:00:00' and pubtime<'2020-03-24 00:00:00' and source="TRS"  and (crawle_time -pubtime) <= 5分钟；

二、QUERY DSL

将‘一’中的SQL转写为dsl语句。

http://localhost:9210/news_small/monitor_caiji_small/_search
{
    "aggs": {
        "it": {
            "terms": {
                "field": "it",
                "order": {
                    "_count": "DESC"
                },
                "shard_size": 100000,
                "size": 10
            }
        },
        "field_count": {
            "cardinality": {
                "precision_threshold": "100000",
                "field": "it"
            }
        }
    },
    "query": {
        "bool": {
            "must": [
                {
                    "term": {
                        "source_type": "TRS"
                    }
                },
                {
                    "script": {
                        "script": {
                            "inline": "(((doc['crawler_time'].value - doc['pubtime'].value)/(3600000.0/60)>= 0 && ((doc['crawler_time'].value - doc['pubtime'].value)/(3600000.0/60)) <=5))"
                        }
                    }
                }
            ],
            "filter": {
                "bool": {
                    "must": [
                        {
                            "range": {
                                "pubtime": {
                                    "gte": "2020-03-23 00:00:00",
                                    "lt": "2020-03-24 00:00:00"
                                }
                            }
                        }
                    ]
                }
            }
        }
    },
    "size": 0
}

三、SCRIPT调试方法

Painless有一个实用工具方法Debug.explain，它会为你抛出异常。 例如，您可以使用_explain来探索script query可用的上下文。

POST /index/_explain/1
{
  "query": {
    "script": {
      "script": "Debug.explain(doc.goals)"
    }
  }
}