SQL实战：如何计算用户购票时的历史最高频到访地点

实现逻辑 #

针对每个用户的每一条购票记录，我们需要统计截止到当前购票时间所有历史到访地点的两个核心指标：

1. 每个地点的累计到访次数
2. 每个地点的最近一次到访时间
   之后按照「到访次数降序、最近到访时间降序」排序，取排名第一的地点作为当前记录的most_visited_place，平局时取最近到访地点的规则也自然满足。

实现代码 #

WITH formatted_users AS (
    -- 第一步：统一转换购票时间为Snowflake支持的timestamp类型，方便时间比较
    SELECT
        user_id,
        place,
        -- 注意：示例中存在如2021-10-21:01:78:89的非法时间，实际使用前需先做数据清洗
        TO_TIMESTAMP(purchase_time, 'YYYY-MM-DD:HH24:MI:SS') AS purchase_ts,
        purchase_time AS original_purchase_time
    FROM Users
)
SELECT 
    main.user_id,
    main.place,
    main.original_purchase_time AS purchase_time,
    -- 按规则排序后取第一位的地点
    GET(
        ARRAY_AGG(hist.place) WITHIN GROUP (
            ORDER BY COUNT(hist.place) DESC, MAX(hist.purchase_ts) DESC
        ),
    0) AS most_visited_place
FROM formatted_users main
-- 关联同一用户所有早于等于当前购票时间的历史购票记录
INNER JOIN formatted_users hist
    ON main.user_id = hist.user_id
    AND hist.purchase_ts <= main.purchase_ts
-- 按当前主记录分组，统计每个主记录对应的历史地点指标
GROUP BY 
    main.user_id,
    main.place,
    main.original_purchase_time,
    main.purchase_ts
-- 排序和示例输出保持一致，可根据需求调整
ORDER BY main.user_id, main.purchase_ts DESC;

注意事项 #

• 示例数据中存在2021-10-21:01:78:89这类非法时间值，实际运行前需要先做数据清洗，否则TO_TIMESTAMP函数会转换报错。
• 若表数据量较大，建议对user_id字段设置集群键，大幅提升关联查询的性能。

内容的提问来源于stack exchange，提问作者R0bert