def test_adding_duplicate_reference_does_not_join_more_queries(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),
).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" '
'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('day',1,TRUNC(\"timestamp\",'DD')),'DD') \"$timestamp\","
'SUM("votes") "$votes_dod" '
'FROM "politics"."politician" '
'GROUP BY "$timestamp" '
'ORDER BY "$timestamp" '
'LIMIT 200000',
str(queries[1]),
)
def test_adding_duplicate_reference_does_not_join_more_queries(self):
queries = slicer.data \
.widget(f.HighCharts()
.axis(f.HighCharts.LineSeries(slicer.metrics.votes))) \
.dimension(slicer.dimensions.timestamp) \
.reference(f.DayOverDay(slicer.dimensions.timestamp),
f.DayOverDay(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 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]))
def test_use_same_nested_query_for_joining_references_with_same_period_and_dimension(self):
queries = slicer.data \
.widget(f.HighCharts()
.axis(f.HighCharts.LineSeries(slicer.metrics.votes))) \
.dimension(slicer.dimensions.timestamp) \
.reference(f.DayOverDay(slicer.dimensions.timestamp),
f.DayOverDay(slicer.dimensions.timestamp, delta=True),
f.DayOverDay(slicer.dimensions.timestamp, delta_percent=True)) \
.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 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]))
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_date_dim_with_annual_interval(self):
queries = (
mock_dataset.query.widget(
f.HighCharts().axis(f.HighCharts.LineSeries(mock_dataset.fields.votes))
)
.dimension(f.year(mock_dataset.fields.timestamp))
.reference(f.DayOverDay(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",\'Y\') "$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\",'Y')),'Y') \"$timestamp\","
'SUM("votes") "$votes_dod" '
'FROM "politics"."politician" '
'GROUP BY "$timestamp" '
'ORDER BY "$timestamp"',
str(queries[1]),
)
def test_filters_on_other_dimensions_are_not_adapted(self):
queries = slicer.data \
.widget(f.HighCharts()
.axis(f.HighCharts.LineSeries(slicer.metrics.votes))) \
.dimension(slicer.dimensions.timestamp) \
.reference(f.DayOverDay(slicer.dimensions.timestamp)) \
.filter(slicer.dimensions.timestamp
.between(date(2018, 1, 1), date(2018, 1, 31))) \
.filter(slicer.dimensions.political_party
.isin(['d'])) \
.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" '
'WHERE "timestamp" BETWEEN \'2018-01-01\' AND \'2018-01-31\' '
'AND "political_party" IN (\'d\') '
'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" '
'WHERE TIMESTAMPADD(\'day\',1,"timestamp") BETWEEN \'2018-01-01\' AND \'2018-01-31\' '
'AND "political_party" IN (\'d\') '
'GROUP BY "$d$timestamp" '
'ORDER BY "$d$timestamp"', str(queries[1]))
def test_single_reference_dod_with_dimension_but_not_reference_dimension_in_query_using_filter(self):
queries = slicer.data \
.widget(f.HighCharts()
.axis(f.HighCharts.LineSeries(slicer.metrics.votes))) \
.dimension(slicer.dimensions.political_party) \
.reference(f.DayOverDay(slicer.dimensions.timestamp)) \
.filter(slicer.dimensions.timestamp.between(date(2000, 1, 1), date(2000, 3, 1))) \
.queries
self.assertEqual(2, len(queries))
with self.subTest('base query is same as without reference'):
self.assertEqual('SELECT '
'"political_party" "$d$political_party",'
'SUM("votes") "$m$votes" '
'FROM "politics"."politician" '
'WHERE "timestamp" BETWEEN \'2000-01-01\' AND \'2000-03-01\' '
'GROUP BY "$d$political_party" '
'ORDER BY "$d$political_party"', str(queries[0]))
with self.subTest('reference query is same as base query with filter on reference dimension shifted'):
self.assertEqual('SELECT '
'"political_party" "$d$political_party",'
'SUM("votes") "$m$votes_dod" '
'FROM "politics"."politician" '
'WHERE TIMESTAMPADD(\'day\',1,"timestamp") BETWEEN \'2000-01-01\' AND \'2000-03-01\' '
'GROUP BY "$d$political_party" '
'ORDER BY "$d$political_party"', str(queries[1]))
def test_reference_on_dimension_with_weekly_interval(self):
weekly_timestamp = slicer.dimensions.timestamp(f.weekly)
queries = slicer.data \
.widget(f.HighCharts()
.axis(f.HighCharts.LineSeries(slicer.metrics.votes))) \
.dimension(weekly_timestamp) \
.reference(f.DayOverDay(weekly_timestamp)) \
.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" '
'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"),\'IW\') "$d$timestamp",'
'SUM("votes") "$m$votes_dod" '
'FROM "politics"."politician" '
'GROUP BY "$d$timestamp" '
'ORDER BY "$d$timestamp"', str(queries[1]))
def test_build_query_with_totals_cat_dimension_with_references(self):
queries = slicer.data \
.widget(f.DataTablesJS(slicer.metrics.votes)) \
.dimension(slicer.dimensions.timestamp,
slicer.dimensions.political_party.rollup()) \
.reference(f.DayOverDay(slicer.dimensions.timestamp)) \
.queries
self.assertEqual(len(queries), 4)
with self.subTest(
'base query is same as without references or totals'):
self.assertEqual(
'SELECT '
'TRUNC("timestamp",\'DD\') "$d$timestamp",'
'"political_party" "$d$political_party",'
'SUM("votes") "$m$votes" '
'FROM "politics"."politician" '
'GROUP BY "$d$timestamp","$d$political_party" '
'ORDER BY "$d$timestamp","$d$political_party"',
str(queries[0]))
with self.subTest('reference query is shifted'):
self.assertEqual(
'SELECT '
'TRUNC(TIMESTAMPADD(\'day\',1,"timestamp"),\'DD\') "$d$timestamp",'
'"political_party" "$d$political_party",'
'SUM("votes") "$m$votes_dod" '
'FROM "politics"."politician" '
'GROUP BY "$d$timestamp","$d$political_party" '
'ORDER BY "$d$timestamp","$d$political_party"',
str(queries[1]))
with self.subTest(
'base totals query is same as base query minus the totals dimension'
):
self.assertEqual(
'SELECT '
'TRUNC("timestamp",\'DD\') "$d$timestamp",'
'NULL "$d$political_party",'
'SUM("votes") "$m$votes" '
'FROM "politics"."politician" '
'GROUP BY "$d$timestamp" '
'ORDER BY "$d$timestamp","$d$political_party"',
str(queries[2]))
with self.subTest(
'reference total query is shifted without the totals dimension'
):
self.assertEqual(
'SELECT '
'TRUNC(TIMESTAMPADD(\'day\',1,"timestamp"),\'DD\') "$d$timestamp",'
'NULL "$d$political_party",'
'SUM("votes") "$m$votes_dod" '
'FROM "politics"."politician" '
'GROUP BY "$d$timestamp" '
'ORDER BY "$d$timestamp","$d$political_party"',
str(queries[3]))
def test_use_same_nested_query_for_joining_references_with_same_period_and_dimension(
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),
f.DayOverDay(mock_dataset.fields.timestamp, delta_percent=True),
)
.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 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]),
)
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_filters_on_other_dimensions_are_not_adapted(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))
.filter(
mock_dataset.fields.timestamp.between(
date(2018, 1, 1), date(2018, 1, 31)
)
)
.filter(mock_dataset.fields.political_party.isin(["d"]))
.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" '
"WHERE \"timestamp\" BETWEEN '2018-01-01' AND '2018-01-31' "
"AND \"political_party\" IN ('d') "
'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" '
"WHERE \"timestamp\" BETWEEN TIMESTAMPADD('day',-1,'2018-01-01') AND TIMESTAMPADD('day',-1,'2018-01-31') "
"AND \"political_party\" IN ('d') "
'GROUP BY "$timestamp" '
'ORDER BY "$timestamp"',
str(queries[1]),
)
def test_single_reference_dod_with_no_dimension_uses_multiple_from_clauses_instead_of_joins(self):
queries = slicer.data \
.widget(f.HighCharts()
.axis(f.HighCharts.LineSeries(slicer.metrics.votes))) \
.reference(f.DayOverDay(slicer.dimensions.timestamp)) \
.queries
self.assertEqual(2, len(queries))
with self.subTest('base query is same as without reference'):
self.assertEqual('SELECT '
'SUM("votes") "$m$votes" '
'FROM "politics"."politician"', str(queries[0]))
with self.subTest('reference query is same as base query'):
self.assertEqual('SELECT '
'SUM("votes") "$m$votes_dod" '
'FROM "politics"."politician"', str(queries[1]))
def test_reference_without_selecting_ref_dimension_using_date_range_filter(self):
queries = (
mock_dataset.query.widget(
f.HighCharts().axis(f.HighCharts.LineSeries(mock_dataset.fields.votes))
)
.dimension(mock_dataset.fields.political_party)
.reference(f.DayOverDay(mock_dataset.fields.timestamp))
.filter(
mock_dataset.fields.timestamp.between(
date(2000, 1, 1), date(2000, 3, 1)
)
)
.sql
)
self.assertEqual(2, len(queries))
with self.subTest("base query is same as without reference"):
self.assertEqual(
"SELECT "
'"political_party" "$political_party",'
'SUM("votes") "$votes" '
'FROM "politics"."politician" '
"WHERE \"timestamp\" BETWEEN '2000-01-01' AND '2000-03-01' "
'GROUP BY "$political_party" '
'ORDER BY "$political_party"',
str(queries[0]),
)
with self.subTest(
"reference query is same as base query with filter on reference dimension shifted"
):
self.assertEqual(
"SELECT "
'"political_party" "$political_party",'
'SUM("votes") "$votes_dod" '
'FROM "politics"."politician" '
"WHERE \"timestamp\" BETWEEN TIMESTAMPADD('day',-1,'2000-01-01') "
"AND TIMESTAMPADD('day',-1,'2000-03-01') "
'GROUP BY "$political_party" '
'ORDER BY "$political_party"',
str(queries[1]),
)
def test_reference_with_no_dimensions_or_filters_creates_same_query(self):
# TODO reduce this to a single query
queries = (mock_dataset.query.widget(f.HighCharts().axis(
f.HighCharts.LineSeries(mock_dataset.fields.votes))).reference(
f.DayOverDay(mock_dataset.fields.timestamp)).sql)
self.assertEqual(2, len(queries))
with self.subTest("base query is same as without reference"):
self.assertEqual(
'SELECT SUM("votes") "$votes" FROM "politics"."politician" ORDER BY 1 LIMIT 200000',
str(queries[0]),
)
with self.subTest("reference query is same as base query"):
self.assertEqual(
'SELECT SUM("votes") "$votes_dod" FROM "politics"."politician" ORDER BY 1 LIMIT 200000',
str(queries[1]),
)
def test_reference_filters_are_applied(self, mock_fetch: Mock, mock_2: Mock, mock_apply_reference_filters: Mock):
db = TestDatabase()
t0 = Table("test0")
dataset = DataSet(
table=t0,
database=db,
fields=[
Field(
"timestamp",
label="Timestamp",
definition=t0.timestamp,
data_type=DataType.date,
),
Field(
"metric0",
label="Metric0",
definition=t0.metric,
data_type=DataType.number,
),
],
)
mock_widget = f.Widget(dataset.fields.metric0)
mock_widget.transform = Mock()
reference_filter = ReferenceFilter(
dataset.fields.metric0,
ComparisonOperator.gt,
5
)
reference = f.DayOverDay(dataset.fields.timestamp, filters=[reference_filter])
df = pd.DataFrame.from_dict({"$value": [1]})
mock_fetch.return_value = 100, df
mock_apply_reference_filters.return_value = df
(
dataset.query()
.dimension(dataset.fields.timestamp)
.widget(mock_widget)
.reference(reference)
).fetch()
mock_apply_reference_filters.assert_called_once_with(df, reference)
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_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]),
)
