PostgreSQL SQL conveniences

For years with Sybase I chafed at having to create "temp tables" with intermediate results in preparation for the final query. PostgreSQL eliminates the need for temp tables because it allows a subquery to follow the FROM of a SELECT. Evidently Oracle allows something similar too.

Another PostgreSQL convenience is generate_series. Sometimes in a SQL query one needs a list of all possible integers in a range. In Sybase, again, this has to be done with a temp table. But in PostgreSQL, one can SELECT FROM generate_series.

PostgreSQL SQL rich syntax obviates the need for stored procedures in these situations.

Computing energy from FFT

The straightforward way to compute energy of a signal is just summing the squares in the time domain. But it can also be computed from the FFT, or more properly speaking the spectrum, which is the square root of the sum of the real part squared and the imaginary part squared. The tricky part is realizing two things:

1. Typically only the positive half of a spectrum is displayed, so if you want to compare apples to apples (i.e. amounts of energy in the same units), you have to add in the negative spectrum as well. Since the spectrum of a real-valued function is symmetric, this just means doubling the energy from the positive spectrum.

2. The DC component of the spectrum (0 Hz) appears just once in the spectrum, so it doesn't have to be doubled.

Thus we have:

Energy = Sum of squares in time domain = DC component squared + 2 * (sum of squares of positive spectrum)