sql - PostgreSQL Mathematical Function -

-

i have table aps_sections many integer fields (such bare_width , worn_width). have multiple tables (such aps_bare_width , aps_worn_width) contain id column , weighting column. id recorded in above columns of aps_sections table. need sum weightings of columns in aps_sections table (whereby weighting value comes tables). have managed using below select statement.

select aps_sections.ogc_fid,        ( aps_bare_width.weighting        + aps_worn_width.weighting        + aps_gradient.weighting        + aps_braiding.weighting        + aps_pigeon.weighting        + aps_depth.weighting        + aps_standing_water.weighting        + aps_running_water.weighting        + aps_roughness.weighting        + aps_surface.weighting        + aps_dynamic.weighting        + aps_ex_cond.weighting        + aps_promotion.weighting        + aps_level_of_use.weighting) calc row_access.aps_sections,      row_access.aps_bare_width,      row_access.aps_worn_width,      row_access.aps_gradient,      row_access.aps_braiding,      row_access.aps_pigeon,      row_access.aps_depth,      row_access.aps_standing_water,      row_access.aps_running_water,      row_access.aps_roughness,      row_access.aps_surface,      row_access.aps_dynamic,      row_access.aps_ex_cond,      row_access.aps_promotion,      row_access.aps_level_of_use aps_bare_width.fid = aps_sections.bare_width ,   aps_worn_width.fid = aps_sections.worn_width ,   aps_gradient.fid = aps_sections.gradient ,   aps_braiding.fid = aps_sections.braiding ,   aps_pigeon.fid = aps_sections.pigeon ,   aps_depth.fid = aps_sections.depth ,   aps_standing_water.fid = aps_sections.standing_water ,   aps_running_water.fid = aps_sections.running_water ,   aps_roughness.fid = aps_sections.roughness ,   aps_surface.fid = aps_sections.surface ,   aps_dynamic.fid = aps_sections.dynamic ,   aps_ex_cond.fid = aps_sections.ex_cond ,   aps_promotion.fid = aps_sections.promotion ,   aps_level_of_use.fid = aps_sections.level_of_use

what need create function adds calculated result physical_sn_priority column of aps_sections table. understanding far function should similar to:

create or replace function row_access.aps_weightings()   returns trigger $body$     begin     new.physical_sn_priority := ;        return new;     end; $body$   language plpgsql volatile   cost 100; alter function public.update_km()   owner postgres;

but don't know put after new.physical_sn_priority :=. beginner sql , postgresql appreciate guidance!

while erwin (as always) correct version helpful, think answer simplest select ... into construction pl/pgsql. (not same select into works insert or create table.)

select ( aps_bare_width.weighting    + /* obvious deletia */    + aps_level_of_use.weighting) new.physical_sn_priority row_access.aps_bare_width,  /* snip */,  row_access.aps_level_of_use aps_bare_width.fid = new.bare_width     , /* snip */     aps_level_of_use.fid = new.level_of_use;  return new;

according documentation, into can appear in several other places in line; find simple understand.

[edit]

while works, on reflection, think schema should revised.

create type weighted_item_t enum ('bare_width', /* ... */, 'level_of_use'); create table current_weights(item_type weighted_item_t, fid int, current_weight float);      /* key , checks omitted */      /* note, if item_type can deduced fid, don't need enum */ create table sections_items(section_id int /* fk aps_sections */,      item_type weighted_item_t, fid int);

now queries going collapse simple sums. need insert records section_items before aps_sections, can done deferred constraints in transaction or without stored procedure, depending on how acquire data , how control have on format. if (and not clear, because won't change on updates) want denormalized total, can with

select sum(current_weight) new.physical_sn_priority section_items natural join current_weights new.section_id=section_items.section_id;

this work out better if additional weighted characteristics added @ future date.


Comments

Post a Comment

Popular posts from this blog

java - Jmockit String final length method mocking Issue -

-
using jmockit 1.2 jar, trying mock string's length method getting unexpected invocation exception: failed: test java.lang.illegalstateexception: missing invocation mocked type @ point; please make sure such invocations appear after declaration of suitable mock field or parameter @ stringdemo.testa$1.<init>(testa.java:17) @ stringdemo.testa.test(testa.java:13) i using testng: @test public void test() throws exception { new expectations() { @mocked("length") string astring; { astring.length(); result = 2; } }; system.out.println(a.showa("test")); } } actual class a: public class { public static int showa(string str){ int a= str.length(); return a; } } this wrong way of recording expected results. shouldn't mock , record string's length() method, record showa() instead. here solution @suppresswarnings("unused...
Read more

What is the difference between data design and data model(ERD) -

-
i have google question , find answer in day. know, data design many many table have list column,and mark pk , fk. why er-diagram have find this? data design process of designing database. main output of data design detailed logical data model of database. some people data design includes of needed logical , physical design choices , physical storage parameters needed generate design in data definition language. technically, database analysis, database analysts (dbas) trained do. while person can both data design , database analysis, these 2 different tasks. data design models data. database analysis takes model , applies 1 or more database engines (relational, hierarchical, nosql). a logical data model 1 of main outputs of data design. data model represented entity relationship diagram, or er diagram.
Read more

ios - Can NSManagedObject conform to NSCoding -

-
i need transfer single object across device. right converting nsmanagedobject dictionary , archiving , sending nsdata. upon receiving unarchiving it. transfer nsmanagedobject archiving , unarchiving instead of creating intermediate data object. @interface test : nsmanagedobject<nscoding> @property (nonatomic, retain) nsstring * title; @end @implementation test @dynamic title; - (id)initwithcoder:(nscoder *)coder { self = [super init]; if (self) { self.title = [coder decodeobjectforkey:@"title"]; //<crash } return self; } - (void)encodewithcoder:(nscoder *)coder { [coder encodeobject:self.title forkey:@"title"]; } @end nsdata *archivedobjects = [nskeyedarchiver archiveddatawithrootobject:testobj]; nsdata *objectsdata = archivedobjects; if ([objectsdata length] > 0) { nsarray *objects = [nskeyedunarchiver unarchiveobjectwithdata:objectsdata]; } the problem above code is. crashes @ self.title in initwithcoder sa...
Read more