brandonhimpfen

-- PostgreSQL JSON / JSONB Examples
--
-- PostgreSQL supports both JSON and JSONB.
--
-- JSON
--   Stores the original JSON text.
--
-- JSONB
--   Stores a binary representation that is faster to query and index.
--

brandonhimpfen

-- PostgreSQL Common Indexing Examples
--
-- Indexes speed up data retrieval by allowing PostgreSQL to locate rows
-- without scanning an entire table.
--
-- Every index has a cost:
-- - Additional storage
-- - Slower INSERTs
-- - Slower UPDATEs
-- - Slower DELETEs

brandonhimpfen

-- PostgreSQL Date Series Examples
--
-- generate_series(start, stop, step) creates a row for each value in a range.
-- When working with dates or timestamps, the step is usually an interval.
--
-- Common uses:
--   - Calendar and reporting ranges
--   - Filling missing dates in time-series results
--   - Monthly summaries
--   - Business-day filtering

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)