Convert Sum(x) Keep( Dense_Rank Last Order by y) from oracle to BigQuery and keep group by in query

user_15689601 0
Viewed 98

I got 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
;

Result is like this: | call_key| sum_resul| | --------|----------| | 1 | 7 | | 2 | 1 |

I need to convert oracle language to BigQuery, but BigQuery needs to have 'group by' in query.

I try to use array_agg as below, but I cannot get the same result as oracle. Please Help!

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
google-bigquery oracle
1 Answers

Consider below

select call_key, sum(status) sum_result from (
  select * from calls where true
  qualify 1 = dense_rank() over(partition by call_key order by customer_contact desc) 
)
group by call_key

if applied to sample data in your question - output is

enter image description here

user_52219440
Related