Pulling Data from Databases

The previous lesson took data from a file and showed that a parsing decision silently dropped 142 rows. The file had one convenience: a row was a record, at least when the quotes were read correctly. A database does not give that convenience. A query returns rows, and what a record of what that row is comes not from the structure of the tables but from how the query is written.

This is the lesson’s measure: whether the unit the analysis asks about and the unit of the row the query returns are the same. The relational model, keys, and writing queries are prerequisites and are not repeated here; the only things measured are the unit of the row and where the row count goes when that unit shifts.

• DC6. The store is fiction and is modeled with node arrays. The join is done with a small helper; query syntax and optimization are not the subject of this course. It is a model.
• DC7. There are three tables: the 1,400-row subscriber table, the 1,877-row reading table that holds one row per subscriber-period, and the 1,328-row field visit table that holds between zero and three rows per subscriber.
• DC8. The unit of analysis is written down explicitly: the question asked is mean consumption per subscriber, that is, a row of the report is a subscriber.
• DC9. The threshold for counting two means as the same is 0.05 m³, because readings are kept with one decimal and a difference below that can only come from rounding.

The Store’s Three Tables

// store.mjs — a fictional relational store of the regional measurement network. THIS
// IS A MODEL: the tables are node arrays, the join is done with a small helper. Query
// syntax is not the subject of this course; what is measured is which unit's record
// the returned row is.
export const SEED = 4711;
export const REGION = ["north", "east", "central", "south", "west"];
const rng = (t) => { let s = t >>> 0;
  return () => { s = (Math.imul(s, 1103515245) + 12345) >>> 0; return s / 4294967296; }; };

const r = rng(SEED);
// subscriber: the unit of analysis itself. Each subscriber appears once.
export const SUBSCRIBER = Array.from({ length: 1400 }, (_, i) => ({
  subscriber_no: 10000 + i, region: REGION[i % 5], type: i % 7 === 0 ? "commercial" : "residential" }));
// reading: one row per subscriber-period pair. The first period of 1400 subscribers, the second period of 477 subscribers.
export const READING = [];
for (let i = 0; i < 1400; i++)
  READING.push({ subscriber_no: 10000 + i, period: "2024-01", reading_m3: 5 + Math.floor(r() * 321) / 10 });
for (let i = 0; i < 477; i++)
  READING.push({ subscriber_no: 10000 + i, period: "2024-02", reading_m3: 5 + Math.floor(r() * 321) / 10 });
// visit: field visits. A subscriber may have zero, one, two, or three visits.
export const VISIT = [];
for (const a of SUBSCRIBER) {
  const p = r(), n = p < 0.41 ? 0 : p < 0.71 ? 1 : p < 0.91 ? 2 : 3;
  for (let j = 0; j < n; j++)
    VISIT.push({ subscriber_no: a.subscriber_no, seq: j + 1, type: j === 0 ? "reading" : "inspection" });
}

// Inner join: each row of the left table appears once for every row of the right table
// whose key matches. If there is no match, the left row does not appear at all.
export const innerJoin = (left, right, key) => {
  const index = new Map();
  for (const s of right) { const k = s[key];
    if (!index.has(k)) index.set(k, []); index.get(k).push(s); }
  return left.flatMap((x) => (index.get(x[key]) ?? []).map((y) => ({ ...x, ...y })));
};
// Left join: if there is no match, the left row appears once, with its right fields empty.
export const leftJoin = (left, right, key) => {
  const index = new Map();
  for (const s of right) { const k = s[key];
    if (!index.has(k)) index.set(k, []); index.get(k).push(s); }
  return left.flatMap((x) => { const e = index.get(x[key]);
    return e ? e.map((y) => ({ ...x, ...y })) : [{ ...x }]; });
};

if (import.meta.url === `file://${process.argv[1]}`) {
  const distribution = new Map();
  for (const a of SUBSCRIBER) {
    const n = VISIT.filter((z) => z.subscriber_no === a.subscriber_no).length;
    distribution.set(n, (distribution.get(n) ?? 0) + 1);
  }
  console.log(`seed ${SEED}; subscriber ${SUBSCRIBER.length} rows, reading ${READING.length} rows, ` +
    `visit ${VISIT.length} rows`);
  console.log(`distinct subscribers in the reading table ${new Set(READING.map((x) => x.subscriber_no)).size}, ` +
    `distinct subscriber-periods ${new Set(READING.map((x) => x.subscriber_no + x.period)).size}`);
  console.log("distribution of visit counts per subscriber:");
  for (const n of [...distribution.keys()].sort())
    console.log(`  visits = ${n}: ${distribution.get(n)} subscribers`);
  console.log(`total of readings ${READING.reduce((s, x) => s + x.reading_m3, 0).toFixed(1)} m3`);
}

seed 4711; subscriber 1400 rows, reading 1877 rows, visit 1328 rows
distinct subscribers in the reading table 1400, distinct subscriber-periods 1877
distribution of visit counts per subscriber:
  visits = 0: 570 subscribers
  visits = 1: 453 subscribers
  visits = 2: 256 subscribers
  visits = 3: 121 subscribers
