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ả.

Tối ưu câu lệnh SQL: Oracle so sánh thời gian

Ở bài viết Oracle so sánh thời gian ngày tháng – Oracle compare date time mình có chia sẻ về cách ta có thể so sánh thời gian trong Oracle. Nhưng trong quá trình triển khai thực thế, việc turning hệ thống thì thấy việc sử dụng các câu lệnh này sẽ có vấn đề về index trên cột này. Ta cùng phân tích và tối ưu câu lệnh SQL cho vấn đề này nhé.

Đối với câu lệnh PL/SQL, cụ thể là Oracle thì hàm to_char(…) gây mất index đối với cột dữ liệu kiểu DATETIME. Tương tự với việc sử dụng toán tử LIKE cho kiểu dữ liệu VARCHAR hoặc NVARCHAR. Nhưng đổi lại việc sử dụng hàm TRUNC lại không làm mất index kiểu dữ liệu DATETIME.
Để tối ưu câu lệnh phải so sánh thời gian ta tránh việc sử dụng hàm to_char(…) để tránh mất index column này. Mặc dù ta nhìn câu lệnh SQL khi turning seckhông được tường minh và dễ hiểu lắm. Đúng thôi, ta buộc phải đánh đổi, cuộc sống mà.

Cách tối ưu khi so sánh ngày tháng

– So sánh ngày:

Kiểu cũ: Ta tiến hành convert dữ liệu dạng ngày sang kiểu string và tiến hành so sánh:

ví dụ:
Cần lấy ngày tạo lớn hơn hoặc bằng ngày 06/04/2018

AND TO_CHAR(datecreate, ‘YYYYMMDD’) >= ‘20180406’;

Turning: Ta có thể viết lại hàm này một chút để nó sẽ chạy nhanh hơn, tránh gây ảnh hưởng hiệu năng hệ thống.
Như câu lệnh trên ta có thể thấy ta đã đều ép kiểu dữ liệu về String để so sánh rồi, chính việc luôn phải convert dữ liệu từ kiểu DATETIME về kiểu String nên việc này sẽ ảnh hưởng đến chỉ số CPU. Ta sẽ để việc so sánh này về so sánh bằng kiểu DATETIME, nghĩa là ta sẽ chuyển đối tượng so sánh về kiểu thời gian, cụ thể câu lệnh trên được viết lại thành:

AND TRUNC(datecreate) >= TO_DATE(‘20180406’,'YYYYMMDD');

Như vậy, hàm TO_DATE(‘20180406’,'YYYYMMDD') chỉ phải sử dụng 1 lần duy nhất để chuyển kiểu String về kiểu DATETIME để so sánh. Hàm TRUNC sẽ không gây mất index mà đơn giản để chính xác ta để nó về thời gian đầu tiên của ngày, nghĩa là 0h 00p.

Hoặc trong khoảng ngày cũng tương tự:
Cách 1:
AND TRUNC(datecreate) >= TO_DATE(‘20180406’,'YYYYMMDD')
AND TRUNC(datecreate) < TO_DATE(‘20180411’,'YYYYMMDD')

– So sánh ngày chi tiết tới thời gian:

Kiểu cũ: Ta tiến hành convert dữ liệu dạng ngày sang kiểu string và tiến hành so sánh:

ví dụ:

Cần lấy ngày tạo lớn hơn hoặc bằng 7h tối ngày 06/04/2018:

AND TO_CHAR(laa.datcre, ‘YYYYMMDD HH24:MI:SS’) >= ‘20180406 19:00:00’

Turning: Việc này thì ta cũng làm tương tự như trên nhưng cần lưu ý xử lý về vấn đề thời gian giờ – phút – giây. Việc chuyển định dạng thời gian này hơi phức tạp, mình sẽ nghiên cứu và bổ sung thêm.

Tối ưu câu lệnh SQL không chỉ tập trung vào các cú pháp và hướng xử lý như việc tối ưu trên mà ta còn cần phải chú ý tới dữ liệu ta sử dụng. Đối với những dữ liệu khi cần tìm kiếm nhiều ta cũng cần quan tâm đến việc đánh Index và bố trí phân vùng cho chúng được hợp lý và tốt hơn.

Oracle: Điều kiện về biến trong mệnh đề Where

Hôm nay làm việc thì có chị trong team đố về việc viết 1 thủ tục Oracle như sau:
Có một bảng dữ liệu gồm 4 cột Họ Tên, Số điện thoại, Số CMT và Email. Viết thủ tục đếm xem có bao nhiêu người thỏa mãn 3 tiêu chí mà người dùng nhập vào là Số điện thoại, Số CMT và Email (người dùng có thể nhập hoặc không nhập bất cứ thông tin ở tiêu chí nào cả).

