时间戳

首页 > 解决方案 > 大查询分析功能未给出预期结果

google-cloud-platform - 大查询分析功能未给出预期结果

问题描述

我正在尝试在 bigquery 中编写 sql，并且我需要根据按列分组和表中的另一列过滤记录我的意思是我想检查按列分组（列名：mnt）是否有更多超过一行然后我必须检查 col2 (col name: zel) 的值，然后我必须应用一个过滤器说 col2 ='X' 并且只通过该记录否则通过即如果 col1 只有一个不同的记录，则不要过滤记录每组价值

所以我写了一个 sql 来做到这一点，我使用了 row_number 和 rank ，dense rank 函数，但我注意到 rank 的值，dense rank 和 row number 函数为一个组返回相同的值

请看下面的代码

#standardsql
with t1 as  (SELECT  mnt, 
case when rank() over (partition by ltrim(rtrim(mnt)) order by 
ltrim(rtrim(mnt)) asc) >1 then 'Y' else 'N' end 
as flag,
rank() over (partition by mnt order by mnt) as rn, 
dense_rank() over (partition by mnt order by mnt) as drn, FROM    
projectname.datasetname.tablename1), 
t2 as ( SELECT 
        mnt,  
        rel, 
        lif, 
        lts, 
lokez FROM projectname.datasetname.tablename2  
WHERE lts <> ""  AND  _PARTITIONTIME = TIMESTAMP(CURRENT_DATE()) ) ,
t3 as  (SELECT  
       lif, 
       lifn,  
       lts,  
       par FROM `projectname.datasetname.tablename3`)  

    ,t4 as (SELECT rcv FROM `projectname.datasetname.tablename4` WHERE mes
    = 'PRO') 
   select * from ( 
   SELECT t1.mnt as mnt,
          t1.flag,
          t1.rn,
          t1.drn
          t2.rel as zel,
          t2.lokez as ZLOEKZ,
          t4.rcv as Zrcv
           FROM t1 left join t2 on replace(t1.mnt, '00000000', '') = 
    REPLACE(t2.mnt, '00000000', '') AND t1.lif = t2.lif and t2.lts <> ""  
    and  
    case when t1.flag = 'Y' and  t2.rel ='X' then 1
         when (t1.flag ='N' and t2.rel=t2.rel) or (t1.flag ='N' and t2.rel 
    is null)  then 1
         when t1.flag = 'Y' and  t2.rel <>'X' then 2
         else 3
         end = 1
    left join t3  ON t1.lif = t3.lif  AND t2.lts = t3.lts AND     
    t3.par = 'BA' left join t4 on t4.rcv = t3.lifn and  t2.lokez is null  ) 
    where     ZLOEKZ is null  order by mnt

如您所见，我正在使用 case 语句，甚至它似乎无法正常工作。我再次粘贴下面的案例条件

case when t1.flag = 'Y' and  t2.rel ='X' then 1
             when (t1.flag ='N' and t2.rel=t2.rel) or (t1.flag ='N' and 
      t2.rel 
        is null)  then 1
             when t1.flag = 'Y' and  t2.rel <>'X' then 2
             else 3
             end = 1

但是预期的记录数不匹配，所以我添加了上面的 sql 行，看看我的分析函数是否给了我想要的结果

rank() over (partition by mnt order by mnt) as rn, 
dense_rank() over (partition by mnt order by mnt) as drn

奇怪的是，对于相同的 mnt 编号，rank、dense rank 和 row_number 函数分配了相同的值我在这里做错了什么。

mnt     flag    rn     drn     rel     lokez     rcv
100      N       1      1       X       abc       123
100      N       1      1      null     xyz       123
100      N       1      1      null     def       234

这是我的输出

我的意思是根据我的相同 mnt 编号的代码，我看到标志设置为 N 而不是 Y，并且对于所有 3 个 mnt，等级和密集等级给我相同的数字，它生成 1 而不是 123（注意等级函数我理解) 但密集等级不应该那样做

我试图尽可能有效地传达这个问题，如果我能提供任何澄清，请告诉我。

任何帮助表示赞赏

谢谢

标签： google-cloud-platformgoogle-bigquerybigquery-standard-sql

解决方案

SELECT * EXCEPT(ct) FROM (
  SELECT *, COUNT() OVER(PARTITION BY mnt) AS ct 
) WHERE ct=1 or zel='X'

这是您提到的问题的代码片段。根据逻辑在您的代码中使用它。

推荐阅读