Complement a certain average value to ensure that the total sum remains unchanged — From SQL to SPL #3

Problem description & analysis:

An invoice table in the SQL Server database has one amount for each project, and each project in the project table has multiple accounts, and the two are associated through ProjectID.

Invoices

Projects

Task: Now we need to associate the two tables and add a SplitAmount field. Roughly on average divide the amount according to the number of accounts in the project, for example, 100 is divided into 3 parts. The amount of N-1 accounts should be rounded to 2 decimal places according to 1/N, which is 33.33. The Nth account should complement the average value to ensure that the total amount remains unchanged, which is 100–33.33 * 2=33.34.

Code comparisons:

SQL：

select *,
       SplitAmount 
       + case when rn = 1 
              then i.Amount - sum  (i.SplitAmount) 
                              over (partition by i.ProjectID)
              else 0
              end  as AdjustedSplitAmount
from(
  select 
      I.*, P.AccountCode,
      round(I.Amount / count(I.InvoiceID) over (partition by P.ProjectID), 2) as SplitAmount,
      row_number() over (partition by P.ProjectID order by p.AccountCode) as rn
  from 
      #Invoices I Inner Join #Projects P on I.ProjectID = P.ProjectID
) i

After SQL grouping, it must aggregate immediately, and cannot retain the grouped subsets and directly add SplitAmount field on the subsets according to the rules. It requires indirect implementation using nested subqueries and window functions, and the sequence numbers also need to be extra generated using window functions. The overall code is cumbersome.

SPL:

With grouped subsets, SPL code can be more natural. try.DEMO

A1： Simple join, load data.

A2： Group, but not aggregate.

A3： Process each group of data and directly add SplitAmount field according to the rules. # is the natural sequence number, and there is no need for additional calculation.

A4： Merge groups.

---

Say goodbye to SQL headaches and streamline your process with SPL! 🎯esProc SPL is open-sourced, please feel free to download and give it a try: Open-Source Address.

Comments

[Judith-Data-Processing-Hacks]

Feel free to share your thoughts with us! We'd love to hear form you!