MySQL 利用Pivoting格式化做報表
此處用到了Pivoting技術
注:Pivoting技術是指一種可以把行轉換為列的技術,在Pivoting的執行過程中可能會用到聚合。
這裡討論的都是靜態Pivoting查詢,即使用者需要提前知道旋轉的屬性列的值,對於動態Pivoting,需要動態地構造查詢字串。
並在日誌結尾時附錄Unpivoting技術
注:Unpivoting可以看作是pivoting的反向操作,即將列旋轉成為行
用Pivoting格式化聚合資料:
(一般用於報表展現)
首先做一張測試表
再插入一些測試資料:
此時可以將表formatting看作是一張彙總表,比如網上商城的購物明細。
這份彙總表顯示了訂單號,訂單日期,員工編號,消費者編號和訂單數量。
要在此彙總表的基礎上進一步統計每個消費者每年的訂單數量,可能會想到用分組來獲得結果,比如:
結果為:
這樣子並不直觀,如果可以透過旋轉得到這樣的結果,那就直觀和清晰多了:
但是當旋轉的元素非常多的說話,會產生較長的查詢字串
要縮短查詢的字串,可以預先產生一張矩陣表,包含每個要旋轉列的屬性:
將formatting和formatting_temp聯接
【Unpivoting】
Unpivoting操作是將列轉換成行,是Pivoting的逆操作
這裡是把之前的formatting旋轉之後的內容匯入到unpivoting表中。
目前unpivoting表的內容
解決方案及思路:
解決這個問題需要將列旋轉成為行,這裡使用的技巧是對每行資料產生3個副本
每個副本產生一個需要旋轉的列,這個過程可以透過如下的cross join來完成
結果為:
接下來,只需要根據orderyear列來去的對應旋轉列的值就可以了
由於最後要得到
過濾掉qty=0的情況
因此這個問題的最終解決方案為:
注:Pivoting技術是指一種可以把行轉換為列的技術,在Pivoting的執行過程中可能會用到聚合。
這裡討論的都是靜態Pivoting查詢,即使用者需要提前知道旋轉的屬性列的值,對於動態Pivoting,需要動態地構造查詢字串。
並在日誌結尾時附錄Unpivoting技術
注:Unpivoting可以看作是pivoting的反向操作,即將列旋轉成為行
用Pivoting格式化聚合資料:
(一般用於報表展現)
首先做一張測試表
-
create table formatting(
-
orderid int not null,
-
orderdate date not null,
-
empid int not null,
-
custid vahrchar(10) not null,
-
qty int not null,
- primary key (orderid,orderdate));
再插入一些測試資料:
-
insert into formatting select 1,'2010-01-02',3,'A',10;
-
insert into formatting select 2,'2010-04-02',2,'B',20;
-
insert into formatting select 3,'2010-05-02',1,'A',30;
-
insert into formatting select 4,'2010-07-02',3,'D',40;
-
insert into formatting select 5,'2011-01-02',4,'A',20;
-
insert into formatting select 6,'2011-01-02',3,'B',30;
-
insert into formatting select 7,'2011-01-02',1,'C',40;
-
insert into formatting select 8,'2009-01-02',2,'A',10;
- insert into formatting select 9,'2009-01-02',3,'B',20;
此時可以將表formatting看作是一張彙總表,比如網上商城的購物明細。
這份彙總表顯示了訂單號,訂單日期,員工編號,消費者編號和訂單數量。
要在此彙總表的基礎上進一步統計每個消費者每年的訂單數量,可能會想到用分組來獲得結果,比如:
-
select custid,year(orderdate) year,sum(qty) sum
-
from formatting
- group by custid,year(orderdate);
這樣子並不直觀,如果可以透過旋轉得到這樣的結果,那就直觀和清晰多了:
-
select custid,
-
ifnull(sum(case when a=2009 then qty end),0) \"2009\",
-
ifnull(sum(case when a=2010 then qty end),0) \"2010\",
-
ifnull(sum(case when a=2011 then qty end),0) \"2011\"
-
from (select custid,year(orderdate) a,qty
-
from formatting) p
- group by custid;
但是當旋轉的元素非常多的說話,會產生較長的查詢字串
要縮短查詢的字串,可以預先產生一張矩陣表,包含每個要旋轉列的屬性:
-
create table formatting_temp(
-
orderyear int primary key,
-
y2009 int,
-
y2010 int,
- y2011 int);
-
insert into formatting_temp select 2009,1,0,0;
-
insert into formatting_temp select 2010,0,1,0;
- insert into formatting_temp select 2011,0,0,1;
將formatting和formatting_temp聯接
-
select custid,
-
sum(qty*y2009) "2009",
-
sum(qty*y2010) "2010",
-
sum(qty*y2011) "2011"
-
from(select custid,YEAR(orderdate) a,qty
-
from formatting) o
-
join formatting_temp p
-
on o.a=p.orderyear
- group by custid;
【Unpivoting】
-
create table unpivoting(
-
custid vahrchar(10),
-
y2009 int,
-
y2010 int,
- y2011 int);
-
insert into unpivoting
-
select custid,
-
ifnull(sum(case when a=2009 then qty end),0) \"2009\",
-
ifnull(sum(case when a=2010 then qty end),0) \"2010\",
-
ifnull(sum(case when a=2011 then qty end),0) \"2011\"
-
from (select custid,year(orderdate) a,qty
-
from formatting) p
- group by custid;
這裡是把之前的formatting旋轉之後的內容匯入到unpivoting表中。
解決方案及思路:
解決這個問題需要將列旋轉成為行,這裡使用的技巧是對每行資料產生3個副本
每個副本產生一個需要旋轉的列,這個過程可以透過如下的cross join來完成
-
select *
-
from unpivoting,(select 2009 as orderyear
-
union all select 2010
- union all select 2011) a;
結果為:
接下來,只需要根據orderyear列來去的對應旋轉列的值就可以了
由於最後要得到
過濾掉qty=0的情況
因此這個問題的最終解決方案為:
-
select custid,orderyear,qty
-
from(
-
select custid,orderyear
-
case orderyear
-
when 2009 then y2009
-
when 2010 then y2010
-
when 2011 then y2011
-
end as qty
-
from unpivoting,(select 2009 as orderyear
-
union all select 2010
-
union all select 2011) a
-
) b
- where qty <> 0;
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29773961/viewspace-1255327/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 利用xml publisher開發報表XML
- sql嵌入html格式顯示報表SQLHTML
- 為什麼說中國式報表特殊-4、報表的格式與資料
- mysql 行格式選擇_Mysql 行格式MySql
- 利用sqlplus手工建立巡檢報表SQL
- 更改XML報表預設輸出格式為EXCELXMLExcel
- 利用PHP指令碼刪除MySQL上所有的表PHP指令碼MySql
- sql入門之23 pivoting insert等SQL
- Mysql優化原則_小表驅動大表IN和EXISTS的合理利用MySql優化
- MyISAM表的儲存格式---行格式
- 利用c#+jquery+echarts生成統計報表(附原始碼)C#jQueryEcharts原始碼
- Oracle Developer 6i報表直接生成PDF檔案格式OracleDeveloper
- T-SQL 儲存過程建立 PDF 格式檔案(報表)SQL儲存過程
- ALV1:使用函式顯示ALV格式報表函式
- 利用儲存的成交訂單,生成實盤交易分析報表
- Linux從辦公自動化做起(轉)Linux
- mysql 日期格式化MySql
- Mysql 匯出txt格式MySql
- mysql的binlog格式MySql
- Python傳送帶附件的圖文格式報表指令碼(smtplib)Python指令碼
- 將Reporting Service報表轉換成.doc或pdf文件格式
- 利用MySQL原資料資訊批量轉換指定庫資料表生成Hive建表語句MySqlHive
- Mysql 行的儲存格式MySql
- 【mysql】關於binlog格式MySql
- MySQL的頁與行格式MySql
- 利用canvas生成海報Canvas
- Win10系統怎麼利用CMD命令匯出MySQL表資料Win10MySql
- 海量資料Excel報表利器——EasyExcel(一 利用反射機制匯出Excel)Excel反射
- EBS 單個報表(非報表集)根據報表名稱獲取報表源程式
- 埃森哲是如何系統化做好資料分析
- Java匯出Pdf格式表單Java
- json格式keyCode表JSON
- ActiveReports 報表應用教程 (3)---圖表報表
- MySQL輸出html格式檔案MySqlHTML
- mysql匯入csv格式檔案MySql
- mysql中時間日期格式化MySql
- iReport 報表、子報表、主從報表、合計、例項解析
- 利用MySQL全備份(mysqldump),如何只恢復一個庫或者一個表?MySql