oracle中通過decode實現行變列的二維表統計展示

表testa：

ID VARCHAR2(20)
USERNAME VARCHAR2(20)
STATUS VARCHAR2(20)

測試資料：

1 123  協商同意
2 rsr 未協商同意
3 rsr 協商同意
5 123 暫停
8 rsr 暫停
11 123  延遲
22 123  延遲
12 rsr  延遲

sql：

select username

,count(decode(status,'協商同意',status))  協商同意
,count(decode(status,'未協商同意',status))  未協商同意
,count(decode(status,'暫停',status)) 暫停
,count(decode(status,'延遲',status))   延遲

from testa group by username

展示效果：

感觸：oracle很強，通過sql可以簡單的實現二維的統計資訊，而無需改變表結構.

這裡涉及decode用法：

我一般都只用前三個欄位decode（欄位，比較值1，返回值）

---

Oracle中的二維表查詢函式

對於日常的報表統計,在oracle中的group by子句產生的查詢往往不夠直觀,還需要excel的透檢視才能產生二維報表.雖然在Oracle 11G中實現了這個功能,但我想鑑於我們目前還是10G的資料庫.寫這麼一個函式還是有必要的.
原始碼已經在下文中,直接在執行程式碼就可以得到函式GET_TWO_DIMENSIONAL_QUERY.
函式構成:該函式有3個引數,第一個引數是表名或者查詢語句,第二個引數是X軸欄位(僅允許單個欄位),第三個引數是Y軸欄位(允許多個欄位,欄位之間使用,分隔)
效果展示:
建立測試表:

create table TEST (name varchar2(255) ,sex varchar2(255),expenseability varchar2(255),district varchar2(255));
insert into TEST values ('張三','男','高消費','浦東新區');
insert into TEST values ('王強','男','高消費','浦東新區');
insert into TEST values ('馬駿','男','中高消費','徐彙區');
insert into TEST values ('可樂','女','中等消費','閔行區');
commit;

得到表:

NAME	SEX	EXPENSEABILITY	DISTRICT
張三	男	高消費	浦東新區
王強	男	高消費	浦東新區
馬駿	男	中高消費	徐彙區
可樂	女	中等消費	閔行區

執行查詢函式: select GET_TWO_DIMENSIONAL_QUERY('TEST','district','sex') from dual 後得到查詢反饋:

select sex,expenseability,sum (case when district='閔行區' then num else 0 end) as "閔行區",
sum (case when district='徐彙區' then num else 0 end) as "徐彙區",
sum (case when district='浦東新區' then num else 0 end) as "浦東新區"
from (select district,sex,expenseability,count(*) num from (TEST) group by district,sex,expenseability)
group by sex,expenseability

執行查詢反饋就可以得到二維報表:

SEX	EXPENSEABILITY	閔行區	徐彙區	浦東新區
男	高消費	0	0	2
女	中等消費	1	0	0
男	中高消費	0	1	0

函式原始碼:

create or replace function GET_TWO_DIMENSIONAL_QUERY (TABLENAME in varchar2 ,P_X in varchar2 ,P_Y in varchar2) 
return clob is 
Result clob;
type curtype is ref cursor;
v_cur curtype;
v_BaseQuery varchar2(4000);
v_sqlstr varchar2(4000);
v_temp varchar2(4000);
begin
--獲取group by 語句
v_BaseQuery:='(select '||P_X||','||P_Y||',count(*) num from ('||TABLENAME||') group by '||P_X||','||P_Y||')';
--選中Y軸欄位
Result:='select '||P_Y||',';
--產生遊標SQL
v_sqlstr :=' select distinct '||P_X||' from ('||v_BaseQuery||')';
--開啟遊標
open v_cur for v_sqlstr;
loop
fetch v_cur into v_temp;
exit when v_cur%notfound;

--判斷值是否為空
if v_temp is null then
--產生X軸SQL
Result:=Result||'sum (case when '||P_X||' is null then num else 0 end) as "'||P_X||'為空'||'",';
Result:=Result||chr(13); 
else
Result:=Result||'sum (case when '||P_X||'='''||v_temp||''' then num else 0 end) as "'||v_temp||'",';
Result:=Result||chr(13);
end if;

end loop; 

--格式對齊
Result:=rtrim(Result,chr(13));
Result:=rtrim(Result,',');

Result:=Result||chr(13);
--將from字句與group by 子句加入 
Result:=Result||'from '||v_BaseQuery||''||chr(13)||'group by '||P_Y||' ';

return Result;
end;

其實只是個程式碼生成函式,不過做報表用用還是挺方便的 ^_^

---

按照行、列進行統計（按兩個維度進行統計）

select * from u_md_dm.s_temp_import_d;

1、按照年和姓名來統計每個人每年的簽到次數

select k.dates,sum(case when k.pro_d_name='張三' then k.times else 0 end) 張三,sum(case when k.pro_d_name='李四' then k.times else 0 end) 李四,sum(case when k.pro_d_name='王五' then k.times else 0 end) 王五 
from (
select substr(pro_d_no,0,5) dates,pro_d_name,count(1) times from u_md_dm.s_temp_import_d group by substr(pro_d_no,0,5),pro_d_name) k
group by k.dates；

另：也可以用下面的語句實現：

with k as
(select substr(pro_d_no,0,5) dates,pro_d_name,count(1) times from u_md_dm.s_temp_import_d group by substr(pro_d_no,0,5),pro_d_name)
select 
k.dates,sum(case when k.pro_d_name='張三' then k.times else 0 end) 張三,sum(case when k.pro_d_name='李四' then k.times else 0 end) 李四,sum(case when k.pro_d_name='王五' then k.times else 0 end) 王五 
from k group by k.dates