我得到了一個如下的 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)
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/414324.html
標籤：