0

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

1reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Yes, we can use window functions in Incorta spark sql using 'PARTITION BY' , refer to the sample sqls 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

    Like
Like Follow
  • 1 mth agoLast active
  • 1Replies
  • 106Views
  • 1 Following

Product Announcement

Incorta 4.9 is now Generally Available (GA)!!!