SQL:Over
lastmod 2025-02-15created 2021-11-265 edits
About the SQL OVER clause.
Usage
- [Recommended] SQL OVER clause
Arguments
- PARTITION BY - Divides the query result set into partitions.
- ORDER BY - Defines the logical order of rows within each partition of the result set.
- ROWS/RANGE - Limits the rows within the partition by specifying start and end points within the partition. Requires the ORDER BY argument; if the ORDER BY argument is specified, the default is from the start of the partition to the current element.
Example
SELECT
DISTINCT ad.a4y_uid AS device_uid,
ad.name AS device_name,
ad.description AS device_description,
COUNT(s.time) OVER(PARTITION BY s.a4y_uid) AS service_count,
MAX(s.time) OVER(PARTITION BY s.a4y_uid) AS service_last_time
FROM {TABLE_A4Y_DEVICE} ad
LEFT JOIN {TABLE_A4Y_SERVICE} s ON s.a4y_uid=ad.a4y_uid
WHERE ad.a4y_uid=$1;