def do_query(**kwargs):
query = """
WITH
o2_measurements as (
SELECT formrecordid as o2id, avg(CAST(answer as INTEGER)) as o2ratio
FROM tb_questiongroupformrecord
WHERE questionid=246
GROUP BY o2id
FETCH FIRST 70 ROW ONLY
),
co2_measurements as (
SELECT formrecordid as co2id, avg(CAST(answer as INTEGER)) as co2ratio
FROM tb_questiongroupformrecord
WHERE questionid=247
GROUP BY co2id
FETCH FIRST 100 ROW ONLY
),
ratios as (
SELECT co2id,CASE WHEN o2id IS NOT NULL AND co2id IS NOT NULL THEN CAST(o2ratio/co2ratio*100 AS INTEGER) ELSE NULL END as ratio
FROM co2_measurements
FULL OUTER JOIN o2_measurements
ON co2id = o2id
)
SELECT count(*) as "total de exames",CASE WHEN ratio/20 IS NOT NULL THEN ratio/20*20 || '-' || ratio/20*20+20-1 ELSE 'Indisponível' END as "razão"
FROM ratios
GROUP BY ratio/20;
"""
df = pd.read_sql(query, con=con.realdata())
return graphs.get_chart(df, "razão", "total de exames")
def do_query(**kwargs):
query = """
WITH answer_count AS (
SELECT count(*), questionid as id
FROM tb_questiongroupformrecord
GROUP BY questionid)
SELECT description, sum(count) as count
FROM answer_count, tb_questions
WHERE questionid = id
GROUP BY description;
"""
df = pd.read_sql(query, con=con.realdata())
return graphs.get_chart(df, "description", "count")
def do_query(**kwargs):
query = """
WITH
sodium_measurements as (
SELECT CAST(answer as INTEGER) as mmol
FROM tb_questiongroupformrecord
WHERE questionid=170
)
SELECT mmol/5*5 || '-' || mmol/5*5+5-1 as "mmol/L", count(*)
FROM sodium_measurements
GROUP BY mmol/5
ORDER BY mmol/5;
"""
df = pd.read_sql(query, con=con.realdata())
return graphs.get_chart(df, "mmol/L", "count")
def do_query(**kwargs):
query = """
WITH ages_table AS (
SELECT CAST(answer as INTEGER) as age
FROM tb_questiongroupformrecord
WHERE questionid=144)
SELECT count(*) FROM ages_table WHERE age < 40
UNION SELECT count(*) FROM ages_table WHERE age >= 40 and age < 60
UNION SELECT count(*) FROM ages_table WHERE age >= 60;
"""
df = pd.read_sql(query, con=con.get())
groups = [">40", "entre 40 e 60", ">60"]
df["group"] = groups
print(df)
return graphs.get_chart(df, "group", "count")
def do_query(**kwargs):
query = """
WITH
outcomes AS (
SELECT values.listofvaluesid as id, values.description
FROM tb_listtype AS list,
tb_listofvalues AS values
WHERE list.description = 'Outcome list'and list.listtypeid = values.listtypeid
),
counts AS (
SELECT COUNT(*),listofvaluesid
FROM tb_questiongroupformrecord
WHERE listofvaluesid IN (SELECT id FROM outcomes)
GROUP BY listofvaluesid
)
SELECT count,description from counts,outcomes
WHERE id = listofvaluesid;
"""
df = pd.read_sql(query, con=con.get())
return graphs.get_chart(df, "description", "count")