jeudi 2 juin 2016

Count distinct values then add rows

Having trouble with a dataset counting the distinct TMC values and adding blank rows up to the value X (or 6636). Basically, i have a dataset that has 550 distinct TMC values and i want the query to count the rows for each of them (its a one to many). So for example, TMC X has 5652 records TMC Y has 5000 records, TMC b 4203 records and i want rows added to each TMC up to 6636 records. "For each TMC, count the values and if they to do not equal 6636, add a row with blank values". The dataset is very large about 3.5 million records so looping statements do take sometime. Ive tried group bys but the looped functions do not like the outputs.

I have tried looping statements, creating a dummy table and inserting into statements, cursors and a number of other things. The two queries that i have ran that took FOREVER and errored out are below.

--  Declare Input Variables.    --
Declare
v_tt_row            testnpmrds2%ROWTYPE;   
i                   number(10) :=0;


CURSOR rs_cursor
IS
    SELECT *
      FROM s5140ice.testnpmrds2; BEGIN     

OPEN rs_cursor;

LOOP             

    FETCH rs_cursor
      INTO v_tt_row;        
    EXIT WHEN rs_cursor%NOTFOUND;

    select count(*)
      into i  
      from testnpmrds2
      where tmc = v_tt_row.tmc;

    then
      while i < 6636 loop
        INSERT
          INTO testnpmrds2 (tmc, date1, epoch, tt_all_veh
            )                                                                                    
           VALUES (v_tt_row.tmc,'no',0,0                        
                      );
       i:=i+1;

       end loop;  

   end if;                    
   END LOOP;

COMMIT; END;


OR

declare i binary_integer :=1;
v_count number;

begin
select count(distinct tmc) into v_count from testnpmrds2; 
if v_count < 6636 
then
Loop
insert into testnpmrds2 select tmc,TT_ALL_VEH,
    LENGTH,
    COUNTY,
    DATE1,
    DATE2,
    ROUTE,
    EPOCH,
    PEAK_PERIODS,
    PEAK_PERIODS_LIT,
    ROUTE_TYPE from testnpmrds2;

i := i + 1;

end loop;
end if;
end

Any help on this one? I am kind of a novice coder so any suggestions would be helpful. Ultimately, in my head it sounds fairly straightforward.

Thanks!

Aucun commentaire:

Enregistrer un commentaire