def test_dimension_is_replaced_by_default_in_the_target_dimension_place_when_result_set_filter_is_present(
self,
):
queries = (
ds.query.widget(f.Pandas(ds.fields.aggr_number))
.dimension(ds.fields.date)
.dimension(ds.fields.text)
.dimension(ds.fields.boolean)
.filter(f.ResultSet(ds.fields.text == "abc"))
.sql
)
self.assertEqual(len(queries), 1)
self.assertEqual(
"SELECT "
'"date" "$date",'
"CASE WHEN \"text\"='abc' THEN 'set(text=''abc'')' ELSE 'complement(text=''abc'')' END \"$text\","
'"boolean" "$boolean",'
'SUM("number") "$aggr_number" '
'FROM "test" '
'GROUP BY "$date","$text","$boolean" '
'ORDER BY "$date","$text","$boolean" '
'LIMIT 200000',
str(queries[0]),
)
def test_deeply_nested_dimension_filter_with_sets_for_data_blending(self):
field_alias = 'state'
fltr = mock_dataset_blender.fields.state.like(
fn.Concat(
fn.Upper(fn.Trim(fn.Concat('%ab', mock_dataset_blender.fields['candidate-id']))),
mock_dataset_blender.fields.winner,
fn.Concat(mock_dataset_blender.fields.timestamp.between('date1', 'date2'), 'c%'),
)
)
queries = (
mock_dataset_blender.query.widget(f.Pandas(mock_dataset_blender.fields['candidate-spend']))
.dimension(mock_dataset_blender.fields[field_alias])
.filter(f.ResultSet(fltr, set_label='set_A', complement_label='set_B'))
.sql
)
self.assertEqual(len(queries), 1)
self.assertEqual(
"SELECT "
f'"sq0"."${field_alias}" "${field_alias}",'
'"sq0"."$candidate-spend" "$candidate-spend" '
'FROM ('
'SELECT '
f'CASE WHEN {fltr} THEN \'set_A\' ELSE \'set_B\' END "${field_alias}",'
'SUM("candidate_spend") "$candidate-spend" '
'FROM "politics"."politician_spend" '
f'GROUP BY "${field_alias}"'
f') "sq0" '
f"ORDER BY \"${field_alias}\" "
"LIMIT 200000",
str(queries[0]),
)
def test_dimension_uses_both_set_and_complement_label_kwargs_when_available(self):
queries = (
ds.query.widget(f.Pandas(ds.fields.aggr_number))
.dimension(ds.fields.text)
.filter(
f.ResultSet(
ds.fields.text == "abc",
set_label="Text is ABC",
complement_label="Text is NOT ABC",
)
)
.sql
)
self.assertEqual(len(queries), 1)
self.assertEqual(
"SELECT "
"CASE WHEN \"text\"='abc' THEN 'Text is ABC' ELSE 'Text is NOT ABC' END "
"\"$text\","
'SUM("number") "$aggr_number" '
'FROM "test" '
"GROUP BY \"$text\" "
"ORDER BY \"$text\" "
"LIMIT 200000",
str(queries[0]),
)
def test_dimension_filter_variations_with_sets(self):
for field_alias, fltr in [
('text', ds.fields.text.like("%abc%")),
('text', ds.fields.text.not_like("%abc%")),
('text', ds.fields.text.isin(["abc"])),
('text', ds.fields.text.notin(["abc"])),
('date', ds.fields.date.between('date1', 'date2')),
('number', ds.fields.number.between(5, 15)),
('number', ds.fields.number.isin([1, 2, 3])),
('number', ds.fields.number.notin([1, 2, 3])),
]:
fltr_sql = fltr.definition.get_sql(quote_char="")
with self.subTest(fltr_sql):
queries = (
ds.query.widget(f.Pandas(ds.fields.aggr_number))
.dimension(ds.fields[field_alias])
.filter(f.ResultSet(fltr, set_label='set_A', complement_label='set_B'))
.sql
)
self.assertEqual(len(queries), 1)
self.assertEqual(
"SELECT "
f"CASE WHEN {fltr} THEN 'set_A' ELSE 'set_B' END \"${field_alias}\","
'SUM("number") "$aggr_number" '
'FROM "test" '
f"GROUP BY \"${field_alias}\" "
f"ORDER BY \"${field_alias}\" "
"LIMIT 200000",
str(queries[0]),
)
def test_dimension_is_inserted_before_conditional_dimension_when_result_set_filter_wont_ignore_dimensions(
self,
):
queries = (
ds.query.widget(f.Pandas(ds.fields.aggr_number))
.dimension(ds.fields.text)
.filter(
f.ResultSet(
ds.fields.text == "abc", will_replace_referenced_dimension=False
)
)
.sql
)
self.assertEqual(len(queries), 1)
self.assertEqual(
"SELECT "
"CASE WHEN \"text\"='abc' THEN 'set(text=''abc'')' ELSE 'complement(text=''abc'')' END \"$set(text='abc')\","
'"text" "$text",'
'SUM("number") "$aggr_number" '
'FROM "test" '
'GROUP BY "$set(text=\'abc\')","$text" '
'ORDER BY "$set(text=\'abc\')","$text" '
'LIMIT 200000',
str(queries[0]),
)
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_deeply_nested_dimension_filter_with_sets(self):
field_alias = 'text'
fltr = ds.fields.text.like(
fn.Concat(
fn.Upper(fn.Trim(fn.Concat('%ab', ds.fields.number))),
ds.fields.aggr_number,
fn.Concat(ds.fields.date.between('date1', 'date2'), 'c%'),
))
queries = (ds.query.widget(f.Pandas(ds.fields.aggr_number)).dimension(
ds.fields[field_alias]).filter(
f.ResultSet(fltr, set_label='set_A',
complement_label='set_B')).sql)
self.assertEqual(len(queries), 1)
self.assertEqual(
"SELECT "
f"CASE WHEN {fltr} THEN 'set_A' ELSE 'set_B' END \"${field_alias}\","
'SUM("number") "$aggr_number" '
'FROM "test" '
f"GROUP BY \"${field_alias}\" "
f"ORDER BY \"${field_alias}\" "
"LIMIT 200000",
str(queries[0]),
)
def test_dimension_breaks_complement_down_even_when_both_labels_are_set_but_wont_group_complement(
self,
):
queries = (
ds.query.widget(f.Pandas(ds.fields.aggr_number))
.dimension(ds.fields.text)
.filter(
f.ResultSet(
ds.fields.text == "abc",
set_label="IS ABC",
complement_label="OTHERS",
will_group_complement=False,
)
)
.sql
)
self.assertEqual(len(queries), 1)
self.assertEqual(
"SELECT "
"CASE WHEN \"text\"='abc' THEN 'IS ABC' ELSE \"text\" END \"$text\","
'SUM("number") "$aggr_number" '
'FROM "test" '
"GROUP BY \"$text\" "
"ORDER BY \"$text\" "
"LIMIT 200000",
str(queries[0]),
)
def test_dimension_with_dimension_modifier_is_replaced_by_default_when_result_set_filter_is_present(
self, ):
queries = (ds.query.widget(f.Pandas(ds.fields.aggr_number)).dimension(
ds.fields.date).dimension(f.Rollup(ds.fields.boolean)).filter(
f.ResultSet(ds.fields.boolean == True)).sql)
self.assertEqual(len(queries), 2)
with self.subTest('base query is the same as without totals'):
self.assertEqual(
"SELECT "
'"date" "$date",'
"CASE WHEN \"boolean\"=true THEN 'set(boolean=true)' ELSE 'complement(boolean=true)' END \"$boolean\","
'SUM("number") "$aggr_number" '
'FROM "test" '
'GROUP BY "$date","$boolean" '
'ORDER BY "$date","$boolean" '
'LIMIT 200000',
str(queries[0]),
)
with self.subTest(
'totals dimension is replaced with _FIREANT_ROLLUP_VALUE_'):
self.assertEqual(
"SELECT "
'"date" "$date",'
'\'_FIREANT_ROLLUP_VALUE_\' "$boolean",'
'SUM("number") "$aggr_number" '
'FROM "test" '
'GROUP BY "$date" '
'ORDER BY "$date","$boolean" '
'LIMIT 200000',
str(queries[1]),
)
def test_lt_expr(self):
queries = ds.query.widget(f.Pandas(ds.fields.aggr_number)).filter(ds.fields.number < 5).sql
self.assertEqual(len(queries), 1)
self.assertEqual(
'SELECT SUM("number") "$aggr_number" FROM "test" WHERE "number"<5 ORDER BY 1 LIMIT 200000',
str(queries[0]),
)
def test_is_true(self):
queries = ds.query.widget(f.Pandas(ds.fields.aggr_number)).filter(ds.fields.boolean.is_(True)).sql
self.assertEqual(len(queries), 1)
self.assertEqual(
'SELECT SUM("number") "$aggr_number" FROM "test" WHERE "boolean" ORDER BY 1 LIMIT 200000',
str(queries[0]),
)
def test_le_expr(self):
queries = ds.query.widget(f.Pandas(ds.fields.aggr_number)).filter(ds.fields.number <= 5).sql
self.assertEqual(len(queries), 1)
self.assertEqual(
"SELECT " 'SUM("number") "$aggr_number" ' 'FROM "test" ' 'WHERE "number"<=5 ' 'ORDER BY 1 ' 'LIMIT 200000',
str(queries[0]),
)
def test_eq_expr_number_0(self):
queries = ds.query.widget(f.Pandas(ds.fields.aggr_number)).filter(ds.fields.boolean == 0).sql
self.assertEqual(len(queries), 1)
self.assertEqual(
"SELECT " 'SUM("number") "$aggr_number" ' 'FROM "test" ' 'WHERE "boolean"=0 ' 'ORDER BY 1 ' 'LIMIT 200000',
str(queries[0]),
)
def test_void_filter_with_no_other_filters(self):
queries = (ds.query.widget(f.Pandas(ds.fields.aggr_number)).filter(
ds.fields.date.void()).sql)
self.assertEqual(len(queries), 1)
self.assertEqual(
"SELECT "
'SUM("number") "$aggr_number" '
'FROM "test"', str(queries[0]))
def test_between_expr(self):
queries = (ds.query.widget(f.Pandas(ds.fields.aggr_number)).filter(
ds.fields.aggr_number.between(5, 7)).sql)
self.assertEqual(len(queries), 1)
self.assertEqual(
"SELECT "
'SUM("number") "$aggr_number" '
'FROM "test" '
'HAVING SUM("number") BETWEEN 5 AND 7',
str(queries[0]),
)
def test_void_filter_with_a_dimension_filter(self):
queries = (ds.query.widget(f.Pandas(ds.fields.aggr_number)).filter(
ds.fields.date == "2019-03-06").filter(ds.fields.date.void()).sql)
self.assertEqual(len(queries), 1)
self.assertEqual(
"SELECT "
'SUM("number") "$aggr_number" '
'FROM "test" '
"WHERE \"date\"='2019-03-06'",
str(queries[0]),
)
def test_void_filter_with_a_metric_filter(self):
queries = (ds.query.widget(f.Pandas(ds.fields.aggr_number)).filter(
ds.fields.aggr_number > 10).filter(ds.fields.date.void()).sql)
self.assertEqual(len(queries), 1)
self.assertEqual(
"SELECT "
'SUM("number") "$aggr_number" '
'FROM "test" '
'HAVING SUM("number")>10',
str(queries[0]),
)
def test_ne_expr_str(self):
queries = (ds.query.widget(f.Pandas(
ds.fields.aggr_number)).filter(ds.fields.text != "abc").sql)
self.assertEqual(len(queries), 1)
self.assertEqual(
"SELECT "
'SUM("number") "$aggr_number" '
'FROM "test" '
"WHERE \"text\"<>'abc'",
str(queries[0]),
)
def test_notin_expr_date(self):
queries = (ds.query.widget(f.Pandas(ds.fields.aggr_number)).filter(
ds.fields.text.notin(("abc", "def"))).sql)
self.assertEqual(len(queries), 1)
self.assertEqual(
"SELECT "
'SUM("number") "$aggr_number" '
'FROM "test" '
"WHERE \"text\" NOT IN ('abc','def')",
str(queries[0]),
)
def test_not_like_expr_str(self):
queries = (ds.query.widget(f.Pandas(ds.fields.aggr_number)).filter(
ds.fields.text.not_like("abc%")).sql)
self.assertEqual(len(queries), 1)
self.assertEqual(
"SELECT "
'SUM("number") "$aggr_number" '
'FROM "test" '
"WHERE NOT LOWER(\"text\") LIKE LOWER('abc%')",
str(queries[0]),
)
def test_notin_expr(self):
queries = (ds.query.widget(f.Pandas(ds.fields.aggr_number)).filter(
ds.fields.date.notin((date(2019, 3, 6), date(2019, 3, 7)))).sql)
self.assertEqual(len(queries), 1)
self.assertEqual(
"SELECT "
'SUM("number") "$aggr_number" '
'FROM "test" '
"WHERE \"date\" NOT IN ('2019-03-06','2019-03-07')",
str(queries[0]),
)
def test_eq_expr_number_1(self):
queries = (ds.query.widget(f.Pandas(
ds.fields.aggr_number)).filter(ds.fields.boolean == 1).sql)
self.assertEqual(len(queries), 1)
self.assertEqual(
"SELECT "
'SUM("number") "$aggr_number" '
'FROM "test" '
'WHERE "boolean"=1',
str(queries[0]),
)
def test_notin_expr(self):
queries = (ds.query.widget(f.Pandas(ds.fields.aggr_number)).filter(
ds.fields.aggr_number.notin((5, 7))).sql)
self.assertEqual(len(queries), 1)
self.assertEqual(
"SELECT "
'SUM("number") "$aggr_number" '
'FROM "test" '
'HAVING SUM("number") NOT IN (5,7)',
str(queries[0]),
)
def test_le_expr(self):
queries = (ds.query.widget(f.Pandas(
ds.fields.aggr_number)).filter(ds.fields.aggr_number <= 5).sql)
self.assertEqual(len(queries), 1)
self.assertEqual(
"SELECT "
'SUM("number") "$aggr_number" '
'FROM "test" '
'HAVING SUM("number")<=5',
str(queries[0]),
)
def test_eq_expr_datetime(self):
queries = (ds.query.widget(f.Pandas(ds.fields.aggr_number)).filter(
ds.fields.date == datetime(2019, 3, 6, 9, 36, 11)).sql)
self.assertEqual(len(queries), 1)
self.assertEqual(
"SELECT "
'SUM("number") "$aggr_number" '
'FROM "test" '
"WHERE \"date\"='2019-03-06T09:36:11'",
str(queries[0]),
)
def test_is_false(self):
queries = (ds.query.widget(f.Pandas(ds.fields.aggr_number)).filter(
ds.fields.boolean.is_(False)).sql)
self.assertEqual(len(queries), 1)
self.assertEqual(
"SELECT "
'SUM("number") "$aggr_number" '
'FROM "test" '
'WHERE NOT "boolean"',
str(queries[0]),
)
def test_ne_expr(self):
queries = (ds.query.widget(f.Pandas(ds.fields.aggr_number)).filter(
ds.fields.date != date(2019, 3, 6)).sql)
self.assertEqual(len(queries), 1)
self.assertEqual(
"SELECT "
'SUM("number") "$aggr_number" '
'FROM "test" '
"WHERE \"date\"<>'2019-03-06'",
str(queries[0]),
)
def test_between_expr(self):
queries = (ds.query.widget(f.Pandas(ds.fields.aggr_number)).filter(
ds.fields.date.between(date(2019, 3, 6), date(2019, 3, 7))).sql)
self.assertEqual(len(queries), 1)
self.assertEqual(
"SELECT "
'SUM("number") "$aggr_number" '
'FROM "test" '
"WHERE \"date\" BETWEEN '2019-03-06' AND '2019-03-07'",
str(queries[0]),
)
def test_eq_expr_float(self):
queries = ds.query.widget(f.Pandas(ds.fields.aggr_number)).filter(ds.fields.aggr_number == 1.0).sql
self.assertEqual(len(queries), 1)
self.assertEqual(
"SELECT "
'SUM("number") "$aggr_number" '
'FROM "test" '
'HAVING SUM("number")=1.0 '
'ORDER BY 1 '
'LIMIT 200000',
str(queries[0]),
)
def test_in_expr(self):
queries = ds.query.widget(f.Pandas(ds.fields.aggr_number)).filter(ds.fields.aggr_number.isin((5, 7))).sql
self.assertEqual(len(queries), 1)
self.assertEqual(
"SELECT "
'SUM("number") "$aggr_number" '
'FROM "test" '
'HAVING SUM("number") IN (5,7) '
'ORDER BY 1 '
'LIMIT 200000',
str(queries[0]),
)