Skip to main content

Time Series Functions

Bucket, aggregate, interpolate, and regress — all in one line of CQL. No separate installation, no UDF registration — these are native functions built into the database.

Most frequently used form
-- 시간별 평균, 빈 버킷은 직전 값으로 채운다
SELECT time_bucket_gapfill(1h, timestamp, '2026-07-01', '2026-07-02'),
       locf(avg(value))
FROM pp.tm_tag_point
WHERE tag_id = 'TAG-001'
  AND timestamp >= '2026-07-01' AND timestamp < '2026-07-02'
GROUP BY tag_id, time_bucket_gapfill(1h, timestamp, '2026-07-01', '2026-07-02')
ORDER BY timestamp ASC;

🔴 Respect the argument order. Time series aggregations mostly follow the (값, 시각) order — unlike standard aggregations like avg(value), which take a single value.

Function List​

Bucketing and Gap-Filling​

FunctionSignatureReturn ValueDescription
time_buckettime_bucket(간격, ts [, 기준시각])timestampBucket start time. Scalar for downsampling
time_bucket_gapfilltime_bucket_gapfill(폭, ts, 시작, 끝)timestampFor GROUP BY. Creates empty buckets as well
locflocf(집계)Same type as argumentFills empty buckets with the previous value (last observation carried forward)
interpolateinterpolate(집계)doubleFills empty buckets with linear interpolation between neighbor points

Value Extraction and Change​

FunctionSignatureDescription
first / lastfirst(값, ts) / last(값, ts)Earliest / latest value in time
deltadelta(값, ts)Last minus first
raterate(값, ts)delta ÷ elapsed seconds (based on endpoints)
derivativederivative(값, ts)Least-squares regression slope, per second
counter_delta / counter_rate(값, ts)Cumulative counter increase / per-second increase rate. Corrects for resets
Use counter_* for cumulative counters

When equipment reboots, a cumulative counter resets to 0. delta interprets that as a «negative change», which corrupts the value, but counter_delta and counter_rate detect the reset and correct for it.

Statistics and Distribution​

FunctionSignatureDescription
percentilepercentile(값, q) — q is [0,1]Exact continuous percentile (linear interpolation)
time_weighted_average(값, ts)Time-weighted average
integral(값, ts)Area under the curve (value·seconds)
variance / stddev(값)Sample variance / standard deviation
histogram(값, 최소, 최대, 버킷수)Equal-width histogram. Length 버킷수+2
approx_count_distinct(값)Approximate count of distinct values
time_weighted_average and avg are different

If the sensor does not report at regular intervals, a simple average gets pulled toward «intervals where it reported frequently». You must weight by the time the value was held to get a physically meaningful average.

Relationships Between Two Variables​

FunctionDescription
corr(y, x)Correlation coefficient
covar_pop(y, x) / covar_samp(y, x)Covariance / Sample covariance
regr_slope(y, x) / regr_intercept(y, x) / regr_r2(y, x)Slope · intercept · R-squared of regressing y on x

Examples​

Downsampling with 5-Minute Buckets​

SELECT time_bucket(5m, timestamp) AS bucket,
       avg(value), min(value), max(value)
FROM pp.tm_tag_point
WHERE tag_id = 'TAG-001'
  AND timestamp >= '2026-07-01' AND timestamp < '2026-07-02'
GROUP BY tag_id, time_bucket(5m, timestamp);

Filling Gaps So the Graph Has No Holes​

SELECT time_bucket_gapfill(10m, timestamp, '2026-07-01', '2026-07-01 06:00'),
       interpolate(avg(value))
FROM pp.tm_tag_point
WHERE tag_id = 'TAG-001'
  AND timestamp >= '2026-07-01' AND timestamp < '2026-07-01 06:00'
GROUP BY tag_id, time_bucket_gapfill(10m, timestamp, '2026-07-01', '2026-07-01 06:00');

locf carries forward the previous value (step), interpolate linearly interpolates (straight line). For signals like equipment status that «stay unchanged until the next change», use locf; for continuous physical quantities like temperature, use interpolate.

Hourly Rate of Change for a Production Counter​

SELECT time_bucket(1h, timestamp) AS bucket,
       counter_rate(value, timestamp) * 3600 AS per_hour
FROM pp.tm_tag_point
WHERE tag_id = 'COUNTER-01'
  AND timestamp >= '2026-07-01' AND timestamp < '2026-07-02'
GROUP BY tag_id, time_bucket(1h, timestamp);

p95 of Response Latency​

SELECT time_bucket(1h, timestamp), percentile(value, 0.95)
FROM pp.tm_tag_point
WHERE tag_id = 'LATENCY-01'
  AND timestamp >= '2026-07-01' AND timestamp < '2026-07-02'
GROUP BY tag_id, time_bucket(1h, timestamp);

Correlating Two Tags​

SELECT corr(a.value, b.value)
FROM pp.tm_tag_point_pair
WHERE pair_id = 'TEMP-VS-POWER'
  AND timestamp >= '2026-07-01' AND timestamp < '2026-07-08';

Things to Keep in Mind​

  • Use functions after narrowing the partition. Make sure to include the partition key (usually tag_id) and a time range in WHERE — without them, the query will scan all nodes.
  • In GROUP BY, include both the partition key and the bucket function (GROUP BY tag_id, time_bucket(...)).
  • The 시작 and 끝 of gap-filling must match the time range of WHERE. If they differ, you'll get buckets across an unintended range.

See Also​