← Documentation / SQL Reference

DOI 1.6 — Universal Tier3 classification coverage, with a DOI 1.0–1.6 research overview

View DOI 1.6 on Zenodo: https://doi.org/10.5281/zenodo.19058174

Old DBBuilt on the earlier, JOIN-based pipeline. Not every model was affected by its data-integrity bug — Standard and High-Fidelity needed correction, SwinIR and Digital-Art were confirmed unaffected. See Superseded Files.

DOI_1_6sql.txt

Three independent completeness checks: the overall Tier3 state distribution, a noTTA×TTA cross-tabulation, and a diagnostic query isolating the small number of NULL-state rows to show what data they're actually missing.

tier3_state is this project's four-way collapse classification per block (Stable / Persistent / Escape / BlindSpot), pre-computed and stored as a column; these queries check its coverage rather than deriving it.

SELECT COUNT(*) FROM blocks;

The total block count, for context on the counts below.

SELECT
    tier3_state,
    COUNT(*) AS cnt,
    ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS pct
FROM blocks
GROUP BY tier3_state
ORDER BY cnt DESC;

The overall distribution across the four states. SUM(COUNT(*)) OVER () is a window function (see How to read SQL): it adds up the per-group counts from the GROUP BY above into a single grand total, but — because of OVER () — still attaches that total to every output row, so each state's percentage of the whole can be computed in the same query instead of a second pass.

SELECT
  tier3_noTTA,
  tier3_TTA,
  delta_tier3,
  tier3_state,
  COUNT(*) AS cnt
FROM blocks
GROUP BY tier3_noTTA, tier3_TTA, delta_tier3, tier3_state
ORDER BY cnt DESC;

Groups by all four pre-computed columns at once, showing every combination of noTTA/TTA sub-state and the derived tier3_state that actually occurs in the data, and how many blocks fall into each — a direct look at how the four-way classification is built from its inputs.

SELECT
  COUNT(*) AS total_null_state,
  COUNT(delta_ssim)    AS has_delta_ssim,
  COUNT(pc1_noTTA)     AS has_pc1_noTTA,
  COUNT(local_ssim_noTTA) AS has_ssim_noTTA
FROM blocks
WHERE tier3_state IS NULL;

Restricted to the rare rows where tier3_state is missing. COUNT(column) (unlike COUNT(*)) only counts rows where that column is not null — so comparing has_delta_ssim/has_pc1_noTTA/has_ssim_noTTA against total_null_state shows whether these NULL-state blocks are missing their classification because of a bug, or because their other input values are missing too (padding/reserved regions with no valid data at all).