How to use windowing functions with aggregates ? | Community
Skip to main content
K
Klaster
Level 2
June 6, 2022
Solved

How to use windowing functions with aggregates ?

  • June 6, 2022
  • 1 reply
  • 1843 views

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

 

 

 

 

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Parvesh_Parmar

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. 

1 reply

Parvesh_Parmar
Community Advisor
Parvesh_ParmarCommunity AdvisorAccepted solution
Community Advisor
June 7, 2022

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. 

Parvesh Parmar – Adobe Community Advisor https://www.linkedin.com/in/parvesh-parmar/
    K
    KlasterAuthor
    Level 2
    June 13, 2022

    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  😀

     

      Terms & ConditionsAccessibility statement

      Loading footer...