Qlik同表Left Join逻辑解析及SQL等效实现咨询

1. 脚本用途与层级关系判断 #

这个脚本确实用于处理层级关系：

• 原表A包含P_A_ID_AA（父级账号ID）和U_P_A_ID_AA（上级父级账号ID）字段，说明每条记录存在上下级关联。
• 第一次Left Join从同表中拉取当前记录的父级信息，重命名为P_*开头的字段；第二次Left Join拉取更上一级的父级信息，重命名为U_P_*开头的字段。最终实现将当前节点、父节点、祖父节点的信息合并到同一条记录中。

2. Qlik同表Left Join关联逻辑与SQL等效实现 #

Qlik关联逻辑 #

Qlik的Left Join默认行为是自动匹配两个表中所有名称相同的字段进行关联，相当于SQL中多字段AND的关联条件。但你的脚本存在逻辑漏洞：

• 你将ACCOUNT_ID_AA重命名为P_ACCOUNT_ID_AA，原表却以P_A_ID_AA作为父级ID字段，二者名称不同，若不指定on条件，Qlik会执行笛卡尔积（而非预期的父级匹配）。
• 正确写法需显式指定关联条件：

  left join (abc)
  LOAD distinct
  ACCOUNT_ID_AA               AS P_ACCOUNT_ID_AA,
  F_a_AA                      AS P_a_AA,
  F_b_AA                      AS P_b_AA,
  F_c_AA                      AS P_c_AA,
  F_d_AA                      AS P_d_AA,
  F_e_AA                      AS P_e_AA,
  F_f_AA                      AS P_f_AA,
  F_g_AA                      AS P_g_AA
  Resident abc
  on P_ACCOUNT_ID_AA = P_A_ID_AA; -- 指定父级ID匹配条件
  
  left join (abc)
  LOAD distinct
  ACCOUNT_ID_AA               AS U_P_ACCOUNT_ID_AA,
  F_a_AA                      AS U_P_a_AA,
  F_b_AA                      AS U_P_b_AA,
  F_c_AA                      AS U_P_c_AA,
  F_d_AA                      AS U_P_d_AA,
  F_e_AA                      AS U_P_e_AA,
  F_f_AA                      AS U_P_f_AA,
  F_g_AA                      AS U_P_g_AA
  Resident abc
  on U_P_ACCOUNT_ID_AA = U_P_A_ID_AA; -- 指定上级父级ID匹配条件
  

PostgreSQL等效实现 #

通过两次自关联LEFT JOIN，直接匹配父级和上级父级的账号ID：

WITH base_data AS (
    SELECT DISTINCT
        AA.ACCOUNT_ID               AS ACCOUNT_ID_AA,
        AA.ID                       AS ID_AA,
        AA.F_a                      AS F_a_AA,
        AA.F_b                      AS F_b_AA,
        AA.F_c                      AS F_c_AA,
        AA.F_d                      AS F_d_AA,
        AA.F_e                      AS F_e_AA,
        AA.F_f                      AS F_f_AA,
        AA.F_g                      AS F_g_AA,
        AA.P_A_ID                   AS P_A_ID_AA,
        AA.U_P_A_ID                 AS U_P_A_ID_AA,
        AA.A_C                      AS A_C_AA,
        AA.R                        AS R_AA,
        AA.F_h__C                   AS F_h__C_AA,
        AA.I                        AS I_AA,
        AA.L                        AS L_AA
    FROM your_db_name.A AA -- 替换为实际数据库名，对应Qlik的$(vdb)
)
SELECT DISTINCT
    bd.*,
    p.F_a_AA AS P_a_AA,
    p.F_b_AA AS P_b_AA,
    p.F_c_AA AS P_c_AA,
    p.F_d_AA AS P_d_AA,
    p.F_e_AA AS P_e_AA,
    p.F_f_AA AS P_f_AA,
    p.F_g_AA AS P_g_AA,
    up.F_a_AA AS U_P_a_AA,
    up.F_b_AA AS U_P_b_AA,
    up.F_c_AA AS U_P_c_AA,
    up.F_d_AA AS U_P_d_AA,
    up.F_e_AA AS U_P_e_AA,
    up.F_f_AA AS U_P_f_AA,
    up.F_g_AA AS U_P_g_AA
FROM base_data bd
LEFT JOIN base_data p
    ON bd.P_A_ID_AA = p.ACCOUNT_ID_AA -- 匹配父级账号ID
LEFT JOIN base_data up
    ON bd.U_P_A_ID_AA = up.ACCOUNT_ID_AA; -- 匹配上级父级账号ID

关于SQL ON子句的疑问 #

不需要匹配所有同名字段。Qlik默认的无on条件Join是按所有同名字段关联，但这不符合你的业务逻辑——你实际需要的是按父级ID字段关联，因此SQL中仅需在ON子句指定这一个关联条件即可。

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