TỔNG QUAN BÀI TOÁN
Giới thiệu về đơn vị thực tập
1.1.1 Sự hình thành và phát triển
Ngân hàng TMCP Ngoại thương Việt Nam (Vietcombank), được thành lập vào ngày 01/4/1963, là ngân hàng thương mại nhà nước đầu tiên thực hiện cổ phần hóa và chính thức hoạt động với tư cách ngân hàng cổ phần từ ngày 02/6/2008 Cổ phiếu Vietcombank (mã VCB) đã được niêm yết tại Sở Giao dịch Chứng khoán TPHCM vào ngày 30/6/2009 Sau hơn 50 năm phát triển, Vietcombank đã đóng góp quan trọng cho sự ổn định và phát triển kinh tế Việt Nam, từ một ngân hàng chuyên doanh ngoại thương trở thành ngân hàng đa năng cung cấp dịch vụ tài chính hàng đầu Vietcombank hiện sở hữu hạ tầng ngân hàng hiện đại, ứng dụng công nghệ tiên tiến trong các dịch vụ ngân hàng điện tử như VCB Internet Banking, SMS Banking, và Phone Banking, thu hút đông đảo khách hàng với sự tiện lợi và an toàn Với hơn 15.000 cán bộ nhân viên và hơn 500 chi nhánh, phòng giao dịch trong và ngoài nước, Vietcombank là một trong những ngân hàng thương mại lớn nhất tại Việt Nam.
Vietcombank hiện có văn phòng đại diện tại TP Hồ Chí Minh, hai công ty con ở nước ngoài và bốn công ty liên doanh Ngân hàng phát triển hệ thống Autobank cùng với mạng lưới hơn 1.726 ngân hàng đại lý tại 158 quốc gia và vùng lãnh thổ Với đội ngũ nhân viên năng lực và nhạy bén, Vietcombank là lựa chọn hàng đầu cho các tập đoàn lớn và khách hàng cá nhân Ngân hàng luôn hướng đến tiêu chuẩn quốc tế và liên tục được bình chọn là “Ngân hàng tốt nhất Việt Nam” Vietcombank cũng là ngân hàng đầu tiên và duy nhất của Việt Nam có mặt trong Top 500 ngân hàng hàng đầu thế giới theo Tạp chí The Banker Với sự nỗ lực không ngừng, Vietcombank hướng tới mục tiêu trở thành một trong 300 tập đoàn ngân hàng tài chính lớn nhất thế giới vào năm 2020.
1.1.2 Bộ máy tổ chức hoạt động tín dụng
Hình 1 Sơ đồ tổ chức khối tín dụng Ngân hàng Ngoại thương Việt Nam [3]
KHAI PHÁ DỮ LIỆU VÀ KINH DOANH THÔNG MINH ỨNG DỤNG TRONG BÀI TOÁN PHÂN TÍCH KINH DOANH VÀ DỰ ĐOÁN MARKETING
Giới thiệu về khai phá dữ liệu và kinh doanh thông minh 26 1 Khái niệm khai phá dữ liệu 26
2.1.1 Khái niệm khai phá dữ liệu
Tổng hợp từ nhiều tài liệu ta có rất nhiều định nghĩa về khai phá dữ liệu:
Theo Tom M Mitchell (1997), khai phá dữ liệu là quá trình sử dụng dữ liệu lịch sử nhằm phát hiện các quy tắc và nâng cao chất lượng quyết định trong tương lai.
Khai phá dữ liệu là một tập hợp các phương pháp được sử dụng trong quá trình khám phá tri thức, nhằm phát hiện những mối quan hệ khác biệt và các mẫu chưa được biết đến trong dữ liệu (Ferruzza, 2015).
Khai phá dữ liệu là quá trình hỗ trợ quyết định, giúp tìm kiếm các mẫu thông tin chưa được biết đến và bất ngờ trong các cơ sở dữ liệu lớn (Parsaye, 2000).
Khai phá dữ liệu, theo Fayyad (2003), là quá trình khám phá tri thức trong các cơ sở dữ liệu, nhằm trích xuất thông tin ẩn, chưa được biết đến và có giá trị hữu ích Quá trình này diễn ra dưới dạng các quy luật, ràng buộc và quy tắc có trong cơ sở dữ liệu.
Khai phá dữ liệu là một bước thiết yếu trong quá trình khám phá tri thức Quá trình khám phá trí thức bao gồm các bước: [5]
Gom dữ liệu là bước khởi đầu quan trọng trong quá trình khai phá dữ liệu, nơi dữ liệu được thu thập từ các cơ sở dữ liệu và kho dữ liệu để phục vụ cho các phân tích tiếp theo.
(2) Trích lọc dữ liệu: Ở bước này dữ liệu được lựa chọn và phân chia theo một số tiêu chuẩn nào đó
Làm sạch và tiền xử lý dữ liệu là bước quan trọng trong khai phá dữ liệu, giúp loại bỏ các lỗi thường gặp như dữ liệu không logic hoặc chứa giá trị vô nghĩa, ví dụ như tuổi = -1 Quá trình này xử lý các dữ liệu không chặt chẽ, coi chúng là thông tin thừa và không có giá trị, nhằm đảm bảo dữ liệu được chuẩn bị sẵn sàng cho các phân tích tiếp theo.
Khai phá dữ liệu là một bước quan trọng trong quá trình phân tích, nơi nhiều thuật toán khác nhau được áp dụng để trích xuất thông tin hữu ích từ dữ liệu.
Trong giai đoạn ước lượng mẫu, việc chiết xuất dữ liệu là rất quan trọng, nhưng không phải tất cả các mẫu đều có giá trị, và một số có thể bị sai lệch Do đó, cần thiết phải xác định các tiêu chuẩn đánh giá ưu tiên để thu được những tri thức hữu ích.
Hình 3 Quá trình khám phá tri thức
Khám phá tri thức và khai phá dữ liệu là hai khái niệm khác nhau trong quá trình xử lý thông tin Khám phá tri thức đề cập đến việc phát hiện tri thức hữu ích từ dữ liệu một cách tổng thể, trong khi khai phá dữ liệu chỉ là một bước trong quy trình này Công việc của khai phá dữ liệu bao gồm xác định bài toán, lựa chọn phương pháp phù hợp và tách ra các tri thức cần thiết từ dữ liệu đã có.
2.1.2 Ưu thế khai phá dữ liệu
Khai phá dữ liệu có một số ưu thế rõ rệt dưới đây: [6]
Khai phá dữ liệu có ưu thế hơn so với phương pháp học máy, vì nó có khả năng xử lý các bộ dữ liệu chứa nhiều nhiễu, không đầy đủ hoặc biến đổi liên tục Ngược lại, phương pháp học máy chủ yếu hiệu quả với các cơ sở dữ liệu đầy đủ, ổn định và tập dữ liệu có kích thước không quá lớn.
Phương pháp thống kê là nền tảng lý thuyết của Khai phá dữ liệu, nhưng vẫn tồn tại một số điểm yếu mà Khai phá dữ liệu đã khắc phục Các phương pháp thống kê chuẩn thường không phù hợp với các kiểu dữ liệu có cấu trúc trong cơ sở dữ liệu Hơn nữa, kết quả phân tích từ thống kê có thể rất phong phú nhưng khó làm rõ Cuối cùng, phương pháp thống kê cần sự hướng dẫn của người dùng để xác định cách thức và địa điểm phân tích dữ liệu.
Khai phá dữ liệu đang ngày càng trở nên quan trọng và hữu ích trong nhiều lĩnh vực khác nhau, nhờ vào những ưu điểm nổi bật của nó Ứng dụng của khai phá dữ liệu không chỉ giúp tối ưu hóa quy trình làm việc mà còn cung cấp thông tin quý giá để đưa ra quyết định hiệu quả hơn.
Khai phá dữ liệu là một phương pháp đã có từ lâu nhưng vẫn thu hút sự quan tâm của các chuyên gia nghiên cứu nhờ vào khả năng ứng dụng thực tiễn rộng rãi trong nhiều lĩnh vực khác nhau, ngày càng trở nên quan trọng trong thời đại số hiện nay.
Lĩnh vực thương mại điện tử:
Công cụ tìm hiểu, định hướng thúc đẩy, giao tiếp với khách hàng
Phân tích hành vi mua sắm trên mạng và cho biết thông tin tiếp thị phù hợp với loại khách hàng trong một phân khu thị trường nhất định
Nhân sự: Giúp nhà tuyển dụng chọn ứng viên thích hợp nhất cho nhu cầu của công ty
Y học: Hỗ trợ bác sĩ phát hiện ra bệnh của bệnh nhân dựa trên các xét nghiệm đầu vào
An ninh mạng và an toàn thông tin đóng vai trò quan trọng trong việc bảo vệ hệ thống khỏi các cuộc tấn công xâm nhập trái phép Ứng dụng hệ thống phát hiện xâm nhập (IDS) và hệ thống ngăn chặn xâm nhập (IPS) giúp phát hiện và ngăn chặn các cuộc tấn công mạng, đảm bảo an toàn cho dữ liệu và hệ thống Việc triển khai hiệu quả các công nghệ này sẽ góp phần nâng cao khả năng phòng chống và ứng phó với các mối đe dọa an ninh mạng.
Trong lĩnh vực tài chính ngân hàng, ứng dụng khai phá dữ liệu đã đóng góp lớn vào hiệu quả kinh doanh của các ngân hàng Sự phát triển của ngành ngân hàng đã làm cho giao dịch trở nên dễ dàng hơn, nhưng cũng dẫn đến sự gia tăng nhanh chóng về khối lượng dữ liệu Các ngân hàng cần khai thác hiệu quả nguồn dữ liệu này để phục vụ cho hoạt động kinh doanh Nhận thức được tầm quan trọng của việc sử dụng dữ liệu, nhiều ngân hàng đã chuyển từ phương pháp phân tích truyền thống sang các công cụ khai phá dữ liệu Ví dụ, vào ngày 08/05/2017, Viện Đào tạo Công nghệ và Quản trị Robusta tại Hà Nội đã tổ chức khóa đào tạo "Khai phá dữ liệu" cho Ngân hàng TMCP Công Thương Việt Nam Hay vào năm 2017, ViettinBank đã nghiên cứu và áp dụng công cụ Data Mining để phân khúc khách hàng, chấm điểm tín dụng, phê duyệt, quảng bá sản phẩm và phát hiện gian lận.
ÁP DỤNG QUY TRÌNH KINH DOANH THÔNG MINH XÂY DỰNG BÁO CÁO TÍN DỤNG THƯỜNG NIÊN VÀ PHÂN TÍCH HÀNH VI KHÁCH HÀNG TRONG KHỐI TÍN DỤNG
Xây dựng kho dữ liệu
Bộ dữ liệu thực nghiệm bao gồm hệ thống dữ liệu thẻ AMEX của 1187 khách hàng, 1244 tài khoản và 1311 thẻ AMEX, kèm theo thông tin chi tiết của 18660 giao dịch và các thông tin bổ sung Thẻ AMEX được doanh nghiệp cung cấp cho nhân viên để thanh toán các chi phí công tác như đi lại và ăn ở Nhân viên sử dụng thẻ để thanh toán, và doanh nghiệp sẽ chịu trách nhiệm thanh toán sao kê với ngân hàng.
Bộ dữ liệu thực nghiệm gồm 10 bảng như sau: EGMIS_CACCP, EGMIS_CARDP, EGMIS_CID, EGMIS_HIERARCHY, EGMIS_MCC, EGMIS_OLSHP, EGMIS_TRANS, EGMIS_CTDTP, EGMIS_OLSTP.
Chiều khách hàng (EGMIS_HIERACHY): Chiều này lưu trữ tất cả các thông tin chi tiết về khách hàng dùng thẻ AMEX của ngân hàng.
Quôc tịch của doanh nghiệp
ICA_ADDR1 Địa chỉ ICA 1
ICA_ADDR2 Địa chỉ ICA 2
ICA_CITY Thành phô ICA
BCA_ADDR1 Địa chỉ BCA 1
BCA_ADDR2 Địa chỉ BCA 2
BCA_CITY Thành phô BCA
NOI_CU_TRU Nơi cư trú
UOC sô người sông phụ thuộc (Không có thu nhập) trong gia di
TONG_TS_KH Tổng tài sản khách hàng
CRDACCT_NBR Khóa chính, tự tăng, mã tài khoản _
CRDACC PDT NBR Mã loại tài khoản
CRDACCT_CUST_NBR Mã chủ tài khoản
CRDACCT Mã chi nhánh(BCA_NBR) của khách hàng
CRDACCT_DTE_OPEN Ngày mở tài khoản _
CRDACCT_DTE_CLOSE Ngày đóng tài khoản
CRDACCT BLK CODE Trạng thái khóa của tài khoản
_DTE_BLK_CODE Ngày khóa tài khoản _
Cardaccount_date_payment_due (Ngày đến hạn thanh toán)
CRDACCT _60DAYS_DUE Số tiền thanh toán sao kê 60 ngày qua (So với ngày sao kê tài khoản)
CRDACCT _90DAYS_DUE Số tiền thanh toán sao kê 90 ngày qua (So với ngày sao kê tài khoản)
CRDACCT 120DAYS DUE Số tiền thanh toán sao kê 120 ngày qua (So với ngày sao kê tài khoản) _
CRDACCT _150DAYS_DUE Số tiền thanh toán sao kê 150 ngày qua (So với ngày sao kê tài khoản) _
CRDACCT _180DAYS_DUE Số tiền thanh toán sao kê 180 ngày qua (So với ngày sao kê tài khoản) _
CRDACCT 210DAYS DUE Số tiền thanh toán sao kê 210 ngày qua (So với ngày sao kê tài khoản) _
CRDACCT _30DAYS_DUE Số tiền thanh toán sao kê 30 ngày qua (So với ngày sao kê tài khoản)
Số tiền thanh toán sao kê từ trước tới nay (So với ngày sao kê tài khoản) _
CRDACCT DATE MAINT Ngày bảo trì bảo dưỡng tài khoản _
Chiều tài khoản (EGMIS CACCP): Đây là chiều lưu giữ thông tin về tài khoản ngân hàng của các nhân viên.
CARD ACCT NBR Mã tài khoản
CARD_PDT_NBR Mã loại thẻ
CARD SUP REL Loại thẻ ngân hàng
_ Mã trạng thái khóa thẻ
CARD_DTE_OPEN _ Ngày mở thẻ
CARD_DTE_CLOSE Ngày đóng thẻ _
CARD_EXP_DATE Ngày hết hạn thẻ (Chỉ có ngày tháng không CARD_DTE_BLK_CODE
CARD_NEW_NBR Thẻ thứ 2 (Mã thẻ) _
_ Ngày bảo trì, bảo dưỡng thẻ
TXN_CARD_NBR Số thẻ giao dịch
TXN_POSTING_DATE Ngày post giao dịch
TXN_CODE Mã kiêu giao dịch (Thanh toán sao kê, quẹt thẻ, phí thường niên thẻ )
TXN_AMT Số tiền giao dịch
TXN_ORG_AMT Số tiền giao dịch của tổ chức
TXN_ORG_CURR_CODE Đơn vị tiền tệ giao dịch
TXN_DTE Ngày giao dịch
TXN_DESC Transaction description (Mô tả về giao dịch)
TXN_SETL_AMT Số tiền thanh toán
TXN_SETL_CURR_CODE Loại tiền tệ thanh toán
TXN_STATE Trạng thái của giao dịch
Chiều tài khoản (EGMIS CACCP): Đây là chiều lưu giữ thông tin về thẻ ngân hàng của các nhân viên.
Chiều EGMIS_TRANS: Đây là chiều lưu thông tin về các giao dịch trong năm
MERCHANT_NBR Mã người bán
MERCHANT_NAME Tên người bán
MERCHANT_CTRY_CODE Mã quốc gia thanh toán (704 là Việt Nam còn lại là quốc tế)
MCC Merchant category code (Mã mục đích giao dịch ví dụ như: di chuyển, nhà hàng, nhà nghỉ khách sạn)
TXN_SEQ_NO Số thứ tự giao dịch, số giao dịch (Khóa chính)
STTM_MONTH Tháng giao dịch lên sao kê
ACC_NBR Mã tài khoản
OLSH_ACCT_NBR Mã tài khoản
OLSH_OLST_DATE Ngày lập bảng sao kê dư nợ hàng tháng
CURRENT_AMT Số tiền thanh toán sao kê
PREV_BAL_AMT Số tiền thanh toán sao kê tháng trước
OLSH_CURR_BAL_RTL Số tiền thanh toán sao kê trong tháng
OLSH_CURR_BAL_CASH Dư nợ tín dụng tháng
OLSH_STMT_BAL_RTL Số tiền thanh toán trong tháng đã lên sao kê OLSH_STMT_BAL_CASH Dư nợ tín dụng trong tháng đã lên sao kê
Chiều (EGMIS_OLSHP): Chiều này lưu trữ tổng số tiền đã chi tiêu trong tháng của thẻ kể từ khi thẻ được mở.
CID ID doanh nghiệp (CID)
MCC Mã mục đích giao dịch _
Chiều EGMIS_CID: Chiều này lưu trữ các mã MCA và ID doanh nghiệp tương ứng.
Chiều EGMIS_MCC: Chiều này lưu trữ nội dung của các Merchant category code tức là mã các mục đích giao dịch ví dụ (5813 là thanh toán khách sạn).
Ngoài EGMIS_TRANS, chúng ta còn có hai bảng quan trọng khác là EGMIS_OLSTP và EGMIS_CTDTP, lưu trữ danh sách các giao dịch đã và chưa lên sao kê với cấu trúc tương tự.
After gathering data from various sources, we found that the CARDP, CACCP, HIERARCHY, and OLSHP dimensions originate from SQL Server 2012, while OLSTP, TRANS, and CTDTP are sourced from Excel Additionally, MCC is derived from XML, and CID comes from raw files such as Notepad By utilizing the OLE DB method, we can consolidate data from these diverse sources effectively.
OLE DB is a Microsoft technology designed to connect data sources, such as Word documents, Excel sheets, Access databases, and PowerPoint presentations, with applications It also supports more complex data sources like mainframe data Previously, Microsoft used ODBC (Open Database Connectivity) for data management, but ODBC faced limitations with certain data formats, particularly those from different operating systems OLE DB overcomes these challenges, providing a more flexible solution for data connectivity.
Hình 10 Tích hợp các chiều dữ liệu từ nhiều nguồn khác nhau vào Oracle
VU CT TNHH PROCTERBtGAMBLE VIETNAM T4-19.04.RMERA POINT
CT TNHH PROCTER&GAMBLE VIETNAM 23 NGHACH 129 NGO XA
1 14 NGUYEN VO THITHACCT TNHH PROCTERe i GAMBLE VIETNAM 343/20 G TO HIEN
NHAT CT TNHH PROCTERBtGAMBLE VIETNAM 124 TA21 THOI AN, Q12 003347
1 14 LE MINH PHU CT TNHH PROCTEReiGAMBLE VIETNAM 15-3 HUNG VUONG 2 P.TAN
HANG CT TNHH PROCTERBtGAMBLE VIETNAM A9.10-26 NGUYEN THUONG
MURAKAMI CT TNHH PROCTERBtGAMBLE VIETNAM #1506,ASlANA
TUYET MAI CT TNHH PROCTERBtGAMBLE VIETNAM 33/30 LE TRONG TAN P,SON
THANH THUY CT TNHH PROCTERBtGAMBLE VIETNAM 6 LE TAN QUOC, P13 Q TAN
MAl CT TNHH PROCTEReiGAMBLE VIETNAM B09-05 HOANG ANH GIA LAI3,
MANH CT TNHH PROCTERBtGAMBLE VIETNAM Kl BEN T HUYVINH NGHE AN
DONG DUONG 141/1 MAC HIEN ΠCH LONG BINH Q5HCM
DONG DUONG 419∕8A TO NGOC VAN.P.UNH DONG , QUAN THU
DONG DUONG 42∕4A BINH THANH, P BINH HUNG HOA B,
O1 9 IE DUC TAI PROCTERaiGAMBLE DONG
DUONG TAN XUAN DONG XOAJ BINH
KHANH PR0CTER8ιGAMBLE DONG
DUONG 1OM∕1D, DONG AN 2,BINH HOA TX THUANAN
OONG DUONG 71/1 DUONG 46,P-HlEP BINH CHAN H.THU DUC
KHOA PR0CTER8ιGAMBLE DONG
DUONG 27∕1A, TRUNG VlNH PHUC 27 Vl NH PHU
DUONG 48/7 DUONG 379 P.TANG NHON PHU A Q.9 HCM
DUONG 1351 NGUYEN THI DINH.P.CAT LAI QUAN2
DONG DUONG 1/45 NGUYEN VAN QUA P-DHT QU AN 12
1 9 VO PHl HUNG PR0CTER8ιGAMBLE DONG
DUONG 113∕1B KHU PHO THANH BINH TT AN THANH
DONG DUONG 97/87 DUONG SO 08,P.TANG NHON PHU B
THINH TONG CTCP BAO HIEM
P ETROUMEX 2409 C2 MADARIN GARDEN HA NO I 002362
DONG TONG CTCP BAO HIEM
XUAN QUY TONG CTCP BAO HIEM
77/11 NHAT CHI MAl P13 QUAN TAN BINH 001884
TUAN TONG CTCP BAO HIEM
002907 000024 0000245 00002456A0 1 NGUYEN THANH SON TONG CTCP BAO HIEM 31/290-KIM MA- QUAN BA DlNH- H ANOI
Xây dựng biểu đồ thực thể quan hệ:
Hình 11 Kho dữ liệu thẻ AMEX
File View Favorites Tools Window Help
Connection User I Table I View Function Others Data Pump Query Report Schedule Model
ORACLE.O CM ORDDATA ORDPLUGI NS
= Begin Transaction i) Memo ’ T Filter I Sort C* Import [♦ Export
CUST-NBR MCA-NBR ICA-NBR BCA-NBR CID CUST-N AME CORP-NAME CUST.ADDR1 CUST.ADDR2
THANG MO T HEJRONG NAM.2017 I THANG-MO-THE-TRONG-NAM-2017
Group by Count all Group by
SELECT * FROM (SELECT "NAVKAT-TABLE".∙ ROWNUM "NAV1CAT.ROWNUM" FROM (SELECT "BfJEGMlS-HIERARCHr.- ROWID "NAVICAT.ROW1D" FROM "Bf JEGMIS-HIE Record 1 of 1000 in page 1
Hình 12 Hình ảnh kho dữ liệu trong hệ quản trị CSDL Oracle Database 11g2
Trích lọc dữ liệu phù hợp với nhu cầu phân tích
3.2.1 Dữ liệu báo cáo tình hình mở thẻ năm 2017
Mục tiêu của báo cáo này là tổng hợp tình hình mở thẻ theo từng tháng trong năm 2017, phân loại theo các tỉnh thành và liệt kê danh sách các thẻ phát sinh trong năm.
Hình 13 Nối bảng tài khoản, bảng thẻ và bảng khách hàng
Inner join bảng tài khoản và bảng card bằng mã tài khoản sau đó inner join với bảng khách hàng bằng mã khách hàng.
CRDACCT-NBR ) Replace 'CRDACCT.NBR' TRIM(CRDACCT-NBR) Unicode string [DT_WS 1
Hình 14 Cắt mã tài khoản
Trong quá trình nối bảng, cần sử dụng hàm TRIM trong cột delivered để loại bỏ các ký tự trống ở đầu và cuối của mã Bên cạnh đó, hàm sort cũng nên được áp dụng để sắp xếp thứ tự các mã, nhằm đảm bảo rằng các mã account được nối với nhau một cách chính xác.
Hình 15 Lọc dữ liệu mở thẻ theo tháng năm và theo địa phương
Chia dữ liệu thành hai luồng: luồng đầu tiên tạo danh sách các thẻ mở trong năm 2017, trong khi luồng thứ hai phân nhóm dữ liệu theo tháng và địa phương để thống kê tình hình mở thẻ Để lấy địa chỉ đầy đủ, chúng ta sử dụng hàm ghép địa chỉ 1, địa chỉ 2 và cust city.
SUBSTRING(CARD-DTE-OPEN-XUJY i M) Unicode string [DT-WS 4
Dùng hàm SUBSTRING để lấy ra năm mở thẻ từ ngày mở thẻ.
TP HO CHl MlNH 8 201708 FL „ 28
MA-KH MA-TAI-KHOAN-
T MA-THE-KH TEN-KH TEN-CHI-NHANH NGAY-MO-
401 *37715925291528-NGUYÊN THI DIEU *5075322291 *20170913 26/21 NGUYEN MINH HOANG, P12 p ,QTAN BINH,HO CHI MINH
119 *37715910052147- NGUYEN HUU UYE J&J MEDICAL *20170119 158/246/3 PHAM VAN HAI P3Q.TA r N BINH,HO CHI MINH
818 *37715916356456' VO SANG XUAN M AJANSSEN -CIUHG VIETNAM *20170818 11C412 SKY GARDEN 3, PHAM VAN r NGHIA P.TAN PHONG Q7,HO CHI MINH
531 *37715914704619ι NGUYEN HOANG H CTYTNHH QUINTILES VIET
NAM *20170531 Cl 18.08 TROPIC GARDEN,49 DUON,G 66,THAO DIEN,QUAN 2,HO CHI MINH
106 *37715923951639∣ NGUYEN TUAN SO⅜VCB TAN BINH *20171106 35-37 DUONG B2, PHUONG TAY THA r NH j QUAN TAN PHU, THANH PHO HO,HO
115 *37715927849983- NGUYEN THI THIE6NH TMCP NGOAI THUONG
VUNG *20171115 SO NHA69∕7A LE HONG PHONG ri PHUONG 7 r VUNG TAU
115 *37715921115984 N GUYENVANTAN N HTMCPNGOAITHUONGVUNG *20171115 184/1/8 XO VIET NGHE TINH rr PHUONG THANG TAM r VUNG TAU Π 000133157 AC0080OOOOO l *20171
115 *37715923007055 1 DOAN VAN TUYEN NH TMCP NGOAI THUONG
106 *37715928211309ι PHAM THI THANH 1VCBTAN BINH *20171106 246/41 HO7A HUNG, P.13 r Q-IO 1 NH r QUAN TAN PHU 1 THANH PHO HO 1 HO CHI
531 *37715911309712- NGUYEN THI HANHCTY TNHH QUINTILES VIET
*20170531 18-07B c/c MY PHU r 60∕68 LAM VA r N BEN 1 P-TAN KIENG 1 QUAN 7 1 HO CHI MINH
323 *37715919628494∣TRAN QUANG MIN *5075322291 *20171102 CONDO HAGL2 r UNIT 820/3.783 TRA r N XUAN SOAN r QUAN 7,HA NOI
103 *37715920192001 TRAN DINH THUANNH TMCP NGOAI THUONG *20171103 39/49 TO49, KHU 4, PHU THO 1 T 1 P TDM 1 BD j BINH DUONG
103 *37715925666840∣ NGUYEN THAJ MIN NH TMCP NGOAI THUONG *20171103 470D 1 XA BINH HOA, PHUONG BINH 1 NHA M I TX THUAN AN 1 BINH D U ONG 1 BINH
*377159285938841DANG THI HUONG NH TMCP NGOAI THUONG *20171103 31/1 TO 1, K5, PHU HOA, TP-TDM rr BD 1 BINH OUONG
103 *37715928991598- NGUYEN KHANH Tl NH TMCP NGOAI THUONG *20171103 137/7 LE HONG PHONG 1 PHU LOI rr TP TDM, BD 1 BINH DUONG
623 *37715918455024 VU NGOC HOANG CONG TY TNHH IBM VIET
NAM *20170623 18D NGO 337 DUONG AU CO, P.NHA r T TAN 1 TAY HO 1 HA NOl
229 *37715912149405 NGUYEN THI NGOC CONG TY TNHH IBM VIET
NAM *20171229 NHA 302 DUONG ANH DAO 3, VINHO r MES RIVERSIDE LONG BIEN r HA NOI
517 *37715913707532* IE THI THU HANG CONG TY TNHH IBM VIET
NAM *20170517 8 NGO 117 HOANG HOATHAM 1 BADI r NH r HA NOI
420 *37715911336126- NGUYEN XUAN TH∕*5075322291 *20170420 736/38 LE DUC TH0 1 P.15 1 Q.G0 VA 1 P 1 HO CHI MINH
524 *37715917965440-NGUYEN PHUONG CTYTNHH MOI GIOI BAO
HIEM *20170524 PHONG 25.1 BLOCK IA LACASA89 r HOANG QUOC 1 HO CHI MINH
111 *37715927326724- PHAM THI LAN J&J CONSUMER *20170111 A3-1003 IMPERIA AN PHU KP5 P.A 1 N PHU Q2 1 H0 CHI MINH
112 *37715916494263 PHAM THI LAN J&J CONSUMER *20170111 A3.1003 IMPERIA AN PHU KP5 P.A 1 N PHU Q2 1 HO CHI MINH
531 *37715913650218 THAN TRONG SON CTYTNHH QUINTILES VIET
*20170531 179/12/9 TRUC 1 P.13 1 QUAN BINH T 1 HANH 1 HO CHI MINH
531 *37715911329179 PHAN THl XUAN DACTY TNHH QUINTILES VIET
NAM *20170531 108 DAO DUY ANH p.9 Q.PHU NHUA r N TP-HCM r HO CHI MlNH
130 *37715919O32639∣ BUI TIEN BAO CONG TY TNHH IBM VIET
930 *37715918092822 VU DUY DONG *5075322291 *20170317 38∕12D r AP XUAN THOI DONG 2, X r AXUAN THOI DONG 1 HOC MON 1 HO CHI MINH
Hình 16 Phân nhóm các thẻ phát sinh theo tháng mở thẻ và địa phương
DIA-CHI-KH Ị
D ∣ A ^ HLCONG J IYCUΛ
DIA-CHI-CHI-NHANH
CUST.ADDR1 + "," + CUST-ADDR2 + "," + CUST-CIT Y Unicode string [DT.WS 8: CORP-ADDR1 + + CORP-ADDR2 + + CORP-CI Unicode string [DT-WS 8;
BCA-ADD RI + "," + BCA-ADDR2 + + BCA-CITY Unicode string [DT.WS 8;
Hình 17 Ghép địa chỉ khách, tập đoàn, chi nhánh
Dùng hàm Aggregations để nhóm tổng sô thẻ được mở theo tháng và theo thành phô.
Xuất ra excel ta được kết quả như sau:
Tính toán lượng giao dịch, tổng tiền và tiền giao dịch trung bình theo các loại giao dịch năm 2017
Hình 19 Nối dữ liệu với bảng giao dịch phát sinh năm 2017
Nối bảng card, account, khách hàng với bảng giao dịch.
ONf TONG-TIEN Tl EN-TB-MOI-GD
Hoàn trằ GD RTM không t/c r I "2000000.00 F 2000000.00
Hoàn trả giao dịch khi khách hàng hủy *63 "350232338.00 F 5560037.11
Hoàn trả số dư tín dụng Γ 7 F 94397079.00 13485297 OO
Hủy phí GD RTM không t/c
Hủy phí thay đổi hạn mức sao kê r I "50000.00 *50000.00
Hủy phí thường niên kỳ sao kê và miên phí thường niên CT khuyến mại r 22 F 10200000.00 , 463636.36
Hủy phí thường niên sao kê Γ 3 "éooooo.oo F 200000.00
Hủy phí trả không đủ số dư tối thiểu Ĩ7 48565719.00 F 2856807.00
Hủy phí vượt hạn mức sao kê *6 F 230465.54 *38410.92
Hủy phí vượt hạn mức^phí trả không đủ r 26 F 59522498,00 *2289326.85
Hủy thanh toán sao kê Ĩ2 4846186451.00 "153848870.92
KH Quẹt thẻ thanh toán r 6184 F 70231610249.00 F 11356987.43
KH Rút tiền từ ATM r 318 "625300000.00 F 1966352.20
KH thanh toán quốc tế F 6619 "23316285826.00 F 3522629.68
KH thanh toán tiền khách sạn nội địa F 7 46457878.00 *6636839.71
KH thanh toán tiền máy bay dubai 4 "19896175.00 "4974043.75
KH thanh toán tiền máy bay nội địa 88 "765897674.00 _ F 8703382.66
Phí đưa thẻ lên danh sách ngừng lưu hành và phí ngừng sử dụng thẻ } □ 1'850000.00
Phi phạt chậm thanh toán 488 '443542760.00 *908899.10
Phí tăng hạn mức vĩnh viễn F I *50000.00 *50000.00
Phí test thẻ AMEX lãn đãu F 2 *250000.00 F 1250D0.00
Phí thường niên thẻ chính F 2S1 "102300000.00 F 364056.94
Thanh toán sao kê tại quầy F 3261 "100630934842.12 *30858918.99
Trong bảng giao dịch ta có các mã giao dịch sau:
HINH_THUC_GIAO_DICH MOTA
3400 Hủy phí thường niên sao kê
2900 Phí test thẻ AMEX lần đầu
1005 KH Quẹt thẻ thanh toán
8300 Hủy thanh toán sao kê
3300 PHI THUONG NIEN THE CHINH
3205 HUY PHI THAY DOI HAN MUC SAO KE
3207 Hủy phí vượt hạn mức,phí trả không đủ
3100 PHI TANG HAN MUC VINH VIEN
5601 HUY PHI GD RTM KHONG T∕C,112812∕VCB
6000 HOAN TRA GD RTM KHONG T∕C,112812∕VCB
1025 Thanh toán tiền khách sạn
3000 Hoàn trả giao dịch khi khách hàng hủy
2500 PHI DUA THE LEN DANH SACH CAM LUU HANH và PHI NGUNG SU DUNG THE
3405 HUY PHI THUONG NIEN KY SAO KE và MIEN PHI THUONG NIEN CT KHUYEN MAI
8400 Thanh toán sao kê tại quầy
5700 Hoàn trả số dư tín dung(Do mình thanh toán nhiều hơn số đã chi)
3208 Hủy phí vượt hạn mức sao kê
Hình 20 Danh sách các nghiệp vụ giao dịch thẻ
Như vậy, TXN_CODE = 8400 tính vào thanh toán sao kê của doanh nghiệp,
TXN_CODE05, 1007, 3800, 3500, 1025, 1006, 6100 tính vào doanh thu thẻ,
TXN_CODE)00, 3300, 3100, 3102, 2500, 3103, 3501 tính vào lợi nhuận của ngân hàng.
I XUJLY-T HU Ị Oddasnew colιιmn> TXN-CODE == 3400 ? "Hủy phí thường niên sao kê" Unicode string [DT-WS 6 ∣
Dùng hàm case when để tạo một cột mới dịch các đoạn mã loại giao dịch thành tên loại giao dịch.
Hình 21 Báo cáo thống kê các nghiệp vụ giao dịch thẻ phát sinh trong năm 2017, tổng tiền, tiền trung bình mỗi giao dịch
TXN-AMT SO-TIEN-TRUNG-BINH-MOI-GD e
Hình 22 Hàm phân nhóm theo loại giáo dịch, để sinh ra báo cáo thống kê các nghiệp vụ giao dịch thẻ phát sinh 2017
Dùng hàm Group by và dùng hàm Count (Transaction), SUM (TRANSACTION
AMOUNT), AVG (TRANSACTION AMOUNT) để tính sô lượng giao dịch, tổng tiền giao dịch và trung bình mỗi giao dịch theo đầu nghiệp vụ giao dịch thẻ.
SO-ΠEN-TRUNG-BINH- Replace 'SOJIEN-TRU ROUNDiSO-UEN TRUNG.B1NH.MOI.GD,2) numeric [DT.NUMERIC]
Chú ý dùng hàm ROUND để lấy sô tiền trung bình mỗi giao dịch làm tròn tới con sô thập phân thứ 2.
Tính doanh thu, lợi nhuận, lượng thanh toán sao kê theo tháng, quý, loại thẻ và địa phương năm 2017 Đầu tiên chúng ta phải tách tháng và tách quý.
Dùng câu lệnh sau để chuyển dữ liệu ngày tháng từ kiểu STRING thành kiểu DATE: (DT DATE) (SUBSTRING(TXN_DTE,1,4) + "-" + SUBSTRING(TXN_DTE,5,2) + "-" +
THANG-CUA-GIAO-DICH I