iT邦幫忙

0

Oracle SQL pivot 問題

  • 分享至 

  • xImage

建立範例表

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 }} |
{{ formatDate(item.duration) }}
直播中

1 個回答

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)

https://ithelp.ithome.com.tw/upload/images/20220118/20050647YbSaqD9YsY.png

看更多先前的回應...收起先前的回應...
尼克 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上語法有錯誤。
感謝協助。

Oracle 可能這裡要加上 from dual

select '2020' from dual
尼克 iT邦大師 1 級 ‧ 2022-01-18 11:44:41 檢舉
(SELECT *
            FROM (SELECT '2020' FROM DUAL) xyear)

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

要不要用MV(materialized view).

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

請指點!沒用過。

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等等)的值
已經產生好放著等.

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 能提供我原本的功能改善是那一部份?

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

如果屠豬士給的處裡方式可行, 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;

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

-- 假設再輸入類似的資料,但是 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

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

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

mv 是用 refresh方式更新.不需要再建立.
尼克大大可以趁此機會引進,這對資料分析有很大的幫助.

我要發表回答

立即登入回答