Month: November 2018

Oracle chuyển hàng thành cột dữ liệu

Trong việc làm báo cáo ta thường xuyên gặp các tình huống thống kê từ raw data phải chuyển dữ liệu từ hàng sang cột. Ta có thể sử dụng Excel để xử lý nhưng từ Oracle 10i thì có hỗ trợ câu lệnh PIVOT để xử lý.
Ta có thể test thử hàm này với giả dữ liệu như sau:

+ Chuẩn bị bảng và data:

—-create table

CREATE TABLE vn_temp_test_pivote (
C_MONTH VARCHAR2(15) NULL,
C_CORIG VARCHAR2(15) NULL,
C_VOLUME NUMBER(5) NULL);

—-test data

insert into vn_temp_test_pivote
values('201801','A',123);

insert into vn_temp_test_pivote
values('201801','B',121);

insert into vn_temp_test_pivote
values('201801','C',122);

insert into vn_temp_test_pivote
values('201802','A',1);

insert into vn_temp_test_pivote
values('201802','B',2);

insert into vn_temp_test_pivote
values('201802','C',3);

insert into vn_temp_test_pivote
values('201803','A',10);

insert into vn_temp_test_pivote
values('201803','B',20);

insert into vn_temp_test_pivote
values('201803','C',30);

Đây ví dụ ta có số lượng view(C_VOLUME) của từng C_CORIG trong từng tháng(C_MONTH).
Dữ liệu được trình bày:

C_MONTH C_CORIG C_VOLUME
201801 A 123
201801 B 121
201801 C 122
201802 A 1
201802 B 2
201802 C 3
201803 A 10
201803 B 20
201803 C 30

+ Ta cần nhìn xem số lượng view của các tháng theo từng loại C_CORIG, ta sử dụng hàm PIVOTE như sau:

SELECT *
FROM vn_temp_test_pivote PIVOT (SUM(C_volume)
FOR C_MONTH
IN ('201801' AS p_201801,
'201802' AS p_201802,
'201803' AS p_201803));

Kết quả:

C_CORIG p_201801 p_201802 p_201803
A 123 1 10
B 121 2 20
C 122 3 30

+ Như ta thấy ở ví dụ trên, nhược điểm của PIVOTE là ta phải liệt kê từng dữ liệu của cột cần chuyển sang hàng, nhưng ta có thể lấy tự động các tháng phát sinh không?
Oracle có hỗ trợ việc này nhưng PIVOTE chỉ có thể hiển thị data nằm trong XML:

SELECT *
FROM vn_temp_test_pivote PIVOT XML (SUM (C_VOLUME)
FOR C_MONTH
IN (SELECT DISTINCT C_MONTH FROM vn_temp_test_pivote));

Kết quả:

C_CORIG XML
A <XML>
B <XML>
C <XML>

Để lấy dữ liệu ra để trình bày ta phải đọc chuỗi XML theo khuôn dạng để trình bày ra.

SELECT C_CORIG,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[1]/column[2]') p1,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[2]/column[2]') p2,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[3]/column[2]') p3,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[4]/column[2]') p4,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[5]/column[2]') p5,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[6]/column[2]') p6,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[7]/column[2]') p7,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[8]/column[2]') p8,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[9]/column[2]') p9,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[10]/column[2]') p10,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[11]/column[2]') p11,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[12]/column[2]') p12,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[13]/column[2]') p13,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[14]/column[2]') p14,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[15]/column[2]') p15,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[16]/column[2]') p16,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[17]/column[2]') p17,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[18]/column[2]') p18,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[19]/column[2]') p19,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[20]/column[2]') p20,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[21]/column[2]') p21,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[22]/column[2]') p22,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[23]/column[2]') p23,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[24]/column[2]') p24,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[25]/column[2]') p25,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[26]/column[2]') p26,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[27]/column[2]') p27,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[28]/column[2]') p28,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[29]/column[2]') p29,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[30]/column[2]') p30
FROM (
SELECT *
FROM vn_temp_test_pivote PIVOT XML (SUM (C_VOLUME)
FOR C_MONTH
IN (SELECT DISTINCT C_MONTH FROM vn_temp_test_pivote))

) ;

Kết quả:

C_CORIG p1 p2 p3 p4 p5 p6...
A 123 1 10 null null
B 121 2 20 null null
C 122 3 30 null null

Phần này ta có thể liệt kê dài ra hoặc có thể check trước sau đó gen câu lệnh rồi ghép vào với nhau nếu muốn đẹp.
Được như thế này rồi nhưng ta vẫn chỉ là định nghĩa cột khi viết thôi, để map lại cột tự động(p1,p2…) với data(201801,201802…) ta sử dụng lấy dữ liệu ở cột 1 và chỉ lấy 1 dòng (vì tất cả các dòng là như nhau) sau đó UNION ALL lại là được:

