Hàm cửa sổ trong Hive

Bài toán đầu tiên: Có bảng dữ liệu điểm số t1 như sau

Yêu cầu: Tính tổng điểm và xếp hạng

• Chuyển hàng thành cột + hàm rank()

CREATE TABLE StudentScores (
    user_id varchar(255),
    subject_name varchar(255),
    points int
);

INSERT INTO StudentScores VALUES 
    ("A", "Chinese", 90), 
    ("A", "Math", 100),
    ("B", "Chinese", 99),
    ("B", "Math", 100);

SELECT
    *,
    rank() OVER(ORDER BY overall_score DESC) AS ranking
FROM (
    SELECT
        user_id,
        MAX(CASE
            WHEN subject_name = "Chinese" THEN points
            ELSE 0
        END) AS chinese_points,
        MAX(CASE
            WHEN subject_name = "Math" THEN points
            ELSE 0
        END) AS math_points,
        SUM(points) AS overall_score
    FROM StudentScores
    GROUP BY user_id
) intermediate_result;

• Tóm tắt:
• Chuyển hàng thành cột: Muốn chuyển thành nhiều cột, trong SELECT phải hiển thị các cột đó, viết trực tiếp ra rồi dùng CASE WHEN
• Hàm rank(): rank() OVER(PARTITION BY ... ORDER BY ...)

Phân tích giữ chân người dùng:

Loại bỏ trùng lặp, sau đó lấy ngày trừ đi 1, liên kết lại để xác định những ngày đăng nhập liên tục

Tính tỷ lệ giữ chân người dùng mới trong tháng 11 (bài tập từ trang Nowcoder)

Ví dụ: Tỷ lệ giữ chân người dùng mới là 66.7%

Ngày 11.1 có 3 người dùng mới: 1, 2, 3

Ngày 11.2 có 2 người dùng mới quay lại: 1, 2

Vậy tỷ lệ giữ chân là 2/3 = 66.7%

• Tóm tắt:
• DATE_SUB(date, INTERVAL 1 DAY) - Ghi nhớ hàm này
• Tính giữ chân: Tổng hợp người dùng, thao tác trên ngày, hai bảng LEFT JOIN, những bản ghi có thể join được là người dùng đăng nhập vào ngày thứ hai, ngày thứ ba

SELECT
    AVG(IF(retained_user.user_id IS NOT NULL, 1, 0)) AS retention_rate
FROM
(
    SELECT DISTINCT
        user_id,
        activity_date
    FROM
        user_activity_log
) current_day_users
LEFT JOIN (
    SELECT DISTINCT
        user_id,
        DATE_SUB(activity_date, INTERVAL 1 DAY) AS previous_date
    FROM
        user_activity_log
) next_day_users ON current_day_users.user_id = next_day_users.user_id 
                 AND current_day_users.activity_date = next_day_users.previous_date

Thực hiện trong Hive:

Trong số học sinh không đến từ Bắc Kinh, tìm 10 người dùng có điểm trung bình cao nhất trong mỗi lớp cùng thông tin chi tiết.

Kết quả có dạng: class, uid, avg_score, scores, province

Bảng điểm số học sinh Score:

Bảng thông tin học sinh stu:

Chìa khóa của bài toán là liệt kê điểm số từng môn học.

Sử dụng phối hợp hai hàm LATERAL VIEW và EXPLODE để phân tách giá trị kiểu mảng.

LATERAL VIEW thường dùng cho logic xử lý từ một hàng thành nhiều hàng.

Trong Hive, để xử lý logic từ nhiều hàng thành một hàng có thể sử dụng hàm COLLECT_SET hoặc COLLECT_LIST, cả hai đều trả về tập hợp kiểu mảng, sau đó lại quay về logic xử lý đối tượng mảng, khác biệt ở chỗ cái trước sẽ loại bỏ trùng lặp. CONCAT(môn học,'-',điểm), sau đó lấy kết quả COLLECT_SET

SELECT
    student_id,
    class_name,
    score_element
FROM 
    ScoreTable
