SQL行列转换实战

isiqi

浏览: 16348058 次
性别:
来自: 济南

最近访客更多访客>>

nison

hellohank

wangyy

devcang

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

2012-07 ( 335)
2012-06 ( 252)
2012-05 ( 362)
更多存档...

SQL 数据结构算法 F#

表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<B］＝1－sign（1＋sign（A－B））不能用-sign（A－B）：因为如果不满足A<b则返回-1，而不是0，这样就不能用在字段选择上了
［A<＝B］＝sign(1－sign（A－B））
［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<B Decode( Sign(A-B), -1, 1, 0 )
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 800THEN 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
部门c1 1 0 0 0 0 1
部门d3 1 2 0 0 1
部门e 1 1 0 0 00 1
部门f 1 1 0 1 0 00