How to use windowing functions with aggregates ?

[Klaster]

Hi,

I have problem - to avoid separately computed aggregates calculated on some level and joining them back I want to use windowing functions which are now implemented by many database vendors.

I have tried in AC using expression like

max(@eff_dt , PartitionBy(@col1,@col2,@col3))

which parses well but unfortunately it was converted to  something like

max(W0.tsEff_dt , PARTITION BY W0.dCol1 , W0.sCol2 , W0.sCol3)

which of course result in error 

and it should be like

max(W0.tsEff_dt) over  PARTITION BY W0.dCol1 , W0.sCol2 , W0.sCol3)

is any way to get this ?

regards

[Parvesh_Parmar · Accepted solution]

Hello @Klaster ,

I would like to suggest you create your own function and call it in expression. 

For e.g. your required syntax in query is " MAX(parm1) OVER (PARTITION BY parm2, parm3,param4) "

Then you can create a custom function: 

<?xml version="1.0" encoding='ISO-8859-1' ?>
<!-- ===========================================================================
Additional SQL functions for Adobe Campaign
========================================================================== -->
<package
namespace = "nms"
name = "package-additional-funclist"
label = "Additional functions"
buildVersion= "6.1"
buildNumber = "10000">

<entities schema="xtk:funcList">
<funcList name="myList" namespace="cus">
<group name="date" label="CUS FNC1">
<function name="cusFun1" type="long" args="(<parm1>,<parm2>,<parm3>,<parm4>)" help="Returns the difference between a date and 18 years"
minArgs="4" maxArgs="4" display="Relative maturity of the person born on the date $1">
<providerPart provider="MSSQL" body="MAX($1) OVER (PARTITION BY $2, $3,$4)"/>
</function>
</group>
</funcList>
</entities>
</package>

Then you can cal this function in expression like cusFun1(@eff_dt , @col1,@col2,@col3)

This is just example just to explain you. You need to use it and update it according to you. 

You can follow the following documentation to create your own function. 

Adding additional SQL functions | Adobe Campaign

Thanks.

Parvesh. 

[Klaster]

Hi @Parvesh_Parmar ,

This is great,

For absolutely happiness just I need to make this function work on fluent number of parameters in PARTITION BY section

But this approach save a lot of time of compare to computer aggregates separately and joining them with main data set.

Thanks a lot 

[dipendu_g]

Hi @Parvesh_Parmar ,

Why I receive the 

XTK-170016 You are not authorized to use SQL expressions.

when I create a similar function

You can refer 

https://experienceleaguecommunities.adobe.com/t5/adobe-campaign-classic-questions/custom-method-for-...

Please guide 

Regards,

Dipendu