在資料分析業務中經常要處理資料檔案。我們知道，對於資料庫中的資料，使用SQL來查詢是非常方便快捷的，所以很容易想到把檔案資料先匯入到資料庫再用SQL來查詢。但是檔案資料匯入資料庫本身也是很繁瑣的工作，那麼有沒有直接對資料檔案使用SQL查詢的辦法呢？本文將介紹這樣的辦法，列舉出用 SQL 查詢檔案資料的各種情況，並提供用 esProc SPL 編寫的程式碼示例。esProc 是專業的資料計算引擎，SPL 中提供了完善的用 SQL 查詢檔案資料的方法。
本文用文字檔案舉例，但同時也適用於Excel檔案。
使用SQL從文字檔案中篩選滿足條件的記錄。
示例：從學生成績表Students_scores.txt中篩選出10班的學生成績，檔案中第一行是列名，第二行開始是資料，如下圖所示。
| A | |
|---|---|
| 1 | $select * from E:/txt/Students_scores.txt where |
使用SQL對文字檔案中的資料進行彙總。
示例：計算學生成績表中全體學生的語文平均分、數學最高分、英語總分。
| A | |
|---|---|
| 1 | $select avg(Chinese),max(Math),sum(English) from E:/txt/Students_scores.txt |
使用SQL對文字檔案中的資料進行跨列計算。
示例：計算學生成績表中每位學生的總分。
| A | |
|---|---|
| 1 | $select *,English+Chinese+Math as total_score from E:/txt/students_scores.txt |
A1中結果如下，增加了一個新的計算列total_score：
在SQL中可以使用CASE語句進行復雜條件計算。
示例：計算學生成績表中每位同學的英語成績是否及格。
| A | |
|---|---|
| 1 | $select *, case when English>=60 then ‘Pass’ else ‘Fail’ end as English_evaluation from E:/txt/students_scores.txt |
A1中結果如下，增加了一個新的計算列English_evaluation：
使用SQL對文字檔案中的資料進行升/降序排序。
示例：將學生成績表按照班號升序、總分降序的順序排列。
| A | |
|---|---|
| 1 | $select * from E:/txt/students_scores.txt order by CLASS,English+Chinese+Math desc |
使用SQL對文字檔案中的資料求TOP-N。
示例：檢視英語成績最高的3個同學成績。
| A | |
|---|---|
| 1 | $select top 3 * from E:/txt/students_scores.txt order by English desc |
使用SQL對文字檔案中的資料進行分組彙總。
示例：查詢各班的英語最低分、語文最高分、數學總分。
| A | |
|---|---|
| 1 | $select CLASS,min(English),max(Chinese),sum(Math) from E:/txt/students_scores.txt group by CLASS |
使用SQL對文字檔案中的資料分組彙總後再過濾。
示例：找出英語平均分低於70分的班級。
| A | |
|---|---|
| 1 | $select CLASS,avg(English) as avg_En from E:/txt/students_scores.txt group by CLASS having avg(English)<70 |
A1中查詢結果如下：
使用SQL對文字檔案中的資料進行去重查詢。
示例：查詢所有班級編號。
| A | |
|---|---|
| 1 | $select distinct CLASS from E:/txt/students_scores.txt |
使用SQL對文字檔案中的資料進行去重計數。
示例：在產品資料檔案中，統計共有多少種不同產品。檔案部分資料如下所示：
| A | |
|---|---|
| 1 | $select count(distinct PID) from E:/txt/PRODUCT_SALE.txt |
使用SQL分組對文字檔案中的資料分組後進行去重計數。
示例：根據產品銷售資料檔案，統計每個產品有銷售記錄的天數。
| A | |
|---|---|
| 1 | $select PID,count(distinct DATE) as no_sdate from E:/txt/PRODUCT_SALE.txt group by PID |
使用SQL對兩個文字檔案中的資料進行關聯查詢。
示例：產品資訊和銷售資訊分別儲存在兩個文字檔案中，計算每次銷售數量小於10的產品的總銷售額。兩個檔案資料結構如下圖：
| A | |
|---|---|
| 1 | $select sum(S.quantity*P.Price) as totalfrom E:/txt/Sales.txt as S join E:/txt/Products.txt as P on S.productid=P.IDwhere S.quantity<=10 |
使用SQL對多個文字檔案中的資料進行關聯查詢。
示例：州資訊，部門資訊和員工資訊分別儲存在3個文字檔案中，查詢California州的HR部門的員工。
| A | |
|---|---|
| 1 | $select e.NAME as NAMEfrom E:/txt/EMPLOYEE_J.txt as e join E:/txt/DEPARTMENT.txt as d on e.DEPTID=d.DEPTID join E:/txt/STATE.txt as s on e.STATEID=s.STATEIDwhere d.NAME=’HR’ and s.NAME=’California’ |
使用SQL對多個文字檔案中的資料進行多級關聯查詢。
示例：州資訊，部門資訊和員工資訊分別儲存在3個文字檔案中，查詢經理在California州的New York州員工。
| A | |
|---|---|
| 1 | $select e.NAME as ENAMEfrom E:/txt/EMPLOYEE.txt as e join E:/txt/DEPARTMENT.txt as d on e.DEPT=d.NAME join E:/txt/EMPLOYEE.txt as emp on d.MANAGER=emp.EIDwhere e.STATE=’New York’ and emp.STATE=’California’ |
支援複雜SQL作為子查詢。
示例：員工資訊和部門資訊分別儲存在2個文字檔案中，找出部門經理最年輕的部門。檔案部分資料如下圖所示：
| A | |
|---|---|
| 1 | $select emp.BIRTHDAY as BIRTHDAY,emp.DEPT as DEPT from E:/txt/DEPARTMENT.txt as dept join E:/txt/EMPLOYEE.txt emp on dept.MANAGER=emp.EIDwhere emp.BIRTHDAY=(select max(BIRTHDAY) from ( select emp1.BIRTHDAY as BIRTHDAY from E:/txt/DEPARTMENT.txt as dept1 join E:/txt/EMPLOYEE.txt as emp1 on dept1.MANAGER=emp1.EID ) ) |
使用SQL的with子句對文字檔案中的資料進行計算。
示例：從部門資料檔案中找出指定部門HR、R&D、Sales，再計算這幾個部門女員工人數和平均工資。資料檔案同上例。
| A | |
|---|---|
| 1 | $with A as(select NAME as DEPT from E:/txt/DEPARTMENT.txtwhere NAME=’HR’ or NAME=’R&D’ or NAME=’Sales’) select A.DEPT DEPT,count(*) NUM,avg(B.SALARY) AVG_SALARY fromA left join E:/txt/EMPLOYEE.txt Bon A.DEPT=B.DEPTwhere B.GENDER=’F’ group by A.DEPT |
《SPL CookBook》中還有更多敏捷計算示例。
本作品採用《CC 協議》，轉載必須註明作者和本文連結