need "row number"

Поиск
Список
Период
Сортировка
От Karsten Hilbert
Тема need "row number"
Дата
Msg-id 20040923222417.F1054@hermes.hilbert.loc
обсуждение исходный текст
Список pgsql-general
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

В списке pgsql-general по дате отправления:

Предыдущее
От: Gaetano Mendola
Дата:
Сообщение: Re: Comparing a varchar of length > 32
Следующее
От: Josué Maldonado
Дата:
Сообщение: Restore a especific function