Các kỹ thuật SQL nâng cao hữu ích nhất để thành công trong ngành công nghệ

[ad_1]

Cú pháp, trường hợp sử dụng và mẹo của chuyên gia để thành thạo SQL nâng cao

Là một chuyên gia dữ liệu giàu kinh nghiệm làm việc trong ngành công nghệ trong nhiều năm, tôi đã xử lý hàng tấn tập dữ liệu lớn. SQL là công cụ được sử dụng thường xuyên nhất để thao tác dữ liệu, truy vấn dữ liệu và phân tích. Mặc dù việc thành thạo SQL cơ bản và trung cấp tương đối dễ dàng, nhưng việc đạt được sự thành thạo công cụ này và sử dụng thành thạo trong nhiều tình huống khác nhau đôi khi lại là một thách thức. Có một số kỹ thuật SQL nâng cao mà bạn phải quen thuộc nếu muốn làm việc cho các công ty công nghệ hàng đầu. Hôm nay, tôi sẽ chia sẻ những kỹ thuật SQL nâng cao hữu ích nhất mà bạn chắc chắn sẽ sử dụng trong công việc của mình. Để giúp bạn hiểu rõ hơn về chúng, tôi sẽ tạo một số trường hợp sử dụng và sử dụng dữ liệu giả để giải thích các tình huống sử dụng chúng và cách sử dụng chúng. Đối với mỗi trường hợp sử dụng, mã lập trình cũng sẽ được cung cấp.

Chức năng cửa sổ

Các hàm cửa sổ thực hiện các phép tính trên một tập hợp các hàng được chỉ định, được gọi là “cửa sổ”, từ một truy vấn và trả về một giá trị duy nhất liên quan đến hàng hiện tại.

Chúng tôi muốn sử dụng dữ liệu bán hàng từ chương trình khuyến mãi tại Star Division Retailer để giải thích các chức năng của cửa sổ. Bảng này chứa ba cột: Mã số người bán hàng, là ID duy nhất cho mỗi nhân viên bán hàng, Phòng, nhân viên bán hàng đến từ đâu và Sản lượng bán ra, là hiệu suất bán hàng của từng người trong quá trình khuyến mại. Ban quản lý của Star Division Retailer muốn xem tổng doanh số bán hàng của từng phòng ban. Nhiệm vụ của bạn là thêm một cột, tổng_phòng_ban, đến bàn.

Đầu tiên, chúng ta tạo bảng promo_sales với 3 cột trong Cơ sở dữ liệu.

CREATE TABLE promo_sales(
  Sale_Person_ID VARCHAR(40) PRIMARY KEY,
  Division VARCHAR(40),
  Sales_Amount int
);

INSERT INTO promo_sales VALUES (001, 'Cosmetics', 500);
INSERT INTO promo_sales VALUES (002, 'Cosmetics', 700);
INSERT INTO promo_sales VALUES (003, 'Vogue', 1000);
INSERT INTO promo_sales VALUES (004, 'Jewelry', 800);
INSERT INTO promo_sales VALUES (005, 'Vogue', 850);
INSERT INTO promo_sales VALUES (006, 'Child', 500);
INSERT INTO promo_sales VALUES (007, 'Cosmetics', 900);
INSERT INTO promo_sales VALUES (008, 'Vogue', 600);
INSERT INTO promo_sales VALUES (009, 'Vogue', 1200);
INSERT INTO promo_sales VALUES (010, 'Jewelry', 900);
INSERT INTO promo_sales VALUES (011, 'Child', 700);
INSERT INTO promo_sales VALUES (012, 'Vogue', 1500);
INSERT INTO promo_sales VALUES (013, 'Cosmetics', 850);
INSERT INTO promo_sales VALUES (014, 'Child', 750);
INSERT INTO promo_sales VALUES (015, 'Jewelry', 950);

