REGR_INTERCEPT() OVER ...
All functions > WINDOW FUNCTION > REGR_INTERCEPT() OVER ...
Returns the y-intercept of the linear regression line over the window frame.
Syntax
REGR_INTERCEPT(expr_y, expr_x) [FILTER (WHERE condition) | WITHIN (WHERE condition)] OVER ([PARTITION BY expr [, ...]] [ORDER BY sort_item [, ...]] [ROWS|RANGE|GROUPS frame])
Notes
- Equivalent to AVG(y) - REGR_SLOPE(y, x) * AVG(x) over the window
- NULL values in either expression are excluded
See also
Examples
FeatureQL
SELECT
-- Intercept for y = 2x + 1 over the frame
f1 := ZIP(
ARRAY(1, 2, 3, 4) AS id,
ARRAY(30, 10, 20, 5) AS v,
ARRAY(3.0e0, 5.0e0, 7.0e0, 9.0e0) AS y,
ARRAY(1.0e0, 2.0e0, 3.0e0, 4.0e0) AS x
).TRANSFORM(SELECT REGR_INTERCEPT(y, x) OVER (ORDER BY id ASC).ROUND(2)).UNWRAP(),
-- Intercept counting only rows where v > 15 (10 and 5 excluded from the frame)
f2 := ZIP(
ARRAY(1, 2, 3, 4) AS id,
ARRAY(30, 10, 20, 5) AS v,
ARRAY(3.0e0, 5.0e0, 7.0e0, 9.0e0) AS y,
ARRAY(1.0e0, 2.0e0, 3.0e0, 4.0e0) AS x
).TRANSFORM(
SELECT REGR_INTERCEPT(y, x) FILTER (WHERE v > 15) OVER (ORDER BY id ASC).ROUND(
2
)
).UNWRAP()
;Result
| f1 ARRAY | f2 ARRAY |
|---|---|
| [null, 1.00, 1.00, 1.00] | [null, null, 1.00, 1.00] |