Post reply

Update values with random values

  • July 17, 2007 at 7:45 pm

    #720244

    What more could I really say about them?  They seem fairly self explanatory in the links I posted on SQLTeam.

     

    I did notice that I posted the link to the date table function, not the number table function, so I corrected that.

     

     

     

     

  • July 18, 2007 at 11:04 am

    #720451

    Michaels solution is slightly better in the average part and standard deviation part.

    Mine is better in the min and max part.

    A_Min           A_Max           A_Avg           A_stdev         B_Min           B_Max           B_Avg           B_stdev

    0.9500000628500 1.0499998679000 0.9999792216210 0.0288983278667 0.9500000000000 1.0500000000000 0.9999620000000 0.0302697208647

    I wonder about the speed difference?

     

    N 56°04'39.16"
    E 12°55'05.25"

  • July 18, 2007 at 11:57 am

    #720475

    Thanks for all your advice, although it maybe a little more complicated than I need for this project.

    I have a 'valeudetails' table, with a field called 'avmvalue' that contains a rounded off number and a field called 'AVM' that is blank.

    I need to take the 'avmvalue' field and either + or - between 1-5 percent randomly.

    I then need to  insert that value into the 'AVM' column.

    The one below works except it just +/- the actual number and not the by percentage. 

    Thanks in advance for all your help

    Susan

     

     

    update valuedetails

    set

    AVM = avmvalue, AVMValue +

    case when RAND(CAST(NEWID() AS VARBINARY)) > 0.50

    then CAST(RAND(CAST(NEWID() AS VARBINARY))*-5+1 AS INT)

    else CAST(RAND(CAST(NEWID() AS VARBINARY))*5+1 AS INT)

    end

    from valuedetails

  • July 18, 2007 at 1:13 pm

    #720507

    If your objective is to increment by a random value between -5 through +5, it does not do that.  Your algorithim does not produce a random distribution, and the range is only -3 to +5.

    I also noticed that Peters algorithim produces an non-random distribution; it doubles the number of zeros.

    I posted an algorithim in this test that produces an even distribution across the values of -5 to +5.  You can see the results for all three methods below for a 1 million row random sample.

    
create table #t (guid uniqueidentifier not null, guid2 uniqueidentifier not null, )
go
insert into #t select newid(), newid() from f_table_number_range(1,1000000)
go
select
 MVJ = a, Rows =count(*)
from
 (
 select top 100 percent
  A = 5-((abs(convert(bigint,convert(varbinary(8),guid)))%11))
 from
  #t x
 ) xx
group by a
order by a
    select
 Peter = a, Rows =count(*)
from
 (
 select top 100 percent
  A =(CHECKSUM(NEWID()) % 6)
 from
  #t x
 ) xx
group by a
order by a
    select
 SG = a, Rows =count(*)
from
 (
 select top 100 percent
  A = case when RAND(CAST(guid2 AS VARBINARY)) > 0.50
 then CAST(RAND(CAST(guid AS VARBINARY))*-5+1 AS INT)
 else CAST(RAND(CAST(guid AS VARBINARY))*5+1 AS INT) end
 from
  #t x
 ) xx
group by a
order by a
go
drop table #t
    Results:
    (1000000 row(s) affected)
    MVJ                  Rows        
-------------------- ----------- 
-5                   90770
-4                   91087
-3                   90490
-2                   91089
-1                   90605
0                    90815
1                    91161
2                    91075
3                    91047
4                    90875
5                    90986
    (11 row(s) affected)
    Peter       Rows        
----------- ----------- 
-5          83663
-4          83228
-3          83078
-2          83144
-1          83634
0           166328
1           83643
2           83428
3           83357
4           83584
5           82913
    (11 row(s) affected)
    SG          Rows        
----------- ----------- 
-3          100192
-2          99789
-1          100390
0           199756
1           99981
2           99762
3           100474
4           99823
5           99833
    (9 row(s) affected)
     

  • July 18, 2007 at 1:39 pm

    #720517

    Ok, I see your point, I can see the ranges, but am still having a hard time getting it all in the update statement

    and making it a percentage. This is my first time using 'Rand'.. Any help would be appreciated.

    Thanks in advance

    Susan

     

    update

    Propertydetails

    set

    ReplyAVM =  AVM_Value +

    case when RAND(CAST(NEWID() AS VARBINARY)) > 0.05

    then CAST(RAND(CAST(NEWID() AS VARBINARY))*-5+1 AS INT)

    else CAST(RAND(CAST(NEWID() AS VARBINARY))*5+1 AS INT)

    end

    from

    Propertydetails

    where

    avm_value <> 0

     

     

     

  • July 18, 2007 at 1:51 pm

    #720524

    This wiil produce a random distribution of -0.05 to +0.05

    select A = ((5-((abs(convert(bigint,convert(varbinary(8),newid())))%11)))*.01)
     

  • July 18, 2007 at 2:02 pm

    #720529

    Thanks for the clarification -

    Here's what I will be using - Do I have to do the top 100 percent? or would you recommend looping/cursoring it?

    update Propertydetails

    set ReplyAVM = AVM_Value +

    (select top 100 percent   ((abs(convert(bigint,convert(varbinary(8),avm_value)))%11)))

    from propertydetails

    where avm_value <>0

     

    Thanks for much for all your help.

    Susan

     

  • July 18, 2007 at 2:21 pm

    #720538

    You haven't posted your table structure, sample data, column datatypes, or desired results, so it is hard to tell exactly what you are after.

    It is best to test something like this first with a SELECT, before running an UPDATE.  When the results look correct, then run it as an update.

    Select 
 AVM_Value ,
 New_ReplyAVM = AVM_Value +
   ((abs(convert(bigint,convert(varbinary(8),avm_value)))%11)))
from
 propertydetails 
where
 avm_value <>0

  • July 18, 2007 at 6:53 pm

    #720590

    Ok, folks... I don't get it... why does everyone keep adding when they should be multiplying???    Using Peter's modified algo which returns .95 to 1.05 (or -5% to +5%), all you need is the following...

     UPDATE ValueDetails

        SET AVM = AVMValue * (ABS(CHECKSUM(NEWID()))%11-5)

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 19, 2007 at 4:00 am

    #720665

    Nice and short.

     

    N 56°04'39.16"
    E 12°55'05.25"

  • July 19, 2007 at 7:34 am

    #720762

    Jeff, I think you need to check that formula.  It produces values in the range of -5 to +5:

    (ABS(CHECKSUM(NEWID()))%11-5)

     

    This may be closer to what you are suggesting.  It produces values in the range of .95 to 1.05.

    .95+((ABS(CHECKSUM(NEWID()))%11)*.01)

     

     

  • July 19, 2007 at 5:32 pm

    #720957

    Crud, you're right, my bad...Thanks, Michaeal.   Except should be this because, as you said, Peter's algo returns +/- 5...

    UPDATE ValueDetails

        SET AVM = AVMValue * (1+(ABS(CHECKSUM(NEWID()))%11-5)*.01)

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply