首页 > 代码库 > Teradata 从外部导入的数据表的 INNER JOIN 问题

Teradata 从外部导入的数据表的 INNER JOIN 问题

2024-07-21 01:10:00   222人阅读

 一、无法INNER JOIN 

SELECT T1.Party_ID, T1.R, T1.F, T1.M,  T2.labFROM (      SELECT Party_ID,        CAST(ROUND((CAST(MAX_R AS DECIMAL(20,4))-CAST(R AS DECIMAL(20,4)))/ (CAST(MAX_R AS DECIMAL(20,4))-CAST(MIN_R AS DECIMAL(20,4)))*100,1)  AS DECIMAL(20,4)) AS R,        CAST(ROUND((CAST(F AS DECIMAL(20,4))-CAST(MIN_F AS DECIMAL(20,4)))/ (CAST(MAX_F AS DECIMAL(20,4))-CAST(MIN_F AS DECIMAL(20,4)))*100,1)  AS DECIMAL(20,4))    AS F,        CAST(ROUND((CAST(M AS DECIMAL(20,4))-CAST(MIN_M AS DECIMAL(20,4)))/ (CAST(MAX_M AS DECIMAL(20,4))-CAST(MIN_M AS DECIMAL(20,4)))*100,1) AS DECIMAL(20,4)) AS M        FROM (        SELECT  t1.Party_ID,  t1.R,  t1.F,  t1.M        FROM    PT_TEMP_P.PTY_ANALYSIS t1        INNER   JOIN   PT_TEMP_P.PTY_LANE  t2        ON      t1.Party_Id  =  t2.Party_Id         WHERE   t1.F>0           AND     t1.M<100000 AND  t1.M>0  ) A ,                ( SELECT        MAX(R) AS MAX_R, MIN(R) AS MIN_R,        MAX(F) AS MAX_F, MIN(F) AS MIN_F,        MAX(M) AS MAX_M, MIN(M) AS MIN_M        FROM PT_TEMP_P.PTY_ANALYSIS        WHERE  F>0  AND  M>0 AND M<100000 ) B  ) T1 , (   SELECT  R,F,M,lab     FROM   PT_TEMP_P.RFM_LABEL_TRANS   ) T2WHERE  T1.R=T2.R AND T1.F = T2.F AND T1.M=T2.M
目标阐述：
表PT_TEMP_P.PTY_ANALYSIS：原始数据表, 包含的变量R、F、M、ID_Nbr；表PT_TEMP_P.RFM_LABEL_TRANS：为从外部导入teradata数据库中的表. 变量为R、F、M、lab, 该表中的R、F、M为”原始数据“标准化后的结果, 另有一列lab表示一个新的变量.表 PT_TEMP_P.PTY_LANE 中的ID_Nbr 为原始数据表中 ID_Nbr 的子集. 首先INNER JOIN 表PT_TEMP_P.PTY_ANALYSIS 和 表PT_TEMP_P.PTY_LANE, 得到表PT_TEMP_P.PTY_LANE中的ID_Nbr对应的R、F、M值,目标：需要通过INNER JOIN 找到表PT_TEMP_P.PTY_LANE中的ID_Nbr对应的lab值.

二、数据检查
SELECT   R,F,M,labFROM PT_TEMP_P.RFM_LABEL_TRANSWHERE   (R=79.4  AND F=13.1AND M=17.4)OR(R=96.7AND F=2.3AND M=3.4)OR(R=88.2AND F=3.9AND M=9)OR(R=96.4AND F=13.9AND M=27.6)....../*其中, where条件中的数据来自表T1 */
利用Excel快速生成文本：



检查结果：表PT_TEMP_P.RFM_LABEL_TRANS 中能够找到表T1中的数据，两个表本应该可以INNER JOIN.
三、解决办法
/*先建表, 将表T1存入某个被定义的表中*/CREATE MULTISET TABLE Pt_temp_p.p01 (Party_ID varchar(255),  R decimal(20,4),  F decimal(20,4),  M decimal(20,4)); INSERT INTO PT_TEMP_P.p01 (    Party_ID,    R,    F,    M ) SELECT Party_ID,        CAST(ROUND((CAST(MAX_R AS DECIMAL(20,4))-CAST(R AS DECIMAL(20,4)))/ (CAST(MAX_R AS DECIMAL(20,4))-CAST(MIN_R AS DECIMAL(20,4)))*100,1)  AS DECIMAL(20,4)) AS R,        CAST(ROUND((CAST(F AS DECIMAL(20,4))-CAST(MIN_F AS DECIMAL(20,4)))/ (CAST(MAX_F AS DECIMAL(20,4))-CAST(MIN_F AS DECIMAL(20,4)))*100,1)  AS DECIMAL(20,4))    AS F,        CAST(ROUND((CAST(M AS DECIMAL(20,4))-CAST(MIN_M AS DECIMAL(20,4)))/ (CAST(MAX_M AS DECIMAL(20,4))-CAST(MIN_M AS DECIMAL(20,4)))*100,1) AS DECIMAL(20,4)) AS M        FROM (        SELECT  t1.Party_ID,  t1.R,  t1.F,  t1.M        FROM  PT_TEMP_P.PTY_ANALYSIS t1        INNER  JOIN   PT_TEMP_P.PTY_LANE  t2        ON  t1.Party_Id  =  t2.Party_Id         WHERE t1.F>0           AND  t1.M<100000 AND  t1.M>50 ) A ,                ( SELECT        MAX(R) AS MAX_R, MIN(R) AS MIN_R,        MAX(F) AS MAX_F, MIN(F) AS MIN_F,        MAX(M) AS MAX_M, MIN(M) AS MIN_M        FROM PT_TEMP_P.PTY_ANALYSIS        WHERE AND F>0  AND  M>50 AND M<100000 ) B/*再INNER JOIN 新建的表与 从外部导入的表*/SELECT  t1.Party_ID, t1.R,t1.F,t1.M,t2.labFROM  pt_temp_p.p01 t1 INNER JOIN   PT_TEMP_P.RFM_LABEL_TRANS t2ON   t1.R=t2.R AND t1.F =t2.F AND t1.M=t2.M
 
无法INNER JOIN 的原因：可能是数据类型不匹配.  
但即使在where条件中将匹配变量通过cast... as...成相同的数据类型，仍然不能成功.
Teradata 从外部导入的数据表的 INNER JOIN 问题






























        

            
                
                  
                
                

                    联系

                    我们