Thread: Re: need ``row number``
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
thinkof.
<<< Karsten Hilbert <Karsten.Hilbert@gmx.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
> 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.Hilbert@gmx.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
Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
> 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.
A fairly common hack for this is to use a sequence:
create temp sequence tseq;
select nextval('tseq'), * from (select .... order by ...) ss;
Note you must do any desired ORDER BY inside the subselect. If it's
outside then it happens after computation of the row numbers, which
is exactly not what you want.
The major limitation of this is that you need to do some auxiliary
operations to create or reset the semaphore before each query.
There are some related hacks in the archives that use PL functions
with private persistent state, instead of a sequence object.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes:
> Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
> > 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.
>
> A fairly common hack for this is to use a sequence:
>
> create temp sequence tseq;
> select nextval('tseq'), * from (select .... order by ...) ss;
But I thought she wanted to get the row number within a group. Not the row
number for the entire result set. A sequence can't do that. Or at least, I
suppose it could but it would be mighty strange to see setval() in a SELECT
query. And I can't think of how to detect the level break in a select query
either.
--
greg