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