天文データセンター SQL講習会 資料[回答例]

山内＠天文データセンター

2011年8月 初版

課題1

1. SELECT objID, objName, ra, dec, flux_09, flux_18
   FROM IrcObjAll
   LIMIT 20
   

2. SELECT objID, objName, ra as lon, dec as lat, flux_09, flux_18
   FROM IrcObjAll
   LIMIT 20
   

課題2

1. SELECT count(*)
   FROM IrcObjAll
   WHERE (flux_09 BETWEEN 17.5 AND 18.0)
         AND fQual_09 = 3
         AND fQual_18 = 3
   

   120件．

2. SELECT *
   FROM IrcObjAll
   WHERE (flux_09 BETWEEN 17.5 AND 18.0)
         AND fQual_09 = 3
         AND fQual_18 = 3
   

課題3

1. SELECT objid, 
          flux_09, fjy2abmag(flux_09) AS mag_09
   FROM ircobjall
   WHERE flux_09 < fabmag2jy(12.2)
   ORDER BY flux_09
   

2. SELECT objid, 
          flux_09, fjy2abmag(flux_09) AS mag_09
   FROM ircobjall
   WHERE flux_09 < fabmag2jy(12.2)
   ORDER BY random()
   LIMIT 100
   

課題4

1. SELECT *
   FROM (
    SELECT objid, 
           flux_09, fjy2abmag(flux_09) AS mag_09,
           nScanC_09
    FROM ircobjall
    WHERE flux_09 < fabmag2jy(12.2)
   ) o
   WHERE 6 <= o.nScanC_09
   

課題5

1. SELECT o.objid, o.objname, o.ra, o.dec,
          o.flux_09, o.flux_18,
          s.*
   FROM IrcObjAll o
   LEFT JOIN SimbadIrcAll s
   ON o.objid = s.objid
   LIMIT 30
   

課題6

1. SELECT type, count(type) as cnt_t,
          specType, count(specType) as cnt_s,
          orderDistance
   FROM SimbadIrcAll
   GROUP BY type, specType, orderDistance
   HAVING (specType IS NOT NULL) AND (orderDistance = 1)
   ORDER By type
   LIMIT 100
   

2. SELECT nScanP_09, count(nScanP_09)
   FROM IrcObjAll
   GROUP BY nScanP_09
   ORDER BY nScanP_09
   

3. SELECT floor(posErrMj*10.0)/10.0 as posErr, count(*)
   FROM IrcObjAll
   GROUP BY floor(posErrMj*10.0)/10.0
   ORDER BY posErr
   

課題7

1. SELECT objid, objname, ra, dec, cx, cy, cz
   FROM IrcObjAll
   WHERE
    (cx BETWEEN
     -0.982513866380272-0.02 AND -0.982513866380272+0.02)
    AND
    (cy BETWEEN
     -0.148249882182357-0.02 AND -0.148249882182357+0.02)
    AND
    (cz BETWEEN
     0.112643130298329-0.02 AND 0.112643130298329+0.02)
   

2. SELECT *
   FROM
   (
    SELECT objid, objname, ra, dec, cx, cy, cz
    FROM IrcObjAll
    WHERE
     (cx BETWEEN
      -0.982513866380272-0.02 AND -0.982513866380272+0.02)
     AND
     (cy BETWEEN
      -0.148249882182357-0.02 AND -0.148249882182357+0.02)
     AND
     (cz BETWEEN
      0.112643130298329-0.02 AND 0.112643130298329+0.02)
   ) o
   WHERE 
    acos( (2.0 - ((o.cx-(-0.982513866380272))^2 +
                  (o.cy-(-0.148249882182357))^2 +
                  (o.cz-0.112643130298329)^2) ) / 2.0 )
    <= 0.02
   

課題8

1. SELECT objid,ra,dec, 
          fGetNearestObjIDEq('irc', ra, dec, 1.0) as objid_irc
   FROM FisObjAll
   LIMIT 100
   

2. SELECT p.objid, p.ra, p.dec,
          q1.objid, q1.ra, q1.dec,
          fDistanceArcminEq(p.ra,p.dec,q1.ra,q1.dec) as distance
   FROM FisObjAll p
   JOIN IrcObjAll q1
   ON fGetNearestObjIDEq('irc', p.ra, p.dec, 1.0) = q1.objID
   LIMIT 100
   

3. SELECT p.objid, p.ra, p.dec,
          q1.objid, q1.ra, q1.dec,
          fDistanceArcminEq(p.ra,p.dec,q1.ra,q1.dec) as distance1,
          q2.objid, q2.ra, q2.dec,
          fDistanceArcminEq(p.ra,p.dec,q2.ra,q2.dec) as distance2
   FROM FisObjAll p
   JOIN IrcObjAll q1
   ON fGetNearestObjIDEq('irc', p.ra, p.dec, 1.0) = q1.objID
   JOIN Iras q2
   ON fGetNearestObjIDEq('iras', p.ra, p.dec, 1.0) = q2.objID
   LIMIT 100
   

課題9

1. SELECT o.*, f.distance
   FROM twomass o,
        fGetNearbyObjCel('twomass', 'j2000',
                         162.991850, 03.792003, 5.0) f
   WHERE o.objid = f.objid
   ORDER BY f.distance
   

2. SELECT *
   FROM (
    SELECT o.*, f.distance
    FROM twomass o,
         fGetNearbyObjCel('twomass', 'j2000',
                          162.991850, 03.792003, 5.0) f
    WHERE o.objid = f.objid
    ORDER BY f.distance
   ) s
   WHERE s.dist_opt IS NOT NULL
   

課題10

1. SELECT fGetNearestObjIDEq('twomass', 360.0*random(), degrees(asin(2.0*random()-1.0)), 3.0) AS rid
   FROM generate_series(1,100)
   GROUP BY rid
   

   注意: Decの引数は，asin() を使って分布を調整する必要があります． 次の図は，4096個の座標をランダムに発生させた例です． asin()を使うと偏りなくサンプルを取得する事ができます．
   
2. 例1:

   SELECT o.*
   FROM twomass o
   WHERE EXISTS 
   (
     SELECT *
     FROM
     (
      SELECT fGetNearestObjIDEq('twomass', 360.0*random(), degrees(asin(2.0*random()-1.0)), 3.0) AS rid
      FROM generate_series(1,100)
     ) s
     WHERE s.rid = o.objid
   )
   

   例2:

   SELECT o.*
   FROM 
    (
     SELECT fGetNearestObjIDEq('twomass', 360.0*random(), degrees(asin(2.0*random()-1.0)), 3.0) AS rid
     FROM generate_series(1,100)
     GROUP BY rid
    ) s,
    twomass o
   WHERE s.rid = o.objid
   

課題11

1. 省略．

2.   $cmd = "SELECT o.* FROM ircobjall o, " .
            "fGetNearbyObjEq('irc',@ra@,@dec@,1.49 * 60) f " .
            "WHERE o.objid = f.objid";