Note: The group() function performs grouping; ~.top expression gets TopN in a group; -3 means getting top3 backwards.
Query Orders.csv in SQL
Complicated preparation, involving installing database, creating tables and importing data, etc.
Write SQL query directly
The xls files in a certain directory are named after dates. Each file stores sale records of a date. And we are trying to find files of January in the year 2020, merge them and calculate the yearly sale amount for each customer.
Usually programming is needed to achieve batch file processing, but it has a high technical threshold
Write SQL directly and execute it
select Client,year(OrderDate) as y,sum(Amount) as Total
from d:/data/202001*.xlsx
group by Client,year(OrderDate)
Note: SPL SQL supports filename containing wildcard characters, so it can automatically merge multiple files.
SPL IDE
Specialized IDE for Advanced Users
All-around programming ability
All-around programming ability
Powerful computing capability
Filter, search, distinct, sort, join, group & aggregation
Group by segment, enumerated grouping, order-based grouping, conditional grouping
Transposition, merge, complement, align with nulls, reverse grouping
relative position, cross-row reference, moving average
A wealth of library functions
Mathematical functions: Round-up, sin & cos, combination, factorial, gcd, matrix
Date functions:Time series, workday, quarter, beginning of a month, end of a month, number of days in a week
String functions:Position of a substring, splitting away date/numbers/words
Support of diverse/multiple data sources
xls, csv, txt, json, xml
MySQL, Access, Oracle, …
SPL XLL
An add-in for powering up Excel computing ability
Versatile formulas
Power up Excel functions
Enable Vlookup to return multiple matches
Enable datedif to calculate quarter/week difference between two dates
Enable filter to perform fuzzy queries
Enable computations on grouped subsets
Get ranking of data in each group
Filter data in each group by an aggregate value
Combine rows in each group into one using a specified separator
Simplify computations between sheets
Cross join two sheets
Full join sheets & calculate difference of sheets
SPL WIN
Interactive file processing interface
Real SQL on File
File format support
Text:txt, csv
Excel:all versions of xls and xlsx
Special format:Json, XML
SQL92 standards
where, group by, join and union
As, between, like, Top-N and limit n
from clause, with clause, nested query, case when
High-efficiency
Programming skills are nonessential
Do not require explicit or inexplicit data loading