(转载)98-老虎刘和chatGPT pk了一把SQL改写优化

newkid

could you optimize this PL/SQL block?

declare

  n number := 100 ;
  v number :=0 ;
begin
  for i in 1..n loop
     for j in i..n-i loop
         v:=v+1 ;
     end loop ;
  end loop ;
  DBMS_OUTPUT.PUT_LINE( 'v='||v ) ;
end ;
/

Here's an optimized version of the PL/SQL block you provided:

DECLARE
n NUMBER := 100;
v NUMBER := 0;
BEGIN
v := ((n * (n + 1)) / 2) - ((n / 2) * (n / 2));
DBMS_OUTPUT.PUT_LINE('v=' || v);
END;

The optimized version of the block uses a mathematical formula to calculate the value of v instead of using nested loops. The formula used is:

v = (n * (n + 1) / 2) - (n / 2) * (n / 2)

This formula calculates the number of times the inner loop is executed without actually executing the loop. It is derived from the fact that the sum of the first n natural numbers is given by the formula:

sum = n * (n + 1) / 2

The inner loop in the original code is equivalent to counting the number of integers between i and n-i inclusive. By using the mathematical formula, we can calculate the sum of these integers without iterating over them one by one.

The optimized version is faster and more efficient than the original version because it avoids the nested loop and uses a mathematical formula instead.

newkid

jihuyao 发表于 2023-3-27 10:59
I am also wondering if chatGPT can see some simple hidden rules/patterns, like two shown below (one  ...

你这两个例子看不出什么逻辑来，如果要观察规律，给的例子又太少。
你自己上不了chatgpt吗？