REGR_SXY

Description

Returns the sum of the products of the deviations of x and y from their respective means, computed over non-null (y, x) pairs in a group, where x is the independent variable and y is the dependent variable. It is equivalent to REGR_COUNT(y, x) * COVAR_POP(y, x).

info

This function is supported since Apache Doris version 4.1.1.

Syntax

REGR_SXY(<y>, <x>)

Parameters

Parameter	Description
<y>	The dependent variable. Supported type: Double.
<x>	The independent variable. Supported type: Double.

Return Value

Returns a Double value representing the sum of the products of the deviations of x and y from their respective means. If there are no rows in the group, or all rows contain NULLs for the expressions, the function returns NULL.

Example

CREATE TABLE test_regr (
  `id` int,
  `x` double,
  `y` double
) DUPLICATE KEY (`id`)
DISTRIBUTED BY HASH(`id`) BUCKETS AUTO
PROPERTIES (
  "replication_allocation" = "tag.location.default: 1"
);

INSERT INTO test_regr VALUES
(1, 0, NULL),
(2, 1, 3),
(2, 2, 5),
(2, 3, 7),
(2, 4, 9),
(2, 5, NULL);

SELECT id, REGR_SXY(y, x) FROM test_regr GROUP BY id ORDER BY id;

+------+-----------------+
| id   | REGR_SXY(y, x)  |
+------+-----------------+
|    1 |            NULL |
|    2 |            10.0 |
+------+-----------------+