def test_use_same_nested_query_for_joining_references_with_same_period_and_dimension_with_different_periods(
self,
):
queries = (
mock_dataset.query.widget(
f.HighCharts().axis(f.HighCharts.LineSeries(mock_dataset.fields.votes))
)
.dimension(timestamp_daily)
.reference(
f.DayOverDay(mock_dataset.fields.timestamp),
f.DayOverDay(mock_dataset.fields.timestamp, delta=True),
# also work with modified dimensions
f.YearOverYear(timestamp_daily),
f.YearOverYear(timestamp_daily, delta=True),
)
.sql
)
self.assertEqual(3, 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("second query for all DoD references"):
self.assertEqual(
"SELECT "
"TRUNC(TIMESTAMPADD('day',1,TRUNC(\"timestamp\",'DD')),'DD') \"$timestamp\","
'SUM("votes") "$votes_dod" '
'FROM "politics"."politician" '
'GROUP BY "$timestamp" '
'ORDER BY "$timestamp"',
str(queries[1]),
)
with self.subTest("third query for all YoY references"):
self.assertEqual(
"SELECT "
"TRUNC(TIMESTAMPADD('week',52,TRUNC(\"timestamp\",'DD')),'DD') \"$timestamp\","
'SUM("votes") "$votes_yoy" '
'FROM "politics"."politician" '
'GROUP BY "$timestamp" '
'ORDER BY "$timestamp"',
str(queries[2]),
)
def test_yoy_with_monthly_interval(self):
queries = (
mock_dataset.query.widget(
f.HighCharts().axis(f.HighCharts.LineSeries(mock_dataset.fields.votes))
)
.dimension(timestamp_monthly)
.reference(f.YearOverYear(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",\'MM\') "$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('year',1,TRUNC(\"timestamp\",'MM')),'MM') \"$timestamp\","
'SUM("votes") "$votes_yoy" '
'FROM "politics"."politician" '
'GROUP BY "$timestamp" '
'ORDER BY "$timestamp"',
str(queries[1]),
)
def test_adapt_dow_for_leap_year_for_yoy_reference_with_date_filter(self):
queries = slicer.data \
.widget(f.HighCharts()
.axis(f.HighCharts.LineSeries(slicer.metrics.votes))) \
.dimension(slicer.dimensions.timestamp(f.weekly)) \
.reference(f.YearOverYear(slicer.dimensions.timestamp)) \
.filter(slicer.dimensions.timestamp.between(date(2018, 1, 1), date(2018, 1, 31))) \
.queries
self.assertEqual(2, len(queries))
with self.subTest('base query is same as without reference'):
self.assertEqual('SELECT '
'TRUNC("timestamp",\'IW\') "$d$timestamp",'
'SUM("votes") "$m$votes" '
'FROM "politics"."politician" '
'WHERE "timestamp" BETWEEN \'2018-01-01\' AND \'2018-01-31\' '
'GROUP BY "$d$timestamp" '
'ORDER BY "$d$timestamp"', str(queries[0]))
with self.subTest('reference query is same as base query with filter on reference dimension shifted'):
self.assertEqual('SELECT '
'TIMESTAMPADD(\'year\',-1,TRUNC(TIMESTAMPADD(\'year\',1,"timestamp"),\'IW\')) '
'"$d$timestamp",'
'SUM("votes") "$m$votes_yoy" '
'FROM "politics"."politician" '
'WHERE TIMESTAMPADD(\'year\',1,"timestamp") BETWEEN \'2018-01-01\' AND \'2018-01-31\' '
'GROUP BY "$d$timestamp" '
'ORDER BY "$d$timestamp"', str(queries[1]))
def test_reference_with_dimension_using_display_definition_includes_it_in_all_queries(self):
queries = slicer.data \
.widget(f.HighCharts()
.axis(f.HighCharts.LineSeries(slicer.metrics.votes))) \
.dimension(slicer.dimensions.timestamp) \
.dimension(slicer.dimensions.candidate) \
.reference(f.YearOverYear(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",'
'"candidate_id" "$d$candidate",'
'"candidate_name" "$d$candidate_display",'
'SUM("votes") "$m$votes" '
'FROM "politics"."politician" '
'GROUP BY "$d$timestamp","$d$candidate","$d$candidate_display" '
'ORDER BY "$d$timestamp","$d$candidate_display"', str(queries[0]))
with self.subTest('reference query is same as base query with filter on reference dimension shifted'):
self.assertEqual('SELECT '
'TRUNC(TIMESTAMPADD(\'year\',1,"timestamp"),\'DD\') "$d$timestamp",'
'"candidate_id" "$d$candidate",'
'"candidate_name" "$d$candidate_display",'
'SUM("votes") "$m$votes_yoy" '
'FROM "politics"."politician" '
'GROUP BY "$d$timestamp","$d$candidate","$d$candidate_display" '
'ORDER BY "$d$timestamp","$d$candidate_display"', str(queries[1]))
def test_dimension_with_multiple_references(self):
queries = slicer.data \
.widget(f.HighCharts()
.axis(f.HighCharts.LineSeries(slicer.metrics.votes))) \
.dimension(slicer.dimensions.timestamp) \
.reference(f.DayOverDay(slicer.dimensions.timestamp)) \
.reference(f.YearOverYear(slicer.dimensions.timestamp, delta_percent=True)) \
.queries
self.assertEqual(3, 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 filter on reference dimension shifted'):
self.assertEqual('SELECT '
'TRUNC(TIMESTAMPADD(\'day\',1,"timestamp"),\'DD\') "$d$timestamp",'
'SUM("votes") "$m$votes_dod" '
'FROM "politics"."politician" '
'GROUP BY "$d$timestamp" '
'ORDER BY "$d$timestamp"', str(queries[1]))
with self.subTest('reference query is same as base query with filter on reference dimension shifted'):
self.assertEqual('SELECT '
'TRUNC(TIMESTAMPADD(\'year\',1,"timestamp"),\'DD\') "$d$timestamp",'
'SUM("votes") "$m$votes_yoy" '
'FROM "politics"."politician" '
'GROUP BY "$d$timestamp" '
'ORDER BY "$d$timestamp"', str(queries[2]))
def test_dimension_with_multiple_references(self):
queries = (
mock_dataset.query.widget(
f.HighCharts().axis(f.HighCharts.LineSeries(mock_dataset.fields.votes))
)
.dimension(timestamp_daily)
.reference(f.DayOverDay(mock_dataset.fields.timestamp))
.reference(
f.YearOverYear(mock_dataset.fields.timestamp, delta_percent=True)
)
.sql
)
self.assertEqual(3, 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 filter on reference dimension shifted"
):
self.assertEqual(
"SELECT "
"TRUNC(TIMESTAMPADD('day',1,TRUNC(\"timestamp\",'DD')),'DD') \"$timestamp\","
'SUM("votes") "$votes_dod" '
'FROM "politics"."politician" '
'GROUP BY "$timestamp" '
'ORDER BY "$timestamp"',
str(queries[1]),
)
with self.subTest(
"reference query is same as base query with filter on reference dimension shifted"
):
self.assertEqual(
"SELECT "
"TRUNC(TIMESTAMPADD('week',52,TRUNC(\"timestamp\",'DD')),'DD') \"$timestamp\","
'SUM("votes") "$votes_yoy" '
'FROM "politics"."politician" '
'GROUP BY "$timestamp" '
'ORDER BY "$timestamp"',
str(queries[2]),
)
def test_reference_with_dimension_using_display_definition_includes_it_in_all_queries(
self,
):
queries = (
mock_dataset.query.widget(
f.HighCharts().axis(f.HighCharts.LineSeries(mock_dataset.fields.votes))
)
.dimension(timestamp_daily)
.dimension(mock_dataset.fields["candidate-name"])
.reference(f.YearOverYear(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",'
'"candidate_name" "$candidate-name",'
'SUM("votes") "$votes" '
'FROM "politics"."politician" '
'GROUP BY "$timestamp","$candidate-name" '
'ORDER BY "$timestamp","$candidate-name"',
str(queries[0]),
)
with self.subTest(
"reference query is same as base query with filter on reference dimension shifted"
):
self.assertEqual(
"SELECT "
"TRUNC(TIMESTAMPADD('week',52,TRUNC(\"timestamp\",'DD')),'DD') \"$timestamp\","
'"candidate_name" "$candidate-name",'
'SUM("votes") "$votes_yoy" '
'FROM "politics"."politician" '
'GROUP BY "$timestamp","$candidate-name" '
'ORDER BY "$timestamp","$candidate-name"',
str(queries[1]),
)
def test_reference_queries_with_multiple_dimensions_includes_all_dimensions(
self):
queries = (mock_dataset.query.widget(f.HighCharts().axis(
f.HighCharts.LineSeries(mock_dataset.fields.votes))).dimension(
timestamp_daily).dimension(
mock_dataset.fields.political_party).reference(
f.YearOverYear(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",'
'"political_party" "$political_party",'
'SUM("votes") "$votes" '
'FROM "politics"."politician" '
'GROUP BY "$timestamp","$political_party" '
'ORDER BY "$timestamp","$political_party" '
'LIMIT 200000',
str(queries[0]),
)
with self.subTest(
"reference query is same as base query with filter on reference dimension shifted"
):
self.assertEqual(
"SELECT "
"TRUNC(TIMESTAMPADD('week',52,TRUNC(\"timestamp\",'DD')),'DD') \"$timestamp\","
'"political_party" "$political_party",'
'SUM("votes") "$votes_yoy" '
'FROM "politics"."politician" '
'GROUP BY "$timestamp","$political_party" '
'ORDER BY "$timestamp","$political_party" '
'LIMIT 200000',
str(queries[1]),
)
