esProc uses simple SQL queries on structured text files

Structured text files refer to TXT files separated by tabs or CSV files separated by commas, which can be seen as data tables. Using simple SQL of esProc can easily operate such files, avoiding the hassle of importing into databases.

This article uses text files as an example, but it also applies to Excel files, assuming that the first row of the file is the title.

	A	B
1	$select * from scores.txt where	// filter
2	$select avg(Chinese),max(Math),sum(English) from scores.txt	// aggregation
3	$select *,English+Chinese+Math as total_score from scores.txt	// compute column
4	$select *, case when English>=60 then 'Pass' else 'Fail' end as English_evaluation from scores.txt	// case
5	$select * from scores.txt order by CLASS,English+Chinese+Math desc	// sort
6	$select top 3 * from scores.txt order by English desc	// top-N
7	$select CLASS,min(English),max(Chinese),sum(Math) from scores.txt group by CLASS	// group aggregation
8	$select CLASS,avg(English) as avg_En from scores.txt group by CLASS having avg(English)<85	// group filter
9	$select distinct CLASS from scores.txt	// distinct
10	$select count(distinct CLASS) from scores.txt	// count distinct
11	$select CLASS,count(distinct English) as num_de from scores.txt group by CLASS	// group count distinct
12
13	$select sum(S.quantity*P.Price) as total from sales.csv as S join product.csv as P on S.productid=P.ID where S.quantity<=50	// 2 files join
14	$select s.ID,c.cname,p.name,s.Date,s.Quantity,p.Price,c.borth,c.state from sales.csv as s join product.csv as p on s.ProductID=p.ID join customer.txt as c on s.CustomerID=c.cid where c.borth<date("2000-01-01")	// multiple files join
15	$select s.ID,c.cname,p.name,s.Date,s.Quantity,p.Price,c.state from sales.csv as s join product.csv as p on s.ProductID=p.ID join customer.txt as c on s.CustomerID=c.cid where c.borth=(select max(borth) from customer.txt)	// subquery
16	$with A as (select cid from customer.txt where state in ('Texas','California','Florida') ) select p.name,count(*) as sales, sum(s.Quantity*p.Price) as total from sales.csv s,product.csv p,A where s.ProductID=p.ID and s.CustomerID=A.cid group by p.name	// with

https://try.esproc.com/splx?35K

A1 filters out the scores of Class 2 students from the scores.txt file. The first row of the file contains column names, and starting from the second row is the data, as shown in the following figure.

A2 calculates the average Chinese score, highest math score, and total English score of all students in the student score table.

A3 calculates the total score of each student in the student score table.

A4: Calculate whether each student's English score in the student score table has passed.

A5: Sort the student score table in ascending order of class ID and descending order of total score.

A6: Check the scores of the top 3 students with the highest English scores.

A7 queries the lowest score in English, highest score in Chinese, and total score in mathematics for each class.

A8: Identify classes with an average English score below 85.

A9: Query all class IDs.

A10 counts the total number of classes with different IDs.

A11: Group by class and count the number of non-repeating English scores in each class.

A13: Sales information and product information are stored separately in two CSV files, and the total sales revenue for products with a sales quantity of less than 50 per order is calculated.

A14: Multiple files associated query, querying order information of customers with a birth date less than 2000-01-01.

A15: Multiple files associated query, and the subquery results are used as filtering criteria to query the order information of the youngest customer.

A16: with clause, query the order quantity and total order amount for each product among customers in the specified state.