Can we use analytic functions like LEAD or LAG in Incorta Sparksql?

amit_kothari — Mon, 19 Sep 2022 19:59:26 GMT

Re: Can we use analytic functions like LEAD or LAG in Incorta Sparksql?

amit_kothari — Mon, 19 Sep 2022 20:00:41 GMT

Yes, we can use window functions in Incorta spark SQL using 'PARTITION BY', refer to the sample SQL below -

Also refer to this useful link - https://databricks.com/blog/2015/07/15/introducing-window-functions-in-spark-sql.html

1) Example 1 using LAG(), compares current and previous month numbers.

SELECT X.MONTH_SEQ,X.MONTH_YEAR,X.ITEM_NUMBER, CASE WHEN X.SALES_AMT > 100 AND X.PREVMONTHAMT > 100 THEN 'EXISTING' WHEN X.SALES_AMT > 100 AND X.PREVMONTHAMT < 100 THEN 'NEW' WHEN X.SALES_AMT < 100 AND X.PREVMONTHAMT < 100 THEN 'INACTIVE' END PROD_STATUS, X.SALES_AMT, X.PREVMONTHAMT FROM (SELECT S.MONTH_SEQ,S.MONTH_YEAR,S.ITEM_NUMBER,S.SALES_AMT, LAG(S.SALES_AMT,1,0 ) OVER ( partition by S.ITEM_NUMBER ORDER BY S.ITEM_NUMBER,S.MONTH_SEQ) AS PREVMONTHAMT FROM test.SAMPLE_SALES S )X

2) Example 2 for computing the end date using LEAD()

SELECT X.PERSON_ID, X.EFFECTIVE_START_DATE, coalesce(date_sub(X.EFFECTIVE_END_DATE,1),TO_DATE('2099-01-01','yyyy-MM-dd')) EFFECTIVE_END_DATE, (case when X.EFFECTIVE_END_DATE IS NULL then 'Y' else 'N' end) current_flag FROM (SELECT PERSON_ID, EFFECTIVE_START_DATE, LEAD(EFFECTIVE_START_DATE,1) OVER ( partition by PERSON_ID ORDER BY PERSON_ID, EFFECTIVE_START_DATE) AS EFFECTIVE_END_DATE FROM EBS_HR_COMMON.PER_ALL_PEOPLE_F WHERE 1=1) X

topic Re: Can we use analytic functions like LEAD or LAG in Incorta Sparksql? in Data & Schema Discussions

Can we use analytic functions like LEAD or LAG in Incorta Sparksql?

Re: Can we use analytic functions like LEAD or LAG in Incorta Sparksql?