* Implement the ordered enumeration conditional grouping from MySQL in one line
SQL only supports equivalent grouping. For enumeration grouping, SQL usually uses case when for transition, but cannot keep the order. To keep the order, you have to join. For example, it is written as follows:
with T2(g,ord) as {
select 'firstGroup',1
union all select 'secondGroup',2
……
}
select T1.*
from T2 join
(select (case
when condition1 then 'firstGroup'
when condition2 then 'secondGroup'
…… end) g
, count(*) n
from A
group by g) T1
on T1.g=T2.g
order by T2.ord asc
Moreover, even like this, the loss of empty groups will still occur.
It would be much more convenient to use SPL in this case. With one line:
=connect(”mysqlDB”).query(“select * from A”).enum([condition1,condition2,…]).new([”firstGroup”,”secondGroup”,…](#):g, ~.len():n)
SPL supports the operation of ordered sets thoroughly. It can express the datasets (including grouped subsets) in the operation process explicitly. In addition to the enumeration grouping of fixed order, it is also easy to realize the overlapping grouping. Please refer to Grouping Data by a Non-field Condition in SPL
When the data is not in the database, it is still convenient for SPL to perform complex calculations:
=file(“d:/t.csv”).import(;,",").enum...
It's also easy to embed esProc into Java applications,please refer to How to Call an SPL Script in Java
For specific usage, please refer to Getting started with esProc
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
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