RaqForum 41 No.
29 View  • 
     ...

From SQL to SPL: Do ordered grouping and aggregation within groups

 

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.

ID Date Invoiced Amount
AAA 2023-01 0 10
AAA 2023-02 0 15
AAA 2023-03 1 15
AAA 2023-04 0 10
AAA 2023-05 0 10
AAA 2023-06 1 10
BBB 2022-05 0 40
BBB 2022-06 1 20
BBB 2022-07 0 30
BBB 2022-08 1 30

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.

ID Date Invoiced Amount
AAA 2023-03 1 40
AAA 2023-06 1 30
BBB 2022-06 1 60
BBB 2022-08 1 60

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 supports convenient ordered calculations, and the code is straightforward. https://try.esproc.com/splx?3dX


 　A
1 $select * from mytable.txt order by ID, Date
2 =A1.run(Amount+=if(ID==ID[-1] && Invoiced[-1]==0,Amount[-1]))
3 =A2.select(Invoiced==1)

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.

Question source：https://stackoverflow.com/questions/78224394/query-to-sum-over-multiple-rows-based-on-column


 
 •  29 View  •