Convert Sum(x) Keep( Dense_Rank Last Order by y) from oracle to BigQuery with limitation factor-CodePudding

I got an oracle query as below.

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
;

The result is like this: | call_key| sum_resul| |:---- |:------:| | 1| 7| | 2| 1|

In my real scenario, the table or other fields are dynamic, the only information I get is the column that needs to sum and the column needs to order by. So in a real scenario, the oracle query may be like as below.

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
;

I try to use array_agg as below, but I cannot get the same result as an 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

I've asked the same question before, but my description wasn't clear enough, so I'm asking again, hope someone can help me, thanks!

The URL of the previous question is as follows: Convert Sum(x) Keep( Dense_Rank Last Order by y) from oracle to BigQuery and keep group by in query

CodePudding user response：

In my real scenario, the table or other fields are dynamic, the only information I get is the column that needs to sum and the column needs to order by

Try below. As you can see the only fields being referenced here are customer_contact and status. all the rest of fields considered to be part of partition by and 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)