5.1 : Generic Queries

On this page we document the demo queries available behind the corresponding buttons on the main query page. All these demo queries address the various tables in the millimil database. The following list gives quick links to the corresponding query.

H1

Find halos at a given redshift (snapnum) within a certain part of the simulation volume (X,Y,Z).

select *
  from millimil..MPAHalo
 where snapnum=50 
   and np between 100 and 1000 
   and x between 10 and 20
   and y between 10 and 20
   and z between 10 and 20

H2

Find the whole progenitor tree, in depth-first order, of a halo identified by its haloId

select PROG.*
  from millimil..MPAHalo PROG,
       millimil..MPAHalo DES
 where DES.haloId = 1
   and PROG.haloId between DES.haloId and DES.lastprogenitorId

H3

Find the progenitors at a given redshift (snapnum) of all halos of mass(np)≥4000 at a later redshift (snapnum). The progenitors are limited in mass as well.

select DES.haloId as descendant_id,
       DES.np as descendant_mass,
       PROG.*
  from millimil..MPAHalo DES,
       millimil..MPAHalo PROG
 where DES.snapnum = 63
   and DES.np > 4000
   and PROG.haloId between DES.haloId and DES.lastprogenitorId
   and PROG.snapnum = 30
   and PROG.np > 100
 order by DES.np desc, PROG.np desc

H4

Find all the halos of mass ≥ 1000 that have just had a major merger, defined by having at least two progenitors of mass ≥ 0.2*descendant mass. Note that this uses the mass of the sub-halos just prior to merging. In general the minor partner in a merger will have been stripped of part of its mass prior tpo the actual merger, so this mass ratio may not properly reflect the actual ratio when the merger commenced. See paper by Bundy, Ellis & Treu (2006, ads) .

select D.haloId,
       D.snapnum,
       D.np as d_np,
       P1.np as p1_np,
       P2.np as p2_np
  from millimil..MPAHalo P1,
       millimil..MPAHalo P2,
       millimil..MPAHalo D
 where P1.SNAPNUM=P2.SNAPNUM
   and P1.haloId < P2.haloId
   and P1.descendantId = D.haloId
   and P2.descendantId = D.haloId
   and P1.np >= .2*D.np
   and P2.np >= .2*D.np
   and D.np > 1000

H5

Find the mass function of halos at z=0 using logarithmic intervals.
Note the use of the

group by [expression]

statement. This groups all rows in the possible result of the

SELECT .. FROM .. WHERE ..

that have the same value for the [expression] and for each group calculates an aggregate function, here the count(*).
NOTE this is not exactly equivalent to a histogram, as only bins for which there is at least one row before the binning will be returned. I.e. when using the result of a query like this in a diagram one should beware of possibly missing bins, and not simply "connect the dots"!

 select power(10, .1*(.5+floor(log10(np)/.1))) as mass,
        count(*) as num
   from millimil..MPAHalo
  where snapnum=63
 group by power(10, .1*(.5+floor(log10(np)/.1)))
 order by mass

HF1

Find all halos residing in background overdensities between 2 and 3, at Gaussian smoothing radius 5 Mpc/h.

select h.*
  from millimil..MPAHalo h,
       millimil..MMField f
 where f.g5 between 2 and 3
   and f.snapnum=63
   and f.snapnum = h.snapnum
   and f.phkey = h.phkey

HF2

This query calculates conditional multiplicity functions of halos in two different environments defined by different values for the dark matter desntiy after Gaussian smoothing of 5/h Mpc. The query consists of two nearly identical parts combined using a union. A union simply adds the rows coming from both parts together in one result. Note that to identify from which of these two a row in the result is returned, a special column (here called "lim") is added which has value 0 for the first part, 1 for the second. We assigne these values in a slightly cumbersome way, namely as 0*haloId and 0*haloid+1 respecitvley. This is due to a "feature" of T-SQL which does not allow "group by" on literals. The multiplicity functions themselves are determined in a way that is equivalent to the example in H5. See there also for a note on missing bins.

select 0*haloId as lim,  
        power(10, .1*(.5+floor(log10(h.np)/.1))) as mass, 
        count(*) as num 
   from millimil..MMHalo h,  
        millimil..MMField f 
  where f.g5 between 3 and 5 
    and f.snapnum=63 
    and f.snapnum = h.snapnum 
    and f.phkey = h.phkey     
  group by 0*haloId, power(10, .1*(.5+floor(log10(h.np)/.1))) 
 union 
  select 0*haloId+1 as lim, 
         power(10, .1*(.5+floor(log10(h.np)/.1))) as mass, 
         count(*) as num 
   from millimil..MMHalo h,  
        millimil..MMField f 
  where f.g5 between .2 and .4 
    and f.snapnum=63 
    and f.snapnum = h.snapnum 
    and f.phkey = h.phkey     
  group by 0*haloId+1, power(10, .1*(.5+floor(log10(h.np)/.1))) 
 order by lim,mass

HF3

Find formation time dependency on background overdensities for halos in particular mass bin.

select zmax, avg(g5) as g5, stdev(g5) as g5err,
       avg(g10) as g10, stdev(g10) as g10err,
       count(*) as num
  from millimil..mmfield f, 
       ( select des.haloId, des.np, des.phkey,max(PROG.redshift) as zmax
           from millimil..MPAHalo PROG,
                millimil..MPAHalo DES
          where DES.snapnum = 63
            and PROG.haloId between DES.haloId and DES.lastprogenitorId
            and prog.np >= des.np/2    
            and des.np between 100 and 200
            and des.haloId = des.firsthaloinfofgroupid
          group by des.haloId, des.np ,des.phkey    
       ) t
 where t.phkey = f.phkey 
   and f.snapnum=63
