Have tested this now and no 1 is indeed fastest, with no 2 not far behind.
3 is quite a bit slower and for some strange reason gives a different value
for the females aged 50.
Will look into this.
RBS
On Sun, 22 Sep 2019, 18:37 Keith Medcalf, <kmedc...@dessus.com> wrote:
>
> On Sunday, 22 September, 2019 04:10, Bart Smissaert <
> bart.smissa...@gmail.com> wrote:
>
> >Have this query:
>
> >SELECT round(((Julianday('now') - 2415018.5) - P.DOB) / 365.25, 0) AS AGE,
> > AVG(BM.numeric_value) AS avg_Hb_Male,
> > AVG(BF.numeric_value) as avg_Hb_Female
> >FROM PATIENTS P INNER JOIN NUM_VALUES BM ON(P.ID = BM.ID AND P.SEX =
> 'Male')
> > INNER join NUM_VALUES BF ON(P.ID = BF.ID AND P.SEX =
> 'Female')
> >WHERE BF.term_text = 'Haemoglobin estimation' and
> > BM.term_text = 'Haemoglobin estimation'
> >GROUP BY AGE
> >HAVING AGE > 18 and AGE < 98
> >ORDER BY AGE ASC
>
> >Which always used to run fine and return rows. There is no error, but
> >there are no returned rows.
>
> What changed between when it was "working" and when it was "not working"?
> As far as I can see it should never have been "working" because the eqijoin
> constraints could never be satisfied except by a Patients that was both
> Male and Female at the same time in the same record (which is impossible).
>
> select round(((Julianday('now') - 2415018.5) - P.DOB) / 365.25, 0) as
> Age,
> avg(case when P.Sex == 'Male' then V.numeric_value else null end)
> as avg_Hb_Male,
> avg(case when P.Sex == 'Female' then V.numeric_value else null
> end) as avg_Hb_Female
> from Patients P, num_values V
> where P.id == V.id
> and V.term_text == 'Haemoglobin estimation'
> group by Age
> having Age between 19 and 97
> order by Age asc;
>
> or, using outer joins:
>
> select round(((Julianday('now') - 2415018.5) - P.DOB) / 365.25, 0) as
> Age,
> avg(BM.numeric_value) as avg_Hb_Male,
> avg(BF.numeric_value) as avg_Hb_Female
> from Patients as P
> left join num_values as BM
> on P.id == BM.id
> and P.Sex == 'Male'
> and BM.term_text == 'Haemoglobin estimation'
> left join num_values as BF
> on P.id == BF.id
> and P.Sex == 'Female'
> and BF.term_text == 'Haemoglobin estimation'
> group by Age
> having Age between 19 and 97
> order by Age asc;
>
> or, using correlated subqueries:
>
> select round(((Julianday('now') - 2415018.5) - P.DOB) / 365.25, 0) as
> Age,
> avg((
> select numeric_value
> from num_values as V
> where P.id == V.id
> and P.Sex == 'Male'
> and V.term_text == 'Haemoglobin estimation'
> )) as avg_Hb_Male,
> avg((
> select numeric_value
> from num_values as V
> where P.id == V.id
> and P.Sex == 'Female'
> and V.term_text == 'Haemoglobin estimation'
> )) as avg_Hb_Female
> from Patients as P
> group by Age
> having Age between 19 and 97
> order by Age asc;
>
> The first one is likely to be the fastest to execute since it does the
> least record lookups and the optimizer has the best chance at optimizing
> the query.
>
>
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
