def test_verticaoracle_multiple_rollups(self):
q = Query.from_(self.table).select(self.table.foo, self.table.fiz,
fn.Sum(self.table.bar)).rollup(
self.table.foo, ).rollup(
self.table.fiz, )
self.assertEqual(
'SELECT "foo","fiz",SUM("bar") FROM "abc" GROUP BY ROLLUP("foo"),ROLLUP("fiz")',
str(q))
def test_orderby_alias(self):
bar = self.t.bar.as_('bar01')
q = Query.from_(self.t) \
.select(fn.Sum(self.t.foo), bar) \
.orderby(bar)
self.assertEqual(
'SELECT SUM("foo"),"bar" "bar01" FROM "abc" ORDER BY "bar01"',
q.get_sql())
def test_groupby__no_alias_platforms(self):
bar = self.t.bar.as_("bar01")
for query_cls in [MSSQLQuery, OracleQuery]:
q = query_cls.from_(self.t).select(fn.Sum(self.t.foo),
bar).groupby(bar)
self.assertEqual(
'SELECT SUM("foo"),"bar" "bar01" FROM "abc" GROUP BY "bar"',
str(q))
def test_verticaoracle_from_parameters(self):
q = Query.from_(self.table).select(self.table.foo, self.table.fiz,
fn.Sum(self.table.bar)).groupby(
self.table.foo, ).rollup(
self.table.fiz, )
self.assertEqual(
'SELECT "foo","fiz",SUM("bar") FROM "abc" GROUP BY "foo",ROLLUP("fiz")',
str(q))
def test_select_field_from_missing_table(self):
with self.assertRaises(JoinException):
Query.from_(self.table_abc).select(self.table_efg.foo)
with self.assertRaises(JoinException):
Query.from_(self.table_abc).where(self.table_efg.foo == 0)
with self.assertRaises(JoinException):
Query.from_(self.table_abc).where(fn.Sum(self.table_efg.foo) == 0)
with self.assertRaises(JoinException):
Query.from_(self.table_abc).select(fn.Sum(self.table_abc.bar * 2) + fn.Sum(self.table_efg.foo * 2))
with self.assertRaises(JoinException):
Query.from_(self.table_abc).groupby(self.table_efg.foo)
with self.assertRaises(JoinException):
Query.from_(self.table_abc).groupby(self.table_abc.foo).having(self.table_efg.bar)
def test_groupby__no_alias(self):
bar = self.t.bar.as_('bar01')
q = Query.from_(self.t) \
.select(fn.Sum(self.t.foo), bar) \
.groupby(bar)
self.assertEqual(
'SELECT SUM("foo"),"bar" "bar01" FROM "abc" GROUP BY "bar"',
q.get_sql(groupby_alias=False))
def test_having_join_and_equality(self):
q = (
Query.from_(self.table_abc)
.join(self.table_efg)
.on(self.table_abc.foo == self.table_efg.foo)
.select(self.table_abc.foo, fn.Sum(self.table_efg.bar), self.table_abc.buz)
.groupby(self.table_abc.foo)
.having(self.table_abc.buz == "fiz")
.having(fn.Sum(self.table_efg.bar) > 100)
)
self.assertEqual(
'SELECT "abc"."foo",SUM("efg"."bar"),"abc"."buz" FROM "abc" '
'JOIN "efg" ON "abc"."foo"="efg"."foo" '
'GROUP BY "abc"."foo" '
'HAVING "abc"."buz"=\'fiz\' AND SUM("efg"."bar")>100',
str(q),
)
def _test_rounded_timeseries(self, increment):
rounded_dt = settings.database.round_date(self.mock_table.dt, increment)
return self.manager._build_data_query(
table=self.mock_table,
joins=[],
metrics=OrderedDict([
('clicks', fn.Sum(self.mock_table.clicks)),
('roi', fn.Sum(self.mock_table.revenue) / fn.Sum(self.mock_table.cost)),
]),
dimensions=OrderedDict([
('date', rounded_dt)
]),
mfilters=[],
dfilters=[],
references={},
rollup=[],
)
def test_groupby__sum_distinct(self):
q = (
Query.from_(self.t)
.groupby(self.t.foo)
.select(self.t.foo, fn.Sum(self.t.bar).distinct())
)
self.assertEqual(
'SELECT "foo",SUM(DISTINCT "bar") FROM "abc" GROUP BY "foo"', str(q)
)
def test_groupby__sum_filter(self):
q = (Query.from_(self.t).groupby(self.t.foo).select(
self.t.foo,
fn.Sum(self.t.bar).filter(self.t.id.eq(1) & self.t.cid.gt(2)),
))
self.assertEqual(
'SELECT "foo",SUM("bar") FILTER(WHERE "id"=1 AND "cid">2) FROM "abc" GROUP BY "foo"',
str(q),
)
def test_prefixes_added_to_groupby(self):
test_query = Query.from_(self.table_abc).join(self.table_efg).on(
self.table_abc.foo == self.table_efg.bar).select(
self.table_abc.foo,
fn.Sum(self.table_efg.buz)).groupby(self.table_abc.foo)
self.assertEqual(
'SELECT "abc"."foo",SUM("efg"."buz") FROM "abc" '
'JOIN "efg" ON "abc"."foo"="efg"."bar" '
'GROUP BY "abc"."foo"', str(test_query))
def test_having_join_and_equality(self):
q = Query.from_(self.table_abc).join(
self.table_efg
).on(
self.table_abc.foo == self.table_efg.foo
).select(
self.table_abc.foo, fn.Sum(self.table_efg.bar), self.table_abc.buz
).groupby(
self.table_abc.foo
).having(
self.table_abc.buz == 'fiz'
).having(
fn.Sum(self.table_efg.bar) > 100
)
self.assertEqual('SELECT "abc"."foo",SUM("efg"."bar"),"abc"."buz" FROM "abc" '
'JOIN "efg" ON "abc"."foo"="efg"."foo" '
'GROUP BY "abc"."foo" '
"HAVING \"abc\".\"buz\"='fiz' AND SUM(\"efg\".\"bar\")>100", str(q))
def test_mysql_rollup_two_groupbys(self):
q = Query.from_(self.table).select(self.table.foo, self.table.fiz,
fn.Sum(self.table.bar)).rollup(
self.table.foo,
self.table.fiz,
vendor='mysql')
self.assertEqual(
'SELECT "foo","fiz",SUM("bar") FROM "abc" GROUP BY "foo","fiz" WITH ROLLUP',
str(q))
def test_metrics(self):
query = self.manager._build_data_query(
table=self.mock_table,
joins=[],
metrics=OrderedDict([
('clicks', fn.Sum(self.mock_table.clicks)),
('roi', fn.Sum(self.mock_table.revenue) /
fn.Sum(self.mock_table.cost)),
]),
dimensions={},
mfilters=[],
dfilters=[],
references={},
rollup=[],
)
self.assertEqual(
'SELECT SUM("clicks") "clicks",SUM("revenue")/SUM("cost") "roi" '
'FROM "test_table"', str(query))
def test_metrics_with_joins(self):
rounded_dt = settings.database.round_date(self.mock_table.dt, 'DD')
locale = self.mock_table.locale
query = self.manager._build_data_query(
table=self.mock_table,
joins=[
(self.mock_join1,
self.mock_table.hotel_id == self.mock_join1.hotel_id,
JoinType.left),
],
metrics=OrderedDict([
('clicks', fn.Sum(self.mock_table.clicks)),
('roi', fn.Sum(self.mock_table.revenue) /
fn.Sum(self.mock_table.cost)),
('hotel_name', self.mock_join1.hotel_name),
('hotel_address', self.mock_join1.address),
('city_id', self.mock_join1.ctid),
('city_name', self.mock_join1.city_name),
]),
dimensions=OrderedDict([
('date', rounded_dt),
('locale', locale),
]),
mfilters=[],
dfilters=[],
references={},
rollup=[],
)
self.assertEqual(
'SELECT '
'ROUND("test_table"."dt",\'DD\') "date","test_table"."locale" "locale",'
'SUM("test_table"."clicks") "clicks",'
'SUM("test_table"."revenue")/SUM("test_table"."cost") "roi",'
'"test_join1"."hotel_name" "hotel_name","test_join1"."address" "hotel_address",'
'"test_join1"."ctid" "city_id","test_join1"."city_name" "city_name" '
'FROM "test_table" '
'LEFT JOIN "test_join1" ON "test_table"."hotel_id"="test_join1"."hotel_id" '
'GROUP BY ROUND("test_table"."dt",\'DD\'),"test_table"."locale" '
'ORDER BY ROUND("test_table"."dt",\'DD\'),"test_table"."locale"',
str(query))
def test_verticaoracle_from_two_groupbys(self):
q = (Query.from_(self.table).select(self.table.foo, self.table.fiz,
fn.Sum(self.table.bar)).rollup(
self.table.foo,
self.table.fiz,
))
self.assertEqual(
'SELECT "foo","fiz",SUM("bar") FROM "abc" GROUP BY ROLLUP("foo","fiz")',
str(q),
)
def test_groupby__alias_with_join(self):
table1 = Table('table1', alias='t1')
bar = table1.bar.as_('bar01')
q = Query.from_(self.t) \
.join(table1).on(self.t.id == table1.t_ref) \
.select(fn.Sum(self.t.foo), bar) \
.groupby(bar)
self.assertEqual('SELECT SUM("abc"."foo"),"t1"."bar" "bar01" FROM "abc" '
'JOIN "table1" "t1" ON "abc"."id"="t1"."t_ref" '
'GROUP BY "bar01"', str(q))
def setUpClass(cls):
test_table = Table('test_table')
cls.test_slicer = Slicer(
table=test_table,
database=TestDatabase(),
metrics=[
# Metric with defaults
Metric('clicks', 'Clicks'),
Metric('conversions', 'Conversions'),
Metric('roi', 'ROI', definition=fn.Sum(test_table.revenue) / fn.Sum(test_table.cost)),
Metric('rpc', 'RPC', definition=fn.Sum(test_table.revenue) / fn.Sum(test_table.clicks)),
Metric('cpc', 'CPC', definition=fn.Sum(test_table.cost) / fn.Sum(test_table.clicks)),
],
dimensions=[
# Continuous date dimension
DatetimeDimension('date', definition=test_table.dt),
# Continuous integer dimension
ContinuousDimension('clicks', label='Clicks CUSTOM LABEL', definition=test_table.clicks),
# Categorical dimension with fixed options
CategoricalDimension('locale', 'Locale', definition=test_table.locale,
display_options=[DimensionValue('us', 'United States'),
DimensionValue('de', 'Germany')]),
# Unique Dimension with single ID field
UniqueDimension('account', 'Account', definition=test_table.account_id,
display_field=test_table.account_name),
]
)
cls.test_slicer.manager.data = Mock()
cls.test_slicer.manager.display_schema = Mock()
def __init__(self, da=None):
super().__init__(da=da)
self.formats.update({
'Total': '{:,.0f}',
'SMS': '{:,.0f}',
'Suncor': '{:,.0f}',
'SMS %': '{:.2%}',
'Suncor %': '{:.2%}'
})
a, b = self.a, self.b
total = fn.Sum(a.Duration).as_('Total')
sum_sms = fn.Sum(a.SMS).as_('Sum_SMS')
sum_suncor = fn.Sum(a.Suncor).as_('Sum_Suncor')
cols = [a.CategoryAssigned, total, sum_sms, sum_suncor]
q = self.q \
.groupby(a.CategoryAssigned) \
.orderby(total) \
f.set_self(vars())
def test_use_double_quotes_on_alias_but_not_on_terms(self):
idx = self.table_abc.index.as_("idx")
val = fn.Sum(self.table_abc.value).as_("val")
q = SnowflakeQuery.from_(self.table_abc).select(
idx, val).groupby(idx).orderby(idx)
self.assertEqual(
'SELECT index "idx",SUM(value) "val" '
"FROM abc "
'GROUP BY "idx" '
'ORDER BY "idx"',
q.get_sql(with_namespace=True),
)
def _indicator(q: QueryBuilder, indicator: ReportIndicator, column: Column) -> QueryBuilder:
if indicator.arithmetic == "sum":
return q.select(fn.Sum(parse_parameter(column.parameter)))
elif indicator.arithmetic == "avg":
return q.select(fn.Avg(parse_parameter(column.parameter)))
elif indicator.arithmetic == "max":
return q.select(fn.Max(parse_parameter(column.parameter)))
elif indicator.arithmetic == "min":
return q.select(fn.Min(parse_parameter(column.parameter)))
elif indicator.arithmetic == "count":
return q.select(fn.Count(parse_parameter(column.parameter)))
else:
return q.select(fn.Max(parse_parameter(column.parameter)))
def test_metrics_filters(self):
query = self.manager._build_data_query(
table=self.mock_table,
joins=[],
metrics=OrderedDict([
('clicks', fn.Sum(self.mock_table.clicks)),
('roi', fn.Sum(self.mock_table.revenue) /
fn.Sum(self.mock_table.cost)),
]),
dimensions=OrderedDict([('device_type',
self.mock_table.device_type)]),
mfilters=[],
dfilters=[self.mock_table.dt[date(2000, 1, 1):date(2001, 1, 1)]],
references={},
rollup=[],
)
self.assertEqual(
'SELECT "device_type" "device_type",SUM("clicks") "clicks",SUM("revenue")/SUM("cost") "roi" '
'FROM "test_table" '
'WHERE "dt" BETWEEN \'2000-01-01\' AND \'2001-01-01\' '
'GROUP BY "device_type" '
'ORDER BY "device_type"', str(query))
def test_verticaoracle_func_partial(self):
q = Query.from_(self.table).select(self.table.foo, self.table.fiz,
self.table.buz,
fn.Sum(self.table.bar)).groupby(
Rollup(
self.table.foo,
self.table.fiz,
),
self.table.buz,
)
self.assertEqual(
'SELECT "foo","fiz","buz",SUM("bar") FROM "abc" GROUP BY ROLLUP("foo","fiz"),"buz"',
str(q))
def test_multidimension_categorical(self):
query = self.manager._build_data_query(
table=self.mock_table,
joins=[],
metrics=OrderedDict([
('clicks', fn.Sum(self.mock_table.clicks)),
('roi', fn.Sum(self.mock_table.revenue) / fn.Sum(self.mock_table.cost)),
]),
dimensions=OrderedDict([
('device_type', self.mock_table.device_type),
('locale', self.mock_table.locale),
]),
mfilters=[],
dfilters=[],
references={},
rollup=[],
)
self.assertEqual(
'SELECT "device_type" "device_type","locale" "locale",'
'SUM("clicks") "clicks",SUM("revenue")/SUM("cost") "roi" '
'FROM "test_table" '
'GROUP BY "device_type","locale" '
'ORDER BY "device_type","locale"', str(query))
def test_groupby_with_case_uses_the_alias(self):
q = Query.from_(self.t).select(
fn.Sum(self.t.foo).as_('bar'),
Case().when(self.t.fname == "Tom",
"It was Tom").else_("It was someone else.").
as_('who_was_it')).groupby(Case().when(
self.t.fname == "Tom",
"It was Tom").else_("It was someone else.").as_('who_was_it'))
self.assertEqual(
"SELECT SUM(\"foo\") \"bar\","
"CASE WHEN \"fname\"='Tom' THEN 'It was Tom' "
"ELSE 'It was someone else.' END \"who_was_it\" "
"FROM \"abc\" "
"GROUP BY \"who_was_it\"", str(q))
def setUp(self):
subquery1 = Query.from_(self.table_abc).select(
self.table_abc.foo,
fn.Sum(self.table_abc.fizz + self.table_abc.buzz).as_('fizzbuzz'),
).groupby(self.table_abc.foo)
subquery2 = Query.from_(self.table_efg).select(
self.table_efg.foo.as_('foo_two'),
self.table_efg.bar,
)
self.query = Query.from_(subquery1).select(
subquery1.foo, subquery1.fizzbuzz).join(subquery2).on(
subquery1.foo == subquery2.foo_two).select(
subquery2.foo_two, subquery2.bar)
def test_pagination_applied_with_orders(self, mock_fetch_data: Mock, mock_paginate: Mock, *mocks):
mock_widget = f.Widget(slicer.metrics.votes)
mock_widget.transform = Mock()
# Need to keep widget the last call in the chain otherwise the object gets cloned and the assertion won't work
slicer.data \
.dimension(slicer.dimensions.timestamp) \
.widget(mock_widget) \
.orderby(slicer.metrics.votes, Order.asc) \
.fetch()
votes_definition_with_alias_matcher = PypikaQueryMatcher(fn.Sum(slicer.table.votes).as_('$d$votes'))
orders = [(votes_definition_with_alias_matcher, Order.asc)]
mock_paginate.assert_called_once_with(mock_fetch_data.return_value, [mock_widget],
limit=None, offset=None, orders=orders)
def _indicator(q: QueryBuilder, indicator: ReportIndicator,
column: Column) -> QueryBuilder:
column_param = parse_parameter(column.parameter)
value_ = column_param.get("value")
if indicator.arithmetic == "sum":
return q.select(fn.Sum(value_))
elif indicator.arithmetic == "avg":
return q.select(fn.Avg(value_))
elif indicator.arithmetic == "max":
return q.select(fn.Max(value_))
elif indicator.arithmetic == "min":
return q.select(fn.Min(value_))
elif indicator.arithmetic == "count":
return q.select(fn.Count(value_))
else:
return q.select(fn.Max(value_))
def test_complicated(self):
t = Table("abc")
is_placebo = t.campaign_extra_info == "placebo"
pixel_mobile_search = Case().when(
is_placebo, t.action_fb_pixel_search + t.action_fb_mobile_search)
unique_impressions = Case().when(is_placebo, t.unique_impressions)
v = fn.Sum(pixel_mobile_search) / fn.Sum(
unique_impressions) - 1.96 * fn.Sqrt(
1 / fn.Sum(unique_impressions) * fn.Sum(pixel_mobile_search) /
fn.Sum(unique_impressions) *
(1 - fn.Sum(pixel_mobile_search) / fn.Sum(unique_impressions)))
self.assertTrue(v.is_aggregate)
def test_groupby_with_case_uses_the_alias(self):
q = (Query.from_(self.t).select(
fn.Sum(self.t.foo).as_("bar"),
Case().when(
self.t.fname == "Tom",
"It was Tom").else_("It was someone else.").as_("who_was_it"),
).groupby(Case().when(
self.t.fname == "Tom",
"It was Tom").else_("It was someone else.").as_("who_was_it")))
self.assertEqual(
'SELECT SUM("foo") "bar",'
"CASE WHEN \"fname\"='Tom' THEN 'It was Tom' "
"ELSE 'It was someone else.' END \"who_was_it\" "
'FROM "abc" '
'GROUP BY "who_was_it"',
str(q),
)