SELECT '1_Code Origin' Code_Origin,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[1]/column[1]') A1,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[2]/column[1]') A2,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[3]/column[1]') A3,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[4]/column[1]') A4,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[5]/column[1]') A5,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[6]/column[1]') A6,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[7]/column[1]') A7,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[8]/column[1]') A8,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[9]/column[1]') A9,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[10]/column[1]') A10,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[11]/column[1]') A11,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[12]/column[1]') A12,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[13]/column[1]') A13,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[14]/column[1]') A14,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[15]/column[1]') A15,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[16]/column[1]') A16,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[17]/column[1]') A17,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[18]/column[1]') A18,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[19]/column[1]') A19,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[20]/column[1]') A20,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[21]/column[1]') A21,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[22]/column[1]') A22,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[23]/column[1]') A23,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[24]/column[1]') A24,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[25]/column[1]') A25,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[26]/column[1]') A26,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[27]/column[1]') A27,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[28]/column[1]') A28,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[29]/column[1]') A29,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[30]/column[1]') A30
FROM (
SELECT *
FROM vn_temp_test_pivote PIVOT XML (SUM (C_VOLUME)
FOR C_MONTH
IN (SELECT DISTINCT C_MONTH FROM vn_temp_test_pivote))

) where rownum=1
UNION
SELECT C_CORIG,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[1]/column[2]') p1,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[2]/column[2]') p2,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[3]/column[2]') p3,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[4]/column[2]') p4,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[5]/column[2]') p5,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[6]/column[2]') p6,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[7]/column[2]') p7,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[8]/column[2]') p8,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[9]/column[2]') p9,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[10]/column[2]') p10,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[11]/column[2]') p11,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[12]/column[2]') p12,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[13]/column[2]') p13,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[14]/column[2]') p14,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[15]/column[2]') p15,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[16]/column[2]') p16,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[17]/column[2]') p17,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[18]/column[2]') p18,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[19]/column[2]') p19,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[20]/column[2]') p20,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[21]/column[2]') p21,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[22]/column[2]') p22,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[23]/column[2]') p23,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[24]/column[2]') p24,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[25]/column[2]') p25,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[26]/column[2]') p26,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[27]/column[2]') p27,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[28]/column[2]') p28,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[29]/column[2]') p29,
EXTRACTVALUE (C_MONTH_XML, '/PivotSet/item[30]/column[2]') p30
FROM (
SELECT *
FROM vn_temp_test_pivote PIVOT XML (SUM (C_VOLUME)
FOR C_MONTH
IN (SELECT DISTINCT C_MONTH FROM vn_temp_test_pivote))

);

Kết quả:

C_CORIG p1 p2 p3 p4 p5 p6...
C_CORIG 201801 201802 201803...
A 123 1 10 null null
B 121 2 20 null null
C 122 3 30 null null

Oracle thông báo về trạng thái các TABLESPACE

Mình cũng chưa nắm được rõ về cấu trúc đặt dữ liệu của Oracle lắm, chỉ biết rằng data sẽ được đặt trong TABLESPACE. Trong 1 DATABASE có nhiều TABLESPACE, mỗi TABLESPACE có thể chứa 1 hoặc nhiều TABLE, nhưng 1 TABLE chỉ được nằm trong 1 TABLESPACE duy nhất.

TABLESPACE được khai báo khi tạo, có địa chỉ đặt ở ổ cứng xác định và được khai báo dung lượng trước. Nếu lượng data vượt quá dung lượng TABLESPACE thì hệ thống sẽ không ghi vào được nữa. Do vậy phải thường xuyên kiểm tra TABLESPACE xem có cái nào gần đầy thì phải nới ra, có thể nới rộng nhiều nhưng thường người ta nới dần dần, dùng đến đâu thì nới tới đó.

Trong thực tế triển khai, nhiều trường hợp không để ý đến TABLESPACE đầy hệ thống sẽ gặp lỗi không thể ghi dữ liệu nên việc DBA phải thường xuyên kiểm tra và nới TABLESPACE nếu cần.

Để kiểm tra thông tin về TABLESPACE ta có thể sử dụng câu lệnh SQL sau để lấy thông tin:

 

