數據開發工程師面試題-窗口函數

表名: 交易明細表

字段: 產品、交易時間(精確到時分秒)、交易金額。

進行以下四個統計需求的實現：

1)、統計每個產品每天的總交易額

2)、統計每個產品每天的當月總交易額

3)、統計每天當日交易額排名前三的產品

4)、統計每個產品每天的最近7天總交易額

例如建表語句如下：

CREATE TABLE transaction_detail (
product string COMMENT '產品',
transaction_time datetime COMMENT '交易時間',
amount decimal(20,2) COMMENT '交易金額'
) ;

以下是使用HiveSQL實現四個統計需求的完整解決方案，結合窗口函數和聚合函數實現：

1. 統計每個產品每天的總交易額

SELECT 
    product,
    to_date(transaction_time) AS trans_date,
    SUM(amount) AS daily_total
FROM transaction_detail
GROUP BY product, to_date(transaction_time);

說明??：

按產品和交易日期分組，使用SUM()聚合函數計算每日交易總額
to_date()函數提取交易時間的日期部分

2. 統計每個產品每天的當月總交易額

WITH daily_summary AS (
    SELECT 
        product,
        to_date(transaction_time) AS trans_date,
        SUM(amount) AS daily_total
    FROM transaction_detail
    GROUP BY product, to_date(transaction_time)
)
SELECT 
    product,
    trans_date,
    daily_total,
    SUM(daily_total) OVER (
        PARTITION BY product, month(trans_date) 
        ORDER BY trans_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS monthly_total
FROM daily_summary;

??說明??：

先創建CTE計算每日交易額
使用窗口函數SUM() OVER()按產品和月份分區，計算整個月的累計交易額
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING確保計算整個分區。
如果需要計算截至當天的累計交易額，需要將UNBOUNDED FOLLOWING替換為CURRENT ROW。

3. 統計每天交易額排名前三的產品

WITH daily_product_summary AS (
    SELECT 
        to_date(transaction_time) AS trans_date,
        product,
        SUM(amount) AS daily_total
    FROM transaction_detail
    GROUP BY to_date(transaction_time), product
),
ranked_products AS (
    SELECT 
        trans_date,
        product,
        daily_total,
        RANK() OVER (
            PARTITION BY trans_date 
            ORDER BY daily_total DESC
        ) AS rank
    FROM daily_product_summary
)
SELECT 
    trans_date,
    product,
    daily_total
FROM ranked_products
WHERE rank <= 3;

??說明??：

先按日期和產品分組計算每日交易額
使用RANK() OVER()窗口函數按日期分區、交易額降序排名
篩選排名≤3的記錄

4. 統計每個產品每天的最近7天總交易額

WITH daily_summary AS (
    SELECT 
        product,
        to_date(transaction_time) AS trans_date,
        SUM(amount) AS daily_total,
        unix_timestamp(to_date(transaction_time)) AS date_ts  -- 轉換為時間戳
    FROM transaction_detail
    GROUP BY product, to_date(transaction_time)
)
SELECT 
    product,
    trans_date,
    daily_total,
    SUM(daily_total) OVER (
        PARTITION BY product 
        ORDER BY date_ts 
        RANGE BETWEEN 6 * 86400 PRECEDING AND CURRENT ROW  -- 6天*86400秒
    ) AS last7d_total
FROM daily_summary;

??說明??：

先計算每日交易額并轉換為Unix時間戳
使用RANGE BETWEEN指定時間范圍窗口（7天=604,800秒）
按產品分區，計算滾動7天交易總額

關鍵函數說明：

??窗口函數結構??：
函數 OVER (PARTITION BY ... ORDER BY ... [窗口范圍])
??時間范圍窗口??：
- RANGE BETWEEN：按實際時間值計算范圍（需數值類型）
- ROWS BETWEEN：按物理行數計算范圍
??排名函數區別??：
- RANK()：并列排名會跳過后續名次（1,1,3）
- DENSE_RANK()：并列不跳名次（1,1,2）
- ROW_NUMBER()：連續編號（1,2,3）

posted @ 2025-05-29 10:17 業余磚家閱讀(128) 評論(0) 收藏舉報

刷新頁面返回頂部

業余磚家

沒有比人更高的山，沒有比腳更長的路。

數據開發工程師面試題-窗口函數

1. 統計每個產品每天的總交易額

2. 統計每個產品每天的當月總交易額

3. 統計每天交易額排名前三的產品

4. 統計每個產品每天的最近7天總交易額

關鍵函數說明：

公告