Postgres不同的行，同时总结

Question

我有一个与此相似的数据集。我需要为客户端挑选最新的元数据（更大的执行时间=更新），包括数量和最新的执行时间和数量＆gt; 0，

| Name     | Quantity | Metadata | Execution time |
| -------- | ---------|----------|----------------|
| Neil     | 1        | [1,3]    |  4             |
| James    | 1        | [2,18]   |  5             |
| Neil     | 1        | [4, 1]   |  6             |
| Mike     | 1        | [5, 42]  |  7             |
| James    | -1       | Null     |  8             |
| Neil     | -1       | Null     |  9             |

例如，查询需要返回：

| Name     | Summed Quantity | Metadata | Execution time |
| -------- | ----------------|----------|----------------|
| James    | 0               | [2,18]   |  5             |
| Neil     | 1               | [4, 1]   |  6             |
| Mike     | 1               | [5, 42]  |  7             |

我的查询无法正常工作，因为它无法正确返回数量的总和。

SELECT 

distinct on (name) name, 
(
    SELECT 
      cast(
        sum(quantity) as int
        
      )
  ) as summed_quantity,
     
  meta,
  execution_time  
FROM 
  table 
where 
 quantity > 0
group by 
  name,  
  meta, 
  execution_time 
order by 
  name, 
  execution_time desc;

此查询给出了IE的结果

| Name     | Summed Quantity | Metadata | Execution time |
| -------- | ----------------|----------|----------------|
| James    | 1               | [2,18]   |  5             |
| Neil     | 1               | [4, 1]   |  6             |
| Mike     | 1               | [5, 42]  |  7             |

。 0从何处，不添加子查询中的数量（我假设是由于独特的子句），我不确定如何修复查询以产生所需的输出。

Answer 1

这可以使用窗口函数（因此使用单个数据）

select
       name
     , sum_qty
     , metadata
     , execution_time
from (
     select 
            *
          , sum(Quantity) over(partition by name) sum_qty
          , row_number() over(partition by name, case when quantity > 0 then 1 else 0 end
                              order by Execution_time DESC) as rn
     from mytable
     ) d
where rn = 1 and quantity > 0
order by name

结果

+-------+---------+----------+----------------+
| name  | sum_qty | metadata | execution_time |
+-------+---------+----------+----------------+
| James |       0 | [2,18]   |              5 |
| Mike  |       1 | [5,42]   |              7 |
| Neil  |       1 | [4,1]    |              6 |
+-------+---------+----------+----------------+

db＆lt;＆gt; fiddle 此处