Oracle--SQL行列轉換實戰
行列轉換例項 表ttt有三個欄位
seq --序列
jcxm --檢查專案
zhi --值
資料分別如下:
seq jcxm zhi
------- -------- --------
11 1 0.50
11 2 0.21
11 3 0.25
12 1 0.24
12 2 0.30
12 3 0.22
實現功能 建立檢視時移動行值為列值
create view v_view1
as
select seq,
sum(decode(jcxm,1, zhi)) 檢測專案1,
sum(decode(jcxm,2, zhi)) 檢測專案2,
sum(decode(jcxm,3, zhi)) 檢測專案3
from ttt
group by seq;
序號 檢測專案1 檢測專案2 檢測專案3
11 0.50 0.21 0.25
12 0.24 0.30 0.22
技巧: 用THEN中的0和1來進行統計(SUM)
jcxm zhi
---- ----
a 1
b 1
a 3
d 2
e 4
f 5
a 5
d 3
d 6
b 5
c 4
b 3
求他的zhi既是1,也是3,也是5的jcxm
方法一
select jcxm
from ttt
group by jcxm
having sum(decode(zhi,1,-1,3,-1,5,-1,0)) = -3
方法二
select jcxm from ttt
group by jcxm having (sign(sum(decode(zhi,1,-1,0)))+
sign(sum(decode(zhi,3,-1,0)))+sign(sum(decode(zhi,5,-1,0)))<=-3);
----------
a
b
說明:
sign()函式根據某個值是0、正數還是負數,分別返回0、1、-1
所以可以用sign和decode來完成比較欄位大小來區某個欄位
select decode(sign(欄位1-欄位2),-1,欄位3,欄位4) from dual;
sign是一個對於寫分析SQL有很強大的功能
下面我對sign進行一些總結:
但屬性student取0和1以外的值,或者student取兩個以上的標法值,問題就不會這麼簡單了
解決辦法就是特徵函式(abs(),sign())
常用的特徵演算法
[A=B]=1-abs(sign(A-B))
[A!=B]=abs(sign(A-B))
[A [A
[A>B]=1-sign(1-sign(A-B))
[A>=B]=sign(1+sign(A-B)))
[NOTα]=1-d [α]
[αANDb ]=d [α]*d [b ] (6)
[αOR b ]=sign(d [α]+d [b ])
例如:
A
A<=B Decode( Sign(A-B), 1, 0, 1 )
A>B Decode( Sign(A-B), 1, 1, 0 )
A>=B Decode( Sign(A-B), -1, 0, 1 )
A=B Decode( A, B, 1, 0 )
A between B and C Decode( Sign(A-B), -1, 0, Decode(Sign(A-C), 1, 0, 1 ))
A is null Decode(A,null,1,0)
A is not null Decode(A,null,0,1) A in (B1,B2,...,Bn) Decode(A,B1,1,B2,1,...,Bn,1,0)
nor LogA Decode( LogA, 0, 1, 0 ) (1-Sign(LogA))
LogA and LogB LogA * LogB
LogA or LogB LogA + LogB
LogA xor LogB Decode(Sign(LogA),Sign(LogB),0,1)
Mod(Sign(LogA),Sign(LogB),2
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
另外一個關於成績的分析例子
SELECT
SUM(CASE WHEN cj <60 THEN 1 ELSE 0 END) as "not passed",
SUM(CASE WHEN cj BETWEEN 60 AND 79 THEN 1 ELSE 0 END) as "passed",
SUM(CASE WHEN cj BETWEEN 80 AND 89 THEN 1 ELSE 0 END) as "good",
SUM(CASE WHEN cj >=90 THEN 1 ELSE 0 END) as "Excellent"
FROM cjtable;
decode用法2
表、檢視結構轉化
現有一個商品銷售表sale,表結構為:
month char(6) --月份
sell number(10,2) --月銷售金額
現有資料為:
200001 1000
200002 1100
200003 1200
200004 1300
200005 1400
200006 1500
200007 1600
200101 1100
200202 1200
200301 1300
想要轉化為以下結構的資料:
year char(4) --年份
------------ ---------------------
month1 number(10,2) --1月銷售金額
month2 number(10,2) --2月銷售金額
month3 number(10,2) --3月銷售金額
month4 number(10,2) --4月銷售金額
month5 number(10,2) --5月銷售金額
month6 number(10,2) --6月銷售金額
month7 number(10,2) --7月銷售金額
month8 number(10,2) --8月銷售金額
month9 number(10,2) --9月銷售金額
month10 number(10,2) --10月銷售金額
month11 number(10,2) --11月銷售金額
month12 number(10,2) --12月銷售金額
結構轉化的SQL語句為:
create or replace view
v_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)
as
select
substrb(month,1,4),
sum(decode(substrb(month,5,2),'01',sell,0)),
sum(decode(substrb(month,5,2),'02',sell,0)),
sum(decode(substrb(month,5,2),'03',sell,0)),
sum(decode(substrb(month,5,2),'04',sell,0)),
sum(decode(substrb(month,5,2),'05',sell,0)),
sum(decode(substrb(month,5,2),'06',sell,0)),
sum(decode(substrb(month,5,2),'07',sell,0)),
sum(decode(substrb(month,5,2),'08',sell,0)),
sum(decode(substrb(month,5,2),'09',sell,0)),
sum(decode(substrb(month,5,2),'10',sell,0)),
sum(decode(substrb(month,5,2),'11',sell,0)),
sum(decode(substrb(month,5,2),'12',sell,0))
from sale
group by substrb(month,1,4);
體會:要用decode /group by/ order by/sign/sum來實現不同報表的生成
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
CASE應用
1 1 部門a 800 男
2 2 部門b 900 女
3 3 部門a 400 男
4 4 部門d 1400 女
5 5 部門e 1200 男
6 6 部門f 500 男
7 7 部門a 300 女
8 8 部門d 1000 男
9 9 部門d 1230 女
10 10 部門b 2000 女
11 11 部門c 2000 男
12 12 部門b 1200 男
SELECT jcxm as 部門,COUNT(seq) as 人數,
SUM(CASE SEX WHEN 1 THEN 1 ELSE 0 END) as 男,
SUM(CASE SEX WHEN 2 THEN 1 ELSE 0 END) as 女,
SUM(CASE SIGN(zhi-800) WHEN -1 THEN 1 ELSE 0 END) as 小於800元,
SUM((CASE SIGN(zhi-800)*SIGN(zhi-1000) /*用*來實現功能*/
WHEN -1 THEN 1 ELSE 0 END)+(CASE zhi
WHEN 800 THEN 1 ELSE 0 END)) as 從800至999, /*注意別名不能以數字開頭*/
SUM((CASE SIGN(zhi-1000)*SIGN(zhi-1200)
WHEN -1 THEN 1 ELSE 0 END)+(CASE zhi
WHEN 1000 THEN 1 ELSE 0 END)) as 從1000元至1199元,
SUM((CASE SIGN(zhi-1200) WHEN 1 THEN 1 ELSE 0 END)
+(CASE zhi WHEN 1200 THEN 1 ELSE 0 END)) as 大於1200元
FroM ttt
GROUP BY jcxm
部門名 人數 男 女 小於800元 從800至999 從1000元至1199元 大於1200元
部門a 3 2 1 2 1 0 0
部門b 3 1 2 0 1 0 2
部門c 1 1 0 0 0 0 1
部門d 3 1 2 0 0 1 2
部門e 1 1 0 0 0 0 1
部門f 1 1 0 1 0 0 0
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/720091/viewspace-839286/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 行列轉換
- Kettle行列轉換
- 偽行列轉換!
- 行列轉換sqlSQL
- pivot、unpivot實現oracle行列轉換Oracle
- sql 實現表的行列轉換SQL
- 行列轉換 交叉表 (轉)
- Oracle-行列轉換Oracle
- MySQL行列轉換拼接MySql
- 行列轉換之大全~~~
- sql server 行列轉換SQLServer
- Spark實現行列轉換pivot和unpivotSpark
- WORD及EXCEL行列轉換實現方法收藏(轉)Excel
- oracle行列轉換-多行轉換成字串Oracle字串
- 行列轉換,列行轉換統計
- oracle行列轉換-行轉列Oracle
- oracle行列轉換-列轉行Oracle
- Restcloud ETl實踐之資料行列轉換RESTCloud
- 幾個行列轉換的實用小例子
- SQL Server 實現行列(縱橫表)轉換SQLServer
- 用ORACLE分析函式實現行列轉換Oracle函式
- mysql行列轉換詳解MySql
- sql server行列轉換案例SQLServer
- Oracle 行列轉換 經典Oracle
- Oracle 行列轉換總結Oracle
- Oracle 行列轉換小結Oracle
- 【SQL 學習】行列轉換SQL
- 【SQL】行列轉換方法示例SQL
- Oracle行列轉換總結Oracle
- Shell練習 行列轉換
- 複雜的行列轉換
- oracle行列轉換-字串轉換成多列Oracle字串
- oracle行列轉換-多列轉換成字串Oracle字串
- 試驗Oracle中實現行列轉換的方法(轉)Oracle
- 使用vue實現行列轉換的一種方法。Vue
- 在Word中實現表格的行列互換 (轉)
- 記錄一個行列轉換
- 通用的行列轉換的方法