Tiếp theo, chúng ta cần tính tổng doanh số bán hàng của từng phòng ban và thêm một cột, tổng_phòng_banđến bảng promo_sales. Nếu không sử dụng hàm cửa sổ, chúng ta sẽ tạo một bảng khác, có tên là department_total, sử dụng mệnh đề “GROUP BY” để lấy số tiền bán hàng cho từng phòng ban. Sau đó, chúng ta sẽ nối các bảng promo_sales và department_total. Hàm cửa sổ cung cấp một cách mạnh mẽ để thực hiện phép tính này trong một truy vấn SQL duy nhất, đơn giản hóa và tối ưu hóa tác vụ xử lý dữ liệu.

Chúng ta có thể sử dụng hàm SUM() để hoàn thành nhiệm vụ.

SELECT 
    Sale_Person_ID, 
    Division, 
    Sales_Amount,
    SUM(Sales_Amount) OVER (PARTITION BY Division) AS dept_total
FROM 
    promo_sales;

Sau đó, bảng promo_sales có thêm một cột tổng_phòng_ban như mong đợi.

Ví dụ này minh họa rằng các hàm cửa sổ không làm giảm số hàng trong tập kết quả, không giống như các hàm tổng hợp được sử dụng với GROUP BY. Các hàm cửa sổ có thể thực hiện các phép tính như tổng đang chạy, trung bình và số đếm, và chúng cũng có thể được sử dụng cho các hoạt động như xếp hạng và nhiều hơn nữa. Bây giờ, chúng ta hãy chuyển sang ví dụ tiếp theo.

Ban quản lý của Star Division Retailer cũng muốn xếp hạng nhân viên bán hàng theo hiệu suất của họ trong quá trình thăng chức trong từng phòng ban. Lần này chúng ta có thể sử dụng RANK() để xếp hạng nhân viên bán hàng.

SELECT 
    Sale_Person_ID, 
    Division, 
    Sales_Amount,
    RANK() OVER (PARTITION BY Division ORDER BY Sales_Amount DESC) AS Rank_in_Dept
FROM 
    promo_sales;

Các hàm cửa sổ được sử dụng rộng rãi trong phân tích dữ liệu. Các loại hàm cửa sổ phổ biến bao gồm hàm xếp hạng, hàm tổng hợp, hàm bù trừ và hàm phân phối.

1. Chức năng xếp hạng: Các hàm xếp hạng gán thứ hạng hoặc số hàng cho mỗi hàng trong một phân vùng của tập kết quả.

· ROW_NUMBER(): Gán các số nguyên tuần tự duy nhất cho các hàng

· RANK(): Gán thứ hạng với khoảng cách cho các mối quan hệ

· DENSE_RANK(): Gán thứ hạng không có khoảng trống cho các mối quan hệ

· NTILE(n): Chia các hàng thành n nhóm gần bằng nhau

2. Chức năng tổng hợp: Các hàm tổng hợp được sử dụng để thực hiện tính toán hoặc chạy số liệu thống kê trên một tập hợp các hàng liên quan đến hàng hiện tại.

· SUM(): Tính tổng giá trị trong một phân vùng

· AVG(): Tính giá trị trung bình trong một phân vùng

· COUNT(): Lấy số lượng phần tử trong một phân vùng

· MAX(): Lấy giá trị lớn nhất trong một phân vùng

· MIN(): Lấy giá trị nhỏ nhất trong một phân vùng

3. Các hàm bù trừ: Các hàm bù trừ cho phép truy cập dữ liệu từ các hàng khác liên quan đến hàng hiện tại. Chúng được sử dụng khi bạn cần so sánh các giá trị giữa các hàng hoặc khi bạn chạy phân tích chuỗi thời gian hoặc phát hiện xu hướng.

· LAG(): Truy cập dữ liệu từ hàng trước

· LEAD(): Truy cập dữ liệu từ hàng tiếp theo

· FIRST_VALUE(): Lấy giá trị đầu tiên trong một tập hợp có thứ tự

· LAST_VALUE(): Lấy giá trị cuối cùng trong một tập hợp đã sắp xếp

4. Các hàm phân phối: Các hàm phân phối tính toán vị trí tương đối của một giá trị trong một nhóm giá trị và cũng giúp bạn hiểu được sự phân phối của các giá trị.

