def test_like(self):
executor = es_query.create_executor("SELECT * FROM symbol WHERE symbol LIKE 'AAP%'")
self.assertEqual({'query': {'wildcard': {u'symbol': 'AAP*'}}}, executor.request)
executor = es_query.create_executor("SELECT * FROM symbol WHERE symbol LIKE %(param1)s")
self.assertEqual(
{'query': {'wildcard': {u'symbol': '%(param1)s'}}, '_parameters_': {
u'param1': {'path': ['query', 'wildcard', u'symbol'], 'field_hint': u'symbol'}}},
executor.request)
def test_in(self):
executor = es_query.create_executor("SELECT * FROM symbol WHERE symbol IN ('AAPL', 'GOOG')")
self.assertEqual({'query': {'terms': {u'symbol': ['AAPL', 'GOOG']}}}, executor.request)
executor = es_query.create_executor("SELECT * FROM symbol WHERE symbol IN %(param1)s")
self.assertEqual(
{'query': {'terms': {u'symbol': '%(param1)s'}},
'_parameters_': {u'param1': {'path': ['query', 'terms', u'symbol'], 'field_hint': u'symbol'}}},
executor.request)
def test_field_eq_string(self):
executor = es_query.create_executor("SELECT * FROM symbol WHERE exchange='nyse'")
self.assertEqual({'query': {'term': {'exchange': 'nyse'}}}, executor.request)
executor = es_query.create_executor("SELECT * FROM symbol WHERE exchange=%(exchange)s")
self.assertEqual({
'query': {'term': {u'exchange': '%(exchange)s'}},
'_parameters_': {u'exchange': {
'path': ['query', 'term', u'exchange'],
'field_hint': 'exchange'
}}}
, executor.request)
def test_type_merged_with_ids(self):
executor = es_query.create_executor("SELECT * FROM symbol WHERE _type = 'symbol' AND _id = '1'")
self.assertEqual({'query': {'ids': {'value': ['1'], 'type': 'symbol'}}}, executor.request)
executor = es_query.create_executor("SELECT * FROM symbol WHERE _type = 'symbol' AND _id IN ('1', '2')")
self.assertEqual({'query': {'ids': {'value': ['1', '2'], 'type': 'symbol'}}}, executor.request)
executor = es_query.create_executor("SELECT * FROM symbol WHERE _id IN ('1', '2') AND _type = 'symbol'")
self.assertEqual({'query': {'ids': {'value': ['1', '2'], 'type': 'symbol'}}}, executor.request)
executor = es_query.create_executor(
"SELECT * FROM symbol WHERE _id IN ('1', '2') AND _type = 'symbol' AND _type='abc'")
self.assertEqual({'query': {
'bool': {'filter': [{'ids': {'type': 'symbol', 'value': ['1', '2']}}, {'type': {'value': 'abc'}}]}}},
executor.request)
def test_field_gt_numeric(self):
executor = es_query.create_executor("SELECT * FROM symbol WHERE last_sale > 1000")
self.assertEqual(
{'query': {'range': {'last_sale': {'gt': 1000.0}}}},
executor.request)
executor = es_query.create_executor("SELECT * FROM symbol WHERE last_sale > %(param1)s")
self.assertEqual(
{'query': {'range': {u'last_sale': {'gt': '%(param1)s'}}},
'_parameters_': {u'param1': {
'path': ['query', 'range', u'last_sale', 'gt'],
'field_hint': 'last_sale'
}}},
executor.request)
def test_field_not_eq_numeric(self):
executor = es_query.create_executor("SELECT * FROM symbol WHERE last_sale != 1000")
self.assertEqual(
{'query': {'bool': {'must_not': {'term': {'last_sale': 1000}}}}},
executor.request)
executor = es_query.create_executor("SELECT * FROM symbol WHERE 1000 != last_sale")
self.assertEqual(
{'query': {'bool': {'must_not': {'term': {'last_sale': 1000}}}}},
executor.request)
executor = es_query.create_executor("SELECT * FROM symbol WHERE last_sale != %(param1)s")
self.assertEqual(
{'query': {'bool': {'must_not': {'term': {u'last_sale': '%(param1)s'}}}}, '_parameters_': {
u'param1': {'path': ['query', 'bool', 'must_not', 'term', u'last_sale'], 'field_hint': u'last_sale'}}},
executor.request)
def test_select_system_field(self):
executor = es_query.create_executor(
'SELECT _id, _type, _index FROM symbol')
self.assertEqual({}, executor.request)
rows = executor.select_response({
"hits": {
"hits": [{
"_score": 1.0,
"_type": "symbol",
"_id": "AVLgXwu88_EnCX8dV9PN",
"_source": {
"exchange": "nasdaq"
},
"_index": "symbol"
}],
"total":
6714,
"max_score":
1.0
},
"_shards": {
"successful": 3,
"failed": 0,
"total": 3
},
"took": 32,
"timed_out": False
})
self.assertEqual([{
'_id': 'AVLgXwu88_EnCX8dV9PN',
'_type': 'symbol',
'_index': 'symbol'
}], rows)
def test_select_expression(self):
executor = es_query.create_executor('SELECT "a.price"/2 FROM symbol')
self.assertEqual({}, executor.request)
rows = executor.select_response({
"hits": {
"hits": [{
"_score": 1.0,
"_type": "symbol",
"_id": "AVLgXwu88_EnCX8dV9PN",
"_source": {
"a": {
"price": 100
}
},
"_index": "symbol"
}],
"total":
6714,
"max_score":
1.0
},
"_shards": {
"successful": 3,
"failed": 0,
"total": 3
},
"took": 32,
"timed_out": False
})
self.assertEqual([{'"a.price"/2': 50}], rows)
def test_order_by_metric(self):
executor = es_query.create_executor(
"SELECT ipo_year, MAX(market_cap) AS c FROM symbol GROUP BY ipo_year ORDER BY c")
self.assertEqual(
{'aggs': {'ipo_year': {'terms': {'field': 'ipo_year', 'order': {'c': 'asc'}, 'size': 0},
'aggs': {'c': {'max': {'field': 'market_cap'}}}}}, 'size': 0},
executor.request)
def test_group_by_date_trunc(self):
executor = es_query.create_executor(
"SELECT year, MAX(adj_close) FROM quote WHERE symbol='AAPL' "
"GROUP BY date_trunc('year',\"date\") AS year")
self.assertEqual(
{
'query': {
'term': {
'symbol': 'AAPL'
}
},
'aggs': {
'year': {
'date_histogram': {
'field': 'date',
'interval': 'year',
'time_zone': '+08:00'
},
'aggs': {
'MAX(adj_close)': {
'max': {
'field': 'adj_close'
}
}
}
}
},
'size': 0
}, executor.request)
def test_group_by_filters(self):
executor = es_query.create_executor(
"SELECT ipo_year_range, COUNT(*) FROM symbol "
"GROUP BY CASE "
" WHEN ipo_year_range > 2000 THEN 'post_2000' "
" WHEN ipo_year_range < 2000 THEN 'pre_2000'"
" ELSE '2000' END AS ipo_year_range")
self.assertEqual(
{
'aggs': {
'ipo_year_range': {
'filters': {
'filters': {
'pre_2000': {
'range': {
'ipo_year_range': {
'lt': 2000
}
}
},
'post_2000': {
'range': {
'ipo_year_range': {
'gt': 2000
}
}
}
},
'other_bucket_key': '2000'
},
'aggs': {}
}
},
'size': 0
}, executor.request)
def test_group_by_numeric_range(self):
executor = es_query.create_executor(
"SELECT ipo_year_range, COUNT(*) FROM symbol "
"GROUP BY CASE "
" WHEN ipo_year_range >= 2000 THEN 'post_2000' "
" WHEN ipo_year_range < 2000 THEN 'pre_2000' END AS ipo_year_range"
)
self.assertEqual(
{
'aggs': {
'ipo_year_range': {
'range': {
'ranges': [{
'from': 2000.0,
'key': 'post_2000'
}, {
'to': 2000.0,
'key': 'pre_2000'
}],
'field':
'ipo_year_range'
},
'aggs': {}
}
},
'size': 0
}, executor.request)
def test_csum(self):
executor = es_query.create_executor([
"SELECT year, MAX(adj_close) AS max_adj_close, CSUM(max_adj_close) FROM quote "
"WHERE symbol='AAPL' GROUP BY date_trunc('year', \"date\") AS year"
])
self.assertEqual(
{
'query': {
'term': {
u'symbol': 'AAPL'
}
},
'aggs': {
u'year': {
'date_histogram': {
'field': u'date',
'interval': 'year',
'time_zone': '+08:00'
},
'aggs': {
u'max_adj_close': {
u'max': {
'field': u'adj_close'
}
},
'CSUM(max_adj_close)': {
'cumulative_sum': {
'buckets_path': u'max_adj_close'
}
}
}
}
},
'size': 0
}, executor.request)
def test_sum_of_squares(self):
executor = es_query.create_executor("SELECT sum_of_squares(last_sale), std_deviation(last_sale) FROM symbol")
rows = executor.select_response({
"hits": {
"hits": [],
"total": 6714,
"max_score": 0.0
},
"_shards": {
"successful": 3,
"failed": 0,
"total": 3
},
"took": 5,
"aggregations": {
"last_sale_extended_stats": {
"count": 6634,
"min": 0.0,
"sum_of_squares": 320576400178.0,
"max": 269500.0,
"sum": 17407390.0,
"std_deviation": 6437.239059099383,
"std_deviation_bounds": {
"upper": 15498.444051270819,
"lower": -10250.512185126712
},
"variance": 41438046.703994706,
"avg": 2623.965933072053
}
},
"timed_out": False
})
self.assertEqual([
{'sum_of_squares(last_sale)': 320576400178.0, 'std_deviation(last_sale)': 6437.239059099383}],
rows)
def test_order_by_metric(self):
executor = es_query.create_executor(
"SELECT ipo_year, MAX(market_cap) AS c FROM symbol GROUP BY ipo_year ORDER BY c"
)
self.assertEqual(
{
'aggs': {
'ipo_year': {
'terms': {
'field': 'ipo_year',
'order': {
'c': 'asc'
},
'size': 0
},
'aggs': {
'c': {
'max': {
'field': 'market_cap'
}
}
}
}
},
'size': 0
}, executor.request)
def test_order_by_extended_stats(self):
executor = es_query.create_executor(
"SELECT ipo_year, STD_DEVIATION(market_cap) AS s FROM symbol GROUP BY ipo_year ORDER BY s"
)
self.assertEqual(
{
'aggs': {
u'ipo_year': {
'terms': {
'field': u'ipo_year',
'order': {
'market_cap_extended_stats.std_deviation':
'asc'
},
'size': 0
},
'aggs': {
u'market_cap_extended_stats': {
'extended_stats': {
'field': u'market_cap'
}
}
}
}
},
'size': 0
}, executor.request)
def test_order_by_histogram(self):
executor = es_query.create_executor(
"SELECT ipo_year_range, MAX(market_cap) AS max_market_cap FROM symbol "
"GROUP BY histogram(ipo_year, 3) AS ipo_year_range ORDER BY ipo_year_range LIMIT 2"
)
self.assertEqual(
{
'aggs': {
'ipo_year_range': {
'aggs': {
'max_market_cap': {
'max': {
'field': 'market_cap'
}
}
},
'histogram': {
'field': 'ipo_year',
'interval': 3,
'order': {
'_key': 'asc'
},
'size': 2
}
}
},
'size': 0
}, executor.request)
def test_one_level(self):
executor = es_query.create_executor([
"WITH all_symbols AS (SELECT MAX(sum_this_year) AS max_all_times FROM symbol)",
"SELECT ipo_year, SUM(market_cap) AS sum_this_year FROM all_symbols GROUP BY ipo_year LIMIT 5"
])
self.assertEqual(
{
'aggs': {
u'max_all_times': {
u'max_bucket': {
'buckets_path': u'ipo_year.sum_this_year'
}
},
u'ipo_year': {
'terms': {
'field': u'ipo_year',
'size': 5
},
'aggs': {
u'sum_this_year': {
u'sum': {
'field': u'market_cap'
}
}
}
}
},
'size': 0
}, executor.request)
def test_filter_without_group_by(self):
executor = es_query.create_executor([
"WITH all_symbols AS (SELECT MAX(market_cap) AS max_all_times FROM symbol)",
"SELECT MAX(market_cap) AS max_at_2000 FROM all_symbols WHERE ipo_year=2000"
])
self.assertEqual(
{
'aggs': {
'level2': {
'filter': {
'term': {
u'ipo_year': 2000
}
},
'aggs': {
u'max_at_2000': {
u'max': {
'field': u'market_cap'
}
}
}
},
u'max_all_times': {
u'max': {
'field': u'market_cap'
}
}
},
'size': 0
}, executor.request)
def test_and_or_must_use_parentheses(self):
try:
executor = es_query.create_executor(
"SELECT * FROM symbol WHERE exchange='nyse' AND sector='Technology' OR ipo_year > 1998")
except:
return
self.fail('should fail')
def test_bucket_script(self):
executor = es_query.create_executor(
[
"WITH all_estimate AS (SELECT err_count/total_count AS err_rate, COUNT(*) AS total_count "
"FROM gs_plutus_debug GROUP BY req.district)",
"WITH err AS (SELECT COUNT(*) AS err_count FROM all_estimate WHERE errno>0)",
]
)
self.assertEqual(
{
"aggs": {
"req.district": {
"terms": {"field": "req.district", "size": 0},
"aggs": {
"err": {"filter": {"range": {u"errno": {"gt": 0}}}, "aggs": {}},
u"err_rate": {
"bucket_script": {
"buckets_path": {u"total_count": "_count", u"err_count": "err._count"},
"script": {"lang": "expression", "inline": u"err_count/total_count"},
}
},
},
}
},
"size": 0,
},
executor.request,
)
def test_two_level(self):
executor = es_query.create_executor(
[
"WITH all_symbols AS (SELECT MAX(sum_this_year) AS max_all_times FROM symbol)",
"WITH finance_symbols AS (SELECT * FROM all_symbols WHERE sector='Finance')",
"SELECT ipo_year, SUM(market_cap) AS sum_this_year FROM finance_symbols GROUP BY ipo_year LIMIT 5",
]
)
self.assertEqual(
{
"aggs": {
u"max_all_times": {u"max_bucket": {"buckets_path": u"finance_symbols>ipo_year.sum_this_year"}},
"finance_symbols": {
"filter": {"term": {u"sector": "Finance"}},
"aggs": {
u"ipo_year": {
"terms": {"field": u"ipo_year", "size": 5},
"aggs": {u"sum_this_year": {u"sum": {"field": u"market_cap"}}},
}
},
},
},
"size": 0,
},
executor.request,
)
def test_or_not(self):
executor = es_query.create_executor("SELECT * FROM symbol WHERE exchange='nyse' OR NOT sector='Technology'")
self.assertEqual(
{'query': {'bool': {'should': [
{'term': {'exchange': 'nyse'}},
{'bool': {'must_not': [{'term': {'sector': 'Technology'}}]}}]}}},
executor.request)
def test_moving_average_with_named_params(self):
executor = es_query.create_executor(
[
"SELECT year, MAX(adj_close) AS max_adj_close, MOVING_Avg(max_adj_close, window=5, settings='{\"alpha\":0.8}') AS ma FROM quote "
"WHERE symbol='AAPL' GROUP BY date_trunc('year', \"date\") AS year"
]
)
self.assertEqual(
{
"query": {"term": {u"symbol": "AAPL"}},
"aggs": {
u"year": {
"date_histogram": {"field": u"date", "interval": "year", "time_zone": "+08:00"},
"aggs": {
u"max_adj_close": {u"max": {"field": u"adj_close"}},
"ma": {
"moving_avg": {
"buckets_path": u"max_adj_close",
"window": 5,
"settings": {"alpha": 0.8},
}
},
},
}
},
"size": 0,
},
executor.request,
)
def test_select_system_field(self):
executor = es_query.create_executor('SELECT _id, _type, _index FROM symbol')
self.assertEqual({}, executor.request)
rows = executor.select_response({
"hits": {
"hits": [
{
"_score": 1.0,
"_type": "symbol",
"_id": "AVLgXwu88_EnCX8dV9PN",
"_source": {
"exchange": "nasdaq"
},
"_index": "symbol"
}
],
"total": 6714,
"max_score": 1.0
},
"_shards": {
"successful": 3,
"failed": 0,
"total": 3
},
"took": 32,
"timed_out": False
})
self.assertEqual([{
'_id': 'AVLgXwu88_EnCX8dV9PN', '_type': 'symbol',
'_index': 'symbol'}],
rows)
def test_select_expression(self):
executor = es_query.create_executor('SELECT "a.price"/2 FROM symbol')
self.assertEqual({}, executor.request)
rows = executor.select_response({
"hits": {
"hits": [
{
"_score": 1.0,
"_type": "symbol",
"_id": "AVLgXwu88_EnCX8dV9PN",
"_source": {
"a": {
"price": 100
}
},
"_index": "symbol"
}
],
"total": 6714,
"max_score": 1.0
},
"_shards": {
"successful": 3,
"failed": 0,
"total": 3
},
"took": 32,
"timed_out": False
})
self.assertEqual([{
'"a.price"/2': 50}],
rows)
def test_drill_down_one_direction(self):
executor = es_query.create_executor([
"WITH all_symbols AS (SELECT MAX(market_cap) AS max_all_times FROM symbol)",
"SELECT ipo_year, MAX(market_cap) AS max_this_year FROM all_symbols GROUP BY ipo_year LIMIT 5"
])
self.assertEqual(
{
'aggs': {
'max_all_times': {
'max': {
'field': 'market_cap'
}
},
'ipo_year': {
'terms': {
'field': 'ipo_year',
'size': 5
},
'aggs': {
'max_this_year': {
'max': {
'field': 'market_cap'
}
}
}
}
},
'size': 0
}, executor.request)
def test_group_by_histogram(self):
executor = es_query.create_executor(
"SELECT ipo_year_range, COUNT(*) FROM symbol "
"GROUP BY histogram(ipo_year, 5) AS ipo_year_range")
self.assertEqual(
{'aggs': {'ipo_year_range': {'aggs': {}, 'histogram': {'field': 'ipo_year', 'interval': 5}}}, 'size': 0},
executor.request)
def test_having_by_count(self):
executor = es_query.create_executor(
"SELECT ipo_year, COUNT(*) AS ipo_count FROM symbol GROUP BY ipo_year HAVING ipo_count > 100")
self.assertEqual(
{'aggs': {u'ipo_year': {'terms': {'field': u'ipo_year', 'size': 0}, 'aggs': {'having': {
'bucket_selector': {'buckets_path': {u'ipo_count': '_count'},
'script': {'lang': 'expression', 'inline': u' ipo_count > 100'}}}}}}, 'size': 0},
executor.request)
def test_order_by_term(self):
executor = es_query.create_executor(
"SELECT ipo_year, COUNT(*) FROM symbol GROUP BY ipo_year ORDER BY ipo_year")
self.assertEqual(
{'aggs': {
'ipo_year': {'terms': {'field': 'ipo_year', 'order': {'_term': 'asc'}, 'size': 0}, 'aggs': {}}},
'size': 0},
executor.request)
def test_field_in_range_not_merged(self):
executor = es_query.create_executor("SELECT * FROM symbol WHERE last_sale > 500 AND last_sale > 600")
self.assertEqual(
{'query': {'bool': {'filter': [
{'range': {'last_sale': {'gt': 500.0}}},
{'range': {'last_sale': {'gt': 600.0}}}]
}}},
executor.request)
def test_group_by_single_value_script(self):
executor = es_query.create_executor(
"SELECT ipo_year_range, COUNT(*) FROM symbol GROUP BY ipo_year / 6 AS ipo_year_range")
self.assertEqual(
{'aggs': {'ipo_year_range': {
'terms': {'field': 'ipo_year', 'size': 0, 'script': {'lang': 'expression', 'inline': '_value / 6'}},
'aggs': {}}}, 'size': 0},
executor.request)
def test_group_by_two(self):
executor = es_query.create_executor("SELECT ipo_year, COUNT(*) FROM symbol GROUP BY ipo_year, abc")
self.assertEqual(
{'aggs': {
'ipo_year': {'terms': {'field': 'ipo_year', 'size': 0}, 'aggs': {
'abc': {'terms': {'field': 'abc', 'size': 0}, 'aggs': {}}}}},
'size': 0},
executor.request)
def test_order_by_histogram(self):
executor = es_query.create_executor(
"SELECT ipo_year_range, MAX(market_cap) AS max_market_cap FROM symbol "
"GROUP BY histogram(ipo_year, 3) AS ipo_year_range ORDER BY ipo_year_range LIMIT 2")
self.assertEqual(
{'aggs': {'ipo_year_range': {'aggs': {'max_market_cap': {'max': {'field': 'market_cap'}}},
'histogram': {'field': 'ipo_year', 'interval': 3, 'order': {'_key': 'asc'},
'size': 2}}}, 'size': 0},
executor.request)
def test_filter_without_group_by(self):
executor = es_query.create_executor([
"WITH all_symbols AS (SELECT MAX(market_cap) AS max_all_times FROM symbol)",
"SELECT MAX(market_cap) AS max_at_2000 FROM all_symbols WHERE ipo_year=2000"])
self.assertEqual(
{'aggs': {'level2': {'filter': {'term': {u'ipo_year': 2000}},
'aggs': {u'max_at_2000': {u'max': {'field': u'market_cap'}}}},
u'max_all_times': {u'max': {'field': u'market_cap'}}}, 'size': 0},
executor.request)
def test_group_by_date_trunc(self):
executor = es_query.create_executor(
"SELECT year, MAX(adj_close) FROM quote WHERE symbol='AAPL' "
"GROUP BY date_trunc('year',\"date\") AS year")
self.assertEqual(
{'query': {'term': {'symbol': 'AAPL'}}, 'aggs': {
'year': {'date_histogram': {'field': 'date', 'interval': 'year', 'time_zone': '+08:00'},
'aggs': {'MAX(adj_close)': {'max': {'field': 'adj_close'}}}}}, 'size': 0},
executor.request)
def test_drill_down_one_direction(self):
executor = es_query.create_executor([
"WITH all_symbols AS (SELECT MAX(market_cap) AS max_all_times FROM symbol)",
"SELECT ipo_year, MAX(market_cap) AS max_this_year FROM all_symbols GROUP BY ipo_year LIMIT 5"])
self.assertEqual(
{'aggs': {'max_all_times': {'max': {'field': 'market_cap'}},
'ipo_year': {'terms': {'field': 'ipo_year', 'size': 5},
'aggs': {'max_this_year': {'max': {'field': 'market_cap'}}}}}, 'size': 0},
executor.request)
def test_order_by_extended_stats(self):
executor = es_query.create_executor(
"SELECT ipo_year, STD_DEVIATION(market_cap) AS s FROM symbol GROUP BY ipo_year ORDER BY s")
self.assertEqual(
{'aggs': {u'ipo_year': {
'terms': {'field': u'ipo_year', 'order': {'market_cap_extended_stats.std_deviation': 'asc'},
'size': 0}, 'aggs': {
u'market_cap_extended_stats': {'extended_stats': {'field': u'market_cap'}}}}}, 'size': 0},
executor.request)
def test_filter_then_group_by(self):
executor = es_query.create_executor([
"WITH all_symbols AS (SELECT MAX(market_cap) AS max_all_times FROM symbol)",
"WITH year_2000 AS (SELECT MAX(market_cap) AS max_at_2000 FROM all_symbols WHERE ipo_year=2000)",
"SELECT sector, MAX(market_cap) AS max_per_sector FROM year_2000 GROUP BY sector LIMIT 2"])
rows = executor.select_response({
"hits": {
"hits": [],
"total": 6714,
"max_score": 0.0
},
"_shards": {
"successful": 1,
"failed": 0,
"total": 1
},
"took": 5,
"aggregations": {
"year_2000": {
"max_at_2000": {
"value": 20310000000.0
},
"sector": {
"buckets": [
{
"max_per_sector": {
"value": 19600000000.0
},
"key": "Health Care",
"doc_count": 18
},
{
"max_per_sector": {
"value": 4440000000.0
},
"key": "Technology",
"doc_count": 16
}
],
"sum_other_doc_count": 24,
"doc_count_error_upper_bound": 0
},
"doc_count": 58
},
"max_all_times": {
"value": 522690000000.0
}
},
"timed_out": False
})
self.assertEqual(
[{"sector": "Health Care", "max_all_times": 522690000000.0, "max_at_2000": 20310000000.0,
"max_per_sector": 19600000000.0, "_bucket_path": ["year_2000", "level3"]},
{"sector": "Technology", "max_all_times": 522690000000.0, "max_at_2000": 20310000000.0,
"max_per_sector": 4440000000.0, "_bucket_path": ["year_2000", "level3"]}],
rows)
def test_and_and(self):
executor = es_query.create_executor(
"SELECT * FROM symbol WHERE exchange='nyse' AND sector='Technology' AND ipo_year=1998")
self.assertEqual(
{'query': {'bool': {'filter': [
{'term': {'exchange': 'nyse'}},
{'term': {'sector': 'Technology'}},
{'term': {'ipo_year': 1998}},
]}}},
executor.request)
def test_group_by_multiple_values_script(self):
executor = es_query.create_executor(
"SELECT shares_count, COUNT(*) FROM symbol GROUP BY market_cap / last_sale AS shares_count")
self.assertEqual(
{'aggs': {'shares_count': {
'terms': {'size': 0, 'script': {
'lang': 'expression',
'inline': "doc['market_cap'].value / doc['last_sale'].value"}},
'aggs': {}}}, 'size': 0},
executor.request)
def test_two_children(self):
executor = es_query.create_executor([
"WITH all_symbols AS (SELECT MAX(market_cap) AS max_all_times FROM symbol)",
"SELECT ipo_year, MAX(market_cap) AS max_this_year FROM all_symbols GROUP BY ipo_year LIMIT 1",
"SELECT sector, MAX(market_cap) AS max_this_sector FROM all_symbols GROUP BY sector LIMIT 1"])
rows = executor.select_response({
"hits": {
"hits": [],
"total": 6714,
"max_score": 0.0
},
"_shards": {
"successful": 1,
"failed": 0,
"total": 1
},
"took": 2,
"aggregations": {
"sector": {
"buckets": [
{
"max_this_sector": {
"value": 34620000000.0
},
"key": "n/a",
"doc_count": 1373
}
],
"sum_other_doc_count": 5341,
"doc_count_error_upper_bound": 0
},
"max_all_times": {
"value": 522690000000.0
},
"ipo_year": {
"buckets": [
{
"max_this_year": {
"value": 54171930444.0
},
"key": 2014,
"doc_count": 390
}
],
"sum_other_doc_count": 2508,
"doc_count_error_upper_bound": 0
}
},
"timed_out": False
})
self.assertEqual(
[{'max_this_year': 54171930444.0, 'ipo_year': 2014, 'max_all_times': 522690000000.0, '_bucket_path': ['level2']},
{'sector': 'n/a', 'max_all_times': 522690000000.0, 'max_this_sector': 34620000000.0, '_bucket_path': ['level3']}],
rows)
def test_and_or_used_parentheses(self):
executor = es_query.create_executor(
"SELECT * FROM symbol WHERE exchange='nyse' AND (sector='Technology' OR ipo_year > 1998)")
self.assertEqual(
{'query': {'bool': {'filter': [
{'term': {'exchange': 'nyse'}},
{'bool': {'should': [
{'term': {'sector': 'Technology'}},
{'range': {'ipo_year': {'gt': 1998.0}}}]}}
]}}},
executor.request)
def test_join_on_one_field(self):
executor = es_query.create_executor(
'SELECT * FROM quote JOIN matched_symbols ON quote.symbol = matched_symbols.symbol', {
'matched_symbols': [
{'symbol': '1'},
{'symbol': '2'}
]
})
self.assertEqual(
{'query': {'bool': {'filter': [{}, {'terms': {u'symbol': ['1', '2']}}]}}},
executor.request)
def test_order_by_can_reference_child_buckets(self):
executor = es_query.create_executor(
["WITH per_year AS (SELECT ipo_year, COUNT(*) AS ipo_count FROM symbol \n"
"GROUP BY ipo_year ORDER BY max_in_finance LIMIT 2)",
"SELECT MAX(market_cap) AS max_in_finance FROM per_year WHERE sector='Finance'"])
self.assertEqual(
{'aggs': {
u'ipo_year': {'terms': {'field': u'ipo_year', 'order': {u'level2.max_in_finance': 'asc'}, 'size': 2},
'aggs': {'level2': {'filter': {'term': {u'sector': 'Finance'}},
'aggs': {u'max_in_finance': {u'max': {'field': u'market_cap'}}}}}}},
'size': 0},
executor.request)
def test_join_on_one_field(self):
executor = es_query.create_executor([
"WITH finance_symbols AS (SELECT * FROM symbol WHERE sector='Finance')",
'SELECT * FROM quote JOIN finance_symbols ON quote.symbol = finance_symbols.symbol'
])
self.assertEqual(
{'query': {'bool': {'filter': [
{}, {'filterjoin': {
u'symbol': {'indices': u'symbol*', 'path': u'symbol',
'query': {'term': {u'sector': 'Finance'}}}}}]
}}},
executor.request)
def test_having_can_reference_child_buckets(self):
executor = es_query.create_executor(
["WITH per_year AS (SELECT ipo_year, COUNT(*) AS ipo_count FROM symbol \n"
"GROUP BY ipo_year HAVING max_in_finance > 200)",
"SELECT MAX(market_cap) AS max_in_finance FROM per_year WHERE sector='Finance'"])
self.assertEqual(
{'aggs': {u'ipo_year': {'terms': {'field': u'ipo_year', 'size': 0}, 'aggs': {
'level2': {'filter': {'term': {u'sector': 'Finance'}},
'aggs': {u'max_in_finance': {u'max': {'field': u'market_cap'}}}}, 'having': {
'bucket_selector': {'buckets_path': {u'max_in_finance': u'level2.max_in_finance'},
'script': {'lang': 'expression', 'inline': u' max_in_finance > 200'}}}}}}, 'size': 0},
executor.request)
def test_having_by_metric(self):
executor = es_query.create_executor(
"SELECT ipo_year, MAX(market_cap) AS max_market_cap FROM symbol GROUP BY ipo_year HAVING max_market_cap > 100")
self.assertEqual(
{'aggs': {'ipo_year': {'terms': {'field': 'ipo_year', 'size': 0}, 'aggs': {'having': {
'bucket_selector': {
'buckets_path': {'max_market_cap': 'max_market_cap'},
'script': {
'lang': 'expression', 'inline': ' max_market_cap > 100'}}},
'max_market_cap': {'max': {
'field': 'market_cap'}}}}},
'size': 0},
executor.request)