total of readings 38690.7 m3

The three tables are kept in three different units. A row of the subscriber table is one subscriber, and the subscriber number is unique there. A row of the reading table is one subscriber-period pair; because the second-period readings of 477 of the 1,400 subscribers were also entered, the table has 1,877 rows and the subscriber number is not unique there. A row of the visit table is one field visit, and a subscriber may not appear in this table at all or may appear three times.

The distribution table shows the real risk. 570 of the 1,400 subscribers have no visit at all, and 121 have three visits. A join erases the first of these two extremes and triples the second. Both effects show up in the number of rows the query returns, and neither produces an error message.

When the Row’s Unit Shifts

// query.mjs — three query forms return three different row sets from the same store.
// What is measured is not whether the row is correct but WHICH UNIT'S record the row
// is: if the analysis asks for subscriber-period, every subscriber-period pair in
// the result set must appear exactly once.
import { SUBSCRIBER, READING, VISIT, innerJoin, leftJoin } from "./store.mjs";

const S1 = innerJoin(READING, SUBSCRIBER, "subscriber_no");   // reading + subscriber
const S2 = innerJoin(S1, VISIT, "subscriber_no");             // + visit, inner join
const S3 = leftJoin(S1, VISIT, "subscriber_no");              // + visit, left join

// How many times the unit of analysis (subscriber-period) appears in a result set.
const unit = (set) => {
  const count = new Map();
  for (const s of set) { const k = s.subscriber_no + s.period; count.set(k, (count.get(k) ?? 0) + 1); }
  return { max: Math.max(...count.values()),
    dropped: READING.length - count.size, multiplied: set.length - count.size };
};
const total = (set) => set.reduce((t, s) => t + s.reading_m3, 0);
const fmt = (w) => (h) => h.map((v, j) => (j ? String(v).padStart(w[j])
  : String(v).padEnd(w[0]))).join("");
const fmt1 = fmt([24, 8, 9, 8, 11, 8, 12, 8]);
console.log(fmt1(["query", "rows in", "rows out", "dropped", "multiplied", "max", "total m3", "ratio"]));
for (const [name, k] of [["S1 reading + subscriber", S1], ["S2 + visit (inner)", S2],
  ["S3 + visit (left)", S3]]) {
  const b = unit(k);
  console.log(fmt1([name, READING.length, k.length, b.dropped, b.multiplied, b.max,
    total(k).toFixed(1), (total(k) / total(S1)).toFixed(2)]));
}
const visited = new Set(VISIT.map((z) => z.subscriber_no));
const zero = SUBSCRIBER.filter((a) => !visited.has(a.subscriber_no));
console.log(`\n${zero.length} subscribers with no visits: ` +
  `${READING.filter((o) => !visited.has(o.subscriber_no)).length} of their reading rows never appear in S2; ` +
  `the query raised no error, the row count is the only sign`);
const b2 = unit(S2), affected = b2.dropped + b2.multiplied;
console.log(`in S2 ${b2.dropped} rows dropped and ${b2.multiplied} rows multiplied: ${affected} rows, ` +
  `so ${(100 * affected / READING.length).toFixed(0)}% of the input was affected. But because the two ` +
  `errors ` +
  `worked in opposite directions, the row count went from 1877 to 1780, only ${READING.length - S2.length} ` +
  `rows dropped, and the total deviated by ${(100 * (1 - total(S2) / total(S1))).toFixed(1)}%`);

// The same question, three decisions: "mean periodic consumption per subscriber".
const subscriberTotal = new Map();
for (const s of S1) subscriberTotal.set(s.subscriber_no, (subscriberTotal.get(s.subscriber_no) ?? 0) + s.reading_m3);
const meanA = total(S1) / S1.length;
const meanB = [...subscriberTotal.values()].reduce((a, b) => a + b, 0) / subscriberTotal.size;
const meanC = total(S2) / S2.length;
const fmt2 = fmt([34, 18, 10]);
console.log(`\nthree decisions for the question "mean consumption per subscriber"`);
console.log(fmt2(["decision", "row unit", "m3"]));
console.log(fmt2(["A mean per reading row", "subscriber-period", meanA.toFixed(2)]));
console.log(fmt2(["B sum per subscriber first", "subscriber", meanB.toFixed(2)]));
console.log(fmt2(["C mean per S2 row", "subscriber-visit", meanC.toFixed(2)]));
// The threshold for counting two means as the same is 0.05 m3: readings are kept
// with one decimal, and a difference below that can only come from rounding.
const THRESHOLD = 0.05;
console.log(`the difference between A and B is ${(meanB - meanA).toFixed(2)} m3, ` +
  `${Math.round((meanB - meanA) / THRESHOLD)} times the threshold of ${THRESHOLD}; both are correct ` +
  `calculations but they do not answer the same question`);
console.log(`C is a mean that weights the readings of subscribers with many visits, and since it ` +
  `differs from A by only ${Math.abs(meanC - meanA).toFixed(2)} m3, the difference goes unnoticed`);

