Skip site navigation (1) Skip section navigation (2)

Re: need ``row number``

From: Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net>
To: pgsql-general(at)postgresql(dot)org, pgsql-sql(at)postgresql(dot)org
Subject: Re: need ``row number``
Date: 2004-09-24 07:21:17
Message-ID: 20040924092117.A1195@hermes.hilbert.loc (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-sql
> You are going to need a set returning function. It will have
> to look up the expected boosters, the expected time elapsed,
> and return them with their sequence numbers if they exists.
> There is no easy way to do it in a view that I can think of.
I am not convinced I'll need a SRF. I am not trying to
calculate something that isn't there yet. I am just trying to
join two views appropriately. I might have to employ some
variant of Celko's integer helper table but I'm not sure how
to proceed.

Karsten

> <<< Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net>  9/23  1:56p >>>
> Hello all,
> 
> yes, I know, "row number" isn't a concept that fits into the
> relational model and I will only be asking for something
> similar.
> 
> explanation (actual views below)
> --------------------------------
> 
> I have a view that holds the vaccinations scheduled for a
> patient (v_vaccs_scheduled4pat) depending on what vaccination
> regimes that patient is on. There are typically between 1 to 5
> vaccinations per disease (indication/regime) which is expressed
> in the vaccination sequence number. Some regimes also have
> booster shots scheduled. Those boosters are to be given
> regularly after a set interval. Those have the sequence number
> field set to NULL.
> 
> There is a second view that lists all the vaccinations
> actually given to a patient per regime (v_pat_vacc4ind).
> This view has dates when the shot was given but no sequence
> number.
> 
> I now want to create a view which correlates the two showing
> me which actual vaccination corresponds to which scheduled
> vaccination. This is what I cannot get my head wrapped around
> although it is probably fairly straightforward.
> 
> The conceptual solution would be to order actual vaccinations
> by date per regime and number them (remember the "row number"
> in the subject line ?). One would then join on that with the
> sequence numbers from the scheduled vaccinations view and treat
> any actual vaccinations where "row number" > max(sequence
> number) as being boosters (medically this is correct, btw).
> Yes, there can and will be several boosters for some regimes.
> 
> raw data
> --------
> full schema here:
>     http://hherb.com/gnumed/schema/
> 
> full schema defs in CVS here:
>     http://savannah.gnu.org/cgi-bin/viewcvs/gnumed/gnumed/gnumed/server/sql/gmClinicalViews.sql
> 
> relevant excerpt:
> 
> --- ==========================================================
> --- vaccination stuff
> --- -----------------------------------------------------
> \unset ON_ERROR_STOP
> drop view v_vacc_regimes;
> \set ON_ERROR_STOP 1
> 
> create view v_vacc_regimes as
> select
> 	vreg.id as pk_regime,
> 	vind.description as indication,
> 	_(vind.description) as l10n_indication,
> 	vreg.name as regime,
> 	coalesce(vreg.comment, '') as comment,
> 	vreg.fk_indication as pk_indication,
> 	vreg.fk_recommended_by as pk_recommended_by
> from
> 	vacc_regime vreg,
> 	vacc_indication vind
> where
> 	vreg.fk_indication = vind.id
> ;
> 
> comment on view v_vacc_regimes is
> 	'all vaccination schedules known to the system';
> 
> --- -----------------------------------------------------
> \unset ON_ERROR_STOP
> drop view v_vacc_defs4reg;
> \set ON_ERROR_STOP 1
> 
> create view v_vacc_defs4reg as
> select
> 	vreg.id as pk_regime,
> 	vind.description as indication,
> 	_(vind.description) as l10n_indication,
> 	vreg.name as regime,
> 	coalesce(vreg.comment, '') as reg_comment,
> 	vdef.is_booster as is_booster,
> 	vdef.seq_no as vacc_seq_no,
> 	vdef.min_age_due as age_due_min,
> 	vdef.max_age_due as age_due_max,
> 	vdef.min_interval as min_interval,
> 	coalesce(vdef.comment, '') as vacc_comment,
> 	vind.id as pk_indication,
> 	vreg.fk_recommended_by as pk_recommended_by
> from
> 	vacc_regime vreg,
> 	vacc_indication vind,
> 	vacc_def vdef
> where
> 	vreg.id = vdef.fk_regime
> 		and
> 	vreg.fk_indication = vind.id
> order by
> 	indication,
> 	vacc_seq_no
> ;
> 
> comment on view v_vacc_defs4reg is
> 	'vaccination event definitions for all schedules known to the system';
> 
> --- -----------------------------------------------------
> \unset ON_ERROR_STOP
> drop view v_vacc_regs4pat;
> \set ON_ERROR_STOP 1
> 
> create view v_vacc_regs4pat as
> select
> 	lp2vr.fk_patient as pk_patient,
> 	vvr.indication as indication,
> 	vvr.l10n_indication as l10n_indication,
> 	vvr.regime as regime,
> 	vvr.comment as comment,
> 	vvr.pk_regime as pk_regime,
> 	vvr.pk_indication as pk_indication,
> 	vvr.pk_recommended_by as pk_recommended_by
> from
> 	lnk_pat2vacc_reg lp2vr,
> 	v_vacc_regimes vvr
> where
> 	vvr.pk_regime = lp2vr.fk_regime
> ;
> 
> comment on view v_vacc_regs4pat is
> 	'selection of configured vaccination schedules a patient is actually on';
> 
> --- -----------------------------------------------------
> \unset ON_ERROR_STOP
> drop view v_vaccs_scheduled4pat;
> \set ON_ERROR_STOP 1
> 
> create view v_vaccs_scheduled4pat as
> select
> 	vvr4p.pk_patient as pk_patient,
> 	vvr4p.indication as indication,
> 	vvr4p.l10n_indication as l10n_indication,
> 	vvr4p.regime as regime,
> 	vvr4p.comment as reg_comment,
> 	vvd4r.is_booster,
> 	vvd4r.vacc_seq_no,
> 	vvd4r.age_due_min,
> 	vvd4r.age_due_max,
> 	vvd4r.min_interval,
> 	vvd4r.vacc_comment as vacc_comment,
> 	vvr4p.pk_regime as pk_regime,
> 	vvr4p.pk_indication as pk_indication,
> 	vvr4p.pk_recommended_by as pk_recommended_by
> from
> 	v_vacc_regs4pat vvr4p,
> 	v_vacc_defs4reg vvd4r
> where
> 	vvd4r.pk_regime = vvr4p.pk_regime
> ;
> 
> comment on view v_vaccs_scheduled4pat is
> 	'vaccinations scheduled for a patient according
> 	 to the vaccination schedules he/she is on';
> 
> --- -----------------------------------------------------
> \unset ON_ERROR_STOP
> drop view v_pat_vacc4ind;
> \set ON_ERROR_STOP 1
> 
> create view v_pat_vacc4ind as
> select
> 	v.fk_patient as pk_patient,
> 	v.id as pk_vaccination,
> 	v.clin_when as date,
> 	vind.description as indication,
> 	_(vind.description) as l10n_indication,
> 	vcine.trade_name as vaccine,
> 	vcine.short_name as vaccine_short,
> 	v.batch_no as batch_no,
> 	v.site as site,
> 	coalesce(v.narrative, '') as narrative,
> 	vind.id as pk_indication,
> 	v.fk_provider as pk_provider,
> 	vcine.id as pk_vaccine,
> 	vpep.pk_health_issue as pk_health_issue,
> 	v.fk_episode as pk_episode,
> 	v.fk_encounter as pk_encounter
> from
> 	vaccination v,
> 	vaccine vcine,
> 	lnk_vaccine2inds lv2i,
> 	vacc_indication vind,
> 	v_pat_episodes vpep
> where
> 	vpep.pk_episode=v.fk_episode
> 		and
> 	v.fk_vaccine = vcine.id
> 		and
> 	lv2i.fk_vaccine = vcine.id
> 		and
> 	lv2i.fk_indication = vind.id
> ;
> 
> comment on view v_pat_vacc4ind is
> 	'vaccinations a patient has actually received for the various indications';
> 
> --- -----------------------------------------------------
> \unset ON_ERROR_STOP
> drop view v_pat_missing_vaccs;
> \set ON_ERROR_STOP 1
> 
> create view v_pat_missing_vaccs as
> select
> 	vvs4p.pk_patient,
> 	vvs4p.indication,
> 	vvs4p.l10n_indication,
> 	vvs4p.regime,
> 	vvs4p.reg_comment,
> 	vvs4p.vacc_seq_no as seq_no,
> 	case when vvs4p.age_due_max is null
> 		then (now() + coalesce(vvs4p.min_interval, vvs4p.age_due_min))
> 		else ((select identity.dob from identity where identity.id=vvs4p.pk_patient) + vvs4p.age_due_max)
> 	end as latest_due,
> 	--- note that ...
> 	--- ... 1) time_left ...
> 	case when vvs4p.age_due_max is null
> 		then coalesce(vvs4p.min_interval, vvs4p.age_due_min)
> 		else (((select identity.dob from identity where identity.id=vvs4p.pk_patient) + vvs4p.age_due_max) - now())
> 	end as time_left,
> 	--- ... and 2) amount_overdue ...
> 	case when vvs4p.age_due_max is null
> 		then coalesce(vvs4p.min_interval, vvs4p.age_due_min)
> 		else (now() - ((select identity.dob from identity where identity.id=vvs4p.pk_patient) + vvs4p.age_due_max))
> 	end as amount_overdue,
> 	--- ... are just the inverse of each other
> 	vvs4p.age_due_min,
> 	vvs4p.age_due_max,
> 	vvs4p.min_interval,
> 	vvs4p.vacc_comment,
> 	vvs4p.pk_regime,
> 	vvs4p.pk_indication,
> 	vvs4p.pk_recommended_by
> from
> 	v_vaccs_scheduled4pat vvs4p
> where
> 	vvs4p.is_booster is false
> 		and
> 	vvs4p.vacc_seq_no > (
> 		select count(*)
> 		from v_pat_vacc4ind vpv4i
> 		where
> 			vpv4i.pk_patient = vvs4p.pk_patient
> 				and
> 			vpv4i.indication = vvs4p.indication
> 	)
> ;
> 
> comment on view v_pat_missing_vaccs is
> 	'vaccinations a patient has not been given yet according
> 	 to the schedules a patient is on and the previously
> 	 received vaccinations';
> 
> --- -----------------------------------------------------
> \unset ON_ERROR_STOP
> drop view v_pat_missing_boosters;
> \set ON_ERROR_STOP 1
> 
> --- FIXME: only list those that DO HAVE a previous vacc (max(date) is not null)
> create view v_pat_missing_boosters as
> select
> 	vvs4p.pk_patient,
> 	vvs4p.indication,
> 	vvs4p.l10n_indication,
> 	vvs4p.regime,
> 	vvs4p.reg_comment,
> 	vvs4p.vacc_seq_no as seq_no,
> 	coalesce(
> 		((select max(vpv4i11.date)
> 		  from v_pat_vacc4ind vpv4i11
> 		  where
> 			vpv4i11.pk_patient = vvs4p.pk_patient
> 				and
> 			vpv4i11.indication = vvs4p.indication
> 		) + vvs4p.min_interval),
> 		(now() - '1 day'::interval)
> 	) as latest_due,
> 	coalesce(
> 		(now() - (
> 			(select max(vpv4i12.date)
> 			from v_pat_vacc4ind vpv4i12
> 			where
> 				vpv4i12.pk_patient = vvs4p.pk_patient
> 					and
> 				vpv4i12.indication = vvs4p.indication) + vvs4p.min_interval)
> 		),
> 		'1 day'::interval
> 	) as amount_overdue,
> 	vvs4p.age_due_min,
> 	vvs4p.age_due_max,
> 	vvs4p.min_interval,
> 	vvs4p.vacc_comment,
> 	vvs4p.pk_regime,
> 	vvs4p.pk_indication,
> 	vvs4p.pk_recommended_by
> from
> 	v_vaccs_scheduled4pat vvs4p
> where
> 	vvs4p.is_booster is true
> 		and
> 	vvs4p.min_interval < age (
> 		(select max(vpv4i13.date)
> 			from v_pat_vacc4ind vpv4i13
> 			where
> 				vpv4i13.pk_patient = vvs4p.pk_patient
> 					and
> 				vpv4i13.indication = vvs4p.indication
> 		))
> ;
> 
> comment on view v_pat_missing_boosters is
> 	'boosters a patient has not been given yet according
> 	 to the schedules a patient is on and the previously
> 	 received vaccinations';
> 
> 
> 
> 
> Thanks,
> 
> Karsten Hilbert, MD, PhD
> GnuMed i18n coordinator
> http://www.gnumed.org
> -- 
> GPG key ID E4071346 @ wwwkeys.pgp.net
> E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
> 
>                         
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

In response to

Responses

pgsql-sql by date

Next:From: Jennifer LeeDate: 2004-09-24 11:50:36
Subject: select column by position
Previous:From: Ian HardingDate: 2004-09-24 00:43:10
Subject: Re: need ``row number``

pgsql-general by date

Next:From: Christian EnklaarDate: 2004-09-24 08:35:42
Subject: Re: Comparing a varchar of length > 32
Previous:From: Jeremy SemeiksDate: 2004-09-24 07:14:40
Subject: Re: books/sites for someone really learning PG's advanced features?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group