Oracle SQL pivot 問題

oracle sql pivot

尼克 2022-01-17 17:38:15 ‧ 1289 瀏覽

分享至

建立範例表

CREATE TABLE TEST_PIVOT
(
  CUSTOMER_ID    VARCHAR2(10 BYTE),
  CUSTOMER_NAME  VARCHAR2(10 BYTE),
  YYMM           VARCHAR2(6 BYTE),
  NT_PRICE       NUMBER
)

建立範例資料

insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('A', 'AAA', '202001', 10000);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('B', 'BBB', '202002', 5000);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('C', 'CCC', '202003', 1000);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('D', 'DDD', '202004', 70);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('E', 'EEE', '202005', 76501);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('F', 'FFF', '202006', 1532);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('G', 'GGG', '202007', 5640);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('H', 'HHH', '202008', 8730);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('I', 'III', '202009', 87530);
commit;

程式

  SELECT customer_id,
         customer_name,
         SUM (CASE WHEN yymm = '202001' THEN nt_price END)     y202001,
         SUM (CASE WHEN yymm = '202002' THEN nt_price END)     y202002,
         SUM (CASE WHEN yymm = '202003' THEN nt_price END)     y202003,
         SUM (CASE WHEN yymm = '202004' THEN nt_price END)     y202004,
         SUM (CASE WHEN yymm = '202005' THEN nt_price END)     y202005,
         SUM (CASE WHEN yymm = '202006' THEN nt_price END)     y202006,
         SUM (CASE WHEN yymm = '202007' THEN nt_price END)     y202007,
         SUM (CASE WHEN yymm = '202008' THEN nt_price END)     y202008,
         SUM (CASE WHEN yymm = '202009' THEN nt_price END)     y202009                                             
    FROM test_pivot
GROUP BY customer_id, customer_name

請問一下若是yymm = '202001' 這是動態數值時候，如何用select 語法修改上面程式？
因為，我要用oracle view做程式應用！
感謝！

石頭 iT邦高手 1 級 ‧ 2022-01-17 19:12:56 檢舉

你要找尋 dynamic pivot
https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm#LNPLS01115

石頭 iT邦高手 1 級 ‧ 2022-01-17 19:13:40 檢舉

https://stackoverflow.com/questions/15491661/dynamic-pivot-in-oracle-sql

尼克 iT邦大師 1 級 ‧ 2022-01-18 08:44:44 檢舉

感謝你的回覆，我知道若是用procedure可以達成，我現在只能用view。

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

一級屠豬士

iT邦大師 1 級 ‧ 2022-01-18 10:47:14

最佳解答

使用PG測試了下

create table test_pivot(
  customer_id    text,
  customer_name  text,
  yymm           text,
  nt_price       numeric
);

insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('A', 'AAA', '202001', 10000);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('B', 'BBB', '202002', 5000);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('C', 'CCC', '202003', 1000);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('D', 'DDD', '202004', 70);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('E', 'EEE', '202005', 76501);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('F', 'FFF', '202006', 1532);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('G', 'GGG', '202007', 5640);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('H', 'HHH', '202008', 8730);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('I', 'III', '202009', 87530);

commit;


---

SELECT customer_id,
         customer_name,
         SUM (CASE WHEN yymm = xyear || '01' THEN nt_price END)     y202001,
         SUM (CASE WHEN yymm = xyear || '02' THEN nt_price END)     y202002,
         SUM (CASE WHEN yymm = xyear || '03' THEN nt_price END)     y202003,
         SUM (CASE WHEN yymm = xyear || '04' THEN nt_price END)     y202004,
         SUM (CASE WHEN yymm = xyear || '05' THEN nt_price END)     y202005,
         SUM (CASE WHEN yymm = xyear || '06' THEN nt_price END)     y202006,
         SUM (CASE WHEN yymm = xyear || '07' THEN nt_price END)     y202007,
         SUM (CASE WHEN yymm = xyear || '08' THEN nt_price END)     y202008,
         SUM (CASE WHEN yymm = xyear || '09' THEN nt_price END)     y202009                                        
  FROM test_pivot
     , (select '2020') as x(xyear)
 GROUP BY customer_id, customer_name;

 customer_id | customer_name | y202001 | y202002 | y202003 | y202004 | y202005 | y202006 | y202007 | y202008 | y202009 
