[ale] db insert driving me nuts

Jim Kinney jim.kinney at gmail.com
Thu Nov 5 15:14:21 EST 2009


I'm working on a postgresql db and have a data file I need to
insert/mung and insert and I'm stumped on the following:

data is  as below

ID   counter_1   counter_2  counter_total   type
1       100              20            120             A
1         15                3              18             U
2         23               22            45              C
2        125              55             180           U
3        15              120            135            A
4        75               25              100           B
...

I need to sum the counter per ID. That's easy. But I also need to
adjust the type U to be A,B,C,D or E if the ID ALSO has a type U but
NOT if the ID has ONLY a type U

So the final data is compacted to be:

ID   counter_1   counter_2  counter_total   type
1       115              23            138             A
2       148             77              225             C
3        15              120            135            A
4        75               25              100           B


The best part is the source of these data sets is two separate files

One file has ID, counter_1, type
the other has ID, counter_2, type

neither file has counter_total as it's a sum of counter_1 and counter_2

each file is millions of lines long.....

-- 
-- 
James P. Kinney III
Actively in pursuit of Life, Liberty and Happiness


More information about the Ale mailing list