Top-K over partition
(K >= 1) |
```mzsql
SELECT fieldA, fieldB, ...
FROM (SELECT DISTINCT fieldA FROM tableA) grp,
LATERAL (SELECT fieldB, ... , fieldZ FROM tableA
WHERE fieldA = grp.fieldA
ORDER BY fieldZ ... LIMIT K) -- K is a number >= 1
ORDER BY fieldA, fieldZ ... ;
```
|
Top-K over partition
(K = 1) |
```mzsql
SELECT DISTINCT ON(fieldA) fieldA, fieldB, ...
FROM tableA
ORDER BY fieldA, fieldZ ... -- Top-K where K is 1;
```
|
First value over partition
order by ... |
```mzsql
SELECT tableA.fieldA, tableA.fieldB, minmax.Z
FROM tableA,
(SELECT fieldA,
MIN(fieldZ) -- Or MAX()
FROM tableA
GROUP BY fieldA) minmax
WHERE tableA.fieldA = minmax.fieldA
ORDER BY fieldA ... ;
```
|
Last value over partition
order by ...
range between unbounded preceding
and unbounded following |
```mzsql
SELECT tableA.fieldA, tableA.fieldB, minmax.Z
FROM tableA,
(SELECT fieldA,
MAX(fieldZ) -- Or MIN()
FROM tableA
GROUP BY fieldA) minmax
WHERE tableA.fieldA = minmax.fieldA
ORDER BY fieldA ... ;
```
|
|
Lag over (order by) whose ordering can be represented by some equality
condition.
|
***To exclude the first row since it has no previous row***
```mzsql
SELECT t1.fieldA, t2.fieldB
FROM tableA t1, tableA t2
WHERE t1.fieldA = t2.fieldA + ...
ORDER BY fieldA;
```
***To include the first row***
```mzsql
SELECT t1.fieldA, t2.fieldB
FROM tableA t1
LEFT JOIN tableA t2
ON t1.fieldA = t2.fieldA + ...
ORDER BY fieldA;
```
|
|
Lead over (order by) whose ordering can be represented by some equality
condition.
|
***To exclude the last row since it has no next row***
```mzsql
SELECT t1.fieldA, t2.fieldB
FROM tableA t1, tableA t2
WHERE t1.fieldA = t2.fieldA - ...
ORDER BY fieldA;
```
***To include the last row***
```mzsql
SELECT t1.fieldA, t2.fieldB
FROM tableA t1
LEFT JOIN tableA t2
ON t1.fieldA = t2.fieldA - ...
ORDER BY fieldA;
```
|