Data preprocessing: scaling, binning, sampling

Data preprocessing turns raw columns into values an analysis or a model can use, and four transformations do most of the work. Scaling puts columns on a common scale: min-max scaling maps a column onto 0 to 1, and a z-score says how many standard deviations a value lies from the mean. Binning, or discretisation, cuts a numeric column into ranges, either of equal width or holding equal numbers of rows. Sampling picks a subset of the rows that stands for the whole, at random, every k-th row, or a fixed share of each group. Compression stores the data in fewer bytes, either exactly or by giving up some detail. All four run inside a database, in SQL, before the data ever leaves it.

Two meanings of normalisation

In data transformation, normalisation means rescaling values, and that is its meaning in the syllabus line quoted below. In database design it means something else: decomposing tables into normal forms such as 3NF and BCNF to remove redundancy. The two share a name and nothing else. This lesson is about values; the normal forms have a module of their own.

Scaling: min-max and z-scores

For a value x in a column with minimum m, maximum M, mean μ and standard deviation σ:

Min-max scaling keeps the shape of the data and squeezes it into 0 to 1. A z-score centres the column on 0 and measures distance in standard deviations, so 2 means “two standard deviations above the mean”. Both are window functions in SQL, and SQLite, whose core has no standard-deviation function, gets the population one as the square root of the mean of squares minus the square of the mean:

-- Monthly spend of eight customers, in rupees: two ways to put it on a common scale.
CREATE TABLE spend (customer TEXT PRIMARY KEY, rupees INTEGER NOT NULL);
INSERT INTO spend VALUES ('c1', 1200), ('c2', 800), ('c3', 4500), ('c4', 2300),
                         ('c5', 950), ('c6', 3100), ('c7', 1500), ('c8', 2650);

-- Min-max: (x - min) / (max - min), into [0, 1].
-- z-score: (x - mean) / standard deviation, here the population standard deviation, sqrt(mean of x² - mean²).
SELECT customer, rupees,
       printf('%.4f', (rupees - min(rupees) OVER ()) * 1.0 / (max(rupees) OVER () - min(rupees) OVER ())) AS min_max,
       printf('%.4f', (rupees - avg(rupees) OVER ())
                      / sqrt(avg(rupees * rupees) OVER () - avg(rupees) OVER () * avg(rupees) OVER ())) AS z_score
FROM spend ORDER BY rupees;
SELECT avg(rupees) AS mean, printf('%.4f', sqrt(avg(rupees * rupees) - avg(rupees) * avg(rupees))) AS population_sd FROM spend;

┌──────────┬────────┬─────────┬─────────┐
│ customer │ rupees │ min_max │ z_score │
├──────────┼────────┼─────────┼─────────┤
│ c2       │ 800    │ 0.0000  │ -1.1159 │
│ c5       │ 950    │ 0.0405  │ -0.9895 │
│ c1       │ 1200   │ 0.1081  │ -0.7790 │
│ c7       │ 1500   │ 0.1892  │ -0.5263 │
│ c4       │ 2300   │ 0.4054  │ 0.1474  │
│ c8       │ 2650   │ 0.5000  │ 0.4421  │
│ c6       │ 3100   │ 0.6216  │ 0.8211  │
│ c3       │ 4500   │ 1.0000  │ 2.0001  │
└──────────┴────────┴─────────┴─────────┘
┌────────┬───────────────┐
│  mean  │ population_sd │
├────────┼───────────────┤
│ 2125.0 │ 1187.4342     │
└────────┴───────────────┘

Python’s statistics module computes the same numbers independently, with fmean for the mean and pstdev for the population standard deviation (Python: statistics):

"""The same min-max and z-scores computed with Python's statistics module, to
check the SQL: fmean for the mean, pstdev for the population standard deviation."""
import math
import statistics

spend = {"c1": 1200, "c2": 800, "c3": 4500, "c4": 2300, "c5": 950, "c6": 3100, "c7": 1500, "c8": 2650}
values = list(spend.values())
lo, hi = min(values), max(values)
mean, sd = statistics.fmean(values), statistics.pstdev(values)

print(f"mean {mean}, population sd {sd:.4f}")
for customer, x in sorted(spend.items(), key=lambda kv: kv[1]):
    print(f"{customer} {x:>5} min-max {(x - lo) / (hi - lo):.4f} z {(x - mean) / sd:.4f}")

# The SQL computed the standard deviation as sqrt(mean of squares - square of mean); compare it with pstdev.
sql_sd = math.sqrt(sum(x * x for x in values) / len(values) - mean * mean)
print(f"largest difference between the two standard deviations: {abs(sql_sd - sd):.1e}")

mean 2125.0, population sd 1187.4342
c2   800 min-max 0.0000 z -1.1159
c5   950 min-max 0.0405 z -0.9895
c1  1200 min-max 0.1081 z -0.7790
c7  1500 min-max 0.1892 z -0.5263
c4  2300 min-max 0.4054 z 0.1474
c8  2650 min-max 0.5000 z 0.4421
c6  3100 min-max 0.6216 z 0.8211
c3  4500 min-max 1.0000 z 2.0001
largest difference between the two standard deviations: 0.0e+00

