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 di.sitecharacter = 'Σ' -- settlement-type site AND findspot IS NOT NULL -- context resolvable AND (p.datemin, p.datemax) NOT IN ( -- eleven placeholder datings (-30,150), (0,100), (0,120), (0,130), (0,150), (0,180), (0,270), (100,200), (150,270), (160,260), (165,270) ) AND ( di.site NOT ILIKE '%Bregenz%' OR btrim(di.findspot) IN ('Böckleareal (period I)', 'Böckleareal (period II)', 'Böckleareal (destruction layer period II)', 'Samian Hoard 1913') )
Stamps pass only if the occurrence is flagged as datable, the site is of
settlement character and the context is named. The pair list excludes eleven
placeholder datings — spans such as 0–100 or 160–260 that stand for
"date not established" rather than for a production period. It matches complete
(datemin, datemax) pairs: until revision 30a the filter read
p.datemax NOT IN (260,120,150) and excluded on the end date alone.
The last clause admits the four checked contexts at Bregenz — the three
Böckleareal complexes and, since revision 31, the Samian Hoard of 1913 — and no
other record of that site. Θ, Σ and the umlaut are U&-escapes in
the statement itself; they are spelled out here for legibility.
Note: the left join to tblpotter still behaves as an inner
join, but now by NULL propagation: where the join fails, both dates
are NULL, the row comparison against the pair list evaluates to
NULL rather than to true, and the stamp is dropped.
Where the eleven pairs come from. A companion query,
sql/wide_potters.sql, lists every potter dated across 100 years or
more that is neither in the pair list nor in a second list of spans checked and
deliberately kept — genuinely long-lived potters, of which there are currently
eight. An empty result means nothing new has appeared since the last review; a
row means somebody has to decide which of the two lists it belongs in. The query
filters nothing itself, which is why it sits beside the model statement rather
than inside it.
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. n is
count_stamps, every qualifying
stamp at the findspot. Until revision 30a it was the subset carrying a die
attribution, and k fell back to k_max wherever no die was recorded —
widening the interval for a reason having nothing to do with the material.
COUNT(*) cannot be NULL, so no fallback remains.
eff_start · eff_end — the dated interval
( midpoint - k_eff * σ )::numeric(10,1) AS eff_start, ( midpoint + k_eff * σ )::numeric(10,1) AS eff_end -- k_eff and σ are written out in full in the statement; the CTE value -- is no longer read here, and there is no COALESCE to k_max.
\[ \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}{\max(r,\,1)} \]
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 ceramic-independent reference ensembles, of which only Pompeii binds the value — method page §7a |
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/\max(r,1)\) | 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 |
| n_stamps_die | \(n_{\text{die}}\) | stamps carrying a die; descriptive since 30a |
| k_eff · sigma_eff | \(k\), \(\sigma\) | the two factors of the half-width |
| k_no_dierecord | — | true where no die is recorded at all; no effect on the interval |
| p_k_min … p_t0 | — | the five model parameters, carried as provenance |
| n_stamps_wide · n_potters_wide · max_potter_span | — | watchdogs for potters dated across 100 years or more |