在MySQL/MariaSQL中要做前後2筆資料之計算(以做到如同excel中的樞鈕分析表的效果,如下圖5)
Step1. 利用select建立2個暫存表格,並對每個表格的每一列做編號,第1個表格的編號是第2個表格相對應列的後推一號.
Step2. 將這2個表格做join
Ref:https://www.ptt.cc/bbs/Database/M.1194440834.A.15F.html
Ref:https://ithelp.ithome.com.tw/articles/10136053
範例如下:
1.資料表結構與資料如下圖1與圖2
2.建立暫存表格方法(如圖3)
表格1. @rownum為變數,使用SELECT @rownum:=1宣告...(因為要在select語法中宣告,而不使用set @rownum:=1)
SELECT @rownum:=@rownum+1 AS NO,SUM(adult) ,SUM(child),d FROM amount,(SELECT @rownum:=1) AS row WHERE d IN (SELECT DISTINCT d FROM amount) GROUP BY d
表格2.
SELECT @rownum2:=@rownum2+1 AS NO,SUM(adult) ,SUM(child),d FROM amount,(SELECT @rownum2:=0) AS row WHERE d IN (SELECT DISTINCT d FROM amount) GROUP BY d
*因為 GROUP by d了,d會是唯一的,所以不用再 d in (select....)
3.將2表格join,表格2的某欄位與表格1的某欄位做計算,如(表格2adult欄位-表格1adult欄位)計算出,每天剩餘量之差
如圖4,因此可以得到一些資訊,例如週末購買人數較多,所以為負,或是3/4~3/5,
成人口罩從每週2個調至每週3個,因此原本前2天要購買的人都保留這2天才開始購買
SELECT a.d,b.adult-a.adult,b.child-a.child from
(SELECT @rownum:=@rownum+1 AS rw,SUM(adult) AS adult,SUM(child) AS child,d FROM amount,(SELECT @rownum:=1) AS r WHERE d IN (SELECT DISTINCT d FROM amount) GROUP BY d ORDER BY rw) AS a
,(SELECT @rownum2:=@rownum2+1 AS rw ,SUM(adult) AS adult,SUM(child) AS child,d FROM amount,(SELECT @rownum2:=0) AS r WHERE d IN (SELECT DISTINCT d FROM amount) GROUP BY d ORDER BY rw) AS b
WHERE a.rw=b.rw;
圖1:資料表結構
圖2:資料
圖3.建立2個暫存表格
圖四.結果
圖五.excel樞紐分析表做出一樣的結果