def test_apply_metric_filter_to_dataset_field_filters_in_nested_dataset_query( self): queries = (mock_dataset_blender.query().widget( f.ReactTable( mock_dataset_blender.fields["candidate-spend-per-wins"]) ).dimension(f.day(mock_dataset_blender.fields.timestamp)).filter( mock_dataset_blender.fields["votes"].gt(10))).sql self.assertEqual(len(queries), 1) self.assertEqual( "SELECT " '"sq0"."$timestamp" "$timestamp",' '"sq1"."$candidate-spend"/"sq0"."$wins" "$candidate-spend-per-wins" ' "FROM (" "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' 'SUM("is_winner") "$wins" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp" ' 'HAVING SUM("votes")>10 ' 'ORDER BY "$timestamp"' ') "sq0" ' "JOIN (" "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' 'SUM("candidate_spend") "$candidate-spend" ' 'FROM "politics"."politician_spend" ' 'GROUP BY "$timestamp" ORDER BY "$timestamp"' ') "sq1" ' "ON " '"sq0"."$timestamp"="sq1"."$timestamp" ' 'ORDER BY "$timestamp"', str(queries[0]), )
def test_apply_set_filter_for_metric_in_primary_dataset_query(self): queries = (mock_dataset_blender.query().widget( f.ReactTable( mock_dataset_blender.fields["candidate-spend-per-wins"]) ).dimension(f.day(mock_dataset_blender.fields.timestamp)).filter( f.ResultSet(mock_dataset_blender.fields["votes"].gt(10)))).sql self.assertEqual(len(queries), 1) self.assertEqual( "SELECT " '"sq0"."$timestamp" "$timestamp",' '"sq0"."$set(SUM(votes)>10)" "$set(SUM(votes)>10)",' '"sq1"."$candidate-spend"/"sq0"."$wins" "$candidate-spend-per-wins" ' "FROM (" "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' 'CASE WHEN SUM("votes")>10 THEN \'set(SUM(votes)>10)\' ELSE \'complement(SUM(votes)>10)\' END "$set(SUM(votes)>10)",' 'SUM("is_winner") "$wins" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp"' ') "sq0" ' "LEFT JOIN (" "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' 'SUM("candidate_spend") "$candidate-spend" ' 'FROM "politics"."politician_spend" ' 'GROUP BY "$timestamp"' ') "sq1" ' "ON " '"sq0"."$timestamp"="sq1"."$timestamp" ' 'ORDER BY "$timestamp" ' 'LIMIT 200000', str(queries[0]), )
def test_multiple_metrics_with_an_order_by_in_query_applies_order_to_wrapping_query( self, ): queries = (mock_dataset_blender.query().widget( f.ReactTable( mock_dataset_blender.fields["votes"], mock_dataset_blender.fields["candidate-spend"], )).dimension(f.day(mock_dataset_blender.fields.timestamp)).orderby( mock_dataset_blender.fields["votes"])).sql self.assertEqual(len(queries), 1) self.assertEqual( "SELECT " '"sq0"."$timestamp" "$timestamp",' '"sq0"."$votes" "$votes",' '"sq1"."$candidate-spend" "$candidate-spend" ' "FROM (" "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp" ORDER BY "$timestamp"' ') "sq0" ' "JOIN (" "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' 'SUM("candidate_spend") "$candidate-spend" ' 'FROM "politics"."politician_spend" ' 'GROUP BY "$timestamp" ORDER BY "$timestamp"' ') "sq1" ' "ON " '"sq0"."$timestamp"="sq1"."$timestamp" ' 'ORDER BY "$votes"', str(queries[0]), )
def test_fields_from_multiple_datasets_results_in_blender_query(self): queries = (mock_dataset_blender.query().widget( f.ReactTable( mock_dataset_blender.fields["candidate-spend"], mock_dataset_blender.fields["voters"], )).dimension(f.day(mock_dataset_blender.fields.timestamp))).sql self.assertEqual(len(queries), 1) self.assertEqual( "SELECT " '"sq0"."$timestamp" "$timestamp",' '"sq1"."$candidate-spend" "$candidate-spend",' '"sq0"."$voters" "$voters" ' "FROM (" "SELECT " 'TRUNC("politician"."timestamp",\'DD\') "$timestamp",' 'COUNT("voter"."id") "$voters" ' 'FROM "politics"."politician" ' 'JOIN "politics"."voter" ON "politician"."id"="voter"."politician_id" ' 'GROUP BY "$timestamp"' ') "sq0" ' "LEFT JOIN (" "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' 'SUM("candidate_spend") "$candidate-spend" ' 'FROM "politics"."politician_spend" ' 'GROUP BY "$timestamp"' ') "sq1" ' "ON " '"sq0"."$timestamp"="sq1"."$timestamp" ' 'ORDER BY "$timestamp" ' 'LIMIT 200000', str(queries[0]), )
def test_apply_dimension_filter_on_unmapped_dimension_field_filters_in_dataset_nested_query( self, ): queries = (mock_dataset_blender.query().widget( f.ReactTable( mock_dataset_blender.fields["candidate-spend-per-wins"]) ).dimension(f.day(mock_dataset_blender.fields.timestamp)).filter( mock_dataset_blender.fields["political_party"] == "d")).sql self.assertEqual(len(queries), 1) self.assertEqual( "SELECT " '"sq0"."$timestamp" "$timestamp",' '"sq1"."$candidate-spend"/"sq0"."$wins" "$candidate-spend-per-wins" ' "FROM (" "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' 'SUM("is_winner") "$wins" ' 'FROM "politics"."politician" ' "WHERE \"political_party\"='d' " 'GROUP BY "$timestamp"' ') "sq0" ' "LEFT JOIN (" "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' 'SUM("candidate_spend") "$candidate-spend" ' 'FROM "politics"."politician_spend" ' 'GROUP BY "$timestamp"' ') "sq1" ' "ON " '"sq0"."$timestamp"="sq1"."$timestamp" ' 'ORDER BY "$timestamp" ' 'LIMIT 200000', str(queries[0]), )
def test_build_query_with_multiple_dimensions_and_visualizations(self): queries = ( mock_dataset.query() .widget(f.ReactTable(mock_dataset.fields.votes, mock_dataset.fields.wins)) .widget( f.HighCharts() .axis(f.HighCharts.LineSeries(mock_dataset.fields.votes)) .axis(f.HighCharts.LineSeries(mock_dataset.fields.wins)) ) .dimension(f.day(mock_dataset.fields.timestamp)) .dimension(mock_dataset.fields.political_party) .sql ) self.assertEqual(len(queries), 1) self.assertEqual( "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' '"political_party" "$political_party",' 'SUM("votes") "$votes",' 'SUM("is_winner") "$wins" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp","$political_party" ' 'ORDER BY "$timestamp","$political_party"', str(queries[0]), )
def test_dimension_with_multiple_joins_includes_joins_ordered__in_query( self): queries = mock_dataset.query \ .widget(f.ReactTable(mock_dataset.fields.votes, mock_dataset.fields.voters)) \ .dimension(f.day(mock_dataset.fields.timestamp)) \ .dimension(mock_dataset.fields['district-name']) \ .sql self.assertEqual(len(queries), 1) self.assertEqual( 'SELECT ' 'TRUNC("politician"."timestamp",\'DD\') "$timestamp",' '"district"."district_name" "$district-name",' 'SUM("politician"."votes") "$votes",' 'COUNT("voter"."id") "$voters" ' 'FROM "politics"."politician" ' 'JOIN "politics"."voter" ' 'ON "politician"."id"="voter"."politician_id" ' 'FULL OUTER JOIN "locations"."district" ' 'ON "politician"."district_id"="district"."id" ' 'GROUP BY "$timestamp","$district-name" ' 'ORDER BY "$timestamp","$district-name" ' 'LIMIT 200000', str(queries[0]))
def test_using_datablender_metric_builds_query(self): queries = (mock_dataset_blender.query().widget( f.ReactTable(mock_dataset_blender. fields["candidate-spend-per-wins"])).dimension( f.day(mock_dataset_blender.fields.timestamp))).sql self.assertEqual(len(queries), 1) self.assertEqual( "SELECT " '"sq0"."$timestamp" "$timestamp",' '"sq1"."$candidate-spend"/"sq0"."$wins" "$candidate-spend-per-wins" ' "FROM (" "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' 'SUM("is_winner") "$wins" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp"' ') "sq0" ' "LEFT JOIN (" "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' 'SUM("candidate_spend") "$candidate-spend" ' 'FROM "politics"."politician_spend" ' 'GROUP BY "$timestamp"' ') "sq1" ' "ON " '"sq0"."$timestamp"="sq1"."$timestamp" ' 'ORDER BY "$timestamp" ' 'LIMIT 200000', str(queries[0]), )
def test_using_fields_from_single_dataset_reduced_to_dataset_query(self): queries = (mock_dataset_blender.query().widget( f.ReactTable(mock_dataset_blender.fields["votes"])).dimension( f.day(mock_dataset_blender.fields.timestamp))).sql self.assertEqual(len(queries), 1) # TODO Optimisation opportunity with desired result: # self.assertEqual( # "SELECT " # 'TRUNC("timestamp",\'DD\') "$timestamp",' # 'SUM("votes") "$votes" ' # 'FROM "politics"."politician" ' # 'GROUP BY "$timestamp" ' # 'ORDER BY "$timestamp"', # str(queries[0]), # ) self.assertEqual( 'SELECT "sq0"."$timestamp" "$timestamp","sq0"."$votes" "$votes" ' "FROM (" 'SELECT TRUNC("timestamp",\'DD\') "$timestamp",SUM("votes") "$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp") "sq0" ' "LEFT JOIN (" 'SELECT TRUNC("timestamp",\'DD\') "$timestamp" ' 'FROM "politics"."politician_spend" ' 'GROUP BY "$timestamp") "sq1" ' 'ON "sq0"."$timestamp"="sq1"."$timestamp" ' 'ORDER BY "$timestamp" ' 'LIMIT 200000', str(queries[0]), )
def test_using_datablender_builds_query_with_mapped_and_unmapped_dimensions( self): queries = (mock_dataset_blender.query().widget( f.ReactTable(mock_dataset_blender. fields["candidate-spend-per-wins"])).dimension( f.day(mock_dataset_blender.fields.timestamp), mock_dataset_blender.fields.political_party, )).sql self.assertEqual(len(queries), 1) self.assertEqual( "SELECT " '"sq0"."$timestamp" "$timestamp",' '"sq0"."$political_party" "$political_party",' '"sq1"."$candidate-spend"/"sq0"."$wins" "$candidate-spend-per-wins" ' "FROM (" "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' '"political_party" "$political_party",' 'SUM("is_winner") "$wins" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp","$political_party" ORDER BY "$timestamp","$political_party"' ') "sq0" ' "JOIN (" "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' 'SUM("candidate_spend") "$candidate-spend" ' 'FROM "politics"."politician_spend" ' 'GROUP BY "$timestamp" ORDER BY "$timestamp"' ') "sq1" ' "ON " '"sq0"."$timestamp"="sq1"."$timestamp" ' 'ORDER BY "$timestamp","$political_party"', str(queries[0]), )
def test_apply_reference_to_blended_query(self): query = (mock_dataset_blender.query().widget( f.ReactTable( mock_dataset_blender.fields["candidate-spend-per-wins"]) ).dimension(f.day(mock_dataset_blender.fields.timestamp)).reference( f.WeekOverWeek(mock_dataset_blender.fields.timestamp))) sql = query.sql self.assertEqual(len(sql), 2) (base_query, ref_query) = sql with self.subTest("base query"): self.assertEqual( "SELECT " '"sq0"."$timestamp" "$timestamp",' '"sq1"."$candidate-spend"/"sq0"."$wins" "$candidate-spend-per-wins" ' "FROM (" "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' 'SUM("is_winner") "$wins" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp" ORDER BY "$timestamp"' ') "sq0" ' "JOIN (" "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' 'SUM("candidate_spend") "$candidate-spend" ' 'FROM "politics"."politician_spend" ' 'GROUP BY "$timestamp" ORDER BY "$timestamp"' ') "sq1" ' "ON " '"sq0"."$timestamp"="sq1"."$timestamp" ' 'ORDER BY "$timestamp"', str(base_query), ) with self.subTest("ref query"): self.assertEqual( "SELECT " '"sq0"."$timestamp" "$timestamp",' '"sq1"."$candidate-spend_wow"/"sq0"."$wins_wow" "$candidate-spend-per-wins_wow" ' "FROM (" "SELECT " "TRUNC(TIMESTAMPADD('week',1,TRUNC(\"timestamp\",'DD')),'DD') \"$timestamp\"," 'SUM("is_winner") "$wins_wow" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp" ORDER BY "$timestamp"' ') "sq0" ' "JOIN (" "SELECT " "TRUNC(TIMESTAMPADD('week',1,TRUNC(\"timestamp\",'DD')),'DD') \"$timestamp\"," 'SUM("candidate_spend") "$candidate-spend_wow" ' 'FROM "politics"."politician_spend" ' 'GROUP BY "$timestamp" ORDER BY "$timestamp"' ') "sq1" ' "ON " '"sq0"."$timestamp"="sq1"."$timestamp" ' 'ORDER BY "$timestamp"', str(ref_query), )
def test_build_query_with_rollup_multiple_dimensions(self): queries = ( mock_dataset.query() .widget(f.ReactTable(mock_dataset.fields.votes)) .dimension( f.day(mock_dataset.fields.timestamp), f.Rollup(mock_dataset.fields["candidate-id"]), f.Rollup(mock_dataset.fields.political_party), ) .sql ) self.assertEqual(len(queries), 3) with self.subTest("base query is same as without references or rollup"): self.assertEqual( "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' '"candidate_id" "$candidate-id",' '"political_party" "$political_party",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp","$candidate-id","$political_party" ' 'ORDER BY "$timestamp","$candidate-id","$political_party"', str(queries[0]), ) with self.subTest( "in first rollup dimension's query, the dimension is replaced with NULL" ): self.assertEqual( "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' '"candidate_id" "$candidate-id",' 'NULL "$political_party",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp","$candidate-id" ' 'ORDER BY "$timestamp","$candidate-id","$political_party"', str(queries[1]), ) with self.subTest( "in the second rollup dimension's query, rollup dimension and all following dimensions " "are replaced with NULL" ): self.assertEqual( "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' 'NULL "$candidate-id",' 'NULL "$political_party",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp" ' 'ORDER BY "$timestamp","$candidate-id","$political_party"', str(queries[2]), )
def test_build_query_with_rollup_dimensions_and_filter_applied_on_correct_rollup_dimension( self, ): queries = (mock_dataset.query().widget( f.ReactTable(mock_dataset.fields.votes)).dimension( f.Rollup(mock_dataset.fields.political_party), f.Rollup(f.day(mock_dataset.fields.timestamp)), ).filter( f.OmitFromRollup( mock_dataset.fields.timestamp.between( date(2018, 1, 1), date(2019, 1, 1)))).sql) self.assertEqual(len(queries), 3) with self.subTest("base query is same as without rollup"): self.assertEqual( "SELECT " '"political_party" "$political_party",' 'TRUNC("timestamp",\'DD\') "$timestamp",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' "WHERE \"timestamp\" BETWEEN '2018-01-01' AND '2019-01-01' " 'GROUP BY "$political_party","$timestamp" ' 'ORDER BY "$political_party","$timestamp" ' 'LIMIT 200000', str(queries[0]), ) with self.subTest( "base rollup query is same as base query minus the timestamp rollup dimension" ): self.assertEqual( "SELECT " '"political_party" "$political_party",' "'_FIREANT_ROLLUP_VALUE_' \"$timestamp\"," 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$political_party","$timestamp" ' 'ORDER BY "$political_party","$timestamp" ' 'LIMIT 200000', str(queries[1]), ) with self.subTest( "base rollup query is same as base query minus the political party rollup dimension" ): self.assertEqual( "SELECT " "'_FIREANT_ROLLUP_VALUE_' \"$political_party\"," "'_FIREANT_ROLLUP_VALUE_' \"$timestamp\"," 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$political_party","$timestamp" ' 'ORDER BY "$political_party","$timestamp" ' 'LIMIT 200000', str(queries[2]), )
def test_build_query_with_rollup_dimension_and_two_filters_only_one_applied_to_rollup( self, ): queries = ( mock_dataset.query() .widget(f.ReactTable(mock_dataset.fields.votes)) .dimension( mock_dataset.fields.political_party, f.Rollup(f.day(mock_dataset.fields.timestamp)), ) .filter( f.OmitFromRollup( mock_dataset.fields.timestamp.between( date(2018, 1, 1), date(2019, 1, 1) ) ), mock_dataset.fields.timestamp.between( date(2018, 3, 1), date(2019, 9, 1) ), ) .sql ) self.assertEqual(len(queries), 2) with self.subTest("base query is same as without rollup with both filters"): self.assertEqual( "SELECT " '"political_party" "$political_party",' 'TRUNC("timestamp",\'DD\') "$timestamp",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' "WHERE \"timestamp\" BETWEEN '2018-01-01' AND '2019-01-01' " "AND \"timestamp\" BETWEEN '2018-03-01' AND '2019-09-01' " 'GROUP BY "$political_party","$timestamp" ' 'ORDER BY "$political_party","$timestamp"', str(queries[0]), ) with self.subTest( "base rollup query is same as base query minus the rollup dimension with one filter" ): self.assertEqual( "SELECT " '"political_party" "$political_party",' 'NULL "$timestamp",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' "WHERE \"timestamp\" BETWEEN '2018-03-01' AND '2019-09-01' " 'GROUP BY "$political_party" ' 'ORDER BY "$political_party","$timestamp"', str(queries[1]), )
def test_using_fields_from_single_dataset_reduced_to_dataset_query(self): queries = (mock_dataset_blender.query().widget( f.ReactTable(mock_dataset_blender.fields["votes"], )).dimension( f.day(mock_dataset_blender.fields.timestamp))).sql self.assertEqual(len(queries), 1) self.assertEqual( "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp" ' 'ORDER BY "$timestamp"', str(queries[0]), )
def test_build_query_with_datetime_interval_daily(self): queries = (mock_dataset.query().widget( f.ReactTable(mock_dataset.fields.votes)).dimension( f.day(mock_dataset.fields.timestamp)).sql) self.assertEqual(len(queries), 1) self.assertEqual( "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp" ' 'ORDER BY "$timestamp" ' 'LIMIT 200000', str(queries[0]), )
def test_blend_data_set_on_query_using_joins(self): query = ( mock_dataset_blender.query() .widget(f.ReactTable(mock_dataset_blender.fields["candidate-spend-per-wins"])) .dimension( f.day(mock_dataset_blender.fields.timestamp), mock_dataset_blender.fields.state, ) ) self.assertEqual( "SELECT " '"sq0"."$timestamp" "$timestamp",' '"sq0"."$state" "$state",' '"sq1"."$candidate-spend"/"sq0"."$wins" "$candidate-spend-per-wins" ' "FROM (" "SELECT " 'TRUNC("politician"."timestamp",\'DD\') "$timestamp",' '"state"."state_name" "$state",' 'SUM("politician"."is_winner") "$wins" ' 'FROM "politics"."politician" ' 'FULL OUTER JOIN "locations"."district" ' 'ON "politician"."district_id"="district"."id" ' 'JOIN "locations"."state" ' 'ON "district"."state_id"="state"."id" ' 'GROUP BY "$timestamp","$state"' ') "sq0" ' "LEFT JOIN (" "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' '"state_name" "$state",' 'SUM("candidate_spend") "$candidate-spend" ' 'FROM "politics"."politician_spend" ' 'GROUP BY "$timestamp","$state"' ') "sq1" ' "ON " '"sq0"."$timestamp"="sq1"."$timestamp" ' 'AND "sq0"."$state"="sq1"."$state" ' 'ORDER BY "$timestamp","$state" ' 'LIMIT 200000', str(query.sql[0]), )
def test_db_specific_querybuilder_class_used_when_needed(self): dataset_blender = copy.deepcopy(mock_dataset_blender) blender = ( dataset_blender.query() .widget( f.ReactTable( dataset_blender.fields["candidate-spend"], dataset_blender.fields["voters"], ) ) .dimension(f.day(dataset_blender.fields.timestamp)) ) # Given all mocks are based on the Vertica database, this is a quick override to avoid a lot of duplicate mocks! blender.dataset.primary_dataset.database = TestMySQLDatabase() queries = blender.sql self.assertEqual(len(queries), 1) self.assertEqual( "SELECT " "`sq0`.`$timestamp` `$timestamp`," "`sq1`.`$candidate-spend` `$candidate-spend`," "`sq0`.`$voters` `$voters` " "FROM (" "SELECT DATE_FORMAT(`politician`.`timestamp`,'%Y-%m-%d 00:00:00') `$timestamp`," "COUNT(`voter`.`id`) `$voters` " "FROM `politics`.`politician` " "JOIN `politics`.`voter` " "ON `politician`.`id`=`voter`.`politician_id` " "GROUP BY `$timestamp`) `sq0` " "LEFT JOIN (" "SELECT TRUNC(`timestamp`,'DD') `$timestamp`," "SUM(`candidate_spend`) `$candidate-spend` " "FROM `politics`.`politician_spend` " "GROUP BY `$timestamp`" ") `sq1` " "ON `sq0`.`$timestamp`=`sq1`.`$timestamp` " "ORDER BY `$timestamp` " "LIMIT 200000", str(queries[0]), )
def test_build_query_with_totals_on_dimension_and_subsequent_dimensions( self): queries = (mock_dataset.query().widget( f.ReactTable(mock_dataset.fields.votes)).dimension( f.day(mock_dataset.fields.timestamp), f.Rollup(mock_dataset.fields["candidate-id"]), mock_dataset.fields.political_party, ).sql) self.assertEqual(len(queries), 2) with self.subTest( "base query is same as without references or totals"): self.assertEqual( "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' '"candidate_id" "$candidate-id",' '"political_party" "$political_party",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp","$candidate-id","$political_party" ' 'ORDER BY "$timestamp","$candidate-id","$political_party" ' 'LIMIT 200000', str(queries[0]), ) with self.subTest( "all dimensions after the rolled up dimension are _FIREANT_ROLLUP_VALUE_" ): self.assertEqual( "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' "'_FIREANT_ROLLUP_VALUE_' \"$candidate-id\"," "'_FIREANT_ROLLUP_VALUE_' \"$political_party\"," 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp" ' 'ORDER BY "$timestamp","$candidate-id","$political_party" ' 'LIMIT 200000', str(queries[1]), )
def test_blended_query_with_orderby_mapped_dimension(self): queries = ( mock_dataset_blender.query() .widget(f.ReactTable(mock_dataset_blender.fields["candidate-spend-per-wins"])) .dimension( f.day(mock_dataset_blender.fields.timestamp), mock_dataset_blender.fields["candidate-id"], ) .orderby(mock_dataset_blender.fields["candidate-id"], Order.desc) ).sql self.assertEqual(len(queries), 1) (query,) = queries self.assertEqual( "SELECT " '"sq0"."$timestamp" "$timestamp",' '"sq0"."$candidate-id" "$candidate-id",' '"sq1"."$candidate-spend"/"sq0"."$wins" "$candidate-spend-per-wins" ' "FROM (" "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' '"candidate_id" "$candidate-id",' 'SUM("is_winner") "$wins" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp","$candidate-id"' ') "sq0" ' "LEFT JOIN (" "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' '"candidate_id" "$candidate-id",' 'SUM("candidate_spend") "$candidate-spend" ' 'FROM "politics"."politician_spend" ' 'GROUP BY "$timestamp","$candidate-id"' ') "sq1" ' "ON " '"sq0"."$timestamp"="sq1"."$timestamp" ' 'AND "sq0"."$candidate-id"="sq1"."$candidate-id" ' 'ORDER BY "$candidate-id" DESC ' 'LIMIT 200000', str(query), )
def test_build_query_with_multiple_dimensions(self): queries = (mock_dataset.query().widget( f.ReactTable(mock_dataset.fields.votes)).dimension( f.day(mock_dataset.fields.timestamp)).dimension( mock_dataset.fields["candidate-id"]).dimension( mock_dataset.fields["candidate-name"]).sql) self.assertEqual(len(queries), 1) self.assertEqual( "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' '"candidate_id" "$candidate-id",' '"candidate_name" "$candidate-name",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp","$candidate-id","$candidate-name" ' 'ORDER BY "$timestamp","$candidate-id","$candidate-name" ' 'LIMIT 200000', str(queries[0]), )
def test_dimension_with_recursive_join_joins_all_join_tables(self): queries = mock_dataset.query \ .widget(f.ReactTable(mock_dataset.fields.votes)) \ .dimension(f.day(mock_dataset.fields.timestamp)) \ .dimension(mock_dataset.fields.state) \ .sql self.assertEqual(len(queries), 1) self.assertEqual( 'SELECT ' 'TRUNC("politician"."timestamp",\'DD\') "$timestamp",' '"state"."state_name" "$state",' 'SUM("politician"."votes") "$votes" ' 'FROM "politics"."politician" ' 'FULL OUTER JOIN "locations"."district" ' 'ON "politician"."district_id"="district"."id" ' 'JOIN "locations"."state" ' 'ON "district"."state_id"="state"."id" ' 'GROUP BY "$timestamp","$state" ' 'ORDER BY "$timestamp","$state"', str(queries[0]))
def test_build_query_with_rollup_cat_dimension_with_references_and_date_filters( self, ): queries = (mock_dataset.query().widget( f.ReactTable(mock_dataset.fields.votes)).dimension( f.day(mock_dataset.fields.timestamp)).dimension( f.Rollup(mock_dataset.fields.political_party)).reference( f.DayOverDay(mock_dataset.fields.timestamp)).filter( mock_dataset.fields.timestamp.between( date(2018, 1, 1), date(2019, 1, 1))).sql) self.assertEqual(len(queries), 4) with self.subTest( "base query is same as without references or rollup"): self.assertEqual( "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' '"political_party" "$political_party",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' "WHERE \"timestamp\" BETWEEN '2018-01-01' AND '2019-01-01' " 'GROUP BY "$timestamp","$political_party" ' 'ORDER BY "$timestamp","$political_party" ' 'LIMIT 200000', str(queries[0]), ) with self.subTest("reference query is shifted"): self.assertEqual( "SELECT " "TRUNC(TIMESTAMPADD(day,1,TRUNC(\"timestamp\",'DD')),'DD') \"$timestamp\"," '"political_party" "$political_party",' 'SUM("votes") "$votes_dod" ' 'FROM "politics"."politician" ' "WHERE \"timestamp\" BETWEEN TIMESTAMPADD(day,-1,'2018-01-01') " "AND TIMESTAMPADD(day,-1,'2019-01-01') " 'GROUP BY "$timestamp","$political_party" ' 'ORDER BY "$timestamp","$political_party" ' 'LIMIT 200000', str(queries[1]), ) with self.subTest( "base rollup query is same as base query minus the rollup dimension" ): self.assertEqual( "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' "'_FIREANT_ROLLUP_VALUE_' \"$political_party\"," 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' "WHERE \"timestamp\" BETWEEN '2018-01-01' AND '2019-01-01' " 'GROUP BY "$timestamp" ' 'ORDER BY "$timestamp","$political_party" ' 'LIMIT 200000', str(queries[2]), ) with self.subTest( "reference total query is shifted without the rollup dimension" ): self.assertEqual( "SELECT " "TRUNC(TIMESTAMPADD(day,1,TRUNC(\"timestamp\",'DD')),'DD') \"$timestamp\"," "'_FIREANT_ROLLUP_VALUE_' \"$political_party\"," 'SUM("votes") "$votes_dod" ' 'FROM "politics"."politician" ' "WHERE \"timestamp\" BETWEEN TIMESTAMPADD(day,-1,'2018-01-01') " "AND TIMESTAMPADD(day,-1,'2019-01-01') " 'GROUP BY "$timestamp" ' 'ORDER BY "$timestamp","$political_party" ' 'LIMIT 200000', str(queries[3]), )
def test_apply_totals_to_blended_query(self): queries = (mock_dataset_blender.query().widget( f.ReactTable(mock_dataset_blender. fields["candidate-spend-per-wins"])).dimension( f.day(mock_dataset_blender.fields.timestamp), Rollup( mock_dataset_blender.fields["candidate-id"]), )).sql self.assertEqual(len(queries), 2) (base_query, totals_query) = queries with self.subTest("base query"): self.assertEqual( "SELECT " '"sq0"."$timestamp" "$timestamp",' '"sq0"."$candidate-id" "$candidate-id",' '"sq1"."$candidate-spend"/"sq0"."$wins" "$candidate-spend-per-wins" ' "FROM (" "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' '"candidate_id" "$candidate-id",' 'SUM("is_winner") "$wins" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp","$candidate-id"' ') "sq0" ' "LEFT JOIN (" "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' '"candidate_id" "$candidate-id",' 'SUM("candidate_spend") "$candidate-spend" ' 'FROM "politics"."politician_spend" ' 'GROUP BY "$timestamp","$candidate-id"' ') "sq1" ' "ON " '"sq0"."$timestamp"="sq1"."$timestamp" ' 'AND "sq0"."$candidate-id"="sq1"."$candidate-id" ' 'ORDER BY "$timestamp","$candidate-id" ' 'LIMIT 200000', str(base_query), ) with self.subTest("totals query"): self.assertEqual( "SELECT " '"sq0"."$timestamp" "$timestamp",' '"sq0"."$candidate-id" "$candidate-id",' '"sq1"."$candidate-spend"/"sq0"."$wins" "$candidate-spend-per-wins" ' "FROM (" "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' "'_FIREANT_ROLLUP_VALUE_' \"$candidate-id\"," 'SUM("is_winner") "$wins" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp","$candidate-id"' ') "sq0" ' "LEFT JOIN (" "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' "'_FIREANT_ROLLUP_VALUE_' \"$candidate-id\"," 'SUM("candidate_spend") "$candidate-spend" ' 'FROM "politics"."politician_spend" ' 'GROUP BY "$timestamp","$candidate-id"' ') "sq1" ' "ON " '"sq0"."$timestamp"="sq1"."$timestamp" ' 'AND "sq0"."$candidate-id"="sq1"."$candidate-id" ' 'ORDER BY "$timestamp","$candidate-id" ' 'LIMIT 200000', str(totals_query), )
def test_build_query_with_rollup_dimension_and_a_reference(self): queries = ( mock_dataset.query() .widget(f.ReactTable(mock_dataset.fields.votes)) .dimension( f.day(mock_dataset.fields.timestamp), f.Rollup(mock_dataset.fields.political_party), ) .reference(f.DayOverDay(mock_dataset.fields.timestamp)) .sql ) self.assertEqual(len(queries), 4) with self.subTest("base query is same as without references or rollup"): self.assertEqual( "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' '"political_party" "$political_party",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp","$political_party" ' 'ORDER BY "$timestamp","$political_party"', str(queries[0]), ) with self.subTest("reference query is shifted"): self.assertEqual( "SELECT " "TRUNC(TIMESTAMPADD('day',1,TRUNC(\"timestamp\",'DD')),'DD') \"$timestamp\"," '"political_party" "$political_party",' 'SUM("votes") "$votes_dod" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp","$political_party" ' 'ORDER BY "$timestamp","$political_party"', str(queries[1]), ) with self.subTest( "base rollup query is same as base query minus the rollup dimension" ): self.assertEqual( "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' 'NULL "$political_party",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp" ' 'ORDER BY "$timestamp","$political_party"', str(queries[2]), ) with self.subTest( "reference total query is shifted without the rollup dimension" ): self.assertEqual( "SELECT " "TRUNC(TIMESTAMPADD('day',1,TRUNC(\"timestamp\",'DD')),'DD') \"$timestamp\"," 'NULL "$political_party",' 'SUM("votes") "$votes_dod" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp" ' 'ORDER BY "$timestamp","$political_party"', str(queries[3]), )
def test_date_value_with_day_interval_is_returned_as_date_string_to_the_day( self): for d in (date(2019, 1, 1), datetime(2019, 1, 1, 12, 30, 2)): with self.subTest("with " + d.__class__.__name__): self.assertEqual("2019-01-01", formats.display_value(d, day(date_field)))
from unittest import TestCase import fireant as f from fireant.tests.dataset.mocks import mock_dataset timestamp_daily = f.day(mock_dataset.fields.timestamp) # noinspection SqlDialectInspection,SqlNoDataSourceInspection class QueryBuilderOperationTests(TestCase): maxDiff = None def test_build_query_with_cumsum_operation(self): queries = (mock_dataset.query.widget( f.ReactTable(f.CumSum( mock_dataset.fields.votes))).dimension(timestamp_daily).sql) self.assertEqual(len(queries), 1) self.assertEqual( 'SELECT ' 'TRUNC("timestamp",\'DD\') "$timestamp",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp" ' 'ORDER BY "$timestamp" ' 'LIMIT 200000', str(queries[0]), ) def test_build_query_with_cummean_operation(self):
def test_apply_set_filter_for_dimension_with_reference_in_both_dataset_queries( self): queries = (mock_dataset_blender.query().widget( f.ReactTable( mock_dataset_blender.fields["candidate-spend-per-wins"]) ).dimension(f.day(mock_dataset_blender.fields.timestamp)).reference( f.WeekOverWeek(mock_dataset_blender.fields.timestamp)).filter( f.ResultSet( mock_dataset_blender.fields['candidate-id'] == 12))).sql self.assertEqual(len(queries), 2) with self.subTest("base query"): self.assertEqual( "SELECT " '"sq0"."$timestamp" "$timestamp",' '"sq0"."$candidate-id" "$candidate-id",' '"sq1"."$candidate-spend"/"sq0"."$wins" "$candidate-spend-per-wins" ' "FROM (" "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' 'CASE WHEN "candidate_id"=12 THEN \'set(candidate_id=12)\' ELSE \'complement(candidate_id=12)\' END "$candidate-id",' 'SUM("is_winner") "$wins" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp","$candidate-id"' ') "sq0" ' "LEFT JOIN (" "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' 'CASE WHEN "candidate_id"=12 THEN \'set(candidate_id=12)\' ELSE \'complement(candidate_id=12)\' END "$candidate-id",' 'SUM("candidate_spend") "$candidate-spend" ' 'FROM "politics"."politician_spend" ' 'GROUP BY "$timestamp","$candidate-id"' ') "sq1" ' "ON " '"sq0"."$timestamp"="sq1"."$timestamp" ' 'AND "sq0"."$candidate-id"="sq1"."$candidate-id" ' 'ORDER BY "$timestamp","$candidate-id" ' 'LIMIT 200000', str(queries[0]), ) with self.subTest("ref query"): self.assertEqual( "SELECT " '"sq0"."$timestamp" "$timestamp",' '"sq0"."$candidate-id" "$candidate-id",' '"sq1"."$candidate-spend_wow"/"sq0"."$wins_wow" "$candidate-spend-per-wins_wow" ' "FROM (" "SELECT " 'TRUNC(TIMESTAMPADD(week,1,TRUNC("timestamp",\'DD\')),\'DD\') "$timestamp",' 'CASE WHEN "candidate_id"=12 THEN \'set(candidate_id=12)\' ' 'ELSE \'complement(candidate_id=12)\' END "$candidate-id",' 'SUM("is_winner") "$wins_wow" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp","$candidate-id"' ') "sq0" ' "LEFT JOIN (" "SELECT " 'TRUNC(TIMESTAMPADD(week,1,TRUNC("timestamp",\'DD\')),\'DD\') "$timestamp",' 'CASE WHEN "candidate_id"=12 THEN \'set(candidate_id=12)\' ' 'ELSE \'complement(candidate_id=12)\' END "$candidate-id",' 'SUM("candidate_spend") "$candidate-spend_wow" ' 'FROM "politics"."politician_spend" ' 'GROUP BY "$timestamp","$candidate-id"' ') "sq1" ' "ON " '"sq0"."$timestamp"="sq1"."$timestamp" ' 'AND "sq0"."$candidate-id"="sq1"."$candidate-id" ' 'ORDER BY "$timestamp","$candidate-id" ' 'LIMIT 200000', str(queries[1]), )