This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing
Problem description & analysis:
A certain database table describes the payment cycle for multiple projects (IDs), with one payment cycle consisting of regular months and a closing month. The regular month only has the current month’s amount but no invoice, Invoiced=0; The closing month includes both the current month’s amount and the invoice, Invoiced=1.
Task: Now we need to identify each payment month for each project and calculate the total amount for that payment cycle. Note that the grouping criteria and order of the payment cycle are related, that is, “when last month’s Invoiced=1, start a new group”, which is different from the common equivalence grouping.
Code comparisons:
SQL：
WITH cte AS (
SELECT *, sum(invoiced) OVER (PARTITION BY ID ORDER BY Date desc) grp
FROM mytable
ORDER BY ID, Date
)
SELECT ID, MAX(date) AS Date, MAX(Invoiced) AS Invoiced, SUM(Amount) AS Amount
FROM cte
GROUP BY ID, grp
ORDER BY ID, Date
SQL does not have a direct ordered grouping, it needs to add a help column using window functions and subqueries, and then group and aggregate based on the help column. The above SQL uses the method of reverse order and then accumulation to gather the help column, which is difficult to understand.
SPL:
SPL supports convenient ordered calculations, and the code is straightforward.
A1： Load data, note that the data has been sorted.
A2： When the ID remains unchanged and the previous month is a regular month, change the Amount to the cumulative value; Otherwise (in the first month of each payment cycle), reset the Amount to the current month’s amount. [-1] represents the previous record.
esProc SPL is open-source and now available here: Open-Source Address.
This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing
Judith-Excel-Sharing | Sciencx (2025-02-18T02:25:24+00:00) Do Ordered Grouping and Aggregation within Groups — From SQL to SPL #1. Retrieved from https://www.scien.cx/2025/02/18/do-ordered-grouping-and-aggregation-within-groups-from-sql-to-spl-1/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.