wiki:db/Queries/testingSharedScans
Last modified 5 years ago Last modified on 09/16/2013 09:15:17 AM

Queries for testing shared scans

These queries are compatible with pt1.1 data set (rplante_DC3b_u_pt11final), which has:

  • 4,012,341 objects
  • 164,989,583 sources
##### full object scan, simple cut
SELECT objectId, ra_PS, decl_PS, uFlux_PS, gFlux_PS, 
       rFlux_PS, iFlux_PS, zFlux_PS, yFlux_PS 
FROM   Object 
WHERE  uFlux_PS between ...

# 1.99e-29 and 1.99001e-29 (    5 rows)
# 1.99e-29 and 1.99100e-29 (  624 rows)
# 1.99e-29 and 1.99900e-29 ( 5426 rows)
# 1.99e-29 and 2.10000e-29 (60827 rows)


##### full object scan, medium-complexity cut, 
##### similar to q020
SELECT 
  ROUND(scisql_fluxToAbMag(uFlux_PS)-scisql_fluxToAbMag(gFlux_PS), 0) AS UG,
  ROUND(scisql_fluxToAbMag(gFlux_PS)-scisql_fluxToAbMag(rFlux_PS), 0) AS GR,
  ROUND(scisql_fluxToAbMag(rFlux_PS)-scisql_fluxToAbMag(iFlux_PS), 0) AS RI,
  ROUND(scisql_fluxToAbMag(iFlux_PS)-scisql_fluxToAbMag(zFlux_PS), 0) AS IZ,
  ROUND(scisql_fluxToAbMag(zFlux_PS)-scisql_fluxToAbMag(yFlux_PS), 0) AS ZY
FROM Object
WHERE
     scisql_fluxToAbMag(gFlux_PS) < 0.2
 AND scisql_fluxToAbMag(uFlux_PS)-scisql_fluxToAbMag(gFlux_PS) >=-0.27
 AND scisql_fluxToAbMag(gFlux_PS)-scisql_fluxToAbMag(rFlux_PS) >=-0.24 
 AND scisql_fluxToAbMag(rFlux_PS)-scisql_fluxToAbMag(iFlux_PS) >=-0.27 
 AND scisql_fluxToAbMag(iFlux_PS)-scisql_fluxToAbMag(zFlux_PS) >=-0.35
 AND scisql_fluxToAbMag(zFlux_PS)-scisql_fluxToAbMag(yFlux_PS) >=-0.40;


##### full object scan with group by
same as the above add: GROUP BY UG, GR, RI, IZ, ZY


##### full object scan with simple aggregation
SELECT  MIN(ra_PS), MAX(ra_PS), MIN(decl_PS), MAX(decl_PS)
FROM    Object;


##### large area (500 sq deg) object scan with simple aggregation
SELECT  AVG(parallax)
FROM    Object
WHERE   qserv_areaspec_box(-3,7,10, 60)


##### full object scan, density per declination zone
SELECT  ROUND(decl_PS, 0) AS declRounded, COUNT(*) AS theCount 
FROM     Object 
GROUP BY declRounded 
ORDER BY theCount;


##### near neighbor, 100 sqdeg
SELECT   COUNT(*) 
FROM     Object o1, Object o2 
WHERE    qserv_areaspec_box(-5,-5, 5, 5) 
  AND    qserv_angSep(o1.ra_PS, o1.decl_PS, o2.ra_PS, o2.decl_PS) < 0.1;


##### full Source scan, with some aggregation per objectId
SELECT   o.objectId, o.gNumObs, ROUND(AVG(psfFlux), 1)
  FROM   Source s, Object o 
  WHERE  o.objectId = s.objectId
    AND  o.gNumObs > 1
GROUP BY s.objectId;