select b.tablespace_name,
b.MEGS,
(b.MEGS - a.MEGS_FREE) as ALLOCATED_MEGS,
round(((b.MEGS - a.MEGS_FREE) / b.MEGS) * 100) PCT_USED,
a.MEGS_FREE, EXTENSIBLE_MEGS
from (select tablespace_name, round(sum(bytes / 1024 / 1024)) MEGS_FREE
from dba_free_space
group by tablespace_name) a,
(select tablespace_name, round(sum((bytes / 1024 / 1024)),0) MEGS,
round(
sum(
case when autoextensible = 'YES' then (maxbytes - bytes)/1024/1024
else 0
end
),0) EXTENSIBLE_MEGS
from dba_data_files
group by tablespace_name
) b
where a.tablespace_name(+) = b.tablespace_name
order by 4 desc;

 

Với các thông tin này ta sẽ biết tình trạng của từng TABLESPACE và nới ra nếu cần.

Ta có thể tự động hóa hàng ngày thông báo qua email tình trạng của TABLESPACE bằng cách đặt crontab run câu lệnh này và gửi email.
Cao cấp hơn, ta cũng có thể làm cho hệ thống tự động kiểm tra TABLESPACE nào đầy thì tiến hành nới ra 1 khoảng. Ví dụ câu lệnh SQL trên ta check xem TABLESPACE nào có dung lượng còn dưới 10% thì tiến hành tự động nới TABLESPACE đó thêm 10% dung lượng hiện có và thông báo tới DBA.

Một bài viết chi tiết về TABLESPACE, rảnh đọc sau: http://www.oravn.com/viewtopic.php?t=48

Adobe đã ứng dụng trí tuệ nhân tạo trong sản phẩm Photoshop

Có một người bạn đã gửi cho mình về một đoạn clip mô tải lại việc Photoshop tự động có thể tách đối tượng ra khỏi ảnh sử dụng Trí tuệ nhân tạo.

Đây có lẽ là thứ đầu tiên về ứng dụng thực tiễn nhất mà mình thấy hay. Bởi vì trước còn thời sinh viên mình cũng hay vọc Photoshop để cắt ảnh gép linh tinh hoặc chỉnh sửa vớ vẩn, việc cắt này đặc biệt khó khăn đối với những hình góc cạnh, phức tạp đặc biệt với tóc hoặc lẫn màu với nền hoặc vật khác. Với Trí tuệ nhân tạo có thể tách được đối tượng do có thể cho việc học máy học các đối tượng cụ thể để có thể tách được.

Nhìn qua demo thấy khá ngon lành, không biết thành phẩm có được như mong đợi không, dù sao cũng chờ để được sử dụng để việc cắt đối tượng từ ảnh được dễ dàng hơn.

Một số bài toán để ôn luyện tư duy khi ôn đội tuyển Olympic

– Bài toán hình xoắn ốc: Bài này dạng cơ bản là cho bình phương của một số nguyên n, tìm cách in ra màn hình vị trí các số bắt đầu từ 1 đến chính nó vào ma trận vuông n*n. Bài toán này luyện cho mình cách điều khiển vòng lặp for và while cho hợp lý và tối ưu. Có nhiều biến thế của bài này để ta có thể ôn luyện.

– Bài toán dò mìn, tìm miền liên thông…: một số bài toán cần sử dụng thuật toán loang để xử lý. Những bài toán như này liên quan đến việc xử lý các điểm lân cận nhau, cần lưu ý việc đánh dấu các điểm đã duyệt tránh bị lặp vô tận dẫn đến tràn bộ nhớ.

– Cộng, trừ, nhân số lớn: Với kiểu dữ liệu của ngôn ngữ lập trình thường bị giới hạn ở mức độ nhất định, để xử lý cộng, trừ, nhân với các con số có từ 10 chữ số trở lên là những số siêu siêu lớn thì kiểu dữ liệu của ngôn ngữ không thế xử lý được. Việc này buộc phải xử lý thủ công, chia để trị. Thực hiện phép tính như cách mà ta được học từ nhỏ. Đối với cộng-trừ ta dùng mảng để lưu từng chữ số sau đó xử lý cộng trừ từng số 1 theo đúng nguyên tắc từ hàng nhỏ hơn đến hàng lớn (từ hàng đơn vị, từ phải sang trái) và tuân thủ nguyên tắc cộng dồn (chính là nhớ). Đối với phép nhân thì tương tự nhưng lưu ý phép nhân chính là phép cộng nhiều số lại với nhau mà thôi.

– Số nguyên tố: Với số nguyên tố thì có muôn vàn kiểu bài toán liên quan, vì số nguyên tố có nhiều tính chất rất “đẹp”. Các bài toán đa phần là tìm số nguyên tố, check xem có phải số nguyên tố hay không, tìm số nguyên tố cùng nhau… Các bài toán này sẽ có 1 số thuật toán kinh điển để tìm, các bạn có thể tìm hiểu.

