如何用窗口函数按登录类型统计订单：解决关联重复与空值问题

问题背景 #

需求为按登录类型统计订单占比：登录类型存储于login表，订单与平台信息存储于orders表。关联过程中遇到以下问题：

• 用户登录记录与订单日期常不一致，关联后出现login_date、loginType空值
• 以Userid、Country并附加login_date <= order_date条件做左连接，会产生重复记录
• 尝试用MAX(login_date)分组去重，仅当登录类型相同时有效，不同登录类型仍存在重复记录

当前使用的SQL如下：

WITH login AS (
SELECT login_date,
Country,
Userid ,
loginType
FROM login
WHERE login_date  between '2023-01-01' AND '2023-09-30'
 ),

orders AS (
SELECT order_date,
Country,
Userid ,
Platform,
COUNT(*) as orders
FROM  orders
WHERE order_date between '2023-01-01' AND '2023-09-30'
GROUP BY 1,2,3,4
 )

SELECT MAX(login_date) login,
order_date,
l. Userid,
o.Country,
o.Platform,
l.loginType,
o.orders
from orders o
left join login l ON l.Userid = o.Userid AND l.Country = o.Country AND o.order_date >= l.login_date 
group by 2,3,4,5,6,7
order by order_date desc    

解决方案：使用窗口函数匹配最近登录记录 #

可以通过ROW_NUMBER()窗口函数为每个订单匹配最近的一条符合条件的登录记录，彻底解决重复问题，同时保留无登录记录的订单（空值）。修改后的SQL如下：

WITH login AS (
    SELECT 
        login_date,
        Country,
        Userid,
        loginType
    FROM login
    WHERE login_date BETWEEN '2023-01-01' AND '2023-09-30'
),
orders AS (
    SELECT 
        order_date,
        Country,
        Userid,
        Platform,
        COUNT(*) as orders
    FROM orders
    WHERE order_date BETWEEN '2023-01-01' AND '2023-09-30'
    GROUP BY order_date, Country, Userid, Platform
),
order_login AS (
    SELECT 
        o.order_date,
        o.Userid,
        o.Country,
        o.Platform,
        o.orders,
        l.login_date,
        l.loginType,
        -- 按订单维度分区，登录日期倒序排序，最近的记录排第1
        ROW_NUMBER() OVER (
            PARTITION BY o.order_date, o.Userid, o.Country, o.Platform
            ORDER BY l.login_date DESC
        ) AS rn
    FROM orders o
    LEFT JOIN login l 
        ON l.Userid = o.Userid 
        AND l.Country = o.Country 
        AND l.login_date <= o.order_date
)
-- 筛选每个订单对应的唯一最近登录记录
SELECT 
    login_date AS login,
    order_date,
    Userid,
    Country,
    Platform,
    loginType,
    orders
FROM order_login
WHERE rn = 1
ORDER BY order_date DESC;

逻辑说明 #

1. 新增order_loginCTE：将订单表与登录表左连接后，用ROW_NUMBER()窗口函数对每个订单（按order_date、Userid、Country、Platform分区）的关联登录记录按login_date倒序排序，最近的登录记录会被标记为rn=1。
2. 最终筛选rn=1的记录：确保每个订单仅匹配一条最近的登录记录，避免重复；左连接保留了无对应登录记录的订单，这类记录的login_date和loginType会显示为空，满足统计需求。

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