Thread: Re: [GENERAL] need ``row number``

Re: [GENERAL] need ``row number``

From
Karsten Hilbert
Date:
> 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

Re: [GENERAL] need ``row number``

From
Tom Lane
Date:
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

Re: [GENERAL] need ``row number``

From
Greg Stark
Date:
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