infinitejoke

[infinitejoke]

SELECT, Beyond the Basics

I admit, I’m somewhat spoiled by ORM tools -- most notably ActiveRecord -- which hide database queries behind layers of objects and method chains.

This makes me lag behind somewhat with the assignments for my DB class, in which I have to write raw SQL.

In this post, I’m going to put together some info on PostgreSQL’s SELECT, going beyond the elementary stuff.

Set operations

UNION, INTERSECT, and EXCEPT share the same general form:

SELECT ... FROM ... (UNION | INTERSECT | EXCEPT) SELECT ... FROM ...

By default, the operations remove duplicate rows from the result set. The behavior can be changed by appending ALL:

UNION ALL does not discard duplicate rows, and therfore performs better.

With INTERSECT ALL, a row that has m duplicates in the left table and n duplicates in the right table will appear min(m,n) times in the result set.

With EXCEPT ALL, a row that has m duplicates in the left table and n duplicates in the right table will appear max(m-n,0) times in the result set.

Multiple clauses in the same SELECT statement are evaluated left to right. INTERSECT has higher precedence than UNION.

Ordering

ORDER BY sorts the result rows according to the specified expressions. Rows that are equal according to the leftmost expression are compared according to the next expression and so on. If they are equal according to all expressions, the order is implementation-dependent.

An expression can be a number, referring to the ordinal (left-to-right) position of the output column, e.g. SELECT city, temp FROM weather ORDER BY 2.

It is possible to specify columns that do not appear in the output list, e.g. SELECT city FROM weather ORDER BY temp.

A name that matches both an output column name and an input column name is interpreted as the output column name.

Ordering options apply only to the expression they follow: ORDER BY 1, 2 DESC is not the same as ORDER BY 1 DESC, 2 DESC.

An expression can be a CASE, for example:

SELECT * FROM people ORDER BY CASE gender WHEN 'female' THEN name ELSE last_name END DESC;

Aggregate Functions

The main ones are avg, count, max, min, sum. A usage example from the documentation is:

SELECT max(temp) FROM weather;

The first limitation to consider is that the WHERE clause is evaluated before aggregate functions are computed (it determines which rows will be included in the aggregate calculation). It means that something like SELECT city FROM weather WHERE temp = max(temp) does not work.

What we need is an independent computation, or a subquery:

SELECT city FROM weather WHERE temp = (SELECT max(temp) FROM weather);

Aggregate functions can be computed for groups of rows specified by GROUP BY:

SELECT city, MAX(temp) FROM weather GROUP BY city;

(Returns every city along with the maximum temperature registered in it.)

Unlike WHERE, a HAVING clause selects group rows after aggregates and groups are computed, making it useful for filtering results:

SELECT city, max(temp) FROM weather GROUP BY city HAVING max(temp) < 40;

When the output of a function depends on the order of inputs, an ORDER BY can be added after the last argument:

SELECT string_agg(city, ', ' ORDER BY date DESC) FROM weather;

PostgreSQL 9.4 added support for a FITLER (WHERE ...) clause that specifies which rows are fed to the aggregate function:

SELECT count(*) AS unfiltered, count(*) FILTER (WHERE i < 5) AS filtered FROM generate_series(1,10) AS s(i);

unfiltered | filtered ------------+---------- 10 | 4

[infinitejoke]

Notes on Dynamics

Moving on from kinematics to dynamics now.

Newton's First Law of Motion

The law of inertia states that an object at rest stays at rest and an object in motion stays in motion with the same speed and in the same direction unless acted upon by an unbalanced force.

In other words, with the lack of opposing forces -- such as friction and air resistance -- an object in motion would travel forever at constant speed. No force is needed to keep something in motion.

Newton's Second Law of Motion

The net force applied on an object causes an acceleration inversely proportional to the mass of the object. Simply put, larger mass makes objects more difficult to turn or change speed.

$$\vec{F}_{net} = m\vec{a}$$

While $m\vec{a}$ is equivalent to the net force acting on the object, it is not a force itself. It is a product of two quantities, mass and acceleration.

Let's explore a different definition of the law based on linear momentum, the product of an object’s mass multiplied by its velocity:

$$\vec{p} = m\vec{v}$$

Here, the rate of change of momentum of an object becomes directly proportional to the force applied, and this change in momentum takes place in the direction of the applied force.

