How To Dev

Introduction When using GROUP BY to aggregate duplicate data rows, relevant individual data rows are lost. Window Function is used to overcome this limitation. It helps perform aggregation calculations on a set of related rows while preserving the original data rows without collapsing them. Window Function executes after WHERE, GROUP BY and HAVING clauses, but before DISTINCT and ORDER BY in the main statement. Therefore, you cannot place a Window Function directly inside the WHERE clause. If you want to filter data based on the result of a Window Function , you must use a temporary table such as a CTE or Subquery . How to Use To construct a Window Function , combine a Function and a Clause as follows: SELECT FUNCTION () OVER ( PARTITION BY col1 ORDER BY col2 ROWS / RANGE BETWEEN < START > AND < END > ) AS col_name FROM table_name; Function : The function placed before the OVER keyword, such as SUM(), AVG(), ROW_NUMBER(...

Introduction COUNT is one of the aggregate functions used to aggregate and compute data In other databases like MySQL, the total row count in a table is a pre-stored value that can be directly retrieved, but in PostgreSQL, to count data accurately, it must inspect every page/tuple to count only the live tuples returned to you Differences COUNT(*) : counts all rows including NULL values COUNT(column) : excludes rows with NULL values Visibility Map (VM) This is a file containing a sequence of bit arrays for each page, containing the following information: Bit 1: whether this page is all-visible (1 or 0) Bit 2: whether this page is all-frozen (1 or 0) How It Works When using COUNT: If there is no index, this is the worst-case scenario because PostgreSQL must use a sequential scan to load pages into RAM PostgreSQL uses a sequential scan and the visibility map to inspect each page, checking the all-visible bit to determine if dead tuples exist in that page If all-visible=0 , indicatin...