Ban đầu tiếp cận cũng thấy dễ nhưng dần dần cũng khoai. Hướng triển khai ban đầu sử dụng cách check điều kiện có nhập hay không trước sau đó tiến hành nối thành một query cuối cùng.

Phần check điều kiện null:

condition:='';
if P_SDT is not null then
condition:= condition || ' AND SDT='||P_SDT;
end if;
if P_EMAIL is not null then
condition:= condition || ' AND EMAIL='||P_EMAIL;
end if;
if P_CMT is not null then
condition:= condition || ' AND CMT='||P_CMT;
end if;

Phần nối câu lệnh để run:

select count(*) into w_count
from NGUOI_DUNG from 1=1 || condition;

Câu lệnh này không thể thực thi được, Oracle không hiểu được để xử lý khi nối chuỗi vào câu lệnh SQL.

hoặc áp dụng chính câu lệnh kiểm tra trong mệnh đề Where:

select count(*) into w_count
from NGUOI_DUNG from 1=1
AND (P_CMT is not null AND CMT=P_CMT)
AND (P_EMAIL is not null AND EMAIL=P_EMAIL)
AND (P_SDT is not null AND SDT=P_SDT)
;

Câu lệnh này không thực thi được do nó luôn thực thi cả phần ngoặc trong AND (Ban đầu mình ngây thơ là trong cả cái AND to, cái CMT=P_CMT sẽ được thực thi khi cái P_CMT is not null đúng) 🙁

Xử nhiều cách khác nhau nhưng đều không được, tra Google thì không tìm được giải pháp khả dĩ do chưa có được từ khóa phù hợp.
Cuối cùng cũng tìm ra một phương pháp khả dĩ và sử dụng được như sau:

select count(*) into w_count
from NGUOI_DUNG from 1=1
and (
SDT=(CASE
WHEN P_SDT is not null THEN P_SDT
ELSE SDT
END
)
)
and (
EMAIL=(CASE
WHEN P_EMAIL is not null THEN P_EMAIL
ELSE EMAIL
END
)
)
and (
CMT=(CASE
WHEN P_CMT is not null THEN P_CMT
ELSE CMT
END
)
)
;

Phần này quan trong ở điểm nếu P_CMT is not null đúng thì CMT=P_CMT còn ngược lại thì mệnh đề CASE WHEN trả về chính CMT để CMT=CMT, 2 cột dữ liệu trung tên thì như điều kiện 1=1 nên nó luôn đúng.

Từ phần này có thể đơn giản hóa bằng câu lệnh NVL, CMT=NVL(P_CMT,CMT,P_CMT). Nhưng viết thế này người nào không rõ về NVL sẽ không hiểu lắm. Cách trên tuy dài nhưng dễ hiểu hơn.

Công cụ tra số điện thoại của facebook người dùng

Xuất phát từ bài viết của anh C Chủ Chứa trước cùng xóm trọ hơn mình 2 khóa. Anh trước code Object-C cho FPT, sau đó đầu quân cho Đại học Đại Nam ở vị trí chiêu sinh (tuyển sinh). Bài viết Share tool có thể check số điện thoại của bất kỳ tài khoản facebook nào -> kinh chưa?
Theo mình biết không thể lấy thông tin đó nếu người dùng ẩn số điện thoại, đó là việc bảo vệ dữ liệu người dùng của facebook. Nhiều người thách thức đều đã bị khuất phục(đều là dân IT và cũng biết cái mà mình biết). Do số của mình lại tạo facebook cho con Hà Phai nên mình thách thức anh đấy để thử tính năng.
Tất nhiên là không tìm được, tò mò mình hỏi cách làm. Vỡ lẽ ra không phải là việc hack được hoặc gì gì đó về kỹ thuật. Mà là ông đấy đã có data của 6 triệu tài khoản chưa ID facebook và số điện thoại. Để lấy các ông đấy chỉ việc so sánh theo ID facebook mà ra được số thôi.
Cái hay và tài ở đây là trước đây facebook chưa tiến hành ẩn các thông tin này đi, các bác đó đã tiến hành quét toàn bộ và đã lấy được các thông tin này. Bây giờ có muốn lấy cũng không được nữa và facebook đã đóng các API này lại. Việc này trước mình làm 1 công ty, đối tác trong buổi chào hàng về Bigdata đã tiến lộ họ đã craw được cái tương tự mà các bác kia làm. Kinh khủng thật.
Mình có kể điều này với 1 đứa em, nó nói đúng là có thằng nó nghĩ ra. Đã phần bọn còn lại như mình là không nghĩ cái mình đang dùng ngon ơ sau đó sẽ bị mất hoặc giới hạn đi.