query                    rows in rows out dropped multiplied     max    total m3   ratio
S1 reading + subscriber     1877     1877       0          0       1     38690.7    1.00
S2 + visit (inner)          1877     1780     766        669       3     35967.4    0.93
S3 + visit (left)           1877     2546       0        669       3     51971.8    1.34

570 subscribers with no visits: 766 of their reading rows never appear in S2; the query raised no error, the row count is the only sign
in S2 766 rows dropped and 669 rows multiplied: 1435 rows, so 76% of the input was affected. But because the two errors worked in opposite directions, the row count went from 1877 to 1780, only 97 rows dropped, and the total deviated by 7.0%

three decisions for the question "mean consumption per subscriber"
decision                                    row unit        m3
A mean per reading row             subscriber-period     20.61
B sum per subscriber first                subscriber     27.64
C mean per S2 row                   subscriber-visit     20.21
the difference between A and B is 7.02 m3, 140 times the threshold of 0.05; both are correct calculations but they do not answer the same question
C is a mean that weights the readings of subscribers with many visits, and since it differs from A by only 0.41 m3, the difference goes unnoticed

S1 is the correct set. When the reading table is joined with the subscriber table, each reading row appears exactly once because the subscriber number is unique on the right side: rows in 1,877, rows out 1,877, dropped zero, multiplied zero. This is the only condition under which a join leaves the row count unchanged — the key being unique on the other side.

S2 adds the visit table to the same set, and two things happen at once. The 766 reading rows of the 570 subscribers with no visit do not appear in the result at all, because an inner join drops unmatched left rows. At the same time, the readings of subscribers with more than one visit multiply: 669 extra rows, and a subscriber-period pair appears at most three times in the result set. The unit is no longer subscriber-period; it is the subscriber-visit pair.

The lesson’s real finding is that two numbers hide each other. 1,435 of the 1,877 rows, that is, about 76% of the input, either dropped or multiplied. But because the two errors worked in opposite directions, the result set came out at 1,780 rows: a net loss of 97 rows, a deviation in the total of 7.0%. Someone looking at the row count or the total cannot see that three quarters of the set is corrupted. The account opens up only when dropped and multiplied are counted separately.

S3 uses a left join instead of an inner join and solves the dropped-row problem: subscribers with no visit stay in the set, dropped is zero. The multiplication remains — the set rises to 2,546 rows and the total rises to 1.34 times. A left join prevents loss, it does not prevent multiplication; they are separate decisions and are counted separately.

The unit function in the code is the test that produces these three rows, and it can be run after every query. The test asks a single question: how many times does the key of the unit of analysis appear in the result set. If the count per key is one on every row, the unit is preserved. If the count is greater than one, there is multiplication, and that key’s values carry weight in the total more than once. If a key that was in the input never appears in the result, there is silent loss. These three come out in a single scan and differ from reading the row count: the number 1,780 says nothing about S2 on its own, but when dropped and multiplied are written side by side, exactly what the set is becomes known. The course’s rule applies here too — if the difference between rows in and rows out cannot be written out item by item, the data has not been prepared, only changed.

Same Question, Three Units

The lower table measures the unit-of-analysis question on its own. The question is fixed: mean consumption per subscriber. According to DC8, a row of the report is a subscriber, so the correct answer is B — 27.64 m³. Decision A takes the mean over reading rows and gives 20.61 m³; this number is not miscalculated, it is only the answer to another question (mean reading per period). Subscribers with two readings are counted twice in A and once in B, and the difference is 7.02 m³, that is, 140 times the threshold.

Decision C is the most dangerous. The mean taken over the rows of S2 gives 20.21 m³ and differs from A by only 0.41 m³. The number looks plausible because the values themselves are not corrupted; the only thing corrupted is how many times each reading is counted. The readings of subscribers with many visits carry weight three times, those of subscribers with no visits carry zero. This mean is the answer to no question, and its closeness to A makes it hard to find.

The shared lesson of these three numbers is this: a query that does not state which unit’s record it is looking at after the join has not said what it returned, even if it says how many rows it returned.

Summary

• The unit of the row a query returns comes not from the structure of the tables but from how the query is written; joining reading with subscriber gives 1,877 rows and preserves the unit as subscriber-period.
• Adding the visit table with an inner join silently drops 766 rows and multiplies 669 rows; the unit shifts to the subscriber-visit pair.
• Because the two errors work in opposite directions, the net row loss is only 97 and the deviation in the total is 7.0%; even though about 76% of the input is corrupted, someone looking at the numbers cannot see the damage.
• The left join brings dropped rows to zero but does not stop multiplication: the set rises to 2,546 rows and the total rises to 1.34 times.
• The same question gives three numbers under three units — 27.64 m³ per subscriber, 20.61 m³ per reading row, 20.21 m³ in the set spread over visits; the last goes unnoticed because it is close to the right one.

Next Step

Both the file and the database gave the data all at once: when the file was read, how many rows it had was known, and when the query ran, the set was in hand. When collecting data from an interface, this assurance is missing. Records arrive piece by piece, the calling side keeps track of how each piece connects to the next, and the source puts a limit on call frequency. The next lesson counts how many records consuming pagination wrongly skips and measures how a queue that obeys the rate limit determines the collection time.