Posts

SQL Window Functions

SQL window functions are SQL single row functions involving row sets:

    window-function over (row-set-definition)

SQL Order Of Operations

For SQL select statements, with only one select operation, the order of operations is: from, join, where, group by, having, select, distinct, order by and limit.

Note that on clauses are parts of join operations, and, windows are parts of select operations.  Note also that can have a where clause operate on the results of a join operation, but, not on the results of a group by operation!

Race And Ethnicity

Race refers to physical characteristics. Ethnicity refers to cultural characteristics.

Managing Python Module Variables With Strings

Invoking the Python globals function returns a dictionary containing module information that can be modified. The dictionary keys are the module variable names as strings. The dictionary values are the module variable values.

Upserts

Upserts are database processes that update and add rows based on primary keys.

Specifying SQL Table Primary Keys Improves Performance

Specifying SQL table primary keys improves performance.

SQL Select Subexpressions

Can often use SQL select subexpressions where SQL values, lists or tables are required. SQL select subexpressions have the following form:
    (select-expression) name

SQL Common Table Expressions

Can define and use temporary SQL tables with SQL common table expressions:
    with name-1 [column-list-1] as (definition-1),
         name-2 [column-list-2] as (definition-2),
         name-3 [column-list-3] as (definition-3)

Hash Tables Lead To Fast Searches

Hash tables are formed from dictionaries by replacing keys with their hashes.  Consider storing the dictionary values at memory addresses equal to the corresponding hashes.  Then, searching for dictionary values would mainly involve just finding hashes!  

P, NP, NP Hard & NP Complete Decision Problems

Decision problems are problems solved by boolean functions of inputs.  Polynomial time Turing machines are Turning machines with the number of required steps always bounded by some polynomial function of input size.  Polynomial time decision problems (P) are decision problems that can always be solved by some polynomial time Turing machine.  Nondeterministic polynomial time decision problems (NP) are decision problems that always have answers that can be verified by some polynomial time Turing machine.  NP hard decision problems are decision problems such that, if any can be solved by some polynomial time Turing machine, then so can all NP decision problems.  NP complete decision problems are decision problems that are both NP hard and NP decision problems.  An example of an NP hard decision problem is the halting problem. An example of an NP complete decision problem is the traveling salesman problem.  An example of a P decision problem is primality determination.

Statistical Powers, P Values And Confidence Levels.

Determining cause and effect relationships can be hampered by noise.  If an effect occurs some percentage of the time in the presence of a potential cause, then that cause and effect pair has a statistical power equal to that percentage.  If an effect occurs some percentage of the time in the absence of a potential cause, then that cause and effect pair has a p value equal to that percentage.  The confidence level is 100 minus the p value percentage.

Mnemonic Major System


The mnemonic major system is a method to remember number sequences:
  1. Convert number sequences to consonant sequences.
  2. Insert vowels as needed to make memorable word sequences.

An Axiom Of Science

Science assumes the future will be similar to the past. This is for reproducibility at different times.

Roll, Pitch And Yaw

Roll


Pitch


Yaw


Geodesic Domes

Icosahedrons are the best possible approximations to spheres with congruent polygons and identical vertices:

 

Consider dividing the surface into smaller congruent triangles:


Also, consider moving the new vertices farther from the center to better approximate a sphere:


Geodesic domes are built from portions of such figures.  The American Institute of Architects calls geodesic domes the "strongest, lightest and most efficient means of enclosing space known to man".

Beginning Of Civilization

Civilizations are groups of people with cities, governments, writing systems, divisions of labor and food surpluses. Agriculture began around 10,000 years ago. Civilizations appeared around 7000 years ago. Some of the earliest civilizations appeared along the Tigris, Euphrates and Nile rivers.

Origin Of Seven Day Weeks

Babylonians likely chose seven day weeks because Moon phases last about that long, many constellations have seven stars, and, they knew about seven "wanders" (Mercury, Venus, Mars, Jupiter, Saturn, Sun and Moon).

Telemetry

Telemetry is automatic device data transmission such as in race cars, aircraft and spacecraft.

Pressure Height Dependence

Consider a horizontal surface of area A submersed in a fluid.  Also, consider increasing the vertical coordinate by the infinitesimal dz.  This will remove the pressure contribution from the weight of an infinitesimal cylinder.  This infinitesimal cylinder has the weight ρgAdz where ρ is the fluid density and g is the freefall acceleration.  Therefore, if dP is the infinitesimal change in pressure:


dP = -ρgdz

Approximating Altitude From Air Pressure

The ideal gas law implies:

P = ρRT / M

where P is the air pressure, ρ is the air density, T is the air temperature, M is the air molar mass, and, R is the ideal gas constant.  Also:


dP = -ρgdz 


where g is the freefall acceleration, and, z is the vertical coordinate.  Therefore:


dP / P = -gMdz / (RT)


If it is assumed that T = To + Lz for some air temperature lapse rate L, where Tis the ground level air temperature, then:


P = Po (1 + Lz / To)-gM / (RL)


where Po is the ground level air pressure.  This allows altitude to be approximated from the air pressure!