A Shape of the query
Three common table expressions prepare the die-based counts and the
coverage factor; the main SELECT then aggregates the stamps and joins those
counts back in. One output row is one findspot.
B Input and selection
FROM / JOIN — the three tables
FROM tbldistribution AS di LEFT JOIN tblpotter p ON lower(trim(di.pottername)) = lower(trim(p.pottername)) LEFT JOIN v_discoverysite vds ON di.site = vds.label LEFT JOIN kfactor k ON k.the_id = vds.id AND k.the_site = di.site AND k.the_findspot = di.findspot
tbldistribution supplies the stamp occurrences (site, findspot, potter, die).
tblpotter supplies each potter's production range
datemin …
datemax. v_discoverysite resolves the site name against the
published archaeology.link location dataset, giving each row the persistent
identifier the_id.
WHERE — which stamps count
WHERE di.isdate = 'Θ' -- occurrence usable for dating AND sitecharacter = 'Σ' -- settlement-type site AND findspot IS NOT NULL -- context resolvable AND p.datemin <> 0 -- potter carries a real date AND p.datemax NOT IN (260,120,150)
Stamps pass only if the occurrence is flagged as datable, the site is of
settlement character, the context is named, and the potter has a usable date.
The final line excludes potters whose end date is exactly 260, 120 or 150.
Note: because
p.datemin <> 0 is tested in the
WHERE clause, the left join to tblpotter behaves as an
inner join — stamps without a matching dated potter do not appear.
GROUP BY — the unit of analysis
GROUP BY vds.id, di.site, di.findspot, di.siteancientname,
di.coordinate1, di.coordinate2, di.pleiades
One row per findspot, not per site. A site with several excavated contexts
produces several rows, each dated on its own material.
C The die counts
Two CTEs, run before the main aggregation.
diecounts — dies within each potter
SELECT vds.id, di.site, di.findspot, di.pottername, COUNT(DISTINCT di.die) AS dies_pp, -- distinct dies of this potter COUNT(*) AS stamps_pp -- occurrences of this potter -- ... same WHERE as above, plus: AND di.die IS NOT NULL GROUP BY vds.id, di.site, di.findspot, di.pottername
Counting
DISTINCT die within each potter and summing afterwards
is what makes the die identity potter-bound. Stamps with no recorded die are
excluded here, because they cannot contribute to a repetition count.
kfactor — totals and the coverage factor
SELECT the_id, the_site, the_findspot, SUM(dies_pp) AS n_dies, ROUND(SUM(stamps_pp)::numeric / NULLIF(SUM(dies_pp),0), 3) AS rep, ( k_max - (k_max - k_min) * (1 - EXP(-SUM(stamps_pp)::numeric / tau)) ) AS k_eff FROM diecounts GROUP BY the_id, the_site, the_findspot
\[ D = \sum \text{dies\_pp} \qquad
n_{\text{die}} = \sum \text{stamps\_pp} \qquad
r = \frac{n_{\text{die}}}{D} \]
Since revision 30a this CTE no
longer feeds \(k\): it supplies \(D\) and \(r\) only. \(k\) is computed in the
main query from count_stamps.
n_dies is the number of distinct potter–die pairs, rep the mean number
of attestations per pair, and k_eff the coverage factor of section E.
D Central tendency
avg_datemin · avg_datemax · midpoint_year
AVG(p.datemin)::integer AS avg_datemin, AVG(p.datemax)::integer AS avg_datemax, ((AVG(p.datemin) + AVG(p.datemax)) / 2.0) AS midpoint_year
\[ \overline{a} = \frac{1}{n}\sum a_i \qquad
\overline{b} = \frac{1}{n}\sum b_i \qquad
m = \frac{\overline{a}+\overline{b}}{2} \]
m is the anchor the dated interval is built around. The averages are
reported for reference; they are not what the plot draws as the box.
Averaging is over stamp occurrences, so a potter attested forty times counts forty
times.
min_datemin … max_datemax
MIN(p.datemin)::integer AS min_datemin, MAX(p.datemin)::integer AS max_datemin, MIN(p.datemax)::integer AS min_datemax, MAX(p.datemax)::integer AS max_datemax
The outermost years any single potter at the findspot allows. Drawn as the short
stubs at either end of a row.
E Dispersion and the dated interval
Two sources of blur are added together: each potter's range is itself wide, and different potters point to different years.
σ — computed inline, not exposed as a column
SQRT( AVG(POWER(p.datemax - p.datemin, 2) / 12.0) + COALESCE(VAR_SAMP((p.datemin + p.datemax) / 2.0), 0) )
\[ \sigma = \sqrt{\;\overline{\left(\frac{(b_i-a_i)^2}{12}\right)}
\;+\; \operatorname{Var}\!\left(\frac{a_i+b_i}{2}\right)\;} \]
\((b-a)^2/12\) is the variance of a uniform distribution across a potter's range;
the second term is the sample variance of the range midpoints. The
COALESCE covers findspots with a single stamp, where no between-stamp
variance exists but the within-stamp width is still defined.
Small σ means the stamps point to a narrow window.
k_eff — coverage factor, from the assemblage size
k_max - (k_max - k_min) * (1 - EXP(-count_stamps / tau)) -- k_min = 0.5 k_max = 1.5 tau = 6 (calibrated, see the method page)
\[ k = k_{\max} - (k_{\max}-k_{\min})\left(1 - e^{-n/\tau}\right) \]
The more stamps a findspot yields, the tighter the interval that may reasonably be
claimed. k falls from 1.5 to 0.5 as material accumulates, saturating
exponentially: the difference between two and twelve stamps matters far more than
between eighty and ninety. Where no die is recorded, k falls back to
k_max.
eff_start · eff_end — the dated interval
( midpoint - COALESCE(MIN(k.k_eff), k_max) * σ )::numeric(10,1) AS eff_start, ( midpoint + COALESCE(MIN(k.k_eff), k_max) * σ )::numeric(10,1) AS eff_end
\[ \text{eff\_start} = m - k\sigma \qquad\qquad \text{eff\_end} = m + k\sigma \]
The virtual fuzzy year: the central region of the date distribution the
stamps imply, drawn as the box in the plot and exported as the findspot's date.
MIN(k.k_eff) merely passes through a value that is constant within the
group.
F The two quality measures
Reported side by side and never combined: they answer different questions, and a findspot may score high on one and low on the other.
q_interval — dating sharpness
ROUND(COALESCE( CASE WHEN (AVG(p.datemax) - AVG(p.datemin)) = 0 THEN NULL ELSE EXP(-(SQRT(VAR_SAMP(p.datemin) + VAR_SAMP(p.datemax)) / ABS(AVG(p.datemax) - AVG(p.datemin)))) END, 0.5), 3)
\[ q_{\text{interval}} = \exp\!\left(-\frac{\sqrt{s^2_a+s^2_b}}{\left|\overline{b}-\overline{a}\right|}\right) \]
Scatter measured in units of the interval's own length, so the result is
dimensionless and lies in \((0,1]\). Near 1: the potters point to nearly the
same years. Below 0.5: they scatter more widely than the interval they
define. Drives the box colour.
q_repetition · die_repetition — hoard character
MIN(k.n_dies) AS n_dies, MIN(k.rep) AS die_repetition, CASE WHEN MIN(k.rep) IS NULL THEN NULL ELSE ROUND(1 - 1.0/GREATEST(MIN(k.rep),1), 3) END AS q_repetition
\[ r = \frac{n_{\text{die}}}{D} \qquad\qquad q_{\text{repetition}} = 1 - \frac{1}{r} \]
When the same die recurs, the assemblage carries the signature of a closed group —
a consignment, hoard or single delivery — rather than ordinary accumulated
material. 0 means every die occurs once; above 0.6 indicates strong
repetition. NULL means no dies were recorded, which is not the same as zero.
q_start · q_end — endpoint sharpness, whisker colour
ROUND(EXP(-(STDDEV_SAMP(p.datemin) / (SELECT t0 FROM params))), 3) AS q_start -- q_end identical, on datemax. t0 = 20 years, a fixed reference length.
\[ q_{\text{start}} = \exp\!\left(-\frac{s_a}{t_0}\right) \qquad
q_{\text{end}} = \exp\!\left(-\frac{s_b}{t_0}\right) \]
Scatter of the start and end dates separately, read against a fixed length of
20 years rather than against the mean year. Until v27c the divisor was
\(|\overline{a}|\), which measured a findspot's quality by where it sat in the
calendar; with \(t_0\) the two measures no longer depend on epoch. Used only to
colour the whiskers.
G Values drawn but not modelled
unc_start_years · unc_end_years · unc_interval_years
COALESCE(STDDEV_SAMP(p.datemin)::integer, 0) AS unc_start_years, COALESCE(STDDEV_SAMP(p.datemax)::integer, 0) AS unc_end_years, COALESCE(SQRT(VAR_SAMP(p.datemin) + VAR_SAMP(p.datemax))::integer, 0) AS unc_interval_years
Whisker lengths. These use a different notion of spread from the \(\sigma\) of
section E and are graphical only — a cue that scatter is present. They are
not bounds of the dated interval.
avg_interval
ROUND(AVG(p.datemin),0)::text || ' to ' || ROUND(AVG(p.datemax),0)::text
A display string of \(\overline{a}\) to \(\overline{b}\), for labels.
H Parameters
| Name | Value | Effect |
|---|---|---|
k_min | 0.5 | narrowest interval, richly attested findspots |
k_max | 1.5 | widest interval, thinly attested findspots |
tau | 6 | assemblage size, in stamps, at which ~63 % of the narrowing is reached; calibrated against five ceramic-independent ensembles |
t0 | 20 | reference length, in years, against which the edge dispersions are read; a stated convention, not a calibrated value |
w | 1.0 | weight of volume against repetition in \(k\); 1.0 = volume only |
| within-stamp variance | \(w^2/12\) | uniform distribution across each potter's range |
tau and t0 are not the same quantity. One is
counted in stamps and drives the width of the box; the other is counted in years and
drives the whisker colours. They carried the same value until tau was
calibrated, and during development both were once set to 6 at the same time — every
check passed, every figure looked plausible, and every whisker colour in the corpus
was wrong. Both are exported on every row, as p_tau and p_t0,
so that any output can be checked against the parameters that produced it.
All named parameters live in the params CTE at the top of the query
and are referenced from there, so a change takes effect in one place.
I Output columns
| Column | Formula | Used for |
|---|---|---|
| the_id | — | archaeology.link identifier |
| the_site · the_findspot | — | grouping unit |
| count_stamps | \(n\) | qualifying stamps at the findspot |
| avg_datemin · avg_datemax | \(\overline{a}\), \(\overline{b}\) | reference |
| min_datemin … max_datemax | extremes | plot stubs |
| midpoint_year | \(m\) | centre of the interval |
| eff_start · eff_end | \(m \pm k\sigma\) | the date · plot box |
| q_interval | \(e^{-\sqrt{s^2_a+s^2_b}/|\overline{b}-\overline{a}|}\) | dating sharpness · box colour |
| n_dies | \(D\) | distinct potter–die pairs |
| die_repetition | \(r = n_{\text{die}}/D\) | attestations per die |
| q_repetition | \(1 - 1/r\) | hoard character |
| q_start · q_end | \(e^{-s/t_0}\) | whisker colour |
| unc_*_years | \(s_a\), \(s_b\), \(\sqrt{s^2_a+s^2_b}\) | whisker lengths, graphical only |
| avg_interval | — | display string |