SQL. Problems and Solutions
Prepare for the second stage of testing
Typical query problems
Least squares method (LSM)

Least squares method (LSM)

Let us have the set of values yiy_i, every one of which corresponds to any moment of time ti(i=1,2,…,N)t_i (i = 1, 2,…, N). We need to find dependency y=f(t)y = f(t), if the sum of squared divergences of points of curve from the appropriate point yiy_i is the minimal in the class of approximating functions. That is

(1)∑i=1n(f(ti)−yi)2→min⁡ (1) \sum_{i=1}^n (f(t_i) - y_i)^2 \rarr \min

We will find the solution in the class of the linear functions (straight lines): y=at+by = at + b. Then the term (1) we can write as the following

∑i=1n(a∗ti+b−yi)2→min⁡ \sum_{i=1}^n(a*t_i + b - y_i)^2 \rarr \min

The necessary condition of the existence of minimum is the equality to zero of two partial derivatives on a and b accordingly:

∑i=1n((a∗ti+b−yi)∗ti)=0 \sum_{i=1}^n((a*t_i + b - y_i)*t_i) = 0

∑i=1n(a∗ti+b−yi)=0 \sum_{i=1}^n(a*t_i + b - y_i) = 0

In designations

∑i=1nti2=stt \sum_{i=1}^nt_i^2 = stt

∑i=1n(yi∗ti)=syt \sum_{i=1}^n(y_i*t_i) = syt

∑i=1n(ti)=st \sum_{i=1}^n(t_i) = st

∑i=1n(yi)=sy \sum_{i=1}^n(y_i) = sy

Noting that

∑i=1nb=b∗N, \sum_{i=1}^nb = b*N,

the system can be rewritten as the following

a∗stt+b∗st−syt=0 a*stt + b*st - syt = 0

a∗st+b∗N−sy=0 a*st +b*N - sy = 0

Solving this system of two linear equations for aa and bb we will get

a=(syt∗N−sy∗st)/(N∗stt−st∗st) a = (syt*N - sy*st)/(N*stt - st*st)

b=(stt∗sy−syt∗st)/(N∗stt−st∗st) b = (stt*sy - syt*st)/(N*stt - st*st)

#Least-Square Method

Conversion of the date to a string