group by zmax

G1

Find galaxies at a given redshift (snapnum) within a certain part of the simulation volume (X,Y,Z)

select *
  from millimil..DeLucia2006a
 where snapnum=63
   and mag_b between -26 and -18 
   and x between 10 and 20
   and y between 10 and 20
   and z between 10 and 20

G2

Find the whole progenitor tree, in depth-first order, of a galaxy identified by its galaxyId

select PROG.*
  from millimil..DeLucia2006a PROG,
       millimil..DeLucia2006a DES
 where DES.galaxyId = 1
   and PROG.galaxyId between DES.galaxyId and DES.lastprogenitorId

G3

Find the progenitors at a given redshift (snapnum) of all galaxies of brightness(magB) ≤ -20 at a later redshift (snapnum). The progenitors are limited in magnitude as well.

select DES.galaxyId as descendant_id,
       DES.stellarMass as descendant_mass,
       PROG.*
  from millimil..DeLucia2006a DES,
       millimil..DeLucia2006a PROG
 where DES.snapnum = 63
   and DES.mag_b < -20
   and PROG.galaxyId between DES.galaxyId and DES.lastprogenitorId
   and PROG.snapnum = 30
   and PROG.mag_b < -19
 order by DES.mag_b asc, PROG.mag_b asc

G4

Find all the galaxies of mag_b < -20 that have just had a major merger, defined by having at least two progenitors of mass ≥ 0.2*descendant mass.

select D.galaxyId,
       D.snapnum,
       D.mag_b as d_mag_b,
       D.sfr as d_sfr,
       P1.mag_b as p1_mag_b,
       P2.mag_b as p2_mag_b,
       D.stellarMass as d_mass,
       P1.stellarMass as p1_mass,
       P2.stellarMass as p2_mass
  from millimil..DeLucia2006a P1,
       millimil..DeLucia2006a P2,
       millimil..DeLucia2006a D
 where P1.SNAPNUM=P2.SNAPNUM
   and P1.galaxyId< P2.galaxyId
   and P1.descendantId = D.galaxyId
   and P2.descendantId = D.galaxyId
   and P1.stellarMass >= .2*D.stellarMass
   and P2.stellarMass >= .2*D.stellarMass
   and D.mag_b <-20

G5

Find the luminosity function of galaxies at z=0. Note the comment for query H5 regarding missing bins.

select .2*(.5+floor(mag_b/.2)) as mag,
       count(*) as num
  from millimil..DeLucia2006a
 where mag_b < -10
   and snapnum=63
 group by .2*(.5+floor(mag_b/.2))
 order by mag

G6

Find the Tully-Fisher relation, Mag vs Vvir for galaxies with bulge/total mass ratio < 0.1.

select vVir, mag_b, mag_v, mag_i, mag_r, mag_k
  from millimil..DeLucia2006a
 where (bulgeMass < 0.1*stellarMass or bulgeMass is null)
   and snapnum = 41

HG1

Find the conditional luminosity functions for galaxies in two ranges of halo masses. Note the comment for query H5 regarding missing bins.

select 0*galaxyId as lim, 
       .2*(.5+floor(mag_b/.2)) as mag,
       count(*) as num
  from millimil..DeLucia2006a g, 
       millimil..MPAHalo h
 where h.np between 2000 and 3000
   and h.snapnum=63
   and g.haloId = h.haloId
   and g.mag_b < 0
 group by 0*galaxyId, .2*(.5+floor(mag_b/.2))
union
select 0*galaxyId+1 as lim, 
       .2*(.5+floor(mag_b/.2)) as mag,
       count(*) as num
  from millimil..DeLucia2006a g, 
       millimil..MPAHalo h
 where h.np between 200 and 300
   and h.snapnum= 63
   and g.haloId = h.haloId
   and g.mag_b < 0
 group by 0*galaxyId+1, .2*(.5+floor(mag_b/.2))
order by lim,  mag

HG2

Find average galaxy properties as function of halo mass.

select pow(10, .1*(.5+floor(log(g.np)/.1)))::real as halo_np,
       avg(g.stellarMass) as stars_avg,
       max(g.stellarMass) as stars_max,
       avg(g.bulgeMass) as bulge_avg,
       max(g.bulgeMass) as bulge_max,
       avg(g.mag_b-g.mag_v) as color_avg
  from millimil..DeLucia2006a g
 where g.snapnum= 63 
   and g.mag_b < 0
 group by halo_np
 order by halo_np

GF1

Find galaxy luminosity functions in overdensities at two different values.

select 0*galaxyId as lim, 
       .2*(.5+floor(mag_b/.2)) as mag,
       count(*) as num
  from millimil..DeLucia2006a g, 
       millimil..MMField f
 where f.g5 between 3 and 5
   and f.snapnum=63
   and f.snapnum = g.snapnum
   and f.phkey = g.phkey    
   and g.mag_b < 0 
 group by 0*galaxyId, .2*(.5+floor(mag_b/.2))
union
 select 0*galaxyId+1 as lim,
        .2*(.5+floor(mag_b/.2)) as mag,
        count(*) as num
  from millimil..DeLucia2006a g, 
       millimil..MMField f
 where f.g5 between .2 and .4
   and f.snapnum=63
   and f.snapnum = g.snapnum
   and f.phkey = g.phkey    
   and g.mag_b < 0 
 group by 0*galaxyId+1, .2*(.5+floor(mag_b/.2))
order by lim,  mag