· PERCENT_RANK(): Tính thứ hạng phần trăm của một hàng

· CUME_DIST(): Tính toán phân phối tích lũy của một giá trị

· PERCENTILE_CONT(): Tính toán giá trị phần trăm liên tục

· PERCENTILE_DISC(): Tính toán giá trị phần trăm rời rạc

Các truy vấn phụ

Truy vấn phụ, còn được gọi là truy vấn lồng nhau hoặc truy vấn bên trong, là truy vấn trong một truy vấn SQL khác. Truy vấn này có thể được sử dụng để tạo một cột mới, một bảng mới hoặc một số điều kiện để hạn chế hơn nữa dữ liệu được truy xuất trong truy vấn chính.

Chúng ta hãy tiếp tục sử dụng bảng dữ liệu promo_sales từ Star Division Retailer để minh họa.

1. Truy vấn phụ để tạo cột mới

Lần này chúng tôi muốn thêm một cột mới để hiển thị sự khác biệt giữa doanh số của mỗi nhân viên bán hàng và doanh số trung bình của toàn bộ phòng ban.

SELECT 
    Sale_Person_ID, 
    Division, 
    Sales_Amount,
    Sales_Amount - (SELECT AVG(Sales_Amount) OVER (PARTITION BY Division) FROM promo_sales) AS sales_diff
FROM 
    promo_sales;

2. Truy vấn con để tạo một bảng mới

Bảng mkt_cost chứa chi phí quảng cáo cho tất cả các phòng ban trong chương trình khuyến mãi này. Để xác định phòng ban nào tiết kiệm chi phí nhất, chúng ta cần tính lợi nhuận từ chi phí quảng cáo cho từng phòng ban. Chúng ta có thể sử dụng truy vấn phụ để tạo bảng mới bao gồm tổng doanh số và chi phí tiếp thị cho các phòng ban này, sau đó phân tích dữ liệu trong bảng mới này.

SELECT 
    Division, 
    dept_ttl,
    Mkt_Cost,
    dept_ttl/Mkt_Cost AS ROAS
FROM 
    (SELECT
        s.Division,
        SUM(s.Sales_Amount) AS dept_ttl,
        c.Mkt_Cost
     FROM 
        promo_sales s
     GROUP BY s.Division
     LEFT JOIN 
        mkt_cost c
     ON s.Division=c.Division
     ) 

3. Truy vấn phụ để tạo điều kiện hạn chế

Truy vấn phụ cũng có thể được sử dụng để chọn những nhân viên bán hàng có doanh số vượt quá doanh số trung bình của tất cả những nhân viên bán hàng.

SELECT 
    Sale_Person_ID, 
    Division, 
    Sales_Amount
FROM 
    promo_sales
WHERE 
    Sales_Amount > (SELECT AVG(wage) FROM promo_sales);

Bên cạnh 3 loại truy vấn phụ trên, có một truy vấn phụ thường được sử dụng —truy vấn phụ có liên quanphụ thuộc vào truy vấn bên ngoài để biết giá trị của nó. Nó được thực thi một lần cho mỗi hàng trong truy vấn bên ngoài.

Có thể sử dụng truy vấn phụ tương quan để tìm những nhân viên bán hàng có hiệu suất bán hàng cao hơn mức trung bình của bộ phận họ trong thời gian khuyến mại.

SELECT 
    ps_1.Sale_Person_ID, 
    ps_1.Division, 
    ps_1.Sales_Amount
FROM 
    promo_sales ps_1
WHERE 
    ps_1.Sales_Amount > (
          SELECT AVG(ps_2.Sales_Amount) 
          FROM promo_sales ps_2 
          WHERE ps_2.Division = ps_1.Division
);

Các truy vấn phụ cho phép bạn viết các truy vấn phức tạp trả lời các câu hỏi phức tạp về dữ liệu của bạn. Nhưng điều quan trọng là phải sử dụng chúng một cách thận trọng, vì việc sử dụng quá mức có thể dẫn đến các vấn đề về hiệu suất, đặc biệt là với các tập dữ liệu lớn.

