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)