from elasticsearch import Elasticsearch
import time
def query_day():
query = {
"query":{
"bool":{
"must":{
"range":{
"photo.tempTime":{
"gte":dt2,
"lte": timeStamp
}
}
}
}
},
"size":0,
"aggs": {
"group_by_ymdh": {
"terms": {"size":50,"field": "ymdh.keyword"}, #size设置返回聚合查询数据条数
"aggs": {
"group_by_camera.deviceId": {
"terms": {"field": "camera.deviceId"},
}
}
}
},
}
allDoc = es.search(index='ccat1', doc_type='demo0', body=query)
a = allDoc["aggregations"]["group_by_ymdh"]["buckets"]
for each_a in a: # each_a:everyday data
date = each_a["key"] # date
b = each_a["group_by_camera.deviceId"]["buckets"]
for each_b in b:
camera_name = each_b["key"]
cur = conn.cursor()
a = cur.execute(" select * from camera")
info = cur.fetchmany(a)
for each in info:
if each[17] == camera_name:
camera_id = (each[0])
company = (each[2])
doc_count = each_b["doc_count"]
cur = conn.cursor()
sqli = "insert into test values(%s,%s,%s,%s,%s,%s)"
cur.execute(sqli,(None,date[-4:-2]+":"+date[-2:],doc_count,camera_id,company,'1'))
cur.close()
conn.commit()
print("date:",date[-4:-2]+":"+date[-2:] ,"camera_id:",camera_id , "doc_count:",doc_count, "company:",company)
if __name__ == '__main__':
timeStamp = int(time.time())
zzz = time.localtime(time.time())
dt = time.strftime("%Y-%m-%d", zzz) + " 00:00:00"
dt1 = time.strptime(dt, "%Y-%m-%d %H:%M:%S")
dt2 = int(time.mktime(dt1))
es = Elasticsearch(['192.358.12.34:9200'])
query_day()
conn.close()
上面是聚合嵌套,下面是聚合内放置多字段
query = {"query": {
"bool": {
"must": [
{"range": {'ymd':{
'gte':start_date,
'lte':end_date
}}
},{"term": {
"camera.deviceId": param
}
}]
}
},
'size':1000,
"aggs": {
"all_ymd": {
"terms": {"size":5000000,"field": "ymd"},
"aggs": {
"capturenum_count": {
"terms": {"field": "photo.capturenum"}
},
"capture_num_count": {
"terms": {"field": "capture_num"}
}
}
}
},
}