正如标题所示,我想选择用GROUP BY分组的每组行中的第一行。

具体来说,如果我有一个如下所示的采购表:

SELECT * FROM purchases;

我的输出:

id customer total
1 Joe 5
2 Sally 3
3 Joe 2
4 Sally 1

我想查询每个客户的最大购买量(总购买量)。类似于:

SELECT FIRST(id), customer, FIRST(total)
FROM  purchases
GROUP BY customer
ORDER BY total DESC;

预期输出:

FIRST(id) customer FIRST(total)
1 Joe 5
2 Sally 3

当前回答

我通过窗口函数dbfiddle的方法:

将每组的row_number()分配给(按agreement_id、order_id划分)为nrow只取组:filter中的第一行(其中nrow=1)

with intermediate as (select 
 *,
 row_number() over ( partition by agreement_id, order_id ) as nrow,
 (sum( suma ) over ( partition by agreement_id, order_id ))::numeric( 10, 2) as order_suma,
from <your table>)

select 
  *,
  sum( order_suma ) filter (where nrow = 1) over (partition by agreement_id)
from intermediate

其他回答

如果要从聚合行集合中选择任何行(根据特定条件)。如果您想使用除max/min之外的另一个(sum/avg)聚合函数。因此,您不能在DISTINCT ON时使用线索

您可以使用下一个子查询:

SELECT  
    (  
       SELECT **id** FROM t2   
       WHERE id = ANY ( ARRAY_AGG( tf.id ) ) AND amount = MAX( tf.amount )   
    ) id,  
    name,   
    MAX(amount) ma,  
    SUM( ratio )  
FROM t2  tf  
GROUP BY name

您可以将amount=MAX(tf.amount)替换为任何需要的条件,但有一个限制:此子查询不能返回多行

但是如果你想做这样的事情,你可能需要寻找窗口函数

在PostgreSQL中,另一种可能是将first_value窗口函数与SELECT DISTINCT结合使用:

select distinct customer_id,
                first_value(row(id, total)) over(partition by customer_id order by total desc, id)
from            purchases;

我创建了一个组合(id,total),因此两个值都由同一个聚合返回。当然,您可以始终应用first_value()两次。

非常快速的解决方案

SELECT a.* 
FROM
    purchases a 
    JOIN ( 
        SELECT customer, min( id ) as id 
        FROM purchases 
        GROUP BY customer 
    ) b USING ( id );

如果表是按id索引的,则速度非常快:

create index purchases_id on purchases (id);

在支持CTE和窗口功能的数据库上:

WITH summary AS (
    SELECT p.id, 
           p.customer, 
           p.total, 
           ROW_NUMBER() OVER(PARTITION BY p.customer 
                                 ORDER BY p.total DESC) AS rank
      FROM PURCHASES p)
 SELECT *
   FROM summary
 WHERE rank = 1

任何数据库都支持:

但你需要添加逻辑来打破联系:

  SELECT MIN(x.id),  -- change to MAX if you want the highest
         x.customer, 
         x.total
    FROM PURCHASES x
    JOIN (SELECT p.customer,
                 MAX(total) AS max_total
            FROM PURCHASES p
        GROUP BY p.customer) y ON y.customer = x.customer
                              AND y.max_total = x.total
GROUP BY x.customer, x.total

对于SQl Server,最有效的方法是:

with
ids as ( --condition for split table into groups
    select i from (values (9),(12),(17),(18),(19),(20),(22),(21),(23),(10)) as v(i) 
) 
,src as ( 
    select * from yourTable where  <condition> --use this as filter for other conditions
)
,joined as (
    select tops.* from ids 
    cross apply --it`s like for each rows
    (
        select top(1) * 
        from src
        where CommodityId = ids.i 
    ) as tops
)
select * from joined

不要忘记为使用过的列创建聚集索引