def do_query(**kwargs):
query = """
WITH
outcome_types 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
),
patient_answers AS(
SELECT participantid, questionid, listofvaluesid, answer
FROM tb_questiongroupformrecord AS answers
INNER JOIN tb_formrecord AS formrecord
ON answers.formrecordid = formrecord.formrecordid
WHERE questionid=144 or listofvaluesid in (SELECT id FROM outcome_types)
),
ages AS(
SELECT participantid, CAST(answer as INTEGER) as age
FROM patient_answers
WHERE questionid = 144
),
outcomes AS (
SELECT participantid, listofvaluesid as outcome
FROM patient_answers
WHERE questionid != 144
),
ages_outcomes AS(
SELECT outcomes.participantid, age, outcome,
CASE
WHEN age < 40 THEN 0
WHEN age > 60 THEN 2
ELSE 1
END AS agegroup
FROM outcomes,ages
WHERE outcomes.participantid = ages.participantid
)
SELECT agegroup, outcome, COUNT(*)
FROM ages_outcomes
GROUP BY outcome,agegroup
ORDER BY agegroup;
"""
df = pd.read_sql(query, con=con.get())
df1 = df.pivot_table(columns=['outcome', 'agegroup'])
outcomes = list(set(df['outcome']))
rows = []
for agegroup in set(df['agegroup']):
row = [agegroup]
for outcome in outcomes:
row.append(df1[outcome,agegroup][0])
rows.append(row)
df2 = pd.DataFrame(rows, columns=['agegroup'] + outcomes)
df2['agegroup'] = 'menores de 40 anos', 'entre 40 e 60 anos', 'maiores de 60 anos'
return graphs.get_grouped_bar_chart(df2, "agegroup", outcomes[0], outcomes[1], "Mortes", "Altas")
def test_query():
query = """
select table_type, count(table_name)
from information_schema.tables
group by table_type
"""
df = pd.read_sql(query, con=con.get())
return graphs.get_bar_chart(df, "table_type", "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 ages_table AS (
SELECT formrecordid as id, CAST(answer as INTEGER) as age
FROM tb_questiongroupformrecord
WHERE questionid= (SELECT questionid FROM tb_questions WHERE description='Age (years)')
),
sexes_table AS (
SELECT formrecordid as id, answers.listofvaluesid as sex, description as sexname
FROM tb_questiongroupformrecord as answers
JOIN tb_listofvalues as sexes
ON answers.listofvaluesid = sexes.listofvaluesid
WHERE questionid=(SELECT questionid FROM tb_questions WHERE description='Sex at Birth')
)
select (age/10)*10 || '-' || (age/10)*10+10-1 as agegroup, sexname, count(*)
from ages_table
join sexes_table
on ages_table.id = sexes_table.id
group by age/10, sexname
order by agegroup,sexname;
"""
df = pd.read_sql(query, con=con.get())
df1 = df.pivot_table(columns=['agegroup', 'sexname'])
sexes = list(set(df['sexname']))
rows = []
for agegroup in sorted(set(df['agegroup'])):
row = [agegroup]
for sex in sexes:
try:
row.append(df1[agegroup, sex][0])
except KeyError:
row.append(0)
rows.append(row)
df2 = pd.DataFrame(rows, columns=['agegroup'] + sexes)
return graphs.get_grouped_bar_chart(df2, 'agegroup', sexes[0], sexes[1],
sexes[0], sexes[1])
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")