– Đệ quy: Bài toán kinh điển của đệ quy chính là bài toán Tháp Hà Nội. Cũng là một cách chia để trị, các bài toán đều quy về cùng 1 dạng, và chỉ cần xử lý được bài toán ở nhân thì sẽ giải được. Với đệ quy thường tốn bộ nhớ đệm để chờ từng lần lượt kết quả của từng lượt đệ quy nến có trường hợp tràn bộ nhớ đệm. Bài toán đệ quy thì có đệ quy quay lui nổi tiếng với bài toán 8 con hậu.

– Một số bài toán khác: tìm đường đi ngắn nhất, quy hoạch động… một số bài thuần toán như: số fibonaci, tính tích phân, tìm căn, tìm nghiệm gần đúng…

Tối ưu (turning) câu lệnh PL/SQL: Việc JOIN và lấy dữ liệu trong câu lệnh SELECT

Trước đây ở công ty C mình cũng ít làm việc về Report nhưng các hàm query lấy dữ liệu thì cũng phải dùng thường xuyên. Các anh làm về Database cũng hay cảnh báo mọi người việc sử dụng câu lệnh SELECT ALL thì không được dùng. Việc SELECT ALL là câu lệnh SELECT *, cũng như việc SELECT những cái gì cần lấy chứ không nên lấy những cột mà không cần dùng.
Câu lệnh SELECT liên quan đến chỉ số I/O của hệ thống. SELECT càng nhiều thì càng làm tăng I/O và ảnh hưởng đến hiệu năng của hệ thống. Nôm na I/O là Input và Output giữa đĩa cứng để dữ liệu và RAM của server, dữ liệu sẽ đi qua cổng I/O này và nếu lớn nó sẽ làm ngẽn hệ thống.
Ví dụ điển hình là câu lệnh:
(1) SELECT COUNT(*) FROM TABLE_A WHERE A=B;
sẽ cho cùng kết quả với câu lệnh:
(2) SELECT COUNT(COLUMN_A1) FROM TABLE_A WHERE A=B;
và câu lệnh:
(3) SELECT COUNT(1) FROM TABLE_A WHERE A=B;

Với câu lệnh 1, hệ thống sẽ lấy toàn bộ các cột dữ liệu và tiến hành đếm các dòng ở các cột này. Việc này sẽ làm tăng I/O ảnh hưởng đến hệ thống và tất nhiên chạy chậm hơn.
Với câu lệnh 2, hệ thống sẽ lấy 1 cột COLUMN_A1 dữ liệu và tiến hành đếm các dòng ở các cột này. Câu lệnh này hệ thống cũng sẽ lấy dữ liệu ở cột COLUMN_A1 ra và đếm, I/O tốn ít hơn và câu lệnh sẽ nhanh hơn.
Với câu lệnh 3, hệ thống sẽ tự hiểu việc sẽ đếm toàn bộ các dòng trong bảng theo điều kiện gì đó. Hệ thống sẽ cần rất ít I/O nên câu lệnh sẽ nhanh hơn và đảm bảo được hiệu năng của hệ thống.

Từ lý thuyết về I/O và ví dụ trên, ta có thể tối ưu các câu lệnh JOIN và lấy dữ liệu sao cho hợp lý, tối ưu được hiệu năng hệ thống nhất là chỉ số I/O. Tránh việc gây nghẽn hệ thống, làm hệ thống giảm được tải, các câu lệnh chạy được nhanh hơn.

Ví dụ:
Câu lệnh chưa tối ưu: Lấy lương từng nhân sự phòng IT của tháng 12/2018

SELECT EP.ID,EP.NAME,SL.AMOUNT
FROM EMPLOYEE EP, SALARY SL
WHERE EP.ID=SL.ID
AND EP.IDDE='IT'
AND SL.MONTH='201812';

Ta có thể tối ưu lại để đỡ tốn I/O như sau:

SELECT EP.ID,EP.NAME,SL.AMOUNT
FROM (SELECT ID,NAME FROM EMPLOYEE WHERE IDDE='IT') EP, (SELECT EPID,AMOUNT FROM SALARY WHERE MONTH='201812') SL
WHERE EP.ID=SL.EPID;

Phần này ta đã giảm I/O bằng cách thay vì lấy toàn bộ dữ liệu bảng SALARY, EMPLOYEE ra để tiến hành JOIN sau đó mới lọc thì ta tiến hành lọc và lấy xác định các cột từ trước sau đó mới tiến hành JOIN vào với các điều kiện để lấy dữ liệu.
Đây chỉ là một ví dụ đơn giản để ta hiểu việc chỉ lấy dữ liệu cần thiết khi dùng thôi, còn thực tế thì phải tùy theo độ phức tạp để lấy dữ liệu một cách hiệu quả.