SQL：具有Distinct的分析函式SUM-有解無憂

這里我們有一個重復的 ID，需要區分 ID 和 SUM，所以它應該是 13 作為 Amount

我知道可以通過首先獲取不同的查詢然后在查詢之上獲取 SUM 來做到這一點。

有沒有辦法在一個查詢中實作這一點

with data as
         (
             select 1 as ID ,'ABC' as Name, 'Paid' as Status, 10 as Amount
             union all
             select 1 as ID ,'ABC' as Name, 'Paid' as Status, 10 as Amount
             union all
             select 2 as ID ,'ABC' as Name, 'Paid' as Status, 1 as Amount
             union all
             select 3 as ID ,'ABC' as Name, 'Paid' as Status, 1 as Amount
             union all
             select 4 as ID ,'ABC' as Name, 'Paid' as Status, 1 as Amount
         )
select   * , sum(amount) over (partition by name ,status) from data

SQL：具有 Distinct 的分析函式 SUM

uj5u.com熱心網友回復：

可能有更好的方法，但這是我的看法：

with data as
         (
             select 1 as ID ,'ABC' as Name, 'Paid' as Status, 10 as Amount
             union all
             select 1 as ID ,'ABC' as Name, 'Paid' as Status, 10 as Amount
             union all
             select 2 as ID ,'ABC' as Name, 'Paid' as Status, 1 as Amount
             union all
             select 3 as ID ,'ABC' as Name, 'Paid' as Status, 1 as Amount
             union all
             select 4 as ID ,'ABC' as Name, 'Paid' as Status, 1 as Amount
         )
select sum(Amount) from (select  distinct *  from data) a

uj5u.com熱心網友回復：

這是我的看法，

with data as
         (
             select 1 as ID ,'ABC' as Name, 'Paid' as Status, 10 as Amount
             union all
             select 1 as ID ,'ABC' as Name, 'Paid' as Status, 10 as Amount
             union all
             select 2 as ID ,'ABC' as Name, 'Paid' as Status, 1 as Amount
             union all
             select 3 as ID ,'ABC' as Name, 'Paid' as Status, 1 as Amount
             union all
             select 4 as ID ,'ABC' as Name, 'Paid' as Status, 1 as Amount
         )
select sum(Amount) from data d where not exists(select 'x' from data d2 where d2.amount = d.amount and d2.id > d.id)

uj5u.com熱心網友回復：

我會利用 Snowflake 的QUALIFY功能來做一些事情：

with data as
         (
             select 1 as ID ,'ABC' as Name, 'Paid' as Status, 10 as Amount
             union all
             select 1 as ID ,'ABC' as Name, 'Paid' as Status, 10 as Amount
             union all
             select 2 as ID ,'ABC' as Name, 'Paid' as Status, 1 as Amount
             union all
             select 3 as ID ,'ABC' as Name, 'Paid' as Status, 1 as Amount
             union all
             select 4 as ID ,'ABC' as Name, 'Paid' as Status, 1 as Amount
         ),
filter as (
    select *
    from data
    qualify row_number() over (partition by id order by name) = 1
)
select   * , sum(amount) over (partition by name ,status) 
from filter;

轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/339039.html

標籤：sql 雪花云数据平台

上一篇：SQL-如何計算期初和期末余額

下一篇：根據其他表中的值更新表列