时间戳

首页 > 解决方案 > First 和 Last 合并窗口函数

sql - First 和 Last 合并窗口函数

问题描述

有没有比从 CTE/表/子查询派生第一个和最后一个值然后加入键更简单的方法来使用第一个和最后一个值窗口函数？

下面的玩具示例。实际上，我的数据集很大，并且在 hadoop 中有更多的记录和字段。

对于每个客户，我想查看他们购买的第一个商品（由 purchase_number 增量表示）以及最后一个 item_origin。

下面的示例查询中的“source_table”。

客户ID 购买编号 物品 item_origin
0001 1 球 俄亥俄州
0001 2 纸 德克萨斯州
0001 3 笔 堪萨斯
0002 1 笔 堪萨斯
0003 1 纸 德克萨斯州
0004 2 飞盘 佛罗里达

如果这是完整的表，结果应该是：

客户ID 第一个项目 last_item_origin
0001 球 堪萨斯
0002 笔 堪萨斯
0003 纸 佛罗里达

这是我目前正在实施的方式，但这似乎有点做作。

WITH min_max AS (
SELECT 
    customer_id,
    MIN(purchase_number) AS min_row, 
    MAX(purchase_number) AS max_row 
FROM 
    source_table
GROUP BY 
    customer_id

),
min_ AS (
SELECT
    a.*
FROM
    source_table a
    JOIN min_max b
        ON a.customer_id = b.customer_id
        AND a.purchase_number = b.min_row
),
max_ AS (
SELECT
    a.*
FROM
    source_table a
    JOIN min_max b
        ON a.customer_id = b.customer_id
        AND a.purchase_number = b.max_row
)

SELECT
    a.customer_id,
    a.item,
    b.item_origin
FROM
    min_ a
    JOIN max_ b
        ON a.customer_id = b.customer_id

标签： sqlpyspark

解决方案

只需使用条件聚合和窗口函数：

select customer_id,
       max(case when seqnum = 1 then item end) as first_item,
       max(case when seqnum_desc = 1 then item_origin end) as last_item_origin
from (select s.*,
             row_number() over (partition by customer_id order by purchase_number) as seqnum,
             row_number() over (partition by customer_id order by purchase_number desc) as seqnum_desc
      from source_table s
     ) s
group by customer_id;

推荐阅读