cancel
Showing results for 
Search instead for 
Did you mean: 

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

amit_kothari
Employee
Employee

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

 
1 REPLY 1

amit_kothari
Employee
Employee

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