Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

How to use windowing functions with aggregates ?

Avatar

Level 2

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

 

 

 

 

1 Accepted Solution

Avatar

Correct answer by
Level 4

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. 

View solution in original post

1 Reply

Avatar

Correct answer by
Level 4

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. 

Avatar

Level 2

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 :grinning_face: