Truy cập dữ liệu sử dụng để thực hành cho lesson này theo 1 trong các cách sau:

  • Download và cài đặt mysql (google hướng dẫn trên mạng, nên cài đặt thêm MySQL Workbench để viết lệnh và sử dụng MySQL). Sau đó tải các file .csv và .txt tại link sau và import vào cơ sở dữ liệu MySQL để tạo các bảng: https://github.com/tuanphan92/datatute-datasets/tree/main/JobSeeker/Lesson3

  • Sử dụng Google Colab notebook sau đây để truy vấn dữ liệu mà không cần cài đặt gì trên máy tính cá nhân: xx

  • Điền thông tin vào Google Form sau đây để yêu cầu truy cập vào Google BigQuery: <https://forms.gle/aN9ZnhqiRCmpGs9TA>. Sau khi mình cấp quyền truy cập, các bạn có thể query dữ liệu tại BigQuery mình đã set up mà không cần cài đặt gì. Lưu ý, số lượng người được phép truy cập sẽ có hạn.

1 Nhóm dữ liệu và tính toán

Khi xử lý và phân tích dữ liệu thì ta rất thường xuyên phải nhóm dữ liệu và thực hiện các tính toán, sử dụng mệnh đề GROUP BY.

Để viết query sử dụng GROUP BY được chính xác thì ta cần nắm vững Order of execution trong SQL query

Bên cạnh các vấn đề hay gặp về sai order of execution, sai syntax, thì có 1 số điểm ta cần lưu ý như sau:

  • WHERE và HAVING: Điều kiện lọc ở mệnh đề WHERE sẽ được thực hiện trước điều kiện lọc ở mệnh đề HAVING. Vì vậy tùy vào yêu cầu của đề bài mà ta sẽ để điều kiện lọc vào WHERE hay HAVING. Với những trường hợp điều kiện lọc để ở mệnh đề WHERE hay HAVING đều được thì hãy ưu tiên sử dụng WHERE do nó sẽ làm giảm số lượng bản ghi cần phải GROUP BY, từ đó làm tăng tốc độ xử lý của query

    Ví dụ: 2 query dưới đây đều trả ra kết quả giống nhau, nhưng query sử dụng WHERE sẽ có tốc độ nhanh hơn, đặc biệt là khi bảng có lượng dữ liệu lớn

    --để điều kiện lọc ở mệnh đề WHERE
SELECT
  ContractDate,
  SUM(Amount) AS TotalAmount
FROM contract
WHERE ContractDate >= '2023-01-01'
GROUP BY ContractDate

--để điều kiện lọc ở mệnh đề HAVING
SELECT
  ContractDate,
  SUM(Amount) AS TotalAmount
FROM contract
GROUP BY ContractDate
HAVING ContractDate >= '2023-01-01'

  • Nhóm dữ liệu theo cột (hoặc tập hợp các cột) mà không phải là duy nhất. Điều này sẽ khiến kết quả tính toán bị sai. Xem ví dụ dưới đây:

    Ta có bảng dữ liệu inventory thể hiện số lượng hàng tồn kho của từng sản phẩm tại các cửa hàng của 1 doanh nghiệp như sau

    StoreID InventoryID InventoryName Number
    1 5 Máy xay sinh tố 421
    1 6 Lò vi sóng Panasonic P244 177
    1 7 Máy xay sinh tố 280
    2 14 Máy đánh trứng HueWei H121 92
    3 14 Máy đánh trứng HueWei H121 141
    3 8 Máy xay sinh tố Samsung S43 322

    Ta được yêu cầu xuất thông tin số lượng hàng tồn kho trên toàn hệ thống theo từng sản phẩm.

    Nếu viết query như sau sẽ trả ra kết quả sai. Ta thấy số lượng của InventoryID 5 và 7 đã bị gộp vào nhau do ta GROUP BY InventoryName thay vì GROUP BY InventoryID. InventoryID 5 và 7 vì lí do nào đó mà có InventoryName giống nhau (có thể do nhập liệu thiếu) nên khi ta GROUP BY InventoryName sẽ cho kết quả sai.

    SELECT
  InventoryName,
  SUM(Number) AS TotalNumber
FROM inventory
GROUP BY InventoryName
    InventoryName Number
    Máy xay sinh tố 701
    Lò vi sóng Panasonic P244 177
    Máy đánh trứng HueWei H121 233
    Máy xay sinh tố Samsung S43 322

    Query đúng phải như sau

    SELECT
  InventoryID, InventoryName
  SUM(Number) AS TotalNumber
FROM inventory
GROUP BY InventoryID, InventoryName
    InventoryID InventoryName Number
    5 Máy xay sinh tố 421
    6 Lò vi sóng Panasonic P244 177
    7 Máy xay sinh tố 280
    14 Máy đánh trứng HueWei H121 233
    8 Máy xay sinh tố Samsung S43 322

  • Tính toán sai do có các giá trị NULL

    Ví dụ 1:

    Ta có bảng dữ liệu orders_tmp như sau:

    DROP TABLE IF EXISTS orders_tmp;

CREATE TEMPORARY TABLE orders_tmp (
    OrderID int,
    ProductID int,
    TotalAmount int
);

INSERT INTO orders_tmp
VALUES
    (1, 22, 150),
    (1, 23, NULL),
    (2, 23, 50),
    (2, 35, 180),
    (3, 22, 400)
;
    df <- read.csv2("data/orders_tmp.csv", header = TRUE, sep = ",", quote = "\"")
df %>% kbl() %>% kable_styling(bootstrap_options = "striped", full_width = F, position = "left")

    OrderID

    ProductID

    TotalAmount

    1

    22

    150

    1

    23

    NULL

    2

    23

    50

    2

    35

    180

    3

    22

    400

    Ta được yêu cầu trích xuất ra dữ liệu tổng giá trị thanh toán trung bình (Average Total Amount) của từng sản phẩm trên mỗi đơn hàng

    SELECT
    ProductID,
    AVG(TotalAmount) AS AvgWithoutNULL,
    (CAST(SUM(TotalAmount) AS FLOAT) / COUNT(DISTINCT OrderID)) AS AvgWithNULL
FROM orders_tmp
GROUP BY ProductID;
    df <- read.csv2("data/orders_tmp_result.csv", header = TRUE, sep = ",", quote = "\"")
df %>% kbl() %>% kable_styling(bootstrap_options = "striped", full_width = F, position = "left")

    ProductID

    AvgWithoutNULL

    AvgWithNULL

    22

    275.0000

    275

    23

    50.0000

    25

    35

    180.0000

    180

    Ta có thể thấy trong trường hợp này cách tính giá trị trung bình sử dụng hàm AVG() trả ra kết quả khác so với cách tính sử dụng hàm SUM() và COUNT(). Cụ thể khác biệt nằm ở ProductID = 23. Hàm AVG() trả ra kết quả giá trị thanh toán trung bình cho ProductID = 23 là 50 do hàm AVG() tự động loại bỏ các giá trị NULL ở cột TotalAmount trong quá trình tính toán, nên kết quả không chính xác. Còn cách tính sử dụng SUM() và COUNT() thì có tính đến các bản ghi có giá trị NULL ở cột TotalAmount.

    Ví dụ 2:

    Ta có 2 bảng customers_tmp và contract_tmp như sau

    DROP TABLE IF EXISTS customers_tmp;
DROP TABLE IF EXISTS contracts_tmp;

CREATE TEMPORARY TABLE customers_tmp (
    CustomerID int,
    CustomerName varchar(50)
);

CREATE TEMPORARY TABLE contracts_tmp (
    CustomerID int,
    ContractID int,
    Amount int
);

INSERT INTO customers_tmp
VALUES
    (1, 'Nguyen Van A'),
    (2, 'Nguyen Van B'),
    (3, 'Le Thi C'),
    (4, 'Hoang Thi D')
;

INSERT INTO contracts_tmp
VALUES
    (1, 488, 1230000),
    (2, 490, 2500000),
    (2, 491, 950000),
    (4, 520, 1100000)
;

    Bảng customer_tmp:

    df <- read.csv2("data/customers_tmp.csv", header = TRUE, sep = ",", quote = "\"")
df %>% kbl() %>% kable_styling(bootstrap_options = "striped", full_width = F, position = "left")

    CustomerID

    CustomerName

    1

    Nguyen Van A

    2

    Nguyen Van B

    3

    Le Thi C

    4

    Hoang Thi D

    Bảng contracts_tmp:

    df <- read.csv2("data/contracts_tmp.csv", header = TRUE, sep = ",", quote = "\"")
df %>% kbl() %>% kable_styling(bootstrap_options = "striped", full_width = F, position = "left")

    CustomerID

    ContractID

    Amount

    1

    488

    1230000

    2

    490

    2500000

    2

    491

    950000

    4

    520

    1100000

    Ta được yêu cầu xuất dữ liệu ID và tên của tất cả các khách hàng, kèm theo số lượng hợp đồng của họ.

    Có thể nhiều bạn sẽ viết query như sau:

    SELECT
    a.CustomerID,
    a.CustomerName,
    COUNT(*) AS NoContracts
FROM customers_tmp a
LEFT JOIN contracts_tmp b
    ON a.CustomerID = b.CustomerID
GROUP BY 
    a.CustomerID,
    a.CustomerName;

    Kết quả trả ra như sau:

    df <- read.csv2("data/contracts_tmp_result_wrong.csv", header = TRUE, sep = ",", quote = "\"")
df %>% kbl() %>% kable_styling(bootstrap_options = "striped", full_width = F, position = "left")

    CustomerID

    CustomerName

    NoContracts

    1

    Nguyen Van A

    1

    2

    Nguyen Van B

    2

    3

    Le Thi C

    1

    4

    Hoang Thi D

    1

    Kết quả này sai vì Le Thi C (CustomerID = 3) không có hợp đồng nào cả, nhưng kết quả trả ra lại là có 1 hợp đồng. Điều này là do COUNT(*) đếm tất cả các dòng, và lệnh LEFT JOIN thì giữ lại tất cả các dòng ở bảng bên trái (bảng customers_tmp) kể cả những dòng không join được với bảng bên phải, nên kết quả trả ra từ COUNT(*) cho Le Thi C là 1.

    Để thực hiện đúng được yêu cầu của đề bài, ta phải viết query như sau (bạn hãy thử tự viết query trước khi xem đáp án):

    SELECT
    a.CustomerID,
    a.CustomerName,
    COUNT(DISTINCT b.ContractID) AS NoContracts --COUNT(DISTINCT ) chỉ đếm các giá trị khác NULL, nếu tất cả giá trị đều NULL thì nó sẽ trả về kết quả = 0
FROM customers_tmp a
LEFT JOIN contracts_tmp b
    ON a.CustomerID = b.CustomerID
GROUP BY 
    a.CustomerID,
    a.CustomerName;
    df <- read.csv2("data/contracts_tmp_result_right.csv", header = TRUE, sep = ",", quote = "\"")
df %>% kbl() %>% kable_styling(bootstrap_options = "striped", full_width = F, position = "left")

    CustomerID

    CustomerName

    NoContracts

    1

    Nguyen Van A

    1

    2

    Nguyen Van B

    2

    3

    Le Thi C

    0

    4

    Hoang Thi D

    1

2 Làm việc với dữ liệu thời gian (date, datetime, timestamp)

https://www.red-gate.com/simple-talk/databases/sql-server/t-sql-programming-sql-server/how-to-get-sql-server-dates-and-times-horribly-wrong/

Dữ liệu thời gian là những kiểu dữ liệu rất quan trọng mà ta thường xuyên gặp trong cơ sở dữ liệu quan hệ, nhưng cũng đồng thời là những kiểu dữ liệu rất hay gây phát sinh lỗi trong khi truy vấn và xử lý dữ liệu.

