• notice
  • Congratulations on the launch of the Sought Tech site

Convert Sum(x)Keep(Dense_RankLastOrderbyy) from oracle to BigQuery with limit factor

I got an oracle query like 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 this: | call key| sum_resul| |:----|:-----:| | 1| 7| | 2| 1|

In my real scene, tables or other fields are dynamic, the only information I get are the columns to be summed and the columns to be sorted.So, in a real scenario, the oracle query might look like the following.

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 tried using array_agg as below, but I can't get the same result with 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 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

uj5u.com enthusiastic netizens replied:

Try below.As you can see, the only field referenced here is customer_contact and status.all other fields are considered 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)

Tags

Technical otaku

Sought technology together

Related Topic

1 Comments

author

lipitor 10mg usa & lt;a href="https://lipiws.top/"& gt;oral lipitor 80mg& lt;/a& gt; lipitor 40mg canada

Tsmmci

2024-03-10

Leave a Reply

+