5.7 Iteration: count
Perform iteration count by loop and then filtering according to the specified count result.
Based on the following sales table, find the number of days needed to achieve 20 orders in each month of the year 2014. Below is part of the sales table:
| OrderID | Customer | SellerId | OrderDate | Amount |
|---|---|---|---|---|
| 10400 | EASTC | 1 | 2014/01/01 | 3063.0 |
| 10401 | HANAR | 1 | 2014/01/01 | 3868.6 |
| 10402 | ERNSH | 8 | 2014/01/02 | 2713.5 |
| 10403 | ERNSH | 4 | 2014/01/03 | 1005.9 |
| 10404 | MAGAA | 2 | 2014/01/03 | 1675.0 |
| … | … | … | … | … |
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”).query@x(“select * from sales”) |
| 2 | =A1.select(year(OrderDate)==2014) |
| 3 | =A2.sort(OrderDate) |
| 4 | =A3.select(seq(month(OrderDate))==20) |
| 5 | =A4.new(month(OrderDate):month,day(OrderDate):day) |
A1 Connect to the data source and read the sales table.
A2 Select data of the year 2014.
A3 Sort selected data by order date.
A4 Use seq() function to get ordinal numbers of orders in each month and select the record numbered 20 from each month.
A5 According to the date of the order numbered 20 in each month, get the month and day from it, which is the desired result.
Execution result:
| Month | Day |
|---|---|
| 1 | 20 |
| 2 | 20 |
| 3 | 20 |
| 4 | 18 |
| … | … |
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
