[GENERAL] how to create data on the fly?
Hello! i am again struggling with a problem i am unsure how to set up. I could easily solve all in the php backend, but this would impede further extensions and doesn't satisfy my curiosity :D so, here's the problem: a patient takes everyday a medecine, and from time to time comes in a result of a blood-sample. Now i first of all, i want to draw a graph showing the dosis taken, and the blood-values mesured. Later on i want to calculate the assimilation rate, the saturation rate, and the effective rate, both in real, and prognosis My actual problem beeing that the dosis may be not simply 1 to n pills per day, but for example day 1 1, day 2 1.25, day 3 .5, day 4 1, etc... the most complex case to now i, has a period over 4 days so i set up: CREATE TABLE patients ( id integer NOT NULL, name text, minzone real, maxzone real, refresh integer ); CREATE TABLE inrdata ( id integer NOT NULL, temps timestamp without time zone, patid integer, inr real ); CREATE TABLE posologie ( id integer NOT NULL, inrid integer, champ text, definition text, valeur real ); In patients i have the persons name, and the boundaries that are wanted for the blood-sample value, in inrdata i have the timepoints where i get a blood-sample-result, and i eventually adjust the posology. in posologie i have the table at a timepoint i have stored at the moment in champ=sequence, definition="1;1.25;0.5;1", and actually i solve the problem with the frontend what i would like is to store the posology iterations in the posology table, and be able to make a select that generates the data on the fly. Taking the different waypoints given by the data in inrdata as starting point and computing for each day the actual dosis but i have no idea how to do this in sql? any help appreciated! ciao Bruno == bbo...@adlp.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to create data on the fly?
Hi, what is the mathematical definition of this sequence? This could be done using plpgsql, but I have to know how to calculate values in the future. Regards, Bartek 2012/2/28 > Hello! > > i am again struggling with a problem i am unsure how to set up. I could > easily solve all in the php backend, but this would impede further > extensions > and doesn't satisfy my curiosity :D > > so, here's the problem: a patient takes everyday a medecine, and from time > to > time comes in a result of a blood-sample. > > Now i first of all, i want to draw a graph showing the dosis taken, and the > blood-values mesured. Later on i want to calculate the assimilation rate, > the > saturation rate, and the effective rate, both in real, and prognosis > > My actual problem beeing that the dosis may be not simply 1 to n pills per > day, > but for example day 1 1, day 2 1.25, day 3 .5, day 4 1, etc... the most > complex case to now i, has a period over 4 days > > so i set up: > > CREATE TABLE patients ( >id integer NOT NULL, >name text, >minzone real, >maxzone real, >refresh integer > ); > > CREATE TABLE inrdata ( >id integer NOT NULL, >temps timestamp without time zone, >patid integer, >inr real > ); > > CREATE TABLE posologie ( >id integer NOT NULL, >inrid integer, >champ text, >definition text, >valeur real > ); > > > In patients i have the persons name, and the boundaries that are wanted > for > the blood-sample value, in inrdata i have the timepoints where i get a > blood-sample-result, and i eventually adjust the posology. > > in posologie i have the table at a timepoint i have stored at the moment in > champ=sequence, definition="1;1.25;0.5;1", > > and actually i solve the problem with the frontend > > what i would like is to store the posology iterations in the posology > table, > and be able to make a select that generates the data on the fly. > Taking > the different waypoints given by the data in inrdata as starting point > and computing for each day the actual dosis > > but i have no idea how to do this in sql? > > any help appreciated! > > > ciao Bruno > == > bbo...@adlp.org > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Re: [GENERAL] how to create data on the fly?
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Bartosz Dmytrak Sent: Tuesday, February 28, 2012 3:33 PM To: bbo...@free.fr Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] how to create data on the fly? Hi, what is the mathematical definition of this sequence? This could be done using plpgsql, but I have to know how to calculate values in the future. Regards, Bartek 2012/2/28 Hello! i am again struggling with a problem i am unsure how to set up. I could easily solve all in the php backend, but this would impede further extensions and doesn't satisfy my curiosity :D so, here's the problem: a patient takes everyday a medecine, and from time to time comes in a result of a blood-sample. Now i first of all, i want to draw a graph showing the dosis taken, and the blood-values mesured. Later on i want to calculate the assimilation rate, the saturation rate, and the effective rate, both in real, and prognosis My actual problem beeing that the dosis may be not simply 1 to n pills per day, but for example day 1 1, day 2 1.25, day 3 .5, day 4 1, etc... the most complex case to now i, has a period over 4 days so i set up: CREATE TABLE patients ( id integer NOT NULL, name text, minzone real, maxzone real, refresh integer ); CREATE TABLE inrdata ( id integer NOT NULL, temps timestamp without time zone, patid integer, inr real ); CREATE TABLE posologie ( id integer NOT NULL, inrid integer, champ text, definition text, valeur real ); In patients i have the persons name, and the boundaries that are wanted for the blood-sample value, in inrdata i have the timepoints where i get a blood-sample-result, and i eventually adjust the posology. in posologie i have the table at a timepoint i have stored at the moment in champ=sequence, definition="1;1.25;0.5;1", and actually i solve the problem with the frontend what i would like is to store the posology iterations in the posology table, and be able to make a select that generates the data on the fly. Taking the different waypoints given by the data in inrdata as starting point and computing for each day the actual dosis but i have no idea how to do this in sql? any help appreciated! ciao Bruno == bbo...@adlp.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general A recent discussion comes to mind: SELECT unnest(champ_array_col), generate_series(1, total_number_of_rows_desired) … LIMIT total_number_of_rows_desired This gets you “greatest-common-multiple” number of rows, with whatever values are part of the array repeating as necessary, which you then limit to your actual desired number of rows. Not sure if this helps but it would at least let you store the periods (in the form on an explicit array) and generate the desired timeline over however many increments are needed. You could also try: (pseudo code) … generate_series(1, length(champ_array_col) * number_of_periods_desired) This will always generate exactly length*period_count rows with each element of the array appearing every length rows and a total of “number_of_periods_desired” times. HTH, David J.
