← Documentation / SQL Reference

DOI 1.4 — SQL corrections and two sigma_stat approaches

View DOI 1.4 on Zenodo: https://doi.org/10.5281/zenodo.18994113

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.

DOI1_4_sql.txt

The corrected queries themselves: dataset/model counts, Δssim spread by μ_gt bin, improvement/degradation rate by μ_gt bin, the noTTA→TTA L/C/S component shift for σ_gt=0 blocks, and the S-term spread by σ_gt bin — the raw material behind this report's two-stage μ_stat computation.

This file is the literal, unedited script behind this report's screen-recorded video — eight queries run in sequence against both Upscayl Standard and SwinIR's databases.

.open --readonly alignment_axis_images.duckdb

DESCRIBE blocks;

SELECT COUNT(*) FROM blocks;

SELECT model, COUNT(*) FROM blocks GROUP BY model;

DESCRIBE blocks lists every column and its type — a quick sanity check on the schema before querying it. The next two confirm the database's size and that it holds both notta and tta rows per model (see Glossary).

SELECT
round(mu_gt, 2) AS mu_bin,
STDDEV(ABS(delta_ssim)) AS stdev_abs_delta,
COUNT(*) AS cnt
FROM blocks
WHERE is_comp_zero = TRUE
AND delta_ssim != 0
GROUP BY mu_bin
HAVING COUNT(*) > 20
ORDER BY mu_bin;

This is what later reports call the phase-transition curve: grouping blocks into 0.01-wide μ_gt bins and computing how spread out |Δssim| is within each bin. WHERE is_comp_zero = TRUE AND delta_ssim != 0 restricts to what this series calls the "Merutan condition" (see is_comp_zero in the Glossary). HAVING COUNT(*) > 20 drops bins with too few blocks to be statistically meaningful.

SELECT
round(mu_gt, 2) AS mu_bin,
AVG(ABS(delta_ssim)) AS avg_abs_delta,
STDDEV(ABS(delta_ssim)) AS stdev_abs_delta,
SUM(CASE WHEN delta_ssim > 0 THEN 1.0 ELSE 0.0 END)
/ COUNT(*) * 100 AS improvement_rate,
SUM(CASE WHEN delta_ssim < 0 THEN 1.0 ELSE 0.0 END)
/ COUNT(*) * 100 AS degradation_rate,
COUNT(*) AS count
FROM blocks
WHERE is_comp_zero = TRUE AND delta_ssim != 0
GROUP BY mu_bin
HAVING COUNT(*) > 20
ORDER BY mu_bin;

Same bins as above, with two more columns: what share of blocks in that bin had TTA improve the score (delta_ssim > 0) versus make it worse (delta_ssim < 0), using the SUM(CASE WHEN ...) / COUNT(*) * 100 pattern from How to read SQL.

WITH paired AS (
SELECT
n.ssim_l AS ssim_l_notta, n.ssim_c AS ssim_c_notta, n.ssim_s AS ssim_s_notta,
t.ssim_l AS ssim_l_tta, t.ssim_c AS ssim_c_tta, t.ssim_s AS ssim_s_tta,
n.delta_ssim, n.mu_gt, n.is_comp_zero
FROM blocks n
JOIN blocks t
ON n.image_id = t.image_id AND n.col = t.col AND n.row = t.row
WHERE n.model = 'notta' AND t.model = 'tta'
)
SELECT
SIGN(delta_ssim) AS sign,
AVG(ssim_l_tta - ssim_l_notta) AS d_l,
AVG(ssim_c_tta - ssim_c_notta) AS d_c,
AVG(ssim_s_tta - ssim_s_notta) AS d_s
FROM paired
WHERE is_comp_zero = TRUE AND mu_gt < 0.07
GROUP BY SIGN(delta_ssim)
ORDER BY sign;

A self-JOIN: the blocks table is joined to itself (aliased n for noTTA rows, t for TTA rows), matching each block's noTTA row to its own TTA row by (image_id, row, col). Once paired up, near-black blocks (μ_gt < 0.07) are grouped by whether their overall score improved or got worse (SIGN(delta_ssim)), and the average noTTA→TTA change is measured separately for each of SSIM's L, C, and S components — pinning down which component actually drove the change, not just the net score.

SELECT
round(sigma_gt, 3) AS sigma_bin,
AVG(ssim_s) AS avg_s,
STDDEV(ssim_s) AS stdev_s,
COUNT(*) AS count
FROM blocks
WHERE is_comp_zero = FALSE AND sigma_gt > 0
GROUP BY sigma_bin
HAVING COUNT(*) > 10
ORDER BY sigma_bin;

The texture-axis counterpart of the phase-transition query above: 0.001-wide σ_gt bins instead of μ_gt, tracking the structure term S instead of Δssim. This series later calls this "Approach A" for computing σ_stat.

SELECT
round(mu_gt, 2) AS mu_bin,
STDDEV(delta_ssim) AS stdev_delta,
COUNT(*) AS count
FROM blocks
WHERE is_comp_zero = TRUE AND delta_ssim != 0
GROUP BY mu_bin
HAVING COUNT(*) > 20
ORDER BY mu_bin;

Nearly identical to the first phase-transition query above, but measures the spread of the signed Δssim directly instead of its absolute value — a second look at the same curve, to see whether the direction of change (not just its size) shows the same pattern.