Biểu thức bảng phổ biến

Biểu thức bảng chung (CTE) là một tập kết quả tạm thời được đặt tên tồn tại trong phạm vi của một câu lệnh SQL duy nhất. CTE được định nghĩa bằng cách sử dụng mệnh đề WITH và có thể được tham chiếu một hoặc nhiều lần trong câu lệnh SELECT, INSERT, UPDATE, DELETE hoặc MERGE tiếp theo.

Về cơ bản có hai loại CTE trong SQL:

1. CTE không đệ quy: CTE không đệ quy được sử dụng để đơn giản hóa các truy vấn phức tạp bằng cách chia nhỏ chúng thành các phần dễ quản lý hơn. Chúng không tự tham chiếu nên là loại CTE đơn giản nhất.
2. CTE đệ quy: CTE đệ quy tự tham chiếu đến chính nó trong định nghĩa của chúng, cho phép bạn làm việc với dữ liệu phân cấp hoặc cấu trúc cây.

Bây giờ, hãy sử dụng CTE không đệ quy để làm việc với bảng dữ liệu promo_sales. Nhiệm vụ là tính toán số tiền bán hàng trung bình từ mỗi phòng ban và so sánh với số tiền trung bình của cửa hàng trong thời gian khuyến mại.

WITH dept_avg AS (
    SELECT 
        Division,
        AVG(Sales_Amount) AS dept_avg
    FROM 
        promo_sales
    GROUP BY 
        Division
),

store_avg AS (
    SELECT AVG(Sales_Amount) AS store_avg
    FROM promo_sales
)

SELECT 
    d.Division,
    d.dept_avg,
    s.store_avg,
    d.dept_avg - s.store_avg AS diff
FROM 
    dept_avg d
CROSS JOIN 
    store_avg s;

Vì CTE đệ quy có thể xử lý dữ liệu phân cấp nên chúng tôi đang cố gắng tạo ra một chuỗi số từ 1 đến 10.

WITH RECURSIVE sequence_by_10(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1
    FROM sequence_by_10
    WHERE n < 10
)
SELECT n FROM sequence_by_10;

CTE mạnh mẽ vì chúng cải thiện khả năng đọc và khả năng bảo trì các truy vấn phức tạp bằng cách đơn giản hóa chúng. Chúng đặc biệt hữu ích khi bạn cần tham chiếu cùng một truy vấn phụ nhiều lần trong truy vấn chính hoặc khi bạn đang làm việc với các cấu trúc đệ quy.

Phần kết luận

Ba kỹ thuật SQL nâng cao có thể cải thiện đáng kể khả năng phân tích và thao tác dữ liệu của bạn. Các hàm cửa sổ cho phép bạn thực hiện các phép tính phức tạp trên nhiều tập hợp hàng trong khi vẫn duy trì ngữ cảnh của từng bản ghi. Các truy vấn phụ cho phép bạn viết các truy vấn phức tạp để trả lời các câu hỏi phức tạp về dữ liệu của mình. CTE cung cấp một cách mạnh mẽ để cấu trúc và đơn giản hóa các truy vấn SQL của bạn, giúp chúng dễ đọc và dễ bảo trì hơn. Bằng cách tích hợp các kỹ thuật nâng cao này vào bộ công cụ SQL của bạn, bạn sẽ có thể nâng cấp các kỹ năng SQL của mình để giải quyết các thách thức dữ liệu phức tạp, cung cấp thông tin chi tiết có giá trị hoặc tạo bảng điều khiển kể chuyện trong vai trò là một chuyên gia dữ liệu.

Các kỹ thuật SQL nâng cao hữu ích nhất để thành công trong ngành công nghệ ban đầu được xuất bản trong Hướng tới Khoa học dữ liệu trên Medium, nơi mọi người tiếp tục cuộc trò chuyện bằng cách làm nổi bật và phản hồi câu chuyện này.

[ad_2]

Source link