Có 1 số lí do chính cho việc hay xảy ra lỗi khi truy vấn và xử lý những kiểu dữ liệu này:

  • Có nhiều kiểu định dạng ngày tháng khác nhau trên thế giới (VD: ngày 1 tháng 8 năm 2023 có thể được thể hiện dưới dạng 1/8/2023 hoặc 8/1/2023 hoặc 2023-08-01, …) dẫn đến việc dễ nhầm lẫn khi sử dụng

  • Dữ liệu từ các nguồn khác nhau được ghi nhận ở những múi giờ khác nhau, nên nếu không để ý sẽ dẫn đến nhầm lẫn khi sử dụng

  • Yêu cầu phân tích dữ liệu đòi hỏi phải biến đổi ngày tháng để thể hiện nhiều mốc thời gian khác nhau như ngày đầu tháng, cuối tháng, quý, tuần, năm tài chính, ngày nghỉ lễ, … nên có thể phát sinh lỗi nếu ta sử dụng các hàm biến đổi thời gian không chính xác

2.1 Converting string/text to datetime (language_setting của server?, STR_TO_TEXT ?) (especially when inserting data into a table)

2.2 Time zones:

Khi phải lấy dữ liệu từ những hệ thống có server đặt ở các quốc gia khác nhau, đôi khi các bạn sẽ gặp tình huống mỗi dataset lưu dữ liệu theo 1 time zone khác nhau (VD: bộ dữ liệu ở Server Việt Nam lưu theo time zone UTC+7, bộ dữ liệu ở Server Australia lưu theo time zone UTC+10).

Vì vậy khi sử dụng, lưu trữ, biến đổi, phân tích dữ liệu, ta nên lưu dữ liệu về cùng 1 time zone, và best practice là lưu về UTC.

Xem ví dụ dưới đây, lưu ý hàm convert_tz dùng để chuyển ngày giờ từ 1 time zone sang 1 time zone khác:

select 
    current_timestamp(), --show current date and time in server's time zone
    now(), --show current date and time in server's time zone
    utc_timestamp(), --show current date and time in UTC
    convert_tz(now(), '+07:00', '+00:00'), --convert current date and time from UTC+7 to UTC
    current_date(), --show current date in server's time zone
    utc_date();--show current date in UTC
df <- read.csv2("data/timezones_query.csv", header = TRUE, sep = ",", quote = "\"")
df %>% kbl() %>% kable_styling(bootstrap_options = "striped", full_width = F, position = "left")
current_timestamp.. now.. utc_timestamp.. convert_tz.now……07.00…..00.00.. current_date.. utc_date..
2023-08-03 23:34:10 2023-08-03 23:34:10 2023-08-03 16:34:10 2023-08-03 16:34:10 2023-08-03 2023-08-03

2.3 Hiểu rõ cơ chế làm tròn của các hàm tính toán về thời gian

Để tính toán khoảng cách về thời gian được chính xác ta nên sử dụng các hàm tính toán có sẵn của cơ sở dữ liệu. Ví dụ dưới đây tính khoảng cách thời gian theo giây, phút, giờ, ngày giữa 2 mốc thời gian trong MySQL

SET @a = '2015-12-31 23:59:02.000', 
    @b = '2016-01-01 00:00:01.000';
SELECT 
    TIMESTAMPDIFF(SECOND, @a, @b) AS DELTA_SECOND,
    TIMESTAMPDIFF(MINUTE, @a, @b) AS DELTA_MINUTE,
    TIMESTAMPDIFF(HOUR, @a, @b) AS DELTA_HOUR,
    TIMESTAMPDIFF(DAY, @a, @b) AS DELTA_DAY;
df <- read.csv2("data/timestampdiff.csv", header = TRUE, sep = ",", quote = "\"")
df %>% kbl() %>% kable_styling(bootstrap_options = "striped", full_width = F, position = "left")
DELTA_SECOND DELTA_MINUTE DELTA_HOUR DELTA_DAY
59 0 0 0

Ta thấy rằng khoảng cách về “second” (giây) được tính toán đúng, nhưng khoảng cách về phút, giờ không chính xác. Khoảng cách về ngày có thể chấp nhận được (với logic là cứ cách từ 24h thì mới tính là cách 1 ngày).

Hãy thử tự viết query để trả ra kết quả chính xác, sau đó xem đáp án dưới đây

SET @a = '2015-12-31 23:59:02.000', 
    @b = '2016-01-01 00:00:01.000';
SELECT 
    TIMESTAMPDIFF(SECOND, @a, @b) AS DELTA_SECOND,
    TIMESTAMPDIFF(SECOND, @a, @b) / 60 AS DELTA_MINUTE,
    TIMESTAMPDIFF(SECOND, @a, @b) / 3600 AS DELTA_HOUR,
    TIMESTAMPDIFF(DAY, @a, @b) AS DELTA_DAY;

Kết quả đúng cần trả ra sẽ như sau:

df <- read.csv2("data/timestampdiff_fix.csv", header = TRUE, sep = ",", quote = "\"")
df %>% kbl() %>% kable_styling(bootstrap_options = "striped", full_width = F, position = "left")
DELTA_SECOND DELTA_MINUTE DELTA_HOUR DELTA_DAY
59 0.9833 0.0164 0

2.4 Tính ngày đầu và cuối tháng

Ngày đầu và cuối tháng là 2 mốc thời gian quan trọng nên ta sẽ thường xuyên gặp phải trong công việc thực tế. Thông thường, các Data Engineers hoặc Database Admins sẽ tạo 1 bảng calendar để lưu trữ ngày tháng giúp các Analysts thuận tiện hơn trong việc xử lý dữ liệu thời gian. Tuy nhiên, nếu không có 1 bảng như vậy thì ta cũng cần biết cách xử lý để tính ra ngày đầu và cuối tháng của 1 date bất kỳ.

Ví dụ ta có 1 bảng calendar đơn giản như sau. Hãy thử viết lệnh SQL để biến đổi từ 1 date bất kỳ ra được kết quả như cột MonthStartDate và MonthEndDate (không cần phải viết code để tạo ra cả bảng calendar mà chỉ cần tìm được ngày đầu và cuối tháng từ 1 date bất kỳ thôi). Sau đó, mở đoạn code dưới đây để xem cách các cột này được tạo ra.

-- create calendar table
DROP TABLE IF EXISTS calendar;
CREATE TABLE calendar (
    Date DATE NOT NULL,
    DateString VARCHAR(8) NOT NULL,
    MonthStartDate DATE NOT NULL,
    MonthEndDate DATE NOT NULL,
    Year INTEGER NOT NULL,
  Month INTEGER NOT NULL, -- 1 to 12
    Day INTEGER NOT NULL, -- 1 to 31
    Quarter INTEGER NOT NULL, -- 1 to 4
    Week INTEGER NOT NULL, -- 1 to 52/53
    DayName VARCHAR(9) NOT NULL, -- 'Monday', 'Tuesday'...
    MonthName VARCHAR(9) NOT NULL, -- 'January', 'February'...
    WeekendFlag CHAR(1) DEFAULT 'F'
);

-- insert values into calendar table
DROP PROCEDURE IF EXISTS fill_date_dimension;
DELIMITER //
CREATE PROCEDURE fill_date_dimension(IN startdate DATE,IN stopdate DATE)
BEGIN
    DECLARE currentdate DATE;
    SET currentdate = startdate;
    WHILE currentdate <= stopdate DO
        INSERT INTO calendar VALUES (
                        currentdate,
                        DATE_FORMAT(currentdate, '%Y%m%d'),
                        DATE_ADD(currentdate, INTERVAL -DAY(currentdate)+1 DAY),
                        LAST_DAY(currentdate),
                        YEAR(currentdate),
                        MONTH(currentdate),
                        DAY(currentdate),
                        QUARTER(currentdate),
                        WEEKOFYEAR(currentdate),
                        DATE_FORMAT(currentdate,'%W'),
                        DATE_FORMAT(currentdate,'%M'),
                        CASE
                            WHEN DAYOFWEEK(currentdate) in (1, 7) THEN 'T' 
                            ELSE 'F' 
                        END);
        SET currentdate = ADDDATE(currentdate,INTERVAL 1 DAY);
    END WHILE;
END
//
DELIMITER ;

CALL fill_date_dimension('2022-01-01','2023-12-31');

