mysql分析函数的实现

最新推荐文章于 2023-06-08 15:31:55 发布

-无-为-

最新推荐文章于 2023-06-08 15:31:55 发布

阅读量1.5k

点赞数

分类专栏： MySQL 文章标签： mysql 分析函数

MySQL 专栏收录该内容

42 篇文章 1 订阅

订阅专栏

SELECT  case1, version1 , rank 
FROM (
      SELECT b.case1,b.version1,
             IF((@p1 = b.case1 AND @p2 != b.version1), @rank := @rank + 1, @rank := 0) AS rank,
             @p1:=b.case1,
             @p2:=b.version1
       FROM (
SELECT a.* FROM(
SELECT 1 case1, 1 version1 UNION ALL 
SELECT 1 case1, 2 version1 UNION ALL 
SELECT 1 case1, 3 version1 UNION ALL 
SELECT 1 case1, 4 version1 UNION ALL 
SELECT 2 case1, 1 version1 UNION ALL 
SELECT 2 case1, 1 version1 UNION ALL 
SELECT 3 case1, 1 version1 UNION ALL 
SELECT 3 case1, 1 version1 UNION ALL 
SELECT 3 case1, 0 version1 UNION ALL 
SELECT 4 case1, 1 version1 )a 
ORDER BY a.case1 DESC,a.version1 DESC
              ) b
   ) result 
  -- where rank=0 
   ORDER BY case1 DESC , version1 DESC;

(casystem)root@localhost [(none)]> SELECT a.* FROM(
    -> SELECT 1 case1, 1 version1 UNION ALL 
    -> SELECT 1 case1, 2 version1 UNION ALL 
    -> SELECT 1 case1, 3 version1 UNION ALL 
    -> SELECT 1 case1, 4 version1 UNION ALL 
    -> SELECT 2 case1, 1 version1 UNION ALL 
    -> SELECT 2 case1, 1 version1 UNION ALL 
    -> SELECT 3 case1, 1 version1 UNION ALL 
    -> SELECT 3 case1, 1 version1 UNION ALL 
    -> SELECT 3 case1, 0 version1 UNION ALL 
    -> SELECT 4 case1, 1 version1 )a 
    -> ORDER BY a.case1 DESC,a.version1 DESC;
+-------+----------+
| case1 | version1 |
+-------+----------+
|     4 |        1 |
|     3 |        1 |
|     3 |        1 |
|     3 |        0 |
|     2 |        1 |
|     2 |        1 |
|     1 |        4 |
|     1 |        3 |
|     1 |        2 |
|     1 |        1 |
+-------+----------+
10 rows in set (0.00 sec)

+-------+----------+------+
| case1 | version1 | rank |
+-------+----------+------+
|     4 |        1 |    0 |
|     3 |        1 |    0 |
|     3 |        1 |    0 |
|     3 |        0 |    1 |
|     2 |        1 |    0 |
|     2 |        1 |    0 |
|     1 |        4 |    0 |
|     1 |        3 |    1 |
|     1 |        2 |    2 |
|     1 |        1 |    3 |
+-------+----------+------+
10 rows in set (0.00 sec)

加上rank=0就可以过滤出来每一组的最大值项。

SELECT  case1, version1 , rank  FROM (       SELECT b.case1,b.version1,              IF((@p1 = b.case1 AND @p2 != b.version1), @rank := @rank + 1, @rank := 0) AS rank,              @p1:=b.case1,              @p2:=b.version1        FROM ( SELECT a.* FROM( SELECT 1 case1, 1 version1 UNION ALL  SELECT 1 case1, 2 version1 UNION ALL  SELECT 1 case1, 3 version1 UNION ALL  SELECT 1 case1, 4 version1 UNION ALL  SELECT 2 case1, 1 version1 UNION ALL  SELECT 2 case1, 1 version1 UNION ALL  SELECT 3 case1, 1 version1 UNION ALL  SELECT 3 case1, 1 version1 UNION ALL  SELECT 3 case1, 0 version1 UNION ALL  SELECT 4 case1, 1 version1 )a  ORDER BY a.case1 DESC,a.version1 DESC               ) b    ) result  where rank=0      ORDER BY case1 DESC , version1 DESC;
+-------+----------+------+
| case1 | version1 | rank |
+-------+----------+------+
|     4 |        1 |    0 |
|     3 |        1 |    0 |
|     3 |        1 |    0 |
|     2 |        1 |    0 |
|     2 |        1 |    0 |
|     1 |        4 |    0 |
+-------+----------+------+
6 rows in set (0.00 sec)

http://hidba.org/?p=295

mysql分析函数的实现

May 10th, 2011 hidba

