[MCOL-1759] Implement regr_sxx, regr_syy, regr_sxy and corr functions as UDAF Created: 2018-10-01  Updated: 2018-10-29  Resolved: 2018-10-15

Status: Closed
Project: MariaDB ColumnStore
Component/s: ExeMgr
Affects Version/s: 1.2
Fix Version/s: 1.2.0

Type: New Feature Priority: Major
Reporter: David Hall (Inactive) Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Problem/Incident
causes MCOL-1793 regr_slope() and regr_r2() produce in... Closed
Sprint: 2018-18

 Description   

We have a requirement to implement the regr_xxx functions for Columnstore. The list of functions to implement did not include regr_sxx, reg_syy or regr_sxy. These functions seem to be part of most other database offerings and should be part of ours.

Also, the corr(x, y) function appears to be in everyone's library as well.

These functions are trivial to implement within the UDAF API. Writing test cases will take longer than implementation.

 Comments   
Comment by David Hall (Inactive) [ 2018-10-09 ]

Add covar_pop and covar_samp as well.

Comment by David Hall (Inactive) [ 2018-10-09 ]

Roman, thought you might like these. May help shed light on the API.

Comment by David Hall (Inactive) [ 2018-10-11 ]

Test:

When used when innodb right now, these functions are rounded to two decimal places. These are not native MariaDB Server functions, but rather the result of implementing them as Server UDAF, since we need the stubs anyway.

CREATE TABLE aggr(k int, v decimal(10,2), v2 decimal(10, 2))engine=columnstore;
CREATE TABLE aggr_innodb(k int, v decimal(10,2), v2 decimal(10, 2));
INSERT INTO aggr VALUES(1, 10, NULL);
INSERT INTO aggr VALUES(2, 10, 11), (2, 20, 22), (2, 25, NULL), (2, 30, 35);
INSERT INTO aggr_innodb VALUES(1, 10, NULL);
INSERT INTO aggr_innodb VALUES(2, 10, 11), (2, 20, 22), (2, 25, NULL), (2, 30, 35);

SELECT k, CORR(v, v2) FROM aggr GROUP BY k;
------------------+

k CORR(v, v2)

------------------+

1 NULL
2 0.9988445981

------------------+
SELECT k, CORR(v, v2) FROM aggr_innodb GROUP BY k;
-----------------+

k CORR(v, v2)

-----------------+

1 NULL
2 1.00

-----------------+

SELECT k, COVAR_POP(v, v2) FROM aggr GROUP BY k;
-------------------+

K COVAR_POP(V, V2)
--+-----------------
1 NULL
2 80.0

-------------------+

SELECT k, COVAR_POP(v, v2) FROM aggr_innodb GROUP BY k;
----------------------+

k COVAR_POP(v, v2)

----------------------+

1 NULL
2 80.00

----------------------+

SELECT k, COVAR_SAMP(v, v2) FROM aggr GROUP BY k;
-----------------------+

k COVAR_SAMP(v, v2)

-----------------------+

1 NULL
2 120.0000000000

-----------------------+

SELECT k, COVAR_SAMP(v, v2) FROM aggr_innodb GROUP BY k;
-----------------------+

k COVAR_SAMP(v, v2)

-----------------------+

1 NULL
2 120.00

-----------------------+

select k, regr_sxx(v, v2) from aggr group by k;
---------------------+

k regr_sxx(v, v2)

---------------------+

1 NULL
2 288.6666666667

---------------------+

select k, regr_sxx(v, v2) from aggr_innodb group by k;
---------------------+

k regr_sxx(v, v2)

---------------------+

1 NULL
2 288.67

---------------------+

select k, regr_sxy(v, v2) from aggr group by k;
---------------------+

k regr_sxy(v, v2)

---------------------+

1 NULL
2 240.0000000000

---------------------+

select k, regr_sxy(v, v2) from aggr_innodb group by k;
---------------------+

k regr_sxy(v, v2)

---------------------+

1 NULL
2 240.00

---------------------+

select k, regr_syy(v, v2) from aggr group by k;
---------------------+

k regr_syy(v, v2)

---------------------+

1 NULL
2 200.0000000000

---------------------+

select k, regr_syy(v, v2) from aggr_innodb group by k;
---------------------+

k regr_syy(v, v2)

---------------------+

1 NULL
2 200.00

---------------------+

Comment by Daniel Lee (Inactive) [ 2018-10-15 ]

Build verified: 1.2.0a

Functions were created.

Result differences are being tracked in a separate ticket.

Generated at Thu Feb 08 02:31:10 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.