def qualstat_getstatdata(srvid, condition=None):
base_query = qualstat_base_statdata()
if condition:
base_query = base_query.where(condition)
return (select([
powa_statements.c.srvid,
column("qualid"), powa_statements.c.queryid,
column("query"), powa_statements.c.dbid,
func.to_json(column("quals")).label("quals"),
sum(column("execution_count")).label("execution_count"),
sum(column("occurences")).label("occurences"),
(sum(column("nbfiltered")) /
sum(column("occurences"))).label("avg_filter"),
case([(sum(column("execution_count")) == 0, 0)],
else_=sum(column("nbfiltered")) /
cast(sum(column("execution_count")), Numeric) *
100).label("filter_ratio")
]).select_from(
join(
base_query, powa_statements,
and_(powa_statements.c.queryid == literal_column("pqnh.queryid"),
powa_statements.c.srvid == literal_column("pqnh.srvid")),
powa_statements.c.srvid == column("srvid"))).group_by(
powa_statements.c.srvid, column("qualid"),
powa_statements.c.queryid, powa_statements.c.dbid,
powa_statements.c.query, column("quals")))
def qualstat_getstatdata():
base_query = qualstat_base_statdata()
return (select([
column("qualid"),
column("queryid").label("queryid"),
func.to_json(column("quals")).label("quals"),
sum(column("count")).label("count"),
sum(column("nbfiltered")).label("nbfiltered"),
case([(sum(column("count")) == 0, 0)],
else_=sum(column("nbfiltered")) /
cast(sum(column("count")), Numeric)).label("filter_ratio")
]).select_from(base_query).group_by(column("qualid"),
literal_column("queryid"),
column("quals")))