今天收到同事的一需求，要求实现以下功能:

drop table test;

create table test(name varchar(10),name1 varchar(10),count bigint);

delete from test;

insert into test values(’1′,’a',2);

insert into test values(’1′,’b',1);;

insert into test values(’1′,’c',4);

insert into test values(’1′,’d',5);

insert into test values(’1′,’e',7);

insert into test values(’1′,’f',8);

insert into test values(’2′,’g',9);

insert into test values(’2′,’h',0);

insert into test values(’2′,’i',21);

insert into test values(’2′,’j',3);

insert into test values(’2′,’k',4);

insert into test values(’2′,’l',56);

insert into test values(’3′,’m',67);

insert into test values(’3′,’n',89);

insert into test values(’3′,’o',12);

insert into test values(’3′,’p',22);

insert into test values(’3′,’q',23);

insert into test values(’3′,’r',42);

insert into test values(’3′,’s',26);

根据name字段分组，取出改组内的前4项，并且按照count字段进行降序排序，由于mysql没有oracle中的分析函数，看上去很简单的需求，但是折腾了许久，还是没有实现，于是乎在网上收罗了一下mysql分析函数是怎么实现的，找到了mysql分析函数的解决办法，学习了一下，于是乎把同事的功能实现了；

select name, name1, count from (select b.name, b.name1, b.count,

if(@name = b.name, @rank := @rank + 1, @rank := 1) as rank,@name:=b.name

from (select name, name1, count from test order by name asc, count desc) b,

(select @rownum := 0, @name := null, @rank := 0) a) result where rank<5;

| name | name1 | count |

+——+——-+——-+

| 1    | f     |     8 |

| 1    | e     |     7 |

| 1    | d     |     5 |

| 1    | c     |     4 |

| 2    | l     |    56 |

| 2    | i     |    21 |

| 2    | g     |     9 |

| 2    | k     |     4 |

| 3    | n     |    89 |

| 3    | m     |    67 |

| 3    | r     |    42 |

| 3    | s     |    26 |

+——+——-+——-+

12 rows in set (0.02 sec)

如果上面的sql初次看到有些让人摸不着头脑的话，你可以看看他的执行计划,然后从执行计划得到一些执行流程，该sql中最核心的技术点为使用自定义变量来保存sql执行过程中的值：

if(@name = b.name, @rank := @rank + 1, @rank := 1) as rank,@name:=b.name

这条判断语句对下面结果进行处理，并生成rank，由于下面查询的结果中对name做了排序，所以@name:=b.name使的相同name值的rank能够递增；

+—-+————-+————+——–+—————+——+———+——+——+—————-+

| id | select_type | table      | type   | possible_keys | key | key_len | ref | rows | Extra          |

+—-+————-+————+——–+—————+——+———+——+——+—————-+

| 1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL | NULL    | NULL |   19 | Using where    |

| 2 | DERIVED     | <derived4> | system | NULL          | NULL | NULL    | NULL |    1 |                |

| 2 | DERIVED     | <derived3> | ALL    | NULL          | NULL | NULL    | NULL |   19 |                |

| 4 | DERIVED     | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL | No tables used |

| 3 | DERIVED     | test       | ALL    | NULL          | NULL | NULL    | NULL |   19 | Using filesort |

如果你对下面的select @rownum := 0, @name := null, @rank := 0看不太明白，可以改写一下sql：

select name, name1, count from (select b.name, b.name1,b.count,

if(@name = b.name, @rank := @rank + 1, @rank := 1) as rank,@name:=b.name

from (select name, name1, count,@rownum := 0, @name := null, @rank := 0

from test order by name asc, count desc) b) result where rank<4;

+—-+————-+————+——+—————+——+———+——+——+—————-+

| id | select_type | table      | type | possible_keys | key | key_len | ref | rows | Extra          |

+—-+————-+————+——+—————+——+———+——+——+—————-+

| 1 | PRIMARY     | <derived2> | ALL | NULL          | NULL | NULL    | NULL |   19 | Using where    |

| 2 | DERIVED     | <derived3> | ALL | NULL          | NULL | NULL    | NULL |   19 |                |

| 3 | DERIVED     | test       | ALL | NULL          | NULL | NULL    | NULL |   19 | Using filesort |

+—-+————-+————+——+—————+——+———+——+——+—————-+

Categories: database Tags:

Comments are closed.

mysql的join buffer 还是时间惹的祸

-无-为-

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql分析函数的实现

http://hidba.org/?p=295mysql分析函数的实现May 10th, 2011hidba今天收到同事的一需求，要求实现以下功能:drop table test;create table test(name varchar(10),name1 varchar(10),count bigint);delete
复制链接

扫一扫