brandonhimpfen

-- SQL Window Function Examples (PostgreSQL)
--
-- Window functions perform calculations across related rows without
-- collapsing them like GROUP BY.
--
-- General syntax:
--
--   function(...) OVER (
--       PARTITION BY ...
--       ORDER BY ...

brandonhimpfen

-- PostgreSQL UPSERT Examples
--
-- UPSERT allows you to insert a row or update an existing row when a
-- uniqueness constraint would otherwise be violated.
--
-- Syntax:
--
--   INSERT ...
--   ON CONFLICT (...)
--   DO NOTHING

brandonhimpfen

#!/usr/bin/env node
/**
 * Compare exact and approximate distinct counts while streaming.
 *
 * The script calculates:
 * - Exact distinct count using an in-memory Set
 * - Approximate distinct count using a fixed-size bitmap
 * - Absolute error
 * - Percentage error
 * - Bitmap occupancy and saturation warnings

brandonhimpfen

#!/usr/bin/env node
/**
 * Estimate distinct value counts in CSV or JSONL while streaming.
 *
 * Uses a fixed-size bitmap and linear counting:
 *
 *   estimated cardinality = -m × ln(V / m)
 *
 * where:
 *   m = total number of bitmap bits

brandonhimpfen

#!/usr/bin/env node
/**
 * Profile approximate numeric percentiles from CSV or JSONL while streaming.
 *
 * Uses reservoir sampling to keep a bounded, approximately uniform sample
 * from each selected numeric column.
 *
 * Reported percentiles:
 * - p01
 * - p05

brandonhimpfen

#!/usr/bin/env node
/**
 * Profile numeric CSV/JSONL columns while streaming.
 *
 * Calculates:
 * - Valid numeric count
 * - Missing, empty, and invalid counts
 * - Minimum and maximum
 * - Sum and mean
 * - Population and sample standard deviation

brandonhimpfen

#!/usr/bin/env node
/**
 * Profile CSV/JSONL value frequencies while streaming.
 *
 * Usage:
 *   node node-profile-value-frequencies.js input.csv --format=csv --columns=country,status
 *   cat input.jsonl | node node-profile-value-frequencies.js --format=jsonl --columns=user.country,status
 *
 * Options:
 *   --top=N          Number of values to show per column (default: 10)

brandonhimpfen

#!/usr/bin/env node
/**
 * Profile CSV/JSONL column completeness while streaming.
 *
 * Usage:
 *   node node-profile-column-completeness.js input.csv --format=csv
 *   cat input.jsonl | node node-profile-column-completeness.js --format=jsonl --columns=id,name,email
 *
 * Notes:
 * - CSV mode uses the header row as columns unless --columns is provided.

brandonhimpfen

#!/usr/bin/env node
/**
 * Detect duplicate CSV headers or duplicate JSONL keys while streaming.
 *
 * Usage:
 *   node node-detect-duplicate-fields.js input.csv --format=csv
 *   cat input.jsonl | node node-detect-duplicate-fields.js --format=jsonl
 *
 * Notes:
 * - CSV mode checks only the header row.

brandonhimpfen

#!/usr/bin/env node
/**
 * Normalize CSV or JSONL field names to snake_case while streaming.
 *
 * Usage:
 *   cat input.jsonl | node node-normalize-fields-snake-case.js --format=jsonl > output.jsonl
 *   node node-normalize-fields-snake-case.js input.csv --format=csv > output.csv
 *
 * Notes:
 * - JSONL mode normalizes object keys recursively.