LATERAL VIEW
    EXPLODE(student_scores) exploded_view AS score_element;

-- Đến đây, chúng ta có thể lấy được
-- id class subject-score
-- 1  1      math-90
-- 1  1      music-100

-- Tiếp tục xử lý
SELECT SPLIT('math_90', '_')[0];

SELECT
    *
FROM (
    SELECT
       student_id, 
       class_name,
       region,
       average_score,
       total_score,
       RANK() OVER(PARTITION BY class_name ORDER BY average_score DESC) position
    FROM (
        SELECT
            temp.student_id,
            temp.class_name,
            temp.region,
            AVG(score_value) average_score,
            SUM(score_value) total_score
        FROM (
            SELECT
                subquery.student_id,
                subquery.class_name,
                subquery.region,
                SPLIT(score_part, '_')[0] AS subject_name,
                SPLIT(score_part, '_')[1] AS score_value
            FROM (
                SELECT
                    s.student_id,
                    s.class_name,
                    s.region,
                    score_part
                FROM 
                    ScoreTable s
                JOIN (
                    SELECT
                        user_id,
                        province
                    FROM
                        StudentInfo
                    WHERE
                        province != '北京'
                ) region_filter ON region_filter.user_id = s.student_id
                LATERAL VIEW
                    EXPLODE(s.student_scores) temp_view AS score_part
            ) subquery
        ) temp
        GROUP BY temp.student_id, temp.class_name, temp.region
    ) calculated_scores
) ranked_results
WHERE ranked_results.position <= 10;

Tóm tắt:

1. Sử dụng EXPLODE để phân tách mảng chuỗi thành nhiều hàng, thông qua LATERAL VIEW liên kết với học sinh tương ứng, bảng dữ liệu trở thành [Học sinh Lớp Môn học Điểm số Tỉnh thành]
2. Để lấy xếp hạng, cần tính điểm trung bình trước, thêm một lớp dựa trên điểm trung bình, sau đó thêm xếp hạng theo lớp vào dòng cuối cùng
3. Thêm một lớp nữa, dùng WHERE để lọc những bản ghi có ranking <= 10

Cách trích xuất JSON?

Tóm tắt:

1. GET_JSON_OBJECT -- Nhập chuỗi, xuất giá trị của key chỉ định
   GET_JSON_OBJECT('{"movie":"594","rate":"4","timeStamp":"978302268","uid":"1"}','$.movie')
2. JSON_TUPLE -- Có thể trích xuất nhiều key trong một lần

SELECT extracted_movie, extracted_rate, extracted_timestamp, extracted_user_id 
FROM json_data a 
LATERAL VIEW JSON_TUPLE(a.json_column, 'movie', 'rate', 'timeStamp', 'uid') b 
AS extracted_movie, extracted_rate, extracted_timestamp, extracted_user_id;

Nhưng nếu dữ liệu đưa vào là mảng JSON, như [{},{}] thì sao?

EXPLODE để phân tách mảng thành nhiều hàng, nhưng thường dữ liệu đưa vào là chuỗi...

1. REGEXP_REPLACE(string, '\\\[|\\\]', '') - Loại bỏ dấu [] khỏi chuỗi trước
2. REGEXP_REPLACE - Thay thế dấu , giữa {},{} bằng ; để tránh bị SPLIT cắt nhầm
3. SPLIT(string, ';') -- Tạo ra mảng các '{}' riêng biệt
4. EXPLODE vào việc, phân tách thành nhiều hàng

SELECT EXPLODE(SPLIT(
    REGEXP_REPLACE(
        REGEXP_REPLACE(
            '[
                {"website":"www.baidu.com","name":"百度"},
                {"website":"google.com","name":"谷歌"}
            ]', 
            '\\[|\\]', ''),  -- Loại bỏ dấu ngoặc vuông hai bên mảng JSON
            
                 '\\}\\,\\{',    -- Thay dấu phẩy giữa các phần tử mảng JSON thành dấu chấm phẩy
                '\\}\\;\\{'),
                
                 '\\;'));    -- Sử dụng dấu chấm phẩy làm ký tự phân cách