-- show calendar table
select * from calendar;
df <- read.csv2("data/calendar.csv", header = TRUE, sep = ",", quote = "\"")
datatable(df, options = list(scrollX = TRUE, scrollY = FALSE))

2.5 Viết điều kiện lọc về thời gian chính xác

Khi lọc dữ liệu thời gian, ta cần lưu ý một số vấn đề sau:

2.5.1 Xác định đúng kiểu dữ liệu giữa date, datetime, timestamp

Ta có bảng dữ liệu date_table như sau:

-- create a temporary table
DROP TABLE IF EXISTS date_table;
CREATE TEMPORARY TABLE date_table (
    Id INT,
    Date DATETIME(3)
);

-- insert values into temporary table
INSERT INTO date_table
VALUES
    (1, '2023-03-11 15:28:40.224'),
    (2, '2023-03-11 23:59:59.998'),
    (3, '2023-03-12 00:00:00.000'),
    (4, '2023-03-12 21:48:25.442'),
    (5, '2023-03-13 00:00:00.000'),
    (6, '2023-03-13 08:09:21.668');
df <- read.csv2("data/date_table.csv", header = TRUE, sep = ",", quote = "\"")
df %>% kbl() %>% kable_styling(bootstrap_options = "striped", full_width = F, position = "left")
Id Date
1 2023-03-11 15:28:40.224
2 2023-03-11 23:59:59.998
3 2023-03-12 00:00:00.000
4 2023-03-12 21:48:25.442
5 2023-03-13 00:00:00.000
6 2023-03-13 08:09:21.668

Yêu cầu: viết query trả ra dữ liệu ở bảng date_table của ngày 12 tháng 3 năm 2023

Nếu bạn viết query như dưới đây, kết quả trả ra sẽ không chính xác

SELECT * 
FROM date_table
WHERE Date = '2023-03-12'
;
df <- read.csv2("data/date_table_wrong_result.csv", header = TRUE, sep = ",", quote = "\"")
df %>% kbl() %>% kable_styling(bootstrap_options = "striped", full_width = F, position = "left")
Id Date
3 2023-03-12 00:00:00.000

Lí do là vì trường Date ở bảng date_table là dạng datetime, nên khi ta viết điều kiện lọc WHERE Date = ‘2023-03-12’ thì cơ sở dữ liệu hiểu điều kiện nàytương tự với điều kiện WHERE Date = ‘2023-03-12 00:00:00.000’ (lúc nửa đêm).

Query đúng để đáp ứng yêu cầu của đề bài là như sau:

SELECT * 
FROM date_table
WHERE Date >= '2023-03-12 00:00:00.000'
    AND Date < '2023-03-13 00:00:00.000'
;

Ta cũng có thể viết query như sau, nhưng cách viết query này sẽ không tận dụng được index ở cột Date (nếu có). Xem thêm ở phần Sử dụng Index chính xác trong bài này

SELECT * 
FROM date_table
WHERE CAST(Date AS DATE) = '2023-03-12'
;

2.6 Issue with subtracting minutes from datetime in sql?

ISO standard 8601 for date format. To comply with this standard, specify the date as yyyy-mm-ddThh:mm:ss.

3 Lệnh CASE

xx

4 Window functions

xx

5 Wildcards

xx

6 PIVOT và UNPIVOT

7 Xử lý các giá trị NULL

8 Sử dụng Index chính xác

8.1 Index là gì

Cũng giống như mục lục của 1 cuốn sách, index giúp ta tìm kiếm dữ liệu trong bảng nhanh hơn. Index thường được tạo trên table hoặc view.

8.1.1 ccc

8.2 Tạo index

xx

8.3 Sử dụng index

SELECT
  Col1, Col2, Col3 --đây là nhóm cột ta dự đoán là khóa chính
FROM Table1
GROUP BY Col1, Col2, Col3
HAVING COUNT(1) > 1 --nếu kết quả trả ra không có gì thì tức là ta dự đoán đúng khóa chính

9 Best practices

Viết code sao cho chạy lại nhiều lần đều không lỗi và trả ra kết quả giống nhau

Ref: https://www.metabase.com/learn/sql-questions/sql-best-practices#sql-best-practices-for-from