$$\vec{F}_{net} = \frac{d\vec{p}}{dt} = m\frac{d\vec{v}}{dt} = m\vec{a}$$

Newton's Third Law

If an object A exerts a force on object B, then object B must exert a force of equal magnitude and opposite direction back on object A.

$$\vec{F}_{12} = -\vec{F}_{21}$$

Conservation of Momentum

The total momentum of a closed system is constant, and when objects interact, their total momentum before the interaction is the same as after the interaction.

$$\sum p_{before} = \sum p_{after}$$

Collision

When two objects separate after collision:

$$p_1 + p_2 = p_1' + p_2'$$ $$m_1v_1 + m_2v_2 = m_1v_1' + m_2v_2'$$

When two objects stick together after collision:

$$p_1 + p_2 = p_{1 + 2}'$$ $$m_1v_1 + m_2v_2 = (m_1 + m_2)v'$$

Energy

Gravitational potential energy is the energy possessed by the object due to the gravitational attraction of the Earth for the object. It depends on the object's vertical position (height) and its mass:

$$E_p = mgh$$

Translational kinetic energy is the energy possessed by the object due to its motion.

$$E_k = \frac{p^2}{2m} = \frac{1}{2}mv^2$$

The standard unit of measurement for energy is the joule, equivalent to $kg*(m/s)^2$.

Rotational motion

In rotational motion, inertial mass $m$ becomes the moment of inertia $I$. For objects rotating about the center, it is defined as:

$I = mr^2$ for a point mass $m$ at a distance $r$ from the axis of rotation

$I = \frac{1}{12}ml^2$ for a rod of length $l$ and mass $m$

$I = \frac{1}{2}mr^2$ for a solid cylinder of radius $r$ and mass $m$

$I = mr^2$ for a cylindrical shell with open ends of radius $r$ and mass $m$

$I = \frac{2}{5}mr^2$ for a solid sphere of radius $r$ and mass $m$

In translational motion, force $F$ causes an object to accelerate. In rotational motion, an object acquires angular acceleration due to torque $\tau$, defined for the length of the moment arm $r$ as:

$$\tau = Fr$$

A direct equivalent of the Second Law for rotational motion is expressed in terms of torque and moment of inertia:

$$\tau = I\varepsilon$$

Angular momentum $L$ is defined as the product of the moment of inertia $I$ and the angular velocity $\omega$.

$$L = I\omega$$

Analogously to linear momentum $p = mv$, it is subject to the conservation of angular momentum principle if there is no external torque on the object.

Rotational kinetic energy thus becomes:

$$E_k = \frac{L^2}{2I} = \frac{1}{2}I\omega^2$$

[infinitejoke]

Pronouncing Maths

I haven’t paid much, if any, attention to the way I pronounce math terms as I’m getting my degree in my native language and I rarely get to talk to anyone in English, let alone about math-related topics.

Now's the time to fix that. I’m going to use the American English pronunciation, which suits my casual speech.

Greek letters

(Source)

Symbol Name IPA Pseudo-phonetic TeX $A\alpha$ alpha /ˈælfə/ AL fuh A, \alpha $B\beta$ beta /ˈbeɪtə/ BAY tuh B, \beta $\Gamma\gamma$ gamma /ˈɡæmə/ GAM uh \Gamma, \gamma $\Delta\delta$ delta /ˈdɛltə/ DELL tuh \Delta, \delta $E\epsilon$ epsilon /ˈɛpsɨlɒn/ EP suh lon E, \epsilon $Z\zeta$ zeta /ˈzeɪtə/ ZAY tuh Z, \zeta $H\eta$ eta /ˈeɪtə/ AY tuh H, \eta $\Theta\theta$ theta /ˈθeɪtə/ THAY tuh \Theta, \theta $I\iota$ iota /aɪˈoʊtə/ eye OH tuh I, \iota $K\kappa$ kappa /ˈkæpə/ CAP uh K, \kappa $\Lambda\lambda$ lambda /ˈlæmdə/ LAM duh \Lambda, \lambda $M\mu$ mu /ˈmjuː/ MYOO M, \mu $N\nu$ nu /ˈnjuː/ NYOO N, \nu $\Xi\xi$ xi /ˈzaɪ/ ZIGH \Xi, \xi $Oo$ omicron /ˈɒmɨkrɒn/ OH mih cron O, o $\Pi\pi$ pi /ˈpaɪ/ PIE \Pi, \pi $P\rho$ rho /ˈroʊ/ ROE P, \rho $\Sigma\sigma$ sigma /ˈsɪɡmə/ SIG muh \Sigma, \sigma $T\tau$ tau /ˈtaʊ/ TOW, rhyming with COW T, \tau $\Upsilon\upsilon$ upsilon /ˈʌpsɨlɒn/ UP suh lon \Upsilon, \upsilon $\Phi\phi$ phi /ˈfaɪ/ FIE \Phi, \phi $X\chi$ chi /ˈkaɪ/ KIGH X, \chi $\Psi\psi$ psi /ˈsaɪ/ SIGH \Psi, \psi $\Omega\omega$ omega /oʊˈmeɪɡə/ oh MAY guh \Omega, \omega