-------------+---------------+---------+---------+---------+---------+---------+---------+---------+---------+---------
 A           | AAA           |   10000 |    NULL |    NULL |    NULL |    NULL |    NULL |    NULL |    NULL |    NULL
 D           | DDD           |    NULL |    NULL |    NULL |      70 |    NULL |    NULL |    NULL |    NULL |    NULL
 E           | EEE           |    NULL |    NULL |    NULL |    NULL |   76501 |    NULL |    NULL |    NULL |    NULL
 H           | HHH           |    NULL |    NULL |    NULL |    NULL |    NULL |    NULL |    NULL |    8730 |    NULL
 I           | III           |    NULL |    NULL |    NULL |    NULL |    NULL |    NULL |    NULL |    NULL |   87530
 B           | BBB           |    NULL |    5000 |    NULL |    NULL |    NULL |    NULL |    NULL |    NULL |    NULL
 G           | GGG           |    NULL |    NULL |    NULL |    NULL |    NULL |    NULL |    5640 |    NULL |    NULL
 C           | CCC           |    NULL |    NULL |    1000 |    NULL |    NULL |    NULL |    NULL |    NULL |    NULL
 F           | FFF           |    NULL |    NULL |    NULL |    NULL |    NULL |    1532 |    NULL |    NULL |    NULL
(9 rows)

回應 13
分享
檢舉

看更多先前的回應...收起先前的回應...

尼克 iT邦大師 1 級 ‧ 2022-01-18 11:16:42 檢舉

SELECT customer_id,
         customer_name,
         SUM (CASE WHEN yymm = xyear || '01' THEN nt_price END)     y202001,
         SUM (CASE WHEN yymm = xyear || '02' THEN nt_price END)     y202002,
         SUM (CASE WHEN yymm = xyear || '03' THEN nt_price END)     y202003,
         SUM (CASE WHEN yymm = xyear || '04' THEN nt_price END)     y202004,
         SUM (CASE WHEN yymm = xyear || '05' THEN nt_price END)     y202005,
         SUM (CASE WHEN yymm = xyear || '06' THEN nt_price END)     y202006,
         SUM (CASE WHEN yymm = xyear || '07' THEN nt_price END)     y202007,
         SUM (CASE WHEN yymm = xyear || '08' THEN nt_price END)     y202008,
         SUM (CASE WHEN yymm = xyear || '09' THEN nt_price END)     y202009                                        
  FROM test_pivot
     , (select '2020') as x(xyear)
 GROUP BY customer_id, customer_name;

這一段語法，我在Oracle上語法有錯誤。
感謝協助。

一級屠豬士 iT邦大師 1 級 ‧ 2022-01-18 11:22:51 檢舉

Oracle 可能這裡要加上 from dual

select '2020' from dual

尼克 iT邦大師 1 級 ‧ 2022-01-18 11:44:41 檢舉

(SELECT *
            FROM (SELECT '2020' FROM DUAL) xyear)

若是單獨這樣可以，若是放到裡面又不行。

一級屠豬士 iT邦大師 1 級 ‧ 2022-01-18 12:08:10 檢舉

要不要用MV(materialized view).

尼克 iT邦大師 1 級 ‧ 2022-01-18 13:35:21 檢舉

請指點！沒用過。

一級屠豬士 iT邦大師 1 級 ‧ 2022-01-18 14:49:09 檢舉

materialized view 是 Oracle 一個很強大的功能, PG也有,但沒有Oracle 的強. MV 可以用來事先產生實體的view,做資料分析很方便,但不是用作OLTP. 可以使用Oracle 內部排程來更新. 以前我都是
利用系統較不忙碌的時段,排程來refresh mv.這也是通常的用法.
https://docs.oracle.com/database/121/DWHSG/refresh.htm#DWHSG-GUID-64068234-BDB0-4C12-AE70-75571046A586
後續Oracle 較新的版本,MV就更強大了.
https://oracle-base.com/articles/misc/materialized-views
這裡可以看到要使用DBMS_REFRESH.make,設定interval.
https://oracle-base.com/articles/12c/real-time-materialized-views-12cr2
12cR2 以後,有新的功能,除了初始建立時,後續是Real-Time 的更新.
這樣會比一般的view更快,因為一些聚合(sum,avg,count等等)的值
已經產生好放著等.

一級屠豬士 iT邦大師 1 級 ‧ 2022-01-18 14:55:15 檢舉

https://docs.oracle.com/en/database/oracle/oracle-database/21/dwhsg/advanced-materialized-views.html#GUID-0702359B-D379-4299-86C4-2958BCD4381D

21c 裡面關於 Real-time Materialized Views 的文件,請參考.這是Oracle 比其他DB強大的地方.

尼克 iT邦大師 1 級 ‧ 2022-01-19 18:20:35 檢舉

感謝你提供資訊，有一事不明白，Materizlized Views 能提供我原本的功能改善是那一部份？

喵凹咿唉思嗯 iT邦研究生 5 級 ‧ 2022-01-19 18:33:07 檢舉

日安, 不知道你想處理到啥程度

如果屠豬士給的處裡方式可行, Oracle下的語法請參考

