def test_dimension_is_replaced_when_references_are_present(self):
queries = (ds.query.widget(f.Pandas(ds.fields.aggr_number)).dimension(
ds.fields.date).dimension(ds.fields.boolean).reference(
f.WeekOverWeek(ds.fields.date)).filter(
f.ResultSet(ds.fields.text == "abc")).sql)
self.assertEqual(len(queries), 2)
with self.subTest("base query"):
self.assertEqual(
"SELECT "
'"date" "$date",'
'"boolean" "$boolean",'
"CASE WHEN \"text\"='abc' THEN 'set(text=''abc'')' ELSE 'complement(text=''abc'')' END \"$text\","
'SUM("number") "$aggr_number" '
'FROM "test" '
'GROUP BY "$date","$boolean","$text" '
'ORDER BY "$date","$boolean","$text" '
'LIMIT 200000',
str(queries[0]),
)
with self.subTest("ref query"):
self.assertEqual(
"SELECT "
'TIMESTAMPADD(week,1,"date") "$date",'
'"boolean" "$boolean",'
"CASE WHEN \"text\"='abc' THEN 'set(text=''abc'')' ELSE 'complement(text=''abc'')' END \"$text\","
'SUM("number") "$aggr_number_wow" '
'FROM "test" '
'GROUP BY "$date","$boolean","$text" '
'ORDER BY "$date","$boolean","$text" '
'LIMIT 200000',
str(queries[1]),
)
def test_wow(self):
queries = (
mock_dataset.query.widget(
f.HighCharts().axis(f.HighCharts.LineSeries(mock_dataset.fields.votes))
)
.dimension(timestamp_daily)
.reference(f.WeekOverWeek(mock_dataset.fields.timestamp))
.sql
)
self.assertEqual(2, len(queries))
with self.subTest("base query is same as without reference"):
self.assertEqual(
"SELECT "
'TRUNC("timestamp",\'DD\') "$timestamp",'
'SUM("votes") "$votes" '
'FROM "politics"."politician" '
'GROUP BY "$timestamp" '
'ORDER BY "$timestamp"',
str(queries[0]),
)
with self.subTest(
"reference query is same as base query with reference dimension shifted"
):
self.assertEqual(
"SELECT "
"TRUNC(TIMESTAMPADD('week',1,TRUNC(\"timestamp\",'DD')),'DD') \"$timestamp\","
'SUM("votes") "$votes_wow" '
'FROM "politics"."politician" '
'GROUP BY "$timestamp" '
'ORDER BY "$timestamp"',
str(queries[1]),
)
def test_dimension_with_single_reference_wow(self):
queries = slicer.data \
.widget(f.HighCharts()
.axis(f.HighCharts.LineSeries(slicer.metrics.votes))) \
.dimension(slicer.dimensions.timestamp) \
.reference(f.WeekOverWeek(slicer.dimensions.timestamp)) \
.queries
self.assertEqual(2, len(queries))
with self.subTest('base query is same as without reference'):
self.assertEqual('SELECT '
'TRUNC("timestamp",\'DD\') "$d$timestamp",'
'SUM("votes") "$m$votes" '
'FROM "politics"."politician" '
'GROUP BY "$d$timestamp" '
'ORDER BY "$d$timestamp"', str(queries[0]))
with self.subTest('reference query is same as base query with reference dimension shifted'):
self.assertEqual('SELECT '
'TRUNC(TIMESTAMPADD(\'week\',1,"timestamp"),\'DD\') "$d$timestamp",'
'SUM("votes") "$m$votes_wow" '
'FROM "politics"."politician" '
'GROUP BY "$d$timestamp" '
'ORDER BY "$d$timestamp"', str(queries[1]))
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_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]),
)
def test_reference_with_rollup_dimension_and_date_range_filter(self):
queries = (
mock_dataset.query.widget(
f.HighCharts().axis(f.HighCharts.LineSeries(mock_dataset.fields.votes))
)
.dimension(Rollup(timestamp_daily))
.reference(f.WeekOverWeek(mock_dataset.fields.timestamp))
.filter(
mock_dataset.fields.timestamp.between(
date(2018, 1, 1), date(2018, 1, 31)
)
)
.sql
)
self.assertEqual(4, len(queries))
base, reference, base_rollup, reference_rollup = queries
with self.subTest("base query applies dimensions and date range filter"):
self.assertEqual(
"SELECT "
'TRUNC("timestamp",\'DD\') "$timestamp",'
'SUM("votes") "$votes" '
'FROM "politics"."politician" '
"WHERE \"timestamp\" BETWEEN '2018-01-01' AND '2018-01-31' "
'GROUP BY "$timestamp" '
'ORDER BY "$timestamp"',
str(base),
)
with self.subTest(
"reference query shifts timestamp dimension and date range filter by a week"
):
self.assertEqual(
"SELECT "
"TRUNC(TIMESTAMPADD('week',1,TRUNC(\"timestamp\",'DD')),'DD') \"$timestamp\","
'SUM("votes") "$votes_wow" '
'FROM "politics"."politician" '
"WHERE \"timestamp\" BETWEEN TIMESTAMPADD('week',-1,'2018-01-01') "
"AND TIMESTAMPADD('week',-1,'2018-01-31') "
'GROUP BY "$timestamp" '
'ORDER BY "$timestamp"',
str(reference),
)
with self.subTest("totals query selects NULL for timestamp dimension"):
self.assertEqual(
"SELECT "
'NULL "$timestamp",'
'SUM("votes") "$votes" '
'FROM "politics"."politician" '
"WHERE \"timestamp\" BETWEEN '2018-01-01' AND '2018-01-31' "
'ORDER BY "$timestamp"',
str(base_rollup),
)
with self.subTest(
"reference totals query selects NULL for timestamp dimension and shifts date range filter"
):
self.assertEqual(
"SELECT "
'NULL "$timestamp",'
'SUM("votes") "$votes_wow" '
'FROM "politics"."politician" '
"WHERE \"timestamp\" BETWEEN TIMESTAMPADD('week',-1,'2018-01-01') "
"AND TIMESTAMPADD('week',-1,'2018-01-31') "
'ORDER BY "$timestamp"',
str(reference_rollup),
)