def select_average(self,
table: str,
field: str,
start: Optional[date] = None,
stop: Optional[date] = None) -> AverageResult:
target = Table(table, schema=self.schema)
target_field = Field(field, table=target)
query = Query.from_(target).select(target.date,
fn.Avg(target_field, alias='average'),
fn.Sum(target_field, alias='sum'),
fn.Count(target_field, alias='count')). \
groupby(target.date).orderby(target.date)
query = self.apply_dates(query, target, start, stop)
result = AverageResult(table=table, field=field, elements=[])
for current in self.run(query):
result.elements.append(
Average(date=date.fromordinal(current[0]),
avg=float(current[1]),
sum=float(current[2]) if current[3] else 0.,
count=int(current[3])))
return result
def build_indicators(indicators: List[ReportIndicator],
dataset_columns: List[Column], dataset_query_alias):
_selects = []
_appear_in_group_by = []
columns = convent_column_list_to_dict(dataset_columns)
for indicator in indicators:
column: Column = columns.get(indicator.columnId, None)
if column is None:
continue
else:
field = Field(column.alias, None,
AliasedQuery(dataset_query_alias))
if indicator.arithmetic == "sum":
_selects.append(fn.Sum(field))
elif indicator.arithmetic == "avg":
_selects.append(fn.Avg(field))
elif indicator.arithmetic == "max":
_selects.append(fn.Max(field))
elif indicator.arithmetic == "min":
_selects.append(fn.Min(field))
elif indicator.arithmetic == "count":
_selects.append(fn.Count(field))
else:
_selects.append(field)
_appear_in_group_by.append(field)
return _selects, _appear_in_group_by
def test_union_as_subquery(self):
abc, efg = Tables('abc', 'efg')
hij = Query.from_(abc).select(abc.t).union(Query.from_(efg).select(efg.t))
q = Query.from_(hij).select(fn.Avg(hij.t))
self.assertEqual('SELECT AVG("sq0"."t") FROM ((SELECT "t" FROM "abc") UNION (SELECT "t" FROM "efg")) "sq0"',
str(q))
def test_union_with_no_quote_char(self):
abc, efg = Tables('abc', 'efg')
hij = Query.from_(abc).select(abc.t).union(Query.from_(efg).select(efg.t))
q = Query.from_(hij).select(fn.Avg(hij.t))
self.assertEqual('SELECT AVG(sq0.t) FROM ((SELECT t FROM abc) UNION (SELECT t FROM efg)) sq0',
q.get_sql(quote_char=None))
def test_intersect_with_no_quote_char(self):
abc, efg = Tables("abc", "efg")
hij = Query.from_(abc).select(abc.t).intersect(Query.from_(efg).select(efg.t))
q = Query.from_(hij).select(fn.Avg(hij.t))
self.assertEqual(
"SELECT AVG(sq0.t) FROM ((SELECT t FROM abc) INTERSECT (SELECT t FROM efg)) sq0",
q.get_sql(quote_char=None),
)
def test_intersect_as_subquery(self):
abc, efg = Tables("abc", "efg")
hij = Query.from_(abc).select(abc.t).intersect(Query.from_(efg).select(efg.t))
q = Query.from_(hij).select(fn.Avg(hij.t))
self.assertEqual(
'SELECT AVG("sq0"."t") FROM ((SELECT "t" FROM "abc") INTERSECT (SELECT "t" FROM "efg")) "sq0"',
str(q),
)
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 _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__avg(self):
q = Q.from_('abc').select(fn.Avg(F('foo')))
self.assertEqual('SELECT AVG(\"foo\") FROM \"abc\"', str(q))
def test_full_example(self):
"""
This is an example using several features of the slicer. It demonstrates usage of metrics, dimensions, filters,
references and also joining metrics from additional tables.
The _build_query function takes a dictionary parameter that expresses all of the options of the slicer aside
from Operations which are done in a post-processing step.
Many of the fields are defined as a dict. In the examples and other tests an OrderedDict is used in many places
so that the field order is maintained for the assertions. In a real example, a regular dict can be used.
The fields in the dictionary are as follows:
:param 'table':
The primary table to query data from. This is the table used for the FROM clause in the query.
:param 'joins':
A list or tuple of tuples. This lists the tables that need to be joined in the query and the criterion to
use to join them. The inner tuples must contain two elements, the first element is the table to join and
the second element is a criterion to join the tables with.
:param 'metrics':
A dict containing the SELECT clause. The values can be strings (as a short cut for a column of the primary
table), a field instance, or an expression containing functions, arithmetic, or anything else supported by
pypika.
:param 'dimensions':
A dict containing the INDEX of the query. These fields will be included in the SELECT clause, the GROUP BY
clause, and the ORDER BY clause. For comparisons, they are also used to join the nested query.
:param 'filters':
A list containing criterion expressions for the WHERE clause. Multiple filters will be combined with an
'AND' operator.
:param 'references':
A dict containing comparison operators. The keys of this dict must match a supported comparison operation.
The value of the key must match the key from the dimensions table. The dimension must also be of the
supported type, for example 'yoy' requires a DATE type dimension.
:param 'rollup':
A list of dimensions to rollup, or provide the totals across groups. When multiple dimensions are included,
rollup works from the last to the first dimension, providing the totals across the dimensions in a tree
structure.
See pypika documentation for more examples of query expressions: http://pypika.readthedocs.io/en/latest/
"""
dt = self.mock_table.dt
query = self.manager._build_data_query(
table=self.mock_table,
joins=[
(self.mock_join1,
self.mock_table.join1_id == self.mock_join1.id,
JoinType.inner),
(self.mock_join2,
self.mock_table.join2_id == self.mock_join2.id,
JoinType.left),
],
metrics=OrderedDict([
# Examples using a field of a table
('foo', fn.Sum(self.mock_table.foo)),
# Examples using a field of a table
('bar', fn.Avg(self.mock_join1.bar)),
# Example using functions and Arithmetic
('ratio', fn.Sum(self.mock_table.numerator) /
fn.Sum(self.mock_table.denominator)),
# Example using functions and Arithmetic
('ratio', fn.Sum(self.mock_table.numerator) /
fn.Sum(self.mock_table.denominator)),
]),
dimensions=OrderedDict([
# Example of using a continuous datetime dimension, where the values are rounded up to the nearest day
('date', settings.database.round_date(dt, 'DD')),
# Example of using a categorical dimension from a joined table
('fiz', self.mock_join2.fiz),
]),
mfilters=[fn.Sum(self.mock_join2.buz) > 100],
dfilters=[
# Example of filtering the query to a date range
dt[date(2016, 1, 1):date(2016, 12, 31)],
# Example of filtering the query to certain categories
self.mock_join2.fiz.isin(['a', 'b', 'c']),
],
references=OrderedDict([
# Example of adding a Week-over-Week comparison to the query
(references.WoW.key, {
'dimension': 'date',
'interval': references.WoW.interval,
})
]),
rollup=[],
)
self.assertEqual(
'SELECT '
# Dimensions
'"sq0"."date" "date","sq0"."fiz" "fiz",'
# Metrics
'"sq0"."foo" "foo","sq0"."bar" "bar","sq0"."ratio" "ratio",'
# Reference Dimension
# Currently not selected
# '"sq1"."dt" "dt_wow",'
# Reference Metrics
'"sq1"."foo" "foo_wow","sq1"."bar" "bar_wow","sq1"."ratio" "ratio_wow" '
'FROM ('
# Main Query
'SELECT '
'ROUND("test_table"."dt",\'DD\') "date","test_join2"."fiz" "fiz",'
'SUM("test_table"."foo") "foo",'
'AVG("test_join1"."bar") "bar",'
'SUM("test_table"."numerator")/SUM("test_table"."denominator") "ratio" '
'FROM "test_table" '
'JOIN "test_join1" ON "test_table"."join1_id"="test_join1"."id" '
'LEFT JOIN "test_join2" ON "test_table"."join2_id"="test_join2"."id" '
'WHERE "test_table"."dt" BETWEEN \'2016-01-01\' AND \'2016-12-31\' '
'AND "test_join2"."fiz" IN (\'a\',\'b\',\'c\') '
'GROUP BY ROUND("test_table"."dt",\'DD\'),"test_join2"."fiz" '
'HAVING SUM("test_join2"."buz")>100'
') "sq0" '
'LEFT JOIN ('
# Reference Query
'SELECT '
'ROUND("test_table"."dt",\'DD\')+INTERVAL \'1 WEEK\' "date","test_join2"."fiz" "fiz",'
'SUM("test_table"."foo") "foo",'
'AVG("test_join1"."bar") "bar",'
'SUM("test_table"."numerator")/SUM("test_table"."denominator") "ratio" '
'FROM "test_table" '
'JOIN "test_join1" ON "test_table"."join1_id"="test_join1"."id" '
'LEFT JOIN "test_join2" ON "test_table"."join2_id"="test_join2"."id" '
'WHERE "test_table"."dt"+INTERVAL \'1 WEEK\' BETWEEN \'2016-01-01\' AND \'2016-12-31\' '
'AND "test_join2"."fiz" IN (\'a\',\'b\',\'c\') '
'GROUP BY ROUND("test_table"."dt",\'DD\')+INTERVAL \'1 WEEK\',"test_join2"."fiz" '
'HAVING SUM("test_join2"."buz")>100'
') "sq1" ON "sq0"."date"="sq1"."date" '
'AND "sq0"."fiz"="sq1"."fiz" '
'ORDER BY "sq0"."date","sq0"."fiz"',
str(query))
def test_is_aggregate_True_for_aggregate_function_or_function_with_aggregate_functions(
self):
self.assertTrue(fn.Sum('a').is_aggregate)
self.assertTrue(fn.Coalesce(fn.Avg('a'), 0).is_aggregate)
self.assertTrue(fn.Coalesce(fn.NullIf(fn.Sum('a'), 0), 0).is_aggregate)
def test__avg(self):
q = Q.from_("abc").select(fn.Avg(F("foo")))
self.assertEqual('SELECT AVG("foo") FROM "abc"', str(q))
def avg(self):
return fn.Coalesce(fn.Avg(self.field.sql), 0, alias=self.alias)