[infinitejoke]

Notes on Kinematics

Driven at this point less by a genuine interest in the topic than by the fear of failing my exams, I set out to fill the glaring holes in my understanding of physics, starting with mechanics. Memorizing precise formulas in high school left me with no actual idea of what's really going on. Instead, I'd like to dig a bit further, and see the relationships between quantities. The following notes were assembled from various web sources. ## Translational motion The basic vector quantities are **displacement** $s$ `[m]`, referring to the object's overall change in position, **velocity** $v$ `[m/s]`, the rate at which the position is changing, and **acceleration** $a$ `[m/s^2]`, the rate at which the velocity is changing. Let's start with acceleration, the first derivative of velocity with respect to time. According to [this informative site](https://physics.info/kinematics-calculus/), we can use the definition to go back to velocity: $$a = \frac{dv}{dt} \implies dv = adt$$ $$\int_{v_0}^v dv = \int_0^t a dt$$ $$v \Big|_{v_0}^v = at \Big|_0^t$$ $$v - v_0 = at$$ $$v = v_0 + at$$ Velocity itself is a derivative, that of position with respect to time. Which means we can use the same process to find the position-time equation: $$v =\frac{ds}{dt} \implies ds = vdt = (v_0 + at)dt$$ $$\int_{s_0}^s ds = \int_0^t (v_0 + at)dt$$ $$s \Big|_{s_0}^s = v_0t + \frac{at^2}{2}\Big|_0^t$$ $$s - s_0 = v_0t + \frac{at^2}{2}$$ $$s = s_0 + v_0t + \frac{at^2}{2}$$ ## Rotational motion Angles are measured in _radians_. This demonstration from Wikipedia is quite informative:  Translational motion quantities have their rotational motion counterparts: **angular displacement** $\theta$ `[rad]`, **angular velocity** $\omega$ `[rad/s]`, and **angular acceleraion** $\varepsilon$ `[rad/s^2]`. Equations can be derived the same way: $$\varepsilon = \frac{d\omega}{dt},\ \int_{\omega_0}^\omega d\omega = \int_0^t \varepsilon dt$$ $$\omega = \omega_0 + \varepsilon t$$ $$\omega = \frac{d\theta}{dt},\ \int_{\theta_0}^\theta d\theta = \int_0^t \(\omega_0 + \varepsilon) dt$$ $$\theta = \theta_0 + \omega_0 t + \frac{\varepsilon t}{2}$$ While angular velocity (as well as angular displacement and angular acceleration) stays the same for every point, _linear_ quantities -- **arc length/distance travelled** $s$, **tangential velocity** $v$ and **tangential acceleration** $a_t$ -- do change depending on how far the point is from the axis. $$s = R\Delta\theta,\ v = R\omega,\ a_t = R\varepsilon$$ Even if the object maintains a constant speed, with $a_t = 0$, the direction of the velocity is still changing due to **centripetal acceleration** $a_c$. Its direction is toward the center, and its magnitude is $$a_c = \frac{v^2}{R} = \frac{(R\omega)^2}{R} = R\omega^2$$ While tangential velocity is tangent to the turning object, angular velocity points in a direction _perpendicular_ to the object, determined by the Right-Hand Rule (illustration from Wikipedia):

Angular acceleration points in the same direction as angular velocity if the rotation is speeding up, otherwise, the vector points in the opposite direction.