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]),
)
