CREATE TABLE portal.tempCorelation AS
WITH temp(nskv, gid, zak, Dat, Gor, X, Y) AS (
	SELECT TRIM(a.nskv) as nskv,
	       a.Gid/a.Dni as gid,
	       a.Zak/a.Dni as zak,
	       a.Dat,
	       a.Gor,
	       b.X,
	       b.Y
	  FROM portal.abdr a
	       INNER JOIN portal.abdadr b ON TRIM(b.nskv) = TRIM(a.nskv)
	 WHERE EXTRACT(YEAR FROM a.Dat) between 2014 AND 2015
	       AND a.Dni != 0 
)
SELECT d.nskv, 
       z.nskv as nskvZ, 
       d.gid, 
       z.zak, 
       TRUNC(SQRT((d.x-z.x)*(d.x-z.x)+(d.y-z.y)*(d.y-z.y))) as d,
       TRUNC(ACOS((z.x-d.x)/(SQRT((z.x-d.x)*(z.x-d.x)+(z.y-d.y)*(z.y-d.y))))*180/PI())+(CASE WHEN (z.y-d.y) < 0 THEN 180 ELSE 0 END) as alpha
  FROM temp d,
       temp z 
 WHERE (d.x-z.x)*(d.x-z.x)+(d.y-z.y)*(d.y-z.y) between 1 and 1000*1000
       AND d.Dat = z.Dat
       AND d.Gor = z.Gor
       AND exists (SELECT 1 FROM portal.abddobg d1 WHERE TRIM(d.nskv) = TRIM(d1.nskv))
       AND exists (SELECT 1 FROM portal.abdzak z1 WHERE TRIM(z.nskv) = TRIM(z1.nskv))
       AND d.gid > 0
       AND z.zak > 0
ORDER BY d.nskv, z.nskv;

CREATE TABLE portal.tempCorelation AS
WITH temp(nskv, gid, zak, Dat, Gor, X, Y) AS (
	SELECT TRIM(a.nskv) as nskv,
	       a.Gid/a.Dni as gid,
	       a.Zak/a.Dni as zak,
	       a.Dat,
	       a.Gor,
	       b.X,
	       b.Y
	  FROM portal.abdr a
	       INNER JOIN portal.abdadr b ON TRIM(b.nskv) = TRIM(a.nskv)
	 WHERE EXTRACT(YEAR FROM a.Dat) between 2014 AND 2015
	       AND a.Dni != 0 
)
SELECT d.nskv, 
       z.nskv as nskvZ, 
       d.gid, 
       z.zak, 
       TRUNC(SQRT((d.x-z.x)*(d.x-z.x)+(d.y-z.y)*(d.y-z.y))) as d,
       TRUNC(ACOS((z.x-d.x)/(SQRT((z.x-d.x)*(z.x-d.x)+(z.y-d.y)*(z.y-d.y))))*180/PI())+(CASE WHEN (z.y-d.y) < 0 THEN 180 ELSE 0 END) as alpha
  FROM temp d,
       temp z 
 WHERE (d.x-z.x)*(d.x-z.x)+(d.y-z.y)*(d.y-z.y) between 1 and 1000*1000
       AND d.Dat = z.Dat
       AND d.Gor = z.Gor
       AND exists (SELECT 1 FROM portal.abddobg d1 WHERE TRIM(d.nskv) = TRIM(d1.nskv))
       AND exists (SELECT 1 FROM portal.abdzak z1 WHERE TRIM(z.nskv) = TRIM(z1.nskv))
       AND d.gid > 0
       AND z.zak > 0
ORDER BY d.nskv, z.nskv;

--drop table portal.tempCorelation;
--drop table portal.tempCorelation2;


CREATE TABLE portal.tempCorelation2 AS
SELECT t.nskv, 
       t.nskvZ, 
       t.gid, 
       t.zak, 
       avg(t.numGid) OVER (PARTITION BY t.nskv, t.nskvZ, t.rankGid) as rankGid,
       count(1) OVER (PARTITION BY t.nskv, t.nskvZ, t.gid) as lx,
       avg(t.numZak) OVER (PARTITION BY t.nskv, t.nskvZ, t.rankZak) as rankZak,
       count(1) OVER (PARTITION BY t.nskv, t.nskvZ, t.zak) as ly,
       t.d,
       t.alpha
  FROM (SELECT *,
               row_number() OVER (PARTITION BY nskv, nskvZ ORDER BY gid) as numGid,
               rank() OVER (PARTITION BY nskv, nskvZ ORDER BY gid) as rankGid,
               row_number() OVER (PARTITION BY nskvZ, nskv ORDER BY zak) as numZak,
               rank() OVER (PARTITION BY nskv, nskvZ ORDER BY zak) as rankZak
          FROM portal.tempCorelation) t
ORDER BY t.nskv, 
	 t.nskvZ, 
	 t.zak;