Posts

Showing posts from June, 2023

SQL Analytic Functions

Image
Window functions play a very important part in analytic queries. I explore some of the window functions using Postgres 13.4. Window function defaults differ from Postgres to Redshift, although the results are the same. The OVER () default for Redshift is (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) . Postgres's default is (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) . When writing this post, Redshift hasn't implemented the RANGE . Therefore, I haven't mixed the two databases and only stuck to Postgres. This post is created using Jupyter Notebook. I've used Jupyter Magics. For example, the Jupyter SQL Magic result set was converted as Pandas data frame and plotted graph. Setup Jupyter Notebook and Postgres Windows functions RANGE and ROWS Functions Other important functions How to create a table with random data Generate Create pglia database using external SQLs Setup Jupyter Notebook and Postgres If you want to experiment with t...