Databases Advanced

What is Window Function?

An SQL function that performs calculations across a set of rows related to the current row without collapsing the result set.

Window functions compute values over a "window" of rows defined by OVER(). Unlike GROUP BY, they do not reduce the number of rows. Common functions include ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM() OVER(), and AVG() OVER().

Example: SELECT name, salary, AVG(salary) OVER(PARTITION BY department) as dept_avg FROM employees. Window functions are essential for running totals, rankings, moving averages, and gap-fill operations.

Related Terms

Database Connection Pooling

A technique that maintains a cache of database connections for reuse, reducing the overhead of creating new connections.

Row-Level Security (RLS)

A database feature that restricts which rows a user can access in a table based on security policies.

PostgreSQL's binary JSON data type that stores JSON documents with indexing, querying, and manipulation capabilities.

The process of copying and maintaining database data across multiple servers for redundancy, failover, and read scaling.

EXPLAIN ANALYZE

A PostgreSQL command that shows the execution plan of a query along with actual runtime statistics for performance tuning.

A version-controlled change to a database schema that can be applied and reversed systematically.

View All Databases Terms →