oracle 分析函数over() ——实现分组后取第一条数据

最新推荐文章于 2025-07-05 09:45:26 发布

团滚滚

最新推荐文章于 2025-07-05 09:45:26 发布

阅读量1.6w

点赞数 2

CC 4.0 BY-SA版权

分类专栏： sql

本文链接：https://blog.youkuaiyun.com/hengqianqq/article/details/82428825

sql 专栏收录该内容

0 篇文章

订阅专栏

本文介绍了分析函数的基本概念及其在SQL中的应用。重点讲解了row_number、rank等常用分析函数，并通过实例展示了如何使用这些函数实现复杂的数据分析任务。此外，还对比了Oracle与MySQL中分析函数的实现方式。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

个人认为分析函数与分组函数的不同，是对原来的数据进行分析，却不会修改原数据的结构、条数。

常用的分析函数如下所列:
row_number() over(partition by … order by …)
rank() over(partition by … order by …)
dense_rank() over(partition by … order by …)
count() over(partition by … order by …)
max() over(partition by … order by …)
min() over(partition by … order by …)
sum() over(partition by … order by …)
avg() over(partition by … order by …)
first_value() over(partition by … order by …)
last_value() over(partition by … order by …)
lag() over(partition by … order by …)
lead() over(partition by … order by …)

over()中的关键字：
over（order by salary rows between 2 preceding and 4 following）行数在前2行和后4行
下面三条语句等效（都是查所有数据）：
over（order by salary rows between unbounded preceding and unbounded following）行数前无限到后无限
over（order by salary range between unbounded preceding and unbounded following）范围前无限到后无限
等效
over(partition by null)

例子：
数据格式
这里写图片描述
分组取第一条的效果

sql：使用分析函数为要分组的数据添加行号，再按行号筛选，相当于分组中每一组取一条数据。

SELECT *  
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY x ORDER BY y DESC) rn, test1.* FROM test1)    
WHERE rn = 1  ;

分析函数的详细用法可以参考：
超级牛皮的oracle的分析函数over(Partition by…) 及开窗函数

mysql中没有分析函数，所以需要做相应转换，附以前写的一个oracle分析函数到mysql的转换：

    <select id="qryCheckAgain" parameterType="hashMap" resultType="hashMap" flushCache="true">
        <![CDATA[
WITH AA AS (SELECT MSGNO,ID_CARD,NAME,CHECKRESULT,BUSINESSCODE,AUTH_USER_ID,BRANCH_ID,SOURCE_TYPE FROM PICS_JOURNAL  WHERE TRANTIME > #{TRANTIME}),
     BB AS (SELECT DISTINCT ID_CARD, NAME FROM AA WHERE MSGNO = 'hist'),
     CC AS (SELECT DISTINCT ID_CARD, NAME FROM AA WHERE MSGNO IN('0002','bhx','1110') AND CHECKRESULT IN ('00', '01', '02', '03')),
     DD AS (SELECT * FROM BB MINUS SELECT * FROM CC), 
     EE AS (SELECT AA.ID_CARD,AA.NAME,AA.BUSINESSCODE,AA.SOURCE_TYPE,AUTH_USER_ID,BRANCH_ID FROM DD D LEFT JOIN AA ON D.ID_CARD = AA.ID_CARD AND D.NAME = AA.NAME)
SELECT *
  FROM (SELECT ROW_NUMBER () OVER (PARTITION BY EE.ID_CARD, EE.NAME,EE.SOURCE_TYPE ORDER BY EE.BUSINESSCODE) RN, EE.*
          FROM  EE) T
WHERE T.RN = 1
        ]]>
    </select>

mysql:

    <select id="qryCheckAgain" parameterType="hashMap" resultType="hashMap" flushCache="true">
        <![CDATA[
SELECT AA.ID_CARD,AA.`NAME`,MSGNO,CHECKRESULT,BUSINESSCODE,AUTH_USER_ID,BRANCH_ID,SOURCE_TYPE FROM
    (SELECT DISTINCT ID_CARD,`NAME` FROM PICS_JOURNAL  WHERE TRANTIME > #{TRANTIME} AND  MSGNO = 'hist' AND (ID_CARD,`NAME`) NOT IN (
        SELECT DISTINCT ID_CARD,`NAME` FROM PICS_JOURNAL  WHERE TRANTIME > #{TRANTIME} AND MSGNO IN('0002','bhx','1110') AND CHECKRESULT IN ('00', '01', '02', '03') AND ID_CARD IS NOT NULL AND `NAME` IS NOT NULL )
    ) AA
    LEFT JOIN (
    SELECT ID_CARD,`NAME`,MSGNO,CHECKRESULT,BUSINESSCODE,AUTH_USER_ID,BRANCH_ID,SOURCE_TYPE FROM (
        SELECT GG.*,IF(@card2=ID_CARD AND @name2=`NAME` AND @source2=SOURCE_TYPE,@rank:=@rank+1,@rank:=1) AS ROWNUM,@card2:=ID_CARD,@name2:=`NAME`,@source2:=SOURCE_TYPE FROM (
            SELECT ID_CARD,`NAME`,MSGNO,CHECKRESULT,BUSINESSCODE,AUTH_USER_ID,BRANCH_ID,SOURCE_TYPE
            FROM PICS_JOURNAL ,(SELECT @rank:=0, @rownum:=0, @card2:=NULL,@name2:=NULL,@source2=NULL) r
            WHERE TRANTIME > #{TRANTIME} ORDER BY ID_CARD,`NAME`,SOURCE_TYPE
            ) GG
        ) MM WHERE MM.ROWNUM=1
    ) BB  ON AA.ID_CARD = BB.ID_CARD AND AA.`NAME` = BB.NAME
        ]]>
    </select>