Problem Description & Analysis:
The Teradata database table has three fields: date, account, and the name of the product purchased by the account.
Task: Now we need to calculate how many types of products each account has purchased before each day, that is, the number of different products purchased from the beginning to the previous day.
Solutions:
Group by ACCOUNT, then by date, process each group in turn, get the interval from first group from to the current group, and perform deduplication and counting.
But SQL grouping must be accompanied by aggregation, and cannot retain grouped subsets for further processing; At this point, it is necessary to change the mindset, set various markers to transition, and write multi-layer nested statements to repeatedly traverse the data table in order to calculate.
SPL allows grouping to retain grouped subsets for further processing. SPL’s cross row references are also much simpler than window functions:
A1: Load data through JDBC.
A2: Group by account.
A3: Process each group of A2: Continue grouping by date, process daily data, get data of groups from the beginning to the previous day, union these groups, and calculate the quantity of different product names. Finally, union the processing results of each upper-level group. [: -1] represents the set from the first member to the previous member, and the function icount is used for unique count, i.e. count (distinct)
A4: Change the 0 in column 3 to null.
✨🔥 esProc SPL FREE Download — Free Trial Available, Try It Free Now!