nginx配置:

http {
    limit_req_zone $binary_remote_addr zone=limit:50m rate=10r/m;
    server {
          server_name xxx;
          error_page 503 @503;
          location @503 {
             rewrite ^(.*)$ /503.html break;
          }
    }
}

这种情况下,如果是GET请求触发限流,会返回自定义的503.html页面内容,但是如果是POST请求触发限流,则会返回405 Method Not Allowed。
查看一下405产生的原因:
http://nginx.org/en/CHANGES

Screen Shot 2022-01-17 at 8.36.31 PM.png

可以看到,自0.7.11版本开始,POST请求静态文件内容会返回405,但我们这请求的POST接口并不是静态文件内容,为何会触发呢?
猜想是因为我们写了rewrite ^(.*)$ /503.html break;这条规则,即POST请求的不再是之前的接口而是这个503.html静态页面,因此,需要针对这个503自定义内容做一下修改:

http {
    limit_req_zone $binary_remote_addr zone=limit:50m rate=10r/m;
    server {
          server_name xxx;
          error_page 503 @503;
          location @503 {
             if ( $request_method = POST ) {
                return 503 'json内容或者文字内容比如 {"code":503,"msg":"xxx"}';
             }
             rewrite ^(.*)$ /503.html break;
          }
    }
}

这样修改后POST请求触发限流后不会返回405了

准备数据

mysql> create table product_orders(id int,product varchar(255),amount int);

mysql> insert into product_orders(id, product, amount)
     values(1,'A',250),(2,'B',150),(3,'C',200),
     (4,'A',250),(5,'B',210),(6,'C',125),
     (7,'A',350),(8,'B',225),(9,'C',150);


mysql> select * from product_orders;
+------+---------+--------+
| id   | product | amount |
+------+---------+--------+
|    1 | A       |    250 |
|    2 | B       |    150 |
|    3 | C       |    200 |
|    4 | A       |    250 |
|    5 | B       |    210 |
|    6 | C       |    125 |
|    7 | A       |    350 |
|    8 | B       |    225 |
|    9 | C       |    150 |
+------+---------+--------+

首先,我们将使用以下SQL查询对组(product列)中的每一行进行排名。

mysql> SELECT id, product, amount,
            @product_rank := IF(@current_product = product, @product_rank + 1, 1) 
             AS product_rank,
            @current_product := product
       FROM product_orders
       ORDER BY product, amount desc;
+------+---------+--------+--------------+-----------------------------+
| id   | product | amount | product_rank | @current_product := product |
+------+---------+--------+--------------+-----------------------------+
|    7 | A       |    350 |            1 | A                           |
|    1 | A       |    250 |            2 | A                           |
|    4 | A       |    250 |            3 | A                           |
|    8 | B       |    225 |            1 | B                           |
|    5 | B       |    210 |            2 | B                           |
|    2 | B       |    150 |            3 | B                           |
|    3 | C       |    200 |            1 | C                           |
|    9 | C       |    150 |            2 | C                           |
|    6 | C       |    125 |            3 | C                           |
+------+---------+--------+--------------+-----------------------------+

在上述查询中,我们首先按amount列将其组中的每条记录按降序排序,然后对其进行排名。 如果要按amount的升序对其进行排序,可以通过更改ORDER by子句来实现。

SELECT id, product, amount, 
       @product_rank := IF(@current_product = product, @product_rank + 1, 1) 
       AS product_rank,
       @current_product := product 
  FROM product_orders
  ORDER BY product, amount asc;

接下来,我们将上述查询用作子查询,以选择每个组的前N行(例如,每个类别的前2行)。

如何选择每个组的前2行
这是使用上述方法为每个组选择前2行的SQL查询。 我们将上面的查询用作子查询,并选择等级小于或等于2的行。

mysql> select id, product, amount from (
         SELECT id, product, amount,
            @product_rank := IF(@current_product = product, @product_rank + 1, 1) 
             AS product_rank,
            @current_product := product
         FROM product_orders
         ORDER BY product, amount desc) ranked_rows
       where product_rank<=2;
+------+---------+--------+
| id   | product | amount |
+------+---------+--------+
|    7 | A       |    350 |
|    1 | A       |    250 |
|    8 | B       |    225 |
|    5 | B       |    210 |
|    3 | C       |    200 |
|    9 | C       |    150 |
+------+---------+--------+

如何选择每个组的前10行
同样,你可以使用以下查询从每个组中选择前10行。

mysql> select id, product, amount from (
         SELECT id, product, amount,
            @product_rank := IF(@current_product = product, @product_rank + 1, 1) 
             AS product_rank,
            @current_product := product
         FROM product_orders
         ORDER BY product, amount desc) ranked_rows
       where product_rank<=10;