kylin-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "王林" <1059790...@qq.com>
Subject apache kylin Left join 查询结果混乱,求解决方案
Date Tue, 20 Nov 2018 00:29:19 GMT
你好:  
  通过kylin页面查询发现如下问题: 
执行如下sql: 
SELECT  
c.DT,  
        COALESCE (C.weekActivityCount, 0),  
        COALESCE (D.monthActivityCount, 0)  
FROM  
(  
        SELECT  
                DT AS DT,  
                count(*) AS weekActivityCount  
        FROM  
                ST_DEVICE_INFO_FACT_TABLE  
        WHERE  
                6 >= LAST_ONLINE_DT_DIFF_DAY  
        AND LAST_ONLINE_DT_DIFF_DAY >= 0  
        AND DT >= '2018-10-11'  
        AND '2018-10-11' >= DT          AND IS_ACTIVATED = 1         GROUP BY         
       dt ) C 
  LEFT JOIN (         SELECT
                DT AS DT,  
                count(*) AS monthActivityCount  
        FROM  
                ST_DEVICE_INFO_FACT_TABLE  
        WHERE  
                29 >= LAST_ONLINE_DT_DIFF_DAY  
        AND LAST_ONLINE_DT_DIFF_DAY >= 0  
        AND DT >= '2018-10-11'  
        AND '2018-10-11' >= DT  
        AND IS_ACTIVATED = 1  
        GROUP BY  
                dt  
) D ON C.DT = D.DT  

查询使用cube: 
CUBE[name=st_device_info_cube_v140],CUBE[name=st_device_info_cube_v140]  
查询结果为: 
2018-10-11,996542,996542  

但是单独执行C,D两个子查询: 
        SELECT  
                DT AS DT,  
                count(*) AS weekActivityCount 
        FROM  
                ST_DEVICE_INFO_FACT_TABLE  
        WHERE  
                6 >= LAST_ONLINE_DT_DIFF_DAY  
        AND LAST_ONLINE_DT_DIFF_DAY >= 0  
        AND DT >= '2018-10-11'  
        AND '2018-10-11' >= DT         
        AND IS_ACTIVATED = 1        
      GROUP BY                 dt 


Cubes: CUBE[name=st_device_info_cube_v140] 
查询结果为:2018-10-11,996542 执行:
SELECT               
  DT AS DT,       
          count(*) AS monthActivityCount     
    FROM      
          ST_DEVICE_INFO_FACT_TABLE       
  WHERE                 
       29 >= LAST_ONLINE_DT_DIFF_DAY   
       AND LAST_ONLINE_DT_DIFF_DAY >= 0
        AND DT >= '2018-10-11'  
        AND '2018-10-11' >= DT  
        AND IS_ACTIVATED = 1  
        GROUP BY  
                dt  

Cubes: CUBE[name=st_device_info_cube_v140]  
查询结果为: 
2018-10-11,1119847  

查询结果与第一个left join查询结果不对应。请问是什么原因?这个问题需要怎么修复呢?
Mime
  • Unnamed multipart/alternative (inline, 8-Bit, 0 bytes)
View raw message