Обсуждение: Filling null values
I have a table that looks like this: homeid city date measurement pre/post 123 san francisco 1/2/2003 1458 pre 123 san francisco NULL 1932 post 124 los angeles 2/4/2005 938 pre 124 NULL NULL 266 pre 124 los angeles 7/4/2006 777 post I'd like to write a query so that I get the following result: homeid city date measurement pre/post 123 san francisco 1/2/2003 1458 pre 123 san francisco 1/2/2003 1932 post 124 los angeles 2/4/2005 938 pre 124 los angeles 2/4/2005 266 pre 124 los angeles 7/4/2006 777 post If a city or date is null, then it will fill from other not null values with the same homeid. If given the choice, it will preferentially fill from a row where homeid AND pre/post match. But if that doesn't match, then it will still fill from the same homeid. Does anyone have ideas for this? Thanks, Jeff
-----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of jeffrey Sent: Friday, August 05, 2011 12:33 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Filling null values I have a table that looks like this: homeid city date measurement pre/post 123 san francisco 1/2/2003 1458 pre 123 san francisco NULL 1932 post 124 los angeles 2/4/2005 938 pre 124 NULL NULL 266 pre 124 los angeles 7/4/2006 777 post I'd like to write a query so that I get the following result: homeid city date measurement pre/post 123 san francisco 1/2/2003 1458 pre 123 san francisco 1/2/2003 1932 post 124 los angeles 2/4/2005 938 pre 124 los angeles 2/4/2005 266 pre 124 los angeles 7/4/2006 777 post If a city or date is null, then it will fill from other not null values with the same homeid. If given the choice, it will preferentially fill from a row where homeid AND pre/post match. But if that doesn't match, then it will still fill from the same homeid. Does anyone have ideas for this? --------------------------------------------------------------- What version are you using? You may be able to accomplish your goals with Window functions. If "homeid" determines "city" you should just remove "city" from the table altogether and create a lookup table where "homeid" is the PK and "city" is one of the other columns. Your date issue is somewhat more problematic to correctly address. As an alternative to Window functions you'd probably want to, generally, create lookup tables. I just described the "homeid" lookup table but the date lookup table is a little more complicated. The general idea would be to ORDER and NUMBER the current records and then create a master lookup using ROW_NUMBER, "homeid", and "date". Then, for any rows missing a date you point into the lookup table and use the date from the lookup record with the largest ROW_NUMBER less than the current row. David J.
Le vendredi 05 août 2011 à 09:32 -0700, jeffrey a écrit : > I have a table that looks like this: > > homeid city date measurement pre/post > 123 san francisco 1/2/2003 1458 pre > 123 san francisco NULL 1932 post > 124 los angeles 2/4/2005 938 pre > 124 NULL NULL 266 pre > 124 los angeles 7/4/2006 777 post > > I'd like to write a query so that I get the following result: > > homeid city date measurement pre/post > 123 san francisco 1/2/2003 1458 pre > 123 san francisco 1/2/2003 1932 post > 124 los angeles 2/4/2005 938 pre > 124 los angeles 2/4/2005 266 pre > 124 los angeles 7/4/2006 777 post > > If a city or date is null, then it will fill from other not null > values with the same homeid. If given the choice, it will > preferentially fill from a row where homeid AND pre/post match. But > if that doesn't match, then it will still fill from the same homeid. > > Does anyone have ideas for this? > If it's possible for you to export the data to a text file, it is very easy to write a small Perl script that will replace NULLs by the appropriate values. -- Vincent Veyron http://marica.fr/ Logiciel de gestion des sinistres et des contentieux pour le service juridique
On 08/05/2011 07:32 PM, jeffrey wrote:
I have a table that looks like this: homeid city date measurement pre/post 123 san francisco 1/2/2003 1458 pre 123 san francisco NULL 1932 post 124 los angeles 2/4/2005 938 pre 124 NULL NULL 266 pre 124 los angeles 7/4/2006 777 post I'd like to write a query so that I get the following result: homeid city date measurement pre/post 123 san francisco 1/2/2003 1458 pre 123 san francisco 1/2/2003 1932 post 124 los angeles 2/4/2005 938 pre 124 los angeles 2/4/2005 266 pre 124 los angeles 7/4/2006 777 post If a city or date is null, then it will fill from other not null values with the same homeid. If given the choice, it will preferentially fill from a row where homeid AND pre/post match. But if that doesn't match, then it will still fill from the same homeid. Does anyone have ideas for this? Thanks, Jeff
You need a primary key on the table to do this. Then you can do a self join and update the correct values.
Sim
Hmm, no-one seemed to mention the obvious - a pl/pgsql function, either triggered or run manually depending if you want to update on insert/update or on demand.
On 7 August 2011 16:05, Sim Zacks <sim@compulab.co.il> wrote:
On 08/05/2011 07:32 PM, jeffrey wrote:I have a table that looks like this: homeid city date measurement pre/post 123 san francisco 1/2/2003 1458 pre 123 san francisco NULL 1932 post 124 los angeles 2/4/2005 938 pre 124 NULL NULL 266 pre 124 los angeles 7/4/2006 777 post I'd like to write a query so that I get the following result: homeid city date measurement pre/post 123 san francisco 1/2/2003 1458 pre 123 san francisco 1/2/2003 1932 post 124 los angeles 2/4/2005 938 pre 124 los angeles 2/4/2005 266 pre 124 los angeles 7/4/2006 777 post If a city or date is null, then it will fill from other not null values with the same homeid. If given the choice, it will preferentially fill from a row where homeid AND pre/post match. But if that doesn't match, then it will still fill from the same homeid. Does anyone have ideas for this? Thanks, Jeff
hi,
try this. If your table name is mytable:
select
a.homeid
, a.city
, coalesce(a.date, (select b.date from mytable b where
b.homeid=a.homeid and b.date is not null order by b.prepost=a.prepost
desc limit 1) ) as date
, a.measurement
, a.prepost
from
mytable a
Thomas
Am 05.08.2011 18:32, schrieb jeffrey:
> I have a table that looks like this:
>
> homeid city date measurement pre/post
> 123 san francisco 1/2/2003 1458 pre
> 123 san francisco NULL 1932 post
> 124 los angeles 2/4/2005 938 pre
> 124 NULL NULL 266 pre
> 124 los angeles 7/4/2006 777 post
>
> I'd like to write a query so that I get the following result:
>
> homeid city date measurement pre/post
> 123 san francisco 1/2/2003 1458 pre
> 123 san francisco 1/2/2003 1932 post
> 124 los angeles 2/4/2005 938 pre
> 124 los angeles 2/4/2005 266 pre
> 124 los angeles 7/4/2006 777 post
>
> If a city or date is null, then it will fill from other not null
> values with the same homeid. If given the choice, it will
> preferentially fill from a row where homeid AND pre/post match. But
> if that doesn't match, then it will still fill from the same homeid.
>
> Does anyone have ideas for this?
>
> Thanks,
> Jeff
>