You are viewing a plain text version of this content. The canonical link for it is here.

Posted to dev@calcite.apache.org by wrstrs <wr...@foxmail.com> on 2021/01/04 12:52:05 UTC

function that computes across rows

Hi All,




Does Calcite support a custom function that computes across rows, such as a day-by-day ratio function which going to implement : DBD&nbsp;




Input:

	SELECT&nbsp;

	&nbsp; &nbsp; DBD(a) AS a_dbd

	FROM

	&nbsp; &nbsp; table_xx

	WHERE

	&nbsp; &nbsp; d = '20210101'

	ORDER BY a_dbd




Convert to:

	SELECT&nbsp;

	&nbsp; &nbsp; CASE

	&nbsp; &nbsp; &nbsp; &nbsp; WHEN a2 IS NULL OR a2 = 0 THEN 0

	&nbsp; &nbsp; &nbsp; &nbsp; ELSE (a1 - a2) / a2

	&nbsp; &nbsp; END AS a_dbd

	FROM

	&nbsp; &nbsp; (SELECT&nbsp;

	&nbsp; &nbsp; &nbsp; &nbsp; SUM(a) AS a1

	&nbsp; &nbsp; FROM

	&nbsp; &nbsp; &nbsp; &nbsp; table_xx

	&nbsp; &nbsp; WHERE

	&nbsp; &nbsp; &nbsp; &nbsp; d = '20210101') t1

	&nbsp; &nbsp; &nbsp; &nbsp; LEFT JOIN

	&nbsp; &nbsp; (SELECT&nbsp;

	&nbsp; &nbsp; &nbsp; &nbsp; SUM(a) AS a2

	&nbsp; &nbsp; FROM

	&nbsp; &nbsp; &nbsp; &nbsp; table_xx

	&nbsp; &nbsp; WHERE

	&nbsp; &nbsp; &nbsp; &nbsp; d = '20201231') t2 ON 1 = 1

	ORDER BY a_dbd




Or a better expression...





Thanks

wrstrs