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 _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 get_start_data_res() -> str:
logging.debug('Получение начальной даты')
p = Table('calc_reg_balance', alias='p')
q = (PostgreSQLQuery.from_(p).select(fn.Min(p.time_start_write)))
result_elements = ''
sql = q.get_sql()
logging.debug(f'SQL: {sql}')
try:
result = engine.execute(sql)
for row in result:
result_elements = row
except Exception as e:
logging.error("Error is ", e)
logging.debug("get_start_data_res() OK...")
return result_elements
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__min(self):
q = Q.from_('abc').select(fn.Min(F('foo')))
self.assertEqual('SELECT MIN(\"foo\") FROM \"abc\"', str(q))
def test__min(self):
q = Q.from_("abc").select(fn.Min(F("foo")))
self.assertEqual('SELECT MIN("foo") FROM "abc"', str(q))
