5.3  :  Boylan-Kolchin etal 2009

We here present SQL queries that produce results that are equivalent to those used to produce various figures in Boylan-Kolchin et al 2009.

Figure 4 The figure compares conditional mass functions for subhalos as function of the mass of the FOF group they belong to. We use the user's MyDB to store temporary results. The first query counts subhalos grouped in mass bins that reside in FOF groups also already grouped in bins. I.e. it calculates all conditional mass functions for all desired FOF mass ranges in one query. We use logarithmic bins and use the number of particles multiplied by the mass per particle as estimate of the mass. We make use of the structure we have imposed on the identifiers for sub-halos and FOF groups as described in [TODO add link]. Note that we count subhalos that are not the central (first) subhalo.
select .5*floor(log10(f.np*6.885e-4)/.5) as mfof
,      .1*floor(log10(s.np*6.885e-4)/.1) as msh
,      count(*) as num
  into fullmassfun_67
  from millenniumii..fof f
  ,    millenniumii..subhalo s
 where f.snapnum=67
   and s.subhaloid between 1000000*f.fofid+1 and (f.fofid+1)*1000000-1
 group by .5*floor(log10(f.np*6.885e-4)/.5)
 ,        .1*floor(log10(s.np*6.885e-4)/.1)
We use this table to calculate a cumulative mass function.
select f1.mfof, f1.msh, sum(f2.num) as numcum
  into cummassfun_67
  from fullmassfun_67 f1
  ,    fullmassfun_67 f2
 where f1.mfof=f2.mfof
   and f2.msh >= f1.msh
group by f1.mfof, f1.msh
Figure 4 in the paper shows the normalised result. The numbers in the cumulative mass function for a given mass range of the FOF groups must be divided by the number of FOF groups in that bin. To do so we first must calculate those bins:
select .5*floor(log10(np*6.885e-4)/.5) as mfof
,      count(*) as num
  into fofmassfun_nsubsgt1_67
  from millenniumii..fof
 where snapnum=67
   and numsubs > 1
 group by .5*floor(log10(np*6.885e-4)/.5)
The result finally is obtained form the following query:
select cf.mfof, cf.msh, cast(cf.numcum as real)/f.num as frac, f.num
into cummassfunnorm_67
  from fofmassfun_nsubsgt1_67 f
  ,    cummassfun_67 cf
 where f.mfof=cf.mfof