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_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_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_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_one_level(self): executor = es_query.create_executor([ "WITH SELECT MAX(sum_this_year) AS max_all_times FROM symbol AS all_symbols", "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_group_by_date_trunc(self): executor = es_query.create_executor( "SELECT year, MAX(adj_close) FROM quote WHERE symbol='AAPL' " "GROUP BY TO_CHAR(date_trunc('year',\"date\"), '%Y-%m-%d') AS year" ) self.assertEqual( { 'query': { 'term': { 'symbol': 'AAPL' } }, 'aggs': { 'year': { 'date_histogram': { 'field': 'date', 'interval': 'year', 'time_zone': '+08:00', 'format': 'yyyy-MM-dd' }, 'aggs': { 'MAX(adj_close)': { 'max': { 'field': 'adj_close' } } } } }, 'size': 0 }, executor.request)
def test_select_nested_field(self): executor = es_query.create_executor('SELECT "a.exchange" FROM symbol') self.assertEqual({}, executor.request) rows = executor.select_response({ "hits": { "hits": [ { "_score": 1.0, "_type": "symbol", "_id": "AVLgXwu88_EnCX8dV9PN", "_source": { "a": { "exchange": "nasdaq" } }, "_index": "symbol" } ], "total": 6714, "max_score": 1.0 }, "_shards": { "successful": 3, "failed": 0, "total": 3 }, "took": 32, "timed_out": False }) self.assertEqual([{ 'a.exchange': 'nasdaq'}], rows)
def test_filter_without_group_by(self): executor = es_query.create_executor([ "WITH SELECT MAX(market_cap) AS max_all_times FROM symbol AS all_symbols", "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_join_on_one_field(self): executor = es_query.create_executor([ "WITH SELECT * FROM symbol WHERE sector='Finance' AS finance_symbols", '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_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_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_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_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_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_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)
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_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_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_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_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_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_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 SELECT MAX(market_cap) AS max_all_times FROM symbol AS all_symbols", "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_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_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_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_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_dot_field(self): executor = es_query.create_executor( "SELECT * FROM symbol WHERE 'nyse'=\"a.exchange\"") self.assertEqual({'query': { 'term': { 'a.exchange': 'nyse' } }}, 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)
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_id_in(self): executor = es_query.create_executor( "SELECT * FROM symbol WHERE _id IN ('1', '2')") self.assertEqual({'query': { 'ids': { 'value': ['1', '2'] } }}, executor.request)
def test_type_eq(self): executor = es_query.create_executor( "SELECT * FROM symbol WHERE _type = 'symbol'") self.assertEqual({'query': { 'type': { 'value': 'symbol' } }}, 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_field_eq_string(self): executor = es_query.create_executor( "SELECT * FROM symbol WHERE exchange='nyse'") self.assertEqual({'query': { 'term': { 'exchange': 'nyse' } }}, 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_is_not_null(self): executor = es_query.create_executor( "SELECT * FROM symbol WHERE last_sale IS NOT NULL") self.assertEqual({'query': { 'exists': { 'field': 'last_sale' } }}, executor.request)
def test_field_can_be_right_operand(self): executor = es_query.create_executor( "SELECT * FROM symbol WHERE 'nyse'=exchange") self.assertEqual({'query': { 'term': { 'exchange': 'nyse' } }}, executor.request) executor = es_query.create_executor( "SELECT * FROM symbol WHERE 1998<ipo_year") self.assertEqual({'query': { 'range': { 'ipo_year': { 'gte': 1998.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_drill_down_one_direction(self): executor = es_query.create_executor([ "WITH SELECT MAX(market_cap) AS max_all_times FROM symbol AS all_symbols", "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_one_level(self): executor = es_query.create_executor([ "WITH SELECT MAX(sum_this_year) AS max_all_times FROM symbol AS all_symbols", "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_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_filter_then_group_by(self): executor = es_query.create_executor([ "WITH SELECT MAX(market_cap) AS max_all_times FROM symbol AS all_symbols", "WITH SELECT MAX(market_cap) AS max_at_2000 FROM all_symbols WHERE ipo_year=2000 AS 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_function(self): executor = es_query.create_executor( "SELECT shares_count, COUNT(*) FROM symbol GROUP BY floor(market_cap / last_sale) AS shares_count") self.assertEqual( {'aggs': {'shares_count': { 'terms': {'size': 0, 'script': { 'lang': 'expression', 'inline': "floor(doc['market_cap'].value / doc['last_sale'].value)"}}, 'aggs': {}}}, 'size': 0}, executor.request)
def test_timestamp(self): executor = es_query.create_executor( "SELECT * FROM symbol WHERE ts > TIMESTAMP '2016-08-08 00:00:00'") self.assertEqual({'query': { 'range': { 'ts': { 'gt': 1470585600000L } } }}, executor.request)
def test_field_lte_numeric(self): executor = es_query.create_executor( "SELECT * FROM symbol WHERE last_sale <= 1000") self.assertEqual({'query': { 'range': { 'last_sale': { 'lte': 1000.0 } } }}, executor.request)
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_moving_average_with_params(self): executor = es_query.create_executor([ "SELECT year, MAX(adj_close) AS max_adj_close, MOVING_Avg(max_adj_close, '{\"window\":5}') 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}}}}}, 'size': 0}, executor.request)
def test_drivative(self): executor = es_query.create_executor([ "SELECT year, MAX(adj_close) AS max_adj_close, DERIVATIVE(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'}}, 'DERIVATIVE(max_adj_close)': {'derivative': {'buckets_path': u'max_adj_close'}}}}}, 'size': 0}, executor.request)
def test_serial_diff(self): executor = es_query.create_executor([ "SELECT year, MAX(adj_close) AS max_adj_close, SERIAL_DIFF(max_adj_close, lag=7) 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'}}, u'ma': {u'serial_diff': {'buckets_path': u'max_adj_close', u'lag': 7}}}}}, '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_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_two_children(self): executor = es_query.create_executor([ "WITH SELECT MAX(market_cap) AS max_all_times FROM symbol AS all_symbols", "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_order_by_can_reference_child_buckets(self): executor = es_query.create_executor( ["WITH SELECT ipo_year, COUNT(*) AS ipo_count FROM symbol \n" "GROUP BY ipo_year ORDER BY max_in_finance LIMIT 2 AS per_year", "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 SELECT * FROM symbol WHERE sector='Finance' AS finance_symbols", '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 SELECT ipo_year, COUNT(*) AS ipo_count FROM symbol \n" "GROUP BY ipo_year HAVING max_in_finance > 200 AS per_year", "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)