常用的分析函数

转载于：https://blog.csdn.net/hengqianqq/article/details/82428825

常用的分析函数如下所列: 
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>