主流数据库之间对SQL:2003标准的不同实现方法比较(第三部分 前n项(Top-n)查询)

翻译 2008年07月31日 13:26:00
本文严禁在未征得本人同意的情况下以任何形式进行转载。本人只接受在邮件中的转载申请,如需转载,请发送邮件至 betteryou@126.com。 

 

 

n(Top-n)查询

 

目标:与上述的简单限制结果集查询类似,但需要包含等值的关系。所以,查询可能会返回超过n行的数据。

 

有的称之为配额查询(quota-query)

 

如下例:

 

SELECT * FROM person ORDER BY age ASC;

+----------+-------------+-----+
|PERSON_ID | PERSON_NAME | AGE |
+----------+-------------+-----+
|        7 | Hilda       |  12 |
|        8 | Bill        |  12 |
|        4 | Joe         |  23 |
|        2 | Veronica    |  23 |
|        3 | Michael     |  27 |
|        9 | Marianne    |  27 |
|        1 | Ben         |  50 |
|       10 | Michelle    |  50 |
|        5 | Irene       |  77 |
|        6 | Vivian      |  77 |
+----------+-------------+-----+

 

如果我们想找出3个最年轻的人(n=3),那么结果集应该如下:

 

+----------+-------------+-----+
|PERSON_ID | PERSON_NAME | AGE |
+----------+-------------+-----+
|        7 | Hilda       |  12 |
|        8 | Bill        |  12 |
|        4 | Joe         |  23 |
|        2 | Veronica    |  23 |
+----------+-------------+-----+

 

标准(Standard)

对于SQL标准,主要有两种方法来获取值:

 

  • 快速变量(fast variant)

SQL:2003中的一项重要添加就是附加的非核心OLAP(联机分析处理)特性。如果DBMS支持基本的OLAP(ID F611特性),那么top-n查询就可以使用RANK()这样的window函数了:

SELECT * FROM (

   SELECT

       RANK() OVER (ORDER BY age ASC) AS ranking,

       person_id,

       person_name,

       age

   FROM person

) AS foo

WHERE ranking <= 3

 

  • 慢速变量(slow variant)

如果DBMS不支持基本的OLAP特性,那么可通过替代方法来解决top-n的问题。这种方法实在很慢,在大部分情况下都不会使用。

 

相关子查询方法:

 

SELECT * FROM person AS px

WHERE (

SELECT COUNT(*)

FROM person AS py

WHERE py.age < px.age

) < 3

PostgreSQL

支持慢速标准SQL在实践中,需要使用PostgreSQL独有的表达式,以便获得较好的查询性能:

 

SELECT *

FROM person

WHERE (

   age <= (

      SELECT age FROM person

      ORDER BY age ASC

      LIMIT 1 OFFSET 2                    -- 2= n-1

)

) IS NOT FALSE

 

DB2

支持快速标准SQL

MS SQL Server

支持快速标准SQL

 

MSSQL 2000支持慢速标准SQL。在实践中,需要使用MSSQL独有的表达式,以便获得较好的查询性能:

 

SELECT TOP 3 WITH TIES *

FROM person

ORDER BY age ASC

MySQL

支持慢速标准SQL。在实践中,必须使用MySQL指定的解决方案,以便取得较好的查询性能:

 

SELECT *

FROM person

WHERE age <= COALESCE(   --COALESCE”关键字与括弧不要有空格

(

   SELECT age

   FROM person

   ORDER BY age ASC

   LIMIT 1 OFFSET 2          -- 2 = n – 1

),

(

   SELECT MAX (age)

   FROM person

)

)

 

COALESCE调用中的第二个参数当表中的集合内容少于n时查询依然能正常工作

Oracle

支持快速标准SQL。但由于Oracle不支持在子查询后加“AS”,所以查询需要简单修改:

 

SELECT * FROM (

   SELECT

        RANK() OVER (ORDER BY age ASC) AS ranking,

        person_id,

        person_name,

        age

   FROM person

)

WHERE ranking <=3

 

原文参考:Comparison of different SQL implementations

主流内存数据库简要比较

由本人搜集资料整理而成,如需转载请注明出处:http://blog.csdn.net/nanfeiyannan/article/details/9003009(谢谢) 主要内存数据库对比   ...
  • nanfeiyannan
  • nanfeiyannan
  • 2013年05月31日 23:15
  • 14969

主流nosql数据库对比

转自:http://asyty.iteye.com/blog/1202106 主流nosql数据库对比 博客分类:  NOSQL nosql数据库对比  目前开源的NOSQL数据...
  • suejl
  • suejl
  • 2016年02月23日 10:52
  • 829

Sql操作之二:不同数据库中表数据迁移

一:在同一服务器 insert  into   数据库name1.dbo.table_name2(字段名称1,字段名称2,……) select  字段名称1,字段名称2,…… from ...
  • llso9001
  • llso9001
  • 2016年02月02日 13:57
  • 2784

斐波那契数列累加求前n项和

斐波那契累加求前n项和 写一个斐波那契求和的算法,第一反应太简单了,递归一下就求出来了 常规的方法:...
  • diaoaa
  • diaoaa
  • 2014年05月18日 20:13
  • 3208

top-N推荐中以recall为指标的实验设计

如何很亮top-N推荐的性能,是个重要的问题。其中,recall的计算并非那么直观的。下面,先看几个paper里不同的计算方法,然后做个总结。 [27]的实验方法: 记处理后的(隐式)评分矩阵为S,...
  • u013166160
  • u013166160
  • 2014年01月08日 16:39
  • 1111

SQL数据库与Lucene数据库性能测试报告

http://www.cnitblog.com/cockerel/archive/2007/07/30/30911.html SQL数据库与Lucene数据库性能测试报告 一、       测...
  • mituan1234567
  • mituan1234567
  • 2015年04月24日 16:21
  • 547

SQL查询前几条数据的方法

sql在不同数据库查询前几条数据  1. ORACLE    SELECT * FROM TABLE1 WHERE ROWNUM    HQL: from table1 t order ...
  • qq8618
  • qq8618
  • 2013年10月19日 16:51
  • 12671

SQL Server 比较两个数据库的视图和存储过程结构差异

IF EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo]....
  • tuzhen007
  • tuzhen007
  • 2014年02月26日 11:28
  • 1014

几款主流数据库的详细比较

http://blog.csdn.net/zhouhx08/article/details/7528254 开发数据库应用,选择一个好的数据库是非常重要的。目前,商品化的数据库管理系统以...
  • loongwong2011
  • loongwong2011
  • 2016年09月02日 09:28
  • 6756

Top-N Recommendation——基于用户的推荐实验

无论是在实体商店还是在网络上,都会有Top-N推荐的情况。基于客户或者基于商品做出推荐。本实验基于Movielens、Ratings的电影数据集,对用户做出Top-N 推荐。主要目的是基于User-B...
  • woshimalingyi
  • woshimalingyi
  • 2016年03月04日 12:16
  • 3599
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:主流数据库之间对SQL:2003标准的不同实现方法比较(第三部分 前n项(Top-n)查询)
举报原因:
原因补充:

(最多只允许输入30个字)