Dienstag, 4. August 2009

Some tips and tricks I used to trace the particles

















This post is just MySQL helper to manage my particles in the database.
With help of my best fiend Karine.

What WE DID!!
create procedure proc @snap int
as

select * into #t from MODEL7_test2 where snap between @snap-10 and @snap+10

heto

select ID, MIN( SQRT( POW((a.posx-b.xc),2)+POW((a.posy-b.yc),2)+POW((a.posz-b.zc),2))) as MinPosMax, Max(SQRT( POW((a.posx-b.xc),2)+POW((a.posy-b.yc),2)+POW((a.posz-b.zc),2))) as MaxPos from #t as a
inner join COMDB_MODEL7 as b on a.snap=b.snap
where a.id in (select id from MODEL7_test2 where snap=@snap) group by id;
oy verchi toxum el where a.id in (select id from #t where snap=@snap

CREATE TEMPORARY TABLE t1 AS SELECT *
FROM MODEL7_test2
WHERE snap
BETWEEN 501 -1
AND 501 +1

create temporary table t1 as select * from MODEL7_test2 where snap between 501-1 and 501+1;
select ID, MIN( SQRT( POW((a.posx-b.xc),2)+POW((a.posy-b.yc),2)+POW((a.posz-b.zc),2))) as MinPosMax, Max(SQRT( POW((a.posx-b.xc),2)+POW((a.posy-b.yc),2)+POW((a.posz-b.zc),2))) as MaxPos from t1 as a
inner join COMDB_MODEL7 as b on a.snap=b.snap and a.snap between 501-1 and 501+1 where a.id in (select id from MODEL7_test2 where snap=501) group by id;

CREATE TEMPORARY TABLE t1 AS SELECT *
FROM MODEL7_test2
WHERE snap
BETWEEN 501 -1
AND 501 +1;# Affected rows: 1978554
SELECT ID, MIN( SQRT( POW( (
a.posx - b.xc
), 2 ) + POW( (
a.posy - b.yc
), 2 ) + POW( (
a.posz - b.zc
), 2 ) ) ) AS MinPos, Max( SQRT( POW( (
a.posx - b.xc
), 2 ) + POW( (
a.posy - b.yc
), 2 ) + POW( (
a.posz - b.zc
), 2 ) ) ) AS MaxPos
FROM t1 AS a
INNER JOIN COMDB_MODEL7 AS b ON a.snap = b.snap
GROUP BY id;
-------------------------------------------------------------