Every value agrees to four decimals, and the two standard deviations agree exactly. Customer c3’s spend of 4,500 has a z-score of 2.0001: two standard deviations above the mean of 2,125.

Binning: equal width and equal frequency

Equal-width binning cuts the range into k bins of the same size. Equal-frequency binning puts the same number of rows in each bin, which SQL’s ntile(k) does directly; SQLite’s manual says ntile divides the partition into k groups as evenly as possible (SQLite: window functions). On skewed data the two disagree sharply:

-- Delivery times of twelve orders, in minutes: most are quick, a few are very late.
CREATE TABLE delivery (id INTEGER PRIMARY KEY, minutes INTEGER NOT NULL);
INSERT INTO delivery (minutes) VALUES (12), (14), (15), (15), (17), (18), (20), (22), (25), (31), (44), (78);

-- Equal width: 4 bins of (max - min) / 4 = 16.5 minutes each. Equal frequency: ntile(4), 3 orders per bin.
WITH b AS (
  SELECT minutes,
         min(3, CAST((minutes - min(minutes) OVER ()) * 4.0 / (max(minutes) OVER () - min(minutes) OVER ()) AS INTEGER)) + 1 AS width_bin,
         ntile(4) OVER (ORDER BY minutes) AS frequency_bin
  FROM delivery)
SELECT 'equal width' AS method, width_bin AS bin, count(*) AS orders, min(minutes) || '-' || max(minutes) AS minutes
FROM b GROUP BY width_bin
UNION ALL
SELECT 'equal frequency', frequency_bin, count(*), min(minutes) || '-' || max(minutes) FROM b GROUP BY frequency_bin
ORDER BY method DESC, bin;

┌─────────────────┬─────┬────────┬─────────┐
│     method      │ bin │ orders │ minutes │
├─────────────────┼─────┼────────┼─────────┤
│ equal width     │ 1   │ 9      │ 12-25   │
│ equal width     │ 2   │ 2      │ 31-44   │
│ equal width     │ 4   │ 1      │ 78-78   │
│ equal frequency │ 1   │ 3      │ 12-15   │
│ equal frequency │ 2   │ 3      │ 15-18   │
│ equal frequency │ 3   │ 3      │ 20-25   │
│ equal frequency │ 4   │ 3      │ 31-78   │
└─────────────────┴─────┴────────┴─────────┘

Equal-width bins put 9 of the 12 orders in the first bin and none in the third: the one 78-minute order stretched the range. Equal-frequency bins hold 3 orders each and adapt to the data, but the two 15-minute orders landed in different bins. Binning also changes the type of a column, in the terms Stevens proposed: from a ratio scale, where differences and ratios mean something, to an ordinal one, where only the order of the bins does (Stevens).

Sampling: random, systematic, stratified and cluster

• A simple random sample gives every row the same chance.
• A systematic sample takes every k-th row.
• A stratified sample takes a fixed share of each group, such as each city.
• A cluster sample picks whole groups, such as whole stores or whole disk blocks, and keeps all their rows.

A sample that must be reproduced, for a report that others will check, needs a fixed rule rather than a fresh random draw. The example keeps a row when a hash of its key falls in the lowest 10 %:

-- 10,000 orders from five cities of very different sizes. Three samples of about 10 %, each one repeatable.
CREATE TABLE orders (id INTEGER PRIMARY KEY, city TEXT NOT NULL, amount INTEGER NOT NULL);
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 10000)
INSERT INTO orders
SELECT i, CASE WHEN i % 100 < 50 THEN 'Mumbai' WHEN i % 100 < 75 THEN 'Pune' WHEN i % 100 < 90 THEN 'Nagpur'
               WHEN i % 100 < 97 THEN 'Nashik' ELSE 'Kolhapur' END,
       200 + (i * 7919) % 1800
FROM n;

-- Simple random sample: keep a row when a hash of its key falls in the lowest 10 % (the same rows on every run).
CREATE VIEW random_10 AS SELECT * FROM orders WHERE (id * 2654435761) % 4294967296 < 429496730;
-- Systematic sample: every 10th order.
CREATE VIEW every_10th AS SELECT * FROM orders WHERE id % 10 = 7;
-- Stratified sample: 10 % of each city, ranked inside the city by the same hash.
CREATE VIEW stratified_10 AS
SELECT id, city, amount FROM (
  SELECT *, row_number() OVER (PARTITION BY city ORDER BY (id * 2654435761) % 4294967296) AS r,
         count(*) OVER (PARTITION BY city) AS c FROM orders)
WHERE r <= round(c * 0.10);

SELECT o.city, count(*) AS population,
       (SELECT count(*) FROM random_10 s WHERE s.city = o.city) AS random,
       (SELECT count(*) FROM every_10th s WHERE s.city = o.city) AS systematic,
       (SELECT count(*) FROM stratified_10 s WHERE s.city = o.city) AS stratified
