Partition - Lag and Lead
Query - Data
SELECT entity, datetime, value
FROM "distance"
WHERE datetime BETWEEN '2018-12-01T00:00:00Z' AND '2018-12-01T05:00:00Z' EXCL
AND entity = 'car-1'
ORDER BY entity, tags, datetime
Results
| entity | datetime | value |
|---------|----------------------|-------|
| car-1 | 2018-12-01 00:00:00 | 0 |
| car-1 | 2018-12-01 01:00:00 | 50 |
| car-1 | 2018-12-01 02:00:00 | 100 |
| car-1 | 2018-12-01 03:00:00 | 120 |
| car-1 | 2018-12-01 04:00:00 | 200 |
LAG
SELECT entity, datetime, value,
LAG(value),
LAG(value, 3),
LAG(value, 3, -1)
FROM "distance"
WHERE datetime BETWEEN '2018-12-01T00:00:00Z' AND '2018-12-01T05:00:00Z' EXCL
AND entity = 'car-1'
ORDER BY entity, tags, datetime
Results
| entity | datetime | value | lag(value) | lag(value, 3) | lag(value, 3, -1) |
|---------|----------------------|--------|-------------|----------------|-------------------|
| car-1 | 2018-12-01 00:00:00 | 0 | | | -1 |
| car-1 | 2018-12-01 01:00:00 | 50 | 0 | | -1 |
| car-1 | 2018-12-01 02:00:00 | 100 | 50 | | -1 |
| car-1 | 2018-12-01 03:00:00 | 120 | 100 | 0 | 0 |
| car-1 | 2018-12-01 04:00:00 | 200 | 120 | 50 | 50 |
LEAD
SELECT entity, datetime, value,
LEAD(value),
LEAD(value, 3),
LEAD(value, 3, -1)
FROM "distance"
WHERE datetime BETWEEN '2018-12-01T00:00:00Z' AND '2018-12-01T05:00:00Z' EXCL
AND entity = 'car-1'
ORDER BY entity, tags, datetime
Results
| entity | datetime | value | lead(value) | lead(value, 3) | lead(value, 3, -1) |
|---------|----------------------|--------|--------------|-----------------|--------------------|
| car-1 | 2018-12-01 00:00:00 | 0 | 50 | 120 | 120 |
| car-1 | 2018-12-01 01:00:00 | 50 | 100 | 200 | 200 |
| car-1 | 2018-12-01 02:00:00 | 100 | 120 | | -1 |
| car-1 | 2018-12-01 03:00:00 | 120 | 200 | | -1 |
| car-1 | 2018-12-01 04:00:00 | 200 | | | -1 |
LAG without Partitioning
SELECT entity, datetime, value,
LAG(value),
LAG(value, 3),
LAG(value, 3, -1)
FROM "distance"
WHERE datetime BETWEEN '2018-12-01T00:00:00Z' AND '2018-12-01T05:00:00Z' EXCL
ORDER BY entity, tags, datetime
Results
| entity | datetime | value | lag(value) | lag(value, 3) | lag(value, 3, -1) |
|---------|----------------------|--------|-------------|----------------|-------------------|
| car-1 | 2018-12-01 00:00:00 | 0 | | | -1 |
| car-1 | 2018-12-01 01:00:00 | 50 | 0 | | -1 |
| car-1 | 2018-12-01 02:00:00 | 100 | 50 | | -1 |
| car-1 | 2018-12-01 03:00:00 | 120 | 100 | 0 | 0 |
| car-1 | 2018-12-01 04:00:00 | 200 | 120 | 50 | 50 |
# Because the result set is not partitioned,
# LAG in row for car-2 entity is able to access previous rows even though they are for entity car-1
| car-2 | 2018-12-01 00:00:00 | 50 | 200 | 100 | 100 |
| car-2 | 2018-12-01 01:00:00 | 100 | 50 | 120 | 120 |
| car-2 | 2018-12-01 02:00:00 | 200 | 100 | 200 | 200 |
| car-2 | 2018-12-01 03:00:00 | 400 | 200 | 50 | 50 |
| car-2 | 2018-12-01 04:00:00 | 400 | 400 | 100 | 100 |
LAG with Partitioning
SELECT entity, datetime, value, row_number(),
LAG(value),
LAG(value, 3),
LAG(value, 3, -1)
FROM "distance"
WHERE datetime BETWEEN '2018-12-01T00:00:00Z' AND '2018-12-01T05:00:00Z' EXCL
WITH ROW_NUMBER(entity, tags ORDER BY time) > 0
ORDER BY entity, tags, datetime
Results
| entity | datetime | value | row_number() | lag(value) | lag(value, 3) | lag(value, 3, -1) |
|---------|----------------------|--------|---------------|-------------|----------------|-------------------|
| car-1 | 2018-12-01 00:00:00 | 0 | 1 | | | -1 |
| car-1 | 2018-12-01 01:00:00 | 50 | 2 | 0 | | -1 |
| car-1 | 2018-12-01 02:00:00 | 100 | 3 | 50 | | -1 |
| car-1 | 2018-12-01 03:00:00 | 120 | 4 | 100 | 0 | 0 |
| car-1 | 2018-12-01 04:00:00 | 200 | 5 | 120 | 50 | 50 |
# Because the result set is partitioned,
# LAG in row for car-2 entity is not able to access previous rows for entity car-1
| car-2 | 2018-12-01 00:00:00 | 50 | 1 | | | -1 |
| car-2 | 2018-12-01 01:00:00 | 100 | 2 | 50 | | -1 |
| car-2 | 2018-12-01 02:00:00 | 200 | 3 | 100 | | -1 |
| car-2 | 2018-12-01 03:00:00 | 400 | 4 | 200 | 50 | 50 |
| car-2 | 2018-12-01 04:00:00 | 400 | 5 | 400 | 100 | 100 |
Reference to absolute value with FIRST_VALUE
SELECT entity, datetime, value, row_number(),
LAG(value),
FIRST_VALUE(value)
FROM "distance"
WHERE datetime BETWEEN '2018-12-01T00:00:00Z' AND '2018-12-01T05:00:00Z' EXCL
WITH ROW_NUMBER(entity, tags ORDER BY time) > 0
ORDER BY entity, tags, datetime
Results
| entity | datetime | value | row_number() | lag(value) | first_value(value) |
|--------|---------------------|-------|--------------|------------|--------------------|
| car-1 | 2018-12-01 00:00:00 | 0 | 1 | | 0 |
| car-1 | 2018-12-01 01:00:00 | 50 | 2 | 0 | 0 |
| car-1 | 2018-12-01 02:00:00 | 100 | 3 | 50 | 0 |
| car-1 | 2018-12-01 03:00:00 | 120 | 4 | 100 | 0 |
| car-1 | 2018-12-01 04:00:00 | 200 | 5 | 120 | 0 |
| car-2 | 2018-12-01 00:00:00 | 50 | 1 | | 50 |
| car-2 | 2018-12-01 01:00:00 | 100 | 2 | 50 | 50 |
| car-2 | 2018-12-01 02:00:00 | 200 | 3 | 100 | 50 |
| car-2 | 2018-12-01 03:00:00 | 400 | 4 | 200 | 50 |
| car-2 | 2018-12-01 04:00:00 | 400 | 5 | 400 | 50 |
LAG with Partitioning and atsd_series Table
SELECT metric, entity, datetime, value, row_number(),
(value-LAG(value))/(time-LAG(time))*3600000 AS speed_per_interval
FROM atsd_series
WHERE metric IN ('distance')
AND datetime BETWEEN '2018-12-01T00:00:00Z' AND '2018-12-01T05:00:00Z' EXCL
WITH ROW_NUMBER(metric, entity, tags ORDER BY metric, time) > 0
| metric | entity | datetime | value | row_number() | speed_per_interval |
|-----------|---------|----------------------|--------|---------------|--------------------|
| distance | car-1 | 2018-12-01 00:00:00 | 0 | 1 | |
| distance | car-1 | 2018-12-01 01:00:00 | 50 | 2 | 50 |
| distance | car-1 | 2018-12-01 02:00:00 | 100 | 3 | 50 |
| distance | car-1 | 2018-12-01 03:00:00 | 120 | 4 | 20 |
| distance | car-1 | 2018-12-01 04:00:00 | 200 | 5 | 80 |
| distance | car-2 | 2018-12-01 00:00:00 | 50 | 1 | |
| distance | car-2 | 2018-12-01 01:00:00 | 100 | 2 | 50 |
| distance | car-2 | 2018-12-01 02:00:00 | 200 | 3 | 100 |
| distance | car-2 | 2018-12-01 03:00:00 | 400 | 4 | 200 |
| distance | car-2 | 2018-12-01 04:00:00 | 400 | 5 | 0 |
