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)
lipitor 10mg usa & lt;a href="https://lipiws.top/"& gt;oral lipitor 80mg& lt;/a& gt; lipitor 40mg canada
Tsmmci
2024-03-10