Machine Learning from Formulas to MariaDB SQL

FORMULA-FIRST LAB

MariaDB brings the lesson closer to a real web system

DuckDB suits isolated analytics; this edition exposes a client-server engine and dialect while preserving mathematical meaning.

01 · FORMULA FIRST

k-Nearest Neighbors

d(x,q)=√Σ(xᵢ−qᵢ)² ; ŷ = mode{yᵢ | i ∈ Nₖ(q)}

Distance is evaluated at prediction time; k controls sensitivity versus smoothness.

xᵢ−qᵢdifference

POW(...,2)squared term

ORDER BY distancenearest set

GROUP BY classmajority vote

-- SQL appears after execution.

—

02 · FORMULA FIRST

Naive Bayes

log P(c|x) ∝ log P(c) + Σⱼ log P(xⱼ|c)

Conditional independence simplifies evidence; logs and smoothing make it numerically robust.

COUNT by classprior

conditional COUNTlikelihood

+1/(n+k)Laplace smoothing

SUM(LOG)stable product

-- SQL appears after execution.

—

03 · FORMULA FIRST

Simple Linear Regression

β₁=Σ(x−x̄)(y−ȳ)/Σ(x−x̄)² ; β₀=ȳ−β₁x̄

Slope is expected change, not causal proof; aggregates become model coefficients.

AVG(x), AVG(y)centres

SUM(x*y)cross-product

SUM(x*x)variation

β₀+β₁xprediction

-- SQL appears after execution.

—

04 · FORMULA FIRST

k-means · one iteration

zᵢ=argminₖ ||xᵢ−μₖ||² ; μₖ=mean{xᵢ:zᵢ=k}

One round assigns and updates; production repeats until declared convergence.

CROSS JOINall pairs

distance²assignment cost

ROW_NUMBERarg min

AVG by clusternew centroid

-- SQL appears after execution.

—

05 · FORMULA FIRST

Z-score Anomaly

z=(x−μ)/σ ; flag=|z|≥τ

A z-score is review evidence, not an automatic fraud verdict.

AVG(target)μ

STDDEV_SAMPσ

divisionstandardise

ABS(z)>=1.5teaching threshold

-- SQL appears after execution.

—

COMPARE, DO NOT REPLACE

Two engines reveal different parts of the truth

Syntax and execution plans may differ; mathematical meaning must remain invariant.

DuckDB edition →SQL recommenders →