## Контекст (таблицы и пример данных) ### Таблицы - `sensor_readings(sensor_id, reading_time, value
SQL Developer
Senior
## Контекст (таблицы и пример данных)
### Таблицы
- `sensor_readings(sensor_id, reading_time, value)`
### Пример данных
**sensor_readings**
| sensor_id | reading_time | value |
|----------:|----------------------|------:|
| 1 | 2025-01-01 10:00:00 | 10.0 |
| 1 | 2025-01-01 10:02:00 | 12.0 |
---
## Задача
Построить ряд по минутам между min и max временем датчика 1 и заполнить пропуски последним известным значением (forward fill).
Вернуть: `minute_ts`, `filled_value`.
Ответы
```sql
WITH bounds AS (
SELECT MIN(reading_time) AS t1, MAX(reading_time) AS t2
FROM sensor_readings
WHERE sensor_id = 1
), minutes AS (
SELECT generate_series((SELECT t1 FROM bounds), (SELECT t2 FROM bounds), INTERVAL '1 minute') AS minute_ts
), joined AS (
SELECT m.minute_ts, r.value
FROM minutes m
LEFT JOIN sensor_readings r
ON r.sensor_id = 1
AND r.reading_time = m.minute_ts
)
SELECT
minute_ts,
LAST_VALUE(value) IGNORE NULLS OVER (ORDER BY minute_ts) AS filled_value
FROM joined
ORDER BY minute_ts;
```