Technical notebook

Project Planning: windows and CTEs

Latest:

Latest from the notebook

Linear functionThe rule of a straight line: m is the slope, how much y rises for each unit of x, and b is the intercept, the value of y when x is zero.in Data collectionLinear regressionPredicting the average of one variable from another by assuming that average changes along a straight line, E(y | x) = b + mx; each real data point sits off the line by its error ε.in Data collectionLeast squaresHow m and b are estimated: the line that makes the sum of squared vertical errors as small as possible, with a closed-form formula that is what LinearRegression computes.in Data collectionSimple and multiple regressionSimple regression uses one input variable, y = β0 + β1x; multiple regression uses several at once, each with its own slope, and is still a single linear equation.in Data collectionInteractionsA new variable that multiplies two existing ones, such as BMI × smoker, so the effect of one depends on the other while the equation stays linear.in Data collectionInput data and labelBefore training, every column is sorted out: the inputs are x, what the model learns from, and the label is y, what we want to predict.in Data collectionMultiple linear regressionThe same line with many x's at once: normalizing the variables, training, validating and reading the residuals and the importance of each one.in Multiple linear regressionINNER JOINJoining two tables on a column they share: only the rows whose key appears on both sides survive.in JOIN between CITY and COUNTRYTable joined to itselfWhen a row points to another row in the same table: join it to itself to know who is whose parent and classify each node.in Hierarchies: binary tree and org chartHAVINGThe WHERE of groups: first you count with GROUP BY, then you filter on that count, here against the maximum another query computes.in Subqueries and HAVINGSubqueriesA query inside another: the one that groups and counts per hacker is joined to the outer table as if it were just another table.in Subqueries and HAVINGCorrelated subqueryA subquery that reads the outer row, so it is evaluated once per row and compares each row against its own group.in Subqueries and HAVINGWindow functionsComputing over neighbouring rows without grouping them: LAG brings the previous date and SUM OVER accumulates a counter that numbers each project.in Project Planning: windows and CTEsPivoting without PIVOTTurning rows into columns by numbering each group with ROW_NUMBER and spreading them out with CASE WHEN, because MySQL has no PIVOT.in OCCUPATIONS: formatting and pivotingManhattan distanceThe sum of the absolute differences between the coordinates of two points: |a − c| + |b − d|.in STATION: geometry and mediansEuclidean distanceThe straight line between two points: the square root of the sum of the squares of their differences.in STATION: geometry and mediansMedianThe value left in the middle when all of them are sorted: in SQL, sort and skip half of the rows.in STATION: geometry and medians
Filter entries by topic

Loose notes.

Ideas I explain inside the posts. Each one leads to its section.

  1. Project Planning: windows and CTEs
  2. Data collection
  3. 墨絵 sumi-e: my Gentoo desktop

Follow the notebook

New entries arrive by RSS. Paste this link into your reader.

Open feed