我得到了一个如下的 oracle 查询。

with table_a as(
select 1 as call_key, date '2021-06-01' as customer_contact, 1 as  status from dual union all
select 1 as call_key, date '2021-06-02' as customer_contact, 2 as  status from dual union all
select 1 as call_key, date '2021-06-03' as customer_contact, 3 as  status from dual union all
select 1 as call_key, date '2021-06-03' as customer_contact, 4 as  status from dual union all
select 2 as call_key, date '2021-06-01' as customer_contact, 1 as  status from dual union all
select 2 as call_key, date '2021-06-04' as customer_contact, 1 as  status from dual
)
select call_key, Sum(status) Keep(Dense_Rank Last Order by customer_contact) as sum_result
from table_a
group by call_key
;

结果是这样的： | 呼叫键| sum_resul| |:---- |:-----:| | 1| 7| | 2| 1|

在我的真实场景中，表格或其他栏位是动态的，我得到的唯一信息是需要求和的列和需要排序的列。因此，在实际场景中，oracle 查询可能如下所示。

select  spce.col1,barc.col2

---- I try to resolve this sum() 
,Sum(cmp.col5) Keep(Dense_Rank Last Order by cmp.col4) as sum_result 

from 
(SELECT spce.col1, spce.col2, spce.*, ddn.col1, ddn.col2, ddn.col3, cmp.col1, cmp.col2 
FROM project_name.tableA spce 
JOIN project_name.tableB ddn ON spce.col1 = ddn.col1 
JOIN project_name.tableC barc ON spce.col2 = barc.col2 
JOIN project_name.tableD cmp ON (barc.col3 = cmp.col3 AND barc.col4 = cmp.col4) WHERE 1 = 1) a11
where   TRUE QUALIFY 1 = DENSE_RANK() OVER (ORDER BY a11.col1 DESC)) a11
group by spce.col1,barc.col2
;

我尝试使用 array_agg 如下，但我不能得到与 oracle 相同的结果。

with calls as (
  select *
    from unnest([struct(1 as call_key, date '2021-06-01' as customer_contact, 1 as  status)
                ,struct(1 as call_key, date '2021-06-02' as customer_contact, 2 as  status)
                ,struct(1 as call_key, date '2021-06-03' as customer_contact, 3 as  status)
                ,struct(1 as call_key, date '2021-06-03' as customer_contact, 4 as  status)
                ,struct(2 as call_key, date '2021-06-01' as customer_contact, 1 as  status)
                ,struct(2 as call_key, date '2021-06-04' as customer_contact, 1 as  status)
                ])
)
select call_key
      ,array_agg(status order by customer_contact,status desc limit 1)[ordinal(1)] as status1
from calls
group by call_key

我之前也问过同样的问题，但是我的描述不够清楚，所以我再问一次，希望有人能帮助我，谢谢！

上一题的网址如下： Convert Sum(x) Keep(Dense_Rank Last Order by y) from oracle to BigQuery and keep group by in query

uj5u.com热心网友回复：

下面试试。如您所见，此处参考的唯一栏位是customer_contact和status。所有其他领域被认为是partition by和group by

select any_value(rec).*, sum(status) sum_result from (
  select (select as struct * except(customer_contact, status) from unnest([c])) rec, status
  from calls c
  where true
  qualify 1 = dense_rank() over(partition by to_json_string(rec) order by customer_contact desc) 
) t
group by to_json_string(rec)