Trở lại vụ trên theo anh C Chủ Chứa thì cái kết quả data số điện thoại với facebook cũng chưa kiếm được ra tiền vì có facebook rồi biết số điện thoại cũng không có nhiều ích lợi lắm, hơn nữa như kỹ thật anh đấy nói việc quét 6 triệu bản ghi thì quá lớn và không làm được(? mình vẫn làm với số liệu còn khủng khiếp hơn nhiều, 6 triệu ăn thua gì).

Nhưng nếu xử lý được thì làm sao? Làm thế nào kiếm được tiền. Theo ý mình thì là dùng cho bọn bán hàng online, có facebook like hoặc quan tâm thì mình có thể lấy số điện thoại để tư vấn tiếp qua SMS hoặc TeleSale. Nhưng anh đấy chia sẻ là hiệu quả không cao, chỉ có mấy thằng nhà đất mới đủ tiềm lực để mua và dùng thôi vì lợi nhuận của nó lớn. Nó cần data nhiều. Như anh chia sẻ tiếp là như anh đấy làm tuyển sinh cũng không thể sử dụng data đó được.

Cũng nhiều cái học được từ vụ này, đúng là đi một ngày đàng học một sàng khôn nhỉ. 😀 Nao đi bia với ông này 1 hôm mới được.

Hướng tiếp cận bài toán bằng cách vét cạn từng Số nguyên tố

Một bài học quý báu mà thầy Thắng có truyền lại cho đội O trong kỳ thi Olympic như sau:
Vào một năm nào đó, kỳ thi của đội anh Hinh hay anh Hùng dự ACM-ICPC có bài toán cần giải đại loại là cho 1 khoảng và in ra các số nguyên tố trong khoảng đó. Tất nhiên kết quả phải được đưa ra dưới x giây.
Xét về kỹ thuật thông thường ta buộc phải kiểm tra từng số trong khoảng đó và xác định xem đó là có phải số nguyên tố hay không? Với bài toán kinh điển kiểm tra có phải số nguyên tố hay không thì có nhiều thuật toán, nhưng số càng lớn thì càng cần nhiều thời gian hơn để kiểm tra. Và việc xử lý vấn đề thời gian là yếu tố quan trọng nhất vì với các thuật toán hiện tại thì không thể xử lý được trong khoảng thời gian mà ban tổ chức đưa ra…
Vì thi ACM-ICPC không chấm thuật toán mà tính pass hay không thông qua tập test của ban tổ chức. Tất nhiên nếu có danh sách tập test và kết quả của ban tổ chức thì chỉ cần IF ELSE cũng đạt điểm.
Một ý tưởng xử lý rất thông minh đó là ta tiến hành liệt kê ra 1 tập sẵn các số nguyên tố nằm trong phạm vi của đề của ban tổ chức trước, sau đó chương trình chỉ cần đọc file và lấy danh sách số nguyên tố trong khoảng mà đề bài yêu cầu. Độ phức tạp sẽ O(n).
Kết quả của đội là đã giải thành công bài toán, bài đã chấp nhận, file sinh danh sách số nguyên tố gần 1GB, mất gần hết thời gian cả cuộc thi.

Đây là bài toán kinh điển về kinh nghiệm xử lý vấn đề, nó thay đổi tư duy và cách tiếp cận bài toán. Cũng như là phải hiểu rõ phương thức và hoàn cảnh. Nó có thể ứng dụng trong nhiều trường hợp trong công việc và đời sống.

P/S: Tất nhiên cách này không thể áp dụng được trong cuộc thi Olympic vì cuộc thi Olympic là cuộc thi thuật toán, khi ta chưa thể code được chương trình nhưng ta đưa ra được quan điểm, giải thuật xử lý đúng đắn thì ban tổ chức vẫn có thể cộng điểm cho chúng ta.
Với bài toán trên nếu giới hạn dung lượng file thì cũng không thể đạt điểm cho bài toán này.

Máy có thể vẽ bằng bút theo một định dạng cho trước với AxiDraw

Trong quá trình làm luận văn về sử dụng sóng não để điều khiển thiết bị thông qua Emotiv. Thầy Vĩnh có đưa ra ý tưởng vẽ lại một bức ảnh gì đó đã được nhìn thấy trong quá khứ. Ý thầy sử dụng Emotive là thiết bị đọc sóng não kết hợp với thiết bị vẽ AxiDraw.

Ý tưởng này để hậu xét nhưng thấy thằng AxiDraw cũng khá hay ho. Là một thiết bị ứng dụng IOT mà điển hình là Adruino.

The AxiDraw V3 Drawing machine by Evil Mad Scientist. AxiDraw.com