文档章节

在每个GROUP BY组中选择第一行?

 技术盛宴
发布于 2019/12/10 21:32
字数 930
阅读 18
收藏 0

顾名思义,我想选择以GROUP BY分组的每组行的第一行。

具体来说,如果我有一个如下的purchases表:

SELECT * FROM purchases;

我的输出:

id | customer | total
---+----------+------
 1 | Joe      | 5
 2 | Sally    | 3
 3 | Joe      | 2
 4 | Sally    | 1

我想查询每个customer购买的最大商品的idtotal )。 像这样:

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

#1楼

由于存在SubQ,该解决方案不是十分有效,正如Erwin指出的那样

select * from purchases p1 where total in
(select max(total) from purchases where p1.customer=customer) order by total desc;

#2楼

这是常见的每组最多n个问题,该问题已经过测试和高度优化 。 就我个人而言,我更喜欢Bill Karwin左联接解决方案带有许多其他解决方案原始帖子 )。

注意,在大多数官方资料之一MySQL手册中 ,可以惊奇地找到许多针对这个常见问题的解决方案! 请参阅常见查询的示例::持有特定列的按组最大值的行


#3楼

快速解决方案

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);

#4楼

在Postgres中,您可以像这样使用array_agg

SELECT  customer,
        (array_agg(id ORDER BY total DESC))[1],
        max(total)
FROM purchases
GROUP BY customer

这将为您提供每个客户最大购买量的id

注意事项:

  • array_agg是一个聚合函数,因此可以与GROUP BY
  • array_agg允许您指定array_agg于自身的排序,因此它不会限制整个查询的结构。 如果需要执行一些与默认值不同的操作,则还提供了有关如何对NULL进行排序的语法。
  • 构建数组后,我们将获取第一个元素。 (Postgres数组是1索引的,而不是0索引的)。
  • 您可以以类似的方式将array_agg用于第三输出列,但max(total)更简单。
  • DISTINCT ON不同,使用array_agg可使您保留GROUP BY ,以防其他原因。

#5楼

我使用这种方式(仅适用于postgresql): https : //wiki.postgresql.org/wiki/First/last_%28aggregate%29

-- Create a function that always returns the first non-NULL item
CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
        SELECT $1;
$$;

-- And then wrap an aggregate around it
CREATE AGGREGATE public.first (
        sfunc    = public.first_agg,
        basetype = anyelement,
        stype    = anyelement
);

-- Create a function that always returns the last non-NULL item
CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
        SELECT $2;
$$;

-- And then wrap an aggregate around it
CREATE AGGREGATE public.last (
        sfunc    = public.last_agg,
        basetype = anyelement,
        stype    = anyelement
);

然后您的示例应该几乎可以按以下方式工作:

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

CAVEAT:忽略NULL行


编辑1-改用postgres扩展名

现在,我使用这种方式: http : //pgxn.org/dist/first_last_agg/

要在ubuntu 14.04上安装:

apt-get install postgresql-server-dev-9.3 git build-essential -y
git clone git://github.com/wulczer/first_last_agg.git
cd first_last_app
make && sudo make install
psql -c 'create extension first_last_agg'

这是一个postgres扩展,为您提供第一个和最后一个功能; 显然比上述方法快。


编辑2-排序和过滤

如果使用聚合函数(如此类),则可以对结果进行排序,而无需对数据进行排序:

http://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES

因此,带有排序的等效示例如下所示:

SELECT first(id order by id), customer, first(total order by id)
  FROM purchases
 GROUP BY customer
 ORDER BY first(total);

当然,您可以按自己认为合适的顺序进行排序和过滤。 这是非常强大的语法。

本文转载自:https://stackoom.com/question/FwhD/在每个GROUP-BY组中选择第一行

粉丝 0
博文 719
码字总数 0
作品 0
深圳
高级程序员
私信 提问
mysql中的where和having子句的区别

having的用法 having字句可以让我们筛选成组后的各种数据,where字句在聚合前先筛选记录,也就是说作用在group by和having字句前。而 having子句在聚合后对组记录进行筛选。 SQL实例: 一、显...

yunlong-w
2015/07/27
177
0
mysql having与where 区别与用法

having的用法 having字句可以让我们筛选成组后的各种数据,where字句在聚合前先筛选记录,也就是说作用在group by和having字句前。而 having子句在聚合后对组记录进行筛选。 SQL实例: 一、显...

Deacyn
2014/07/02
120
0
mysql having的用法

having字句可以让我们筛选成组后的各种数据,where字句在聚合前先筛选记录,也就是说作用在group by和having字句前。而 having子句在聚合后对组记录进行筛选。我的理解就是真实表中没有此数据...

new个对象
2018/04/11
0
0
MySQL的EXPLAIN的EXTRA

MySQL查询优化器执行查询的过程中对查询计划的重要补充信息。 (1)using where 使用了where子句来过滤元组 (2)using temporary 用临时表保存中间结果,常用于GROUP BY 和 ORDER BY操作中 ...

xixicat
2014/07/20
4.8K
0
SQL优化一(SQL使用技巧)

1、行列转换: decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值); select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值   sign()函数根据某个值是0、正数...

jmcui
2017/05/14
0
0

没有更多内容

加载失败,请刷新页面

加载更多

如何使用Selenium WebDriver截屏

有谁知道是否可以使用Selenium WebDriver截屏? (注:不是硒RC) #1楼 吉顿 import org.openqa.selenium.OutputType as OutputTypeimport org.apache.commons.io.FileUtils as FileUtils......

技术盛宴
30分钟前
4
0
高阶函数

定义:能够包装函数的,使原本函数增加一些额外的福利的函数 比如: function higherOrderFn(fn){} 能够对fn增加一些额外的福利 应用场景: (function () { var getLogin = functio...

gtandsn
36分钟前
9
0
架构师成长之后

小致Daddy
38分钟前
6
0
Git 介绍和日常命令

git 介绍 GitHub是一个面向开源及私有软件项目的托管平台,因为只支持git 作为唯一的版本库格式进行托管,故名GitHub。 GitHub于2008年4月10日正式上线,除了Git代码仓库托管及基本的 Web管理...

郭靖Michael
今天
9
0
技巧以管理R会话中的可用内存

人们使用什么技巧来管理交互式R会话的可用内存? 我使用下面的函数(基于Petr Pikal和David Hinds在2004年r-help列表中的发布)列出(和/或排序)最大的对象,并偶尔对其中一些对象进行rm() ...

javail
今天
6
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部