elasticsearch-sql
elasticsearch-sql copied to clipboard
NLPchina
这样的子查询 es语句能转吗？
select count(DISTINCT deviceId) from uv where deviceId in (select deviceId from uv where userId = 'unknown') and uv.userId != 'unknown';
可以
select count(DISTINCT deviceId) from uv where deviceId in (select deviceId from uv where userId = 'unknown') and uv.userId <> 'unknown'
本地子查询
select deviceId from uv where userId = 'unknown'
只有一条数据：device1
{
"from": 0,
"size": 0,
"query": {
"bool": {
"filter": [
{
"bool": {
"must": [
{
"bool": {
"must": [
{
"bool": {
"should": [
{
"match_phrase": {
"deviceId": {
"query": "device1",
"slop": 0,
"zero_terms_query": "NONE",
"boost": 1
}
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
},
{
"bool": {
"must_not": [
{
"match_phrase": {
"uv.userId": {
"query": "unknown",
"slop": 0,
"zero_terms_query": "NONE",
"boost": 1
}
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
},
"_source": {
"includes": [
"COUNT"
],
"excludes": []
},
"aggregations": {
"COUNT(DISTINCT deviceId)": {
"cardinality": {
"field": "deviceId"
}
}
}
}