sql query

Jude DaShiell jdashiel at shellworld.net
Tue Nov 4 11:26:07 UTC 2014


Yes Tim, that's my intent.  I don't purge data since the menu hasn't yet 
changed here.  I will have to work on the select statements since really 
what should come up is the sample sizes broken by Doctor visit dates.  
The first sample really started April 27, 2014 and ended June 6, 2014 
the second sample ran from June 7, 2014 to August 22, 2014, and this 
third sample runs from August 23, 2014 through some date I think 
February 12, 2015 but I'll check on that over at the office later today. 
 I can reconstruct the health table by feeding it selective record sets 
when I want to find out about these smaller sample sizes as opposed to 
the whole data set.

On Wed, 29 Oct 2014, Tim Chase wrote:

> It does help a bit.  It looks like you only take one measurement each
> day (the date is a PRIMARY KEY so it has to be unique).  Based on
> what I'm seeing of your various queries (particularly where you're
> rebuilding the weekday tables), it looks like your aim is to aggregate
> across all Monday samples, all Tuesday samples, etc. (not just the
> most recent Monday samples).  Is this your intent?  Or do you purge
> out data older than a week?
> 
> -tim
> 
> On October 28, 2014, Jude DaShiell wrote:
> > Hi Tim,
> > 
> > This may help:
> > create table health ( 
> > date   date PRIMARY KEY,
> > Cystalic_Pressure    int NOT NULL, check (Cystalic_Pressure > 0),
> > --- cystalic pressure
> > Dyastalic_Pressure int NOT NULL, check (Dyastalic_Pressure > 0),
> > --- dyastalic pressure
> > Pulse int NOT NULL, check (Pulse > 0),
> > --- pulse
> > Blood_Sugar int NOT NULL, check (Blood_Sugar > 0),
> > --- blood sugar
> > weekday int NOT NULL, check (weekday > -1), check (weekday < 7)
> > --- weekday number saturday=6
> > );
> > On Sun, 26 Oct 2014, Tim Chase wrote:
> > 
> > > On October 25, 2014, Jude DaShiell wrote:
> > > > jude=# \i phealth1.sql
> > > > psql:phealth1.sql:97: ERROR:  missing FROM-clause entry for
> > > > table "h" LINE 66:  (select h.dyastalic_pressure
> > > 
> > > Ah, my apologies.  Without actual database access, it's a bit
> > > tough to vet these queries.  I was missing some table aliases
> > > that I've put in the SQL below.  Sorry 'bout that.
> > > 
> > > -tim
> > > 
> > > 
> > > select
> > >  'Combined' as Title,
> > >  min(date) as "Starting Date:",
> > >  max(date) as "Ending Date:",
> > >  count(cystalic_pressure) as "Cystalic Sample Size:",
> > >  round(avg(cystalic_pressure),1) as "Average Cystalic Pressure:",
> > >  min(cystalic_pressure) as "Min Cystalic Pressure:",
> > >  max(cystalic_pressure) as "Max Cystalic Pressure:",
> > >  (select h.cystalic_pressure
> > >   from health h
> > >   where date between now() - interval '1 week' and now()
> > >   group by h.cystalic_pressure
> > >   order by count(h.cystalic_pressure) desc, h.cystalic_pressure
> > >   limit 1) as "Mode Cystalic Pressure:",
> > >  count(dyastalic_pressure) as "Dyastalic Sample Size:",
> > >  round(avg(dyastalic_pressure),1) as "Average Dyastalic
> > > Pressure:", min(dyastalic_pressure) as "Min Dyastalic Pressure:",
> > >  max(dyastalic_pressure) as "Max Dyastalic Pressure:",
> > >  (select h.dyastalic_pressure
> > >   from health h
> > >   where date between now() - interval '1 week' and now()
> > >   group by h.dyastalic_pressure
> > >   order by count(h.dyastalic_pressure) desc, h.dyastalic_pressure
> > >   limit 1) as "Mode Dyastalic Pressure:",
> > >  count(pulse) as "Pulse Sample Size:",
> > >  round(avg(pulse),1) as "Average Pulse:",
> > >  min(pulse) as "Min Pulse:",
> > >  max(pulse) as "Max Pulse:",
> > >  (select h.pulse
> > >   from health h
> > >   where date between now() - interval '1 week' and now()
> > >   group by h.pulse
> > >   order by count(h.pulse) desc, h.pulse
> > >   limit 1) as "Mode Pulse:",
> > >  count(blood_sugar) as "Sugar Sample Size:",
> > >  round(avg(blood_sugar),1) as "Average Sugar:",
> > >  min(blood_sugar) as "Min Sugar:",
> > >  max(blood_sugar) as "Max Sugar:",
> > >  (select h.blood_sugar
> > >   from health h
> > >   where date between now() - interval '1 week' and now()
> > >   group by h.blood_sugar
> > >   order by count(h.blood_sugar) desc, h.blood_sugar
> > >   limit 1) as "Mode Sugar:"
> > > from health
> > > where date between now() - interval '1 week' and now()
> > > 
> > > union all
> > > 
> > > select
> > >  to_char(date, 'Day') as Title,
> > >  date as "Starting Date:",
> > >  date as "Ending Date:",
> > >  count(cystalic_pressure) as "Cystalic Sample Size:",
> > >  round(avg(cystalic_pressure),1) as "Average Cystalic Pressure:",
> > >  min(cystalic_pressure) as "Min Cystalic Pressure:",
> > >  max(cystalic_pressure) as "Max Cystalic Pressure:",
> > >  (select h.cystalic_pressure
> > >   from health h
> > >   where h.date = health.date
> > >   group by h.cystalic_pressure
> > >   order by count(h.cystalic_pressure) desc, h.cystalic_pressure
> > >   limit 1) as "Mode Cystalic Pressure:",
> > >  count(dyastalic_pressure) as "Dyastalic Sample Size:",
> > >  round(avg(dyastalic_pressure),1) as "Average Dyastalic
> > > Pressure:", min(dyastalic_pressure) as "Min Dyastalic Pressure:",
> > >  max(dyastalic_pressure) as "Max Dyastalic Pressure:",
> > >  (select h.dyastalic_pressure
> > >   from health h
> > >   where h.date = health.date
> > >   group by h.dyastalic_pressure
> > >   order by count(h.dyastalic_pressure) desc, h.dyastalic_pressure
> > >   limit 1) as "Mode Dyastalic Pressure:",
> > >  count(pulse) as "Pulse Sample Size:",
> > >  round(avg(pulse),1) as "Average Pulse:",
> > >  min(pulse) as "Min Pulse:",
> > >  max(pulse) as "Max Pulse:",
> > >  (select h.pulse
> > >   from health h
> > >   where h.date = health.date
> > >   group by h.pulse
> > >   order by count(h.pulse) desc, h.pulse
> > >   limit 1) as "Mode Pulse:",
> > >  count(blood_sugar) as "Sugar Sample Size:",
> > >  round(avg(blood_sugar),1) as "Average Sugar:",
> > >  min(blood_sugar) as "Min Sugar:",
> > >  max(blood_sugar) as "Max Sugar:",
> > >  (select h.blood_sugar
> > >   from health h
> > >   where h.date = health.date
> > >   group by h.blood_sugar
> > >   order by count(h.blood_sugar) desc, h.blood_sugar
> > >   limit 1) as "Mode Sugar:"
> > > from health
> > > where date >= now() - interval '1 week'
> > > group by date
> > > order by "Starting Date:", Title;
> > > 
> > > 
> > > 
> > > 
> > > 
> > > 
> > > 
> > > 
> > > _______________________________________________
> > > Blinux-list mailing list
> > > Blinux-list at redhat.com
> > > https://www.redhat.com/mailman/listinfo/blinux-list
> > > 
> > > 
> > 
> > jude <jdashiel at shellworld.net>
> > Twitter: @jdashiel
> > 
> > _______________________________________________
> > Blinux-list mailing list
> > Blinux-list at redhat.com
> > https://www.redhat.com/mailman/listinfo/blinux-list
> 
> _______________________________________________
> Blinux-list mailing list
> Blinux-list at redhat.com
> https://www.redhat.com/mailman/listinfo/blinux-list
> 
> 

jude <jdashiel at shellworld.net>
Twitter: @jdashiel




More information about the Blinux-list mailing list