使用FULL JOIN查询数据时遗漏部分行的SQL问题求助

背景与需求 #

• 拥有两张业务表：
  • Orders表：存储订单信息，包含订单金额、下单用户ID、订单创建日期、状态变更日期等字段
  • Payments表：存储支付信息，包含支付用户ID、支付金额、支付创建日期等字段
• 需求：按年份分组统计每个用户的订单总金额、支付总金额等数据（暂不考虑按月分组）

问题现象 #

当用户（如用户A）在2024年没有订单记录但存在支付记录时，该用户的2024年统计行被遗漏。

现有SQL语句 #

SELECT order_data.user_id,
       order_data.username,
       order_data.customer_email,
       order_data.customer_mobile,
       order_data.year                                             as year,
       order_data.orders_amount                                    as order_amount,
       COALESCE(payment_data.payment_amount, 0)                    as payment_amount,
       COALESCE(payment_amount - orders_amount, 0 - orders_amount) as balance,
       order_data.latest_order_date,
       payment_data.latest_payment_date
FROM (SELECT orders.user_id,
             orders.username,
             orders.customer_email,
             orders.customer_mobile,
             date_part('year', orders.status_change_date) as year,
             sum(orders.amount)                           as orders_amount,
             max(orders.create_date)                      as latest_order_date
      from arag_araqum.orders orders
      GROUP BY orders.user_id, orders.username, orders.customer_email, orders.customer_mobile, year) as order_data
         FULL JOIN (Select payment.user_id,
                           date_part('year', payment.create_date) as year,
                           sum(payment.amount)                    as payment_amount,
                           max(payment.create_date)               as latest_payment_date
                    from arag_araqum.payments as payment
                    GROUP BY user_id, year) as payment_data
                   ON order_data.user_id = payment_data.user_id
                       AND
                      order_data.year = payment_data.year;

表结构定义 #

Orders表 #

create table orders
(
    id                 bigint generated always as identity
        constraint order_pkey
            primary key,
    user_id            text not null,
    username           text not null,
    customer_email     text not null,
    customer_mobile    text,
    amount             bigint,
    create_date        date,
    status_change_date date
);

Payments表 #

create table payments
(
    id          bigint generated always as identity
        constraint payment_pkey
            primary key,
    user_id     text not null,
    amount      bigint,
    create_date date
);

测试场景 #

用户A在2024年无订单记录，但存在支付记录。

问题根源 #

现有SQL用FULL JOIN虽然能关联两边数据，但查询时直接取order_data里的用户信息（username、customer_email等）和年份字段，当用户只有支付记录时，order_data对应的字段为空，导致这条统计记录的关键信息缺失，最终被遗漏或无法正常显示。

修正后的SQL #

SELECT COALESCE(order_data.user_id, payment_data.user_id) AS user_id,
       COALESCE(order_data.username, (SELECT username FROM arag_araqum.orders WHERE user_id = payment_data.user_id LIMIT 1)) AS username,
       COALESCE(order_data.customer_email, (SELECT customer_email FROM arag_araqum.orders WHERE user_id = payment_data.user_id LIMIT 1)) AS customer_email,
       COALESCE(order_data.customer_mobile, (SELECT customer_mobile FROM arag_araqum.orders WHERE user_id = payment_data.user_id LIMIT 1)) AS customer_mobile,
       COALESCE(order_data.year, payment_data.year) AS year,
       COALESCE(order_data.orders_amount, 0) AS order_amount,
       COALESCE(payment_data.payment_amount, 0) AS payment_amount,
       COALESCE(payment_data.payment_amount, 0) - COALESCE(order_data.orders_amount, 0) AS balance,
       order_data.latest_order_date,
       payment_data.latest_payment_date
FROM (SELECT orders.user_id,
             orders.username,
             orders.customer_email,
             orders.customer_mobile,
             date_part('year', orders.status_change_date) AS year,
             sum(orders.amount) AS orders_amount,
             max(orders.create_date) AS latest_order_date
      FROM arag_araqum.orders orders
      GROUP BY orders.user_id, orders.username, orders.customer_email, orders.customer_mobile, year) AS order_data
FULL JOIN (SELECT payment.user_id,
                  date_part('year', payment.create_date) AS year,
                  sum(payment.amount) AS payment_amount,
                  max(payment.create_date) AS latest_payment_date
           FROM arag_araqum.payments AS payment
           GROUP BY user_id, year) AS payment_data
ON order_data.user_id = payment_data.user_id AND order_data.year = payment_data.year;

修改说明 #

1. 核心字段补全：用COALESCE函数优先取订单数据的用户ID和年份，为空时取支付数据的对应值，确保这两个关键字段始终有值。
2. 用户信息补全：当订单数据为空时，通过子查询从Orders表中获取该用户的历史基础信息（假设用户至少有过一次订单）；如果存在从未下单的用户，可将子查询替换为默认值，比如COALESCE(order_data.username, '未知用户')。
3. 金额计算简化：统一用COALESCE把空金额转为0，余额计算直接用支付总金额减订单总金额，避免复杂的条件判断。
4. 保留全连接逻辑：FULL JOIN确保所有用户的年份组合都被覆盖，包括只有订单、只有支付、两者都有的情况。

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