FROM orders o GROUP BY o.city ORDER BY population DESC;
SELECT (SELECT avg(amount) FROM orders) AS population_mean, (SELECT avg(amount) FROM random_10) AS random_mean,
       (SELECT avg(amount) FROM every_10th) AS systematic_mean, (SELECT avg(amount) FROM stratified_10) AS stratified_mean;

┌──────────┬────────────┬────────┬────────────┬────────────┐
│   city   │ population │ random │ systematic │ stratified │
├──────────┼────────────┼────────┼────────────┼────────────┤
│ Mumbai   │ 5000       │ 501    │ 500        │ 500        │
│ Pune     │ 2500       │ 244    │ 200        │ 250        │
│ Nagpur   │ 1500       │ 156    │ 200        │ 150        │
│ Nashik   │ 700        │ 64     │ 0          │ 70         │
│ Kolhapur │ 300        │ 35     │ 100        │ 30         │
└──────────┴────────────┴────────┴────────────┴────────────┘
┌─────────────────┬─────────────┬─────────────────┬─────────────────┐
│ population_mean │ random_mean │ systematic_mean │ stratified_mean │
├─────────────────┼─────────────┼─────────────────┼─────────────────┤
│ 1100.62         │ 1103.812    │ 1102.0          │ 1104.459        │
└─────────────────┴─────────────┴─────────────────┴─────────────────┘

The random and stratified samples kept each city’s share close to the population’s, and the stratified one exactly. The systematic sample went wrong: it has no order from Nashik and more than three times Kolhapur’s share, because the cities repeat in a pattern of 100 rows that its step of 10 keeps hitting in the same places. PostgreSQL has sampling built in: TABLESAMPLE BERNOULLI or SYSTEM after a table name, with REPEATABLE (seed) for the same sample twice (PostgreSQL 18: SELECT).

Compression: lossless and lossy

• Lossless compression gives back exactly the original: dictionary encoding replaces repeated values by short codes, and run-length encoding stores a value once with the number of times it repeats.
• Lossy compression gives back an approximation: quantisation rounds values to fewer levels, and dimensionality reduction such as principal component analysis keeps fewer columns that carry most of the variation.

Columnar warehouses lean on the lossless encodings, because a column of one type with many repeats compresses far better than a row of mixed types. Lossy methods are a decision about the analysis, not about storage.

Exercise

-- z-score of each day's amount within its shop: shop, day, z (population standard deviation, 3 decimals).
SELECT shop, day, 0 AS z
FROM sales;

# Sample tests: each one builds the table, runs your query on it and compares the rows it returns (in any order).
tests:
  - name: "one shop, three days"
    setup: |
      CREATE TABLE sales (shop TEXT NOT NULL, day INTEGER NOT NULL, amount INTEGER NOT NULL, PRIMARY KEY (shop, day));
      INSERT INTO sales VALUES ('Pune', 1, 10), ('Pune', 2, 20), ('Pune', 3, 30);
    expected:
      columns: [shop, day, z]
      rows:
        - [Pune, 1, -1.225]
        - [Pune, 2, 0.0]
        - [Pune, 3, 1.225]
  - name: "two shops on very different scales: each scaled within itself"
    setup: |
      CREATE TABLE sales (shop TEXT NOT NULL, day INTEGER NOT NULL, amount INTEGER NOT NULL, PRIMARY KEY (shop, day));
      INSERT INTO sales VALUES ('Mumbai', 1, 100), ('Mumbai', 2, 200), ('Mumbai', 3, 300), ('Mumbai', 4, 400),
        ('Kochi', 1, 5), ('Kochi', 2, 5), ('Kochi', 3, 8);
    expected:
      columns: [shop, day, z]
      rows:
        - [Mumbai, 1, -1.342]
        - [Mumbai, 2, -0.447]
        - [Mumbai, 3, 0.447]
        - [Mumbai, 4, 1.342]
        - [Kochi, 1, -0.707]
        - [Kochi, 2, -0.707]
        - [Kochi, 3, 1.414]

Check yourself

Scaling that survives outliers, constant columns and new data, measured Pro: opens with a Pro, Premium or Ultimate pass

Bins and samples you can reproduce, with their failures measured Pro: opens with a Pro, Premium or Ultimate pass

Compression for analysis: dictionary, run-length and quantisation, with sizes Pro: opens with a Pro, Premium or Ultimate pass

Interview questions

Key takeaways

• Normalisation of values rescales them; normalisation of schemas means normal forms. Data-transformation syllabi mean the first.
• Min-max maps a column onto 0 to 1; a z-score counts standard deviations from the mean. Both are window functions in SQL.
• Equal-width bins follow the range and equal-frequency bins follow the rows; skewed data separates them sharply.
• Random, systematic, stratified and cluster samples differ in how rows are chosen; a hash of the key makes a sample repeatable.
• Lossless compression returns the data exactly, lossy compression approximately, within a bound you state.