机智的造假->sql给Echart提供数据

最新推荐文章于 2023-04-09 17:14:09 发布

转载最新推荐文章于 2023-04-09 17:14:09 发布 · 133 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/datacool/p/datacool20180815.html

本文介绍了一种使用SQL脚本模拟随上班时间递增的数据方法。该脚本通过不同时间段设置不同的计算逻辑，确保生成的数据既符合业务场景又接近真实情况。

数据要求：要求数据随着上班时间的延长要递增，要看起来像真数据

declare @key int;                 
    declare cur_rate cursor for select keyID from #t1;
    open cur_rate;
    fetch next from cur_rate into @key;
    while @@fetch_status<>-1
       begin
          declare @rate2 numeric(9,2);
          declare @rate3 numeric(9,2); 
          declare @hour int;
          SELECT @hour=DATEPART(hh,getdate());
          if @hour>0 and @hour<10 
            begin
                select @rate2= 73+cast(ceiling(rand() * 10) as float)+cast(ceiling(rand() * 100) as float)/100;
                select @rate3=@rate2-cast(ceiling(rand() * 100) as float)/100; 
                if @key % 4=0 
                begin
                   set @rate2=98-cast(ceiling(rand() * 100) as float)/100;
                   set @rate3=86+cast(ceiling(rand() * 100) as float)/100;
                end;
            end
          if @hour>10 and @hour<14 
            begin
                select @rate2= 85+cast(ceiling(rand() * 10) as float)+cast(ceiling(rand() * 100) as float)/100;
                select @rate3=@rate2-cast(ceiling(rand() * 100) as float)/100;
                if @key % 7=0 
                begin
                   set @rate2=100-cast(ceiling(rand() * 100) as float)/100;
                   set @rate3=98+cast(ceiling(rand() * 100) as float)/100;
                end;
            end 
          if @hour>14 and @hour<23 
             begin
                select @rate2= 91+cast(ceiling(rand() * 10) as float)+cast(ceiling(rand() * 100) as float)/100;
                if @rate2>100
                   begin
                       set @rate2=100;
                       set @rate3=100;
                   end
                else begin
                   select @rate3=@rate2-cast(ceiling(rand() * 100) as float)/100;
                end
                if @key % 12=0 
                begin
                   set @rate2=100-cast(ceiling(rand() * 100) as float)/100;
                   set @rate3=100-cast(ceiling(rand() * 100) as float)/100;
                end;
             end  
          update #t1 set rate1=100,rate2=@rate2,rate3=@rate3 where keyID=@key; 
          fetch next from cur_rate into @key;
       end
    close cur_rate; 
    deallocate cur_rate;

转载于:https://www.cnblogs.com/datacool/p/datacool20180815.html