SELECT customer_id,
         customer_name,
         SUM (CASE WHEN yymm = xyear || '01' THEN nt_price END)     y202001,
         SUM (CASE WHEN yymm = xyear || '02' THEN nt_price END)     y202002,
         SUM (CASE WHEN yymm = xyear || '03' THEN nt_price END)     y202003,
         SUM (CASE WHEN yymm = xyear || '04' THEN nt_price END)     y202004,
         SUM (CASE WHEN yymm = xyear || '05' THEN nt_price END)     y202005,
         SUM (CASE WHEN yymm = xyear || '06' THEN nt_price END)     y202006,
         SUM (CASE WHEN yymm = xyear || '07' THEN nt_price END)     y202007,
         SUM (CASE WHEN yymm = xyear || '08' THEN nt_price END)     y202008,
         SUM (CASE WHEN yymm = xyear || '09' THEN nt_price END)     y202009                                        
  FROM test_pivot
     , (select '2020' xyear from dual) B
 GROUP BY customer_id, customer_name;

一級屠豬士 iT邦大師 1 級 ‧ 2022-01-19 18:36:41 檢舉

MV 是可以先產生聚合,例如把各個年份的資料都聚合好,你只要MV那個年份的資料就好.這是OLAP部分的作法.

一級屠豬士 iT邦大師 1 級 ‧ 2022-01-19 18:56:52 檢舉

-- 假設再輸入類似的資料,但是 2021年
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('A', 'AAA', '202101', 10000);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('B', 'BBB', '202102', 5000);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('C', 'CCC', '202103', 1000);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('D', 'DDD', '202104', 70);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('E', 'EEE', '202105', 76501);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('F', 'FFF', '202106', 1532);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('G', 'GGG', '202107', 5640);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('H', 'HHH', '202108', 8730);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('I', 'III', '202109', 87530);

create materialized view mv_test_pivot as
select customer_id, customer_name, left(yymm, 4) as xyear
     , SUM (CASE WHEN right(yymm,2) = '01' THEN nt_price END) as m01
     , SUM (CASE WHEN right(yymm,2) = '02' THEN nt_price END) as m02
     , SUM (CASE WHEN right(yymm,2) = '03' THEN nt_price END) as m03
     , SUM (CASE WHEN right(yymm,2) = '04' THEN nt_price END) as m04
     , SUM (CASE WHEN right(yymm,2) = '05' THEN nt_price END) as m05
     , SUM (CASE WHEN right(yymm,2) = '06' THEN nt_price END) as m06
     , SUM (CASE WHEN right(yymm,2) = '07' THEN nt_price END) as m07
     , SUM (CASE WHEN right(yymm,2) = '08' THEN nt_price END) as m08
     , SUM (CASE WHEN right(yymm,2) = '09' THEN nt_price END) as m09
  from test_pivot
 group by customer_id, customer_name, left(yymm, 4);

-- 查詢mv 即可.
select *
  from mv_test_pivot
 order by xyear, customer_id;

```
-- 假設再輸入類似的資料,但是 2021年
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('A', 'AAA', '202101', 10000);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('B', 'BBB', '202102', 5000);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('C', 'CCC', '202103', 1000);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('D', 'DDD', '202104', 70);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('E', 'EEE', '202105', 76501);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('F', 'FFF', '202106', 1532);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('G', 'GGG', '202107', 5640);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('H', 'HHH', '202108', 8730);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('I', 'III', '202109', 87530);

create materialized view mv_test_pivot as
select customer_id, customer_name, left(yymm, 4) as xyear
     , SUM (CASE WHEN right(yymm,2) = '01' THEN nt_price END) as m01
     , SUM (CASE WHEN right(yymm,2) = '02' THEN nt_price END) as m02
     , SUM (CASE WHEN right(yymm,2) = '03' THEN nt_price END) as m03
     , SUM (CASE WHEN right(yymm,2) = '04' THEN nt_price END) as m04
     , SUM (CASE WHEN right(yymm,2) = '05' THEN nt_price END) as m05
     , SUM (CASE WHEN right(yymm,2) = '06' THEN nt_price END) as m06
     , SUM (CASE WHEN right(yymm,2) = '07' THEN nt_price END) as m07
     , SUM (CASE WHEN right(yymm,2) = '08' THEN nt_price END) as m08
     , SUM (CASE WHEN right(yymm,2) = '09' THEN nt_price END) as m09
  from test_pivot
 group by customer_id, customer_name, left(yymm, 4);

-- 查詢mv 即可.
select *
  from mv_test_pivot
 order by xyear, customer_id;
```

![https://ithelp.ithome.com.tw/upload/images/20220119/20050647SbnaNLKqCF.png](https://ithelp.ithome.com.tw/upload/images/20220119/20050647SbnaNLKqCF.png)

修改

尼克 iT邦大師 1 級 ‧ 2022-01-20 08:18:17 檢舉

真是厲害！受我一拜！
感謝！