菜鸟公园
  • 编程技能
    • PHP
    • CPP
    • Golang
    • MySQL
    • 工具分享
  • 英语学习
  • 信文随笔
  • 关于本站
sign in/up

标签归档：php

2019-08-23 作者：菜鸟DJ 0
PHP

MySQL 分组之后如何取Top(N)?

最近碰到一个有意思的问题，因为MySQL里没有top n的用法，所以如果要实现取数据的前几操作只能通过排序之后加limit限制数量，但是这种用法又跟group 冲突。这篇文章就是来分析下分组取topN的解题思路。

现在创建一个测试表。用户的商品消费数据（测试表就不建立索引了）

CREATE TABLE `tb_user_consume` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned DEFAULT NULL,
  `goods_id` int(11) unsigned DEFAULT NULL,
  `goods_name` varchar(50) CHARACTER SET utf8mb4 DEFAULT NULL,
  `price` decimal(10,2) unsigned DEFAULT NULL,
  `num` int(10) unsigned DEFAULT NULL,
  `total` decimal(10,2) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tb_user_consume`(`user_id`,`goods_id`,`goods_name`,`price`,`num`,`total`)
VALUES
(100,1,"1号商品",1.3,10, price*num),
(100,2,"2号商品",1.15,12, price*num),
(100,3,"3号商品",3.4,5, price*num),
(100,4,"4号商品",18.99,2, price*num),
(100,5,"5号商品",7.4,9, price*num),'
(101,1,"1号商品",1.3,13, price*num),
(101,2,"2号商品",1.15,12, price*num),
(101,3,"3号商品",3.4,20, price*num),
(101,4,"4号商品",18.99,8, price*num),
(101,5,"5号商品",7.4,7, price*num),'
(102,1,"1号商品",1.3,21, price*num),
(102,2,"2号商品",1.15,3, price*num),
(102,3,"3号商品",3.4,51, price*num),
(102,4,"4号商品",18.99,23, price*num),
(102,5,"5号商品",7.4,22, price*num),'
(103,1,"1号商品",1.3,2, price*num),
(103,2,"2号商品",1.15,7, price*num),
(103,3,"3号商品",3.4,9, price*num),
(103,4,"4号商品",18.99,22, price*num),
(103,5,"5号商品",7.4,99, price*num),'
(104,1,"1号商品",1.3,77, price*num),
(104,2,"2号商品",1.15,54, price*num),
(104,3,"3号商品",3.4,23, price*num),
(104,4,"4号商品",18.99,23, price*num),
(104,5,"5号商品",7.4,44, price*num)
;

假如现在有一个需求是，筛选出用户消费商品总价最高的前三个商品。

粗一看，这个需求也没有什么实现上的难度，就是根据用户分组，取出表里total最高的三行记录就可以了。
对没有错，需求就是这么简单，解题思路也不难，那么我们开始着手编码了。

第一步，做一个子查询，

取出表里total最高的三行记录

sql写起来也很简单，如下所示

SELECT * FROM `tb_user_consume` WHERE user_id = 100 ORDER BY total DESC LIMIT 3;

第二步，按照用户分组
取出所有用户

SELECT * FROM `tb_user_consume` ORDER BY total DESC LIMIT 3 GROUP BY user_id;

看这个好像是满足了需求，别急，我们运行一下。

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘GROUP BY user_id’ at line 1

报错了，很明显上面的sql有语法错误。limit 只能用在查询语句的最后面。
那么我们要怎么去实现这个需求呢？用单一的子查询好像都没法直接的按照用户分组来取数据。
一般到这种时候，我们很可能就直接用代码来解决了。

先取出所有用户列表。 SELECT DISTINCT(user_id) AS uid FROM tb_user_consume;

然后遍历用户列表，按照上面的查询语句查出所有的用户前三total信息 SELECT * FROM tb_user_consume WHERE user_id = 100 ORDER BY total DESC LIMIT 3;

这种方法不是不可取，在表里的数据不多的时候，用这个也能完成需求，抛去执行效率不说，我们就说开发效率，又是写代码，又是写sql。还要去联调，是不是很费时费力？
那么到底能不能通过sql语句直接查询出来呢？

首先我们要想，上面不能实现的痛点在哪里？没有办法先limit 3，对不对？那我们能不能通过排序筛选的方式来实现，排序后达到三个的数量我们就停止。
按照机器的思维应该是，先order by user_id, 然后 order by total desc。

SELECT * FROM tb_user_consume ORDER BY user_id ,total DESC;

在这个结果集里当user_id 输出3个记录行就停止。 本文的重点来了 怎么实现这个呢？
通过谷歌（其实是百度）发现mysql里有一个 case when的条件判断。正好满足我们的需求。【 在这个结果集里当user_id 输出3个记录行就停止 】 美哉！开撸。

SELECT @rnd :=
    CASE
    WHEN @userid = `user_id` THEN
    @rnd := @rnd+1
    ELSE 1
    END rnd, @userid := `user_id`, user_id,total,goods_id,goods_name,price
FROM `tb_user_consume`,
    (SELECT @rnd := 1,
         @userid:=0) b
    ORDER BY  `user_id` ,`total` DESC

像这样我们就可以以rnd变量来标记我们的结果集排序结果了。这样我们把它作为一个子查询在外面加上限制条件就拿到指定的行数。
最终的sql如下：

SELECT *
FROM 
    (SELECT @rnd :=
        CASE
        WHEN @userid = `user_id` THEN
        @rnd := @rnd+1
        ELSE 1
        END rnd, @userid := `user_id`, user_id,total,goods_id,goods_name,price
    FROM `tb_user_consume`,
        (SELECT @rnd := 1,
         @userid:=0) b
        ORDER BY  `user_id` ,`total` DESC) aa
    WHERE rnd <=3;

最终的查询展示结果：

+------+----------------------+---------+--------+----------+------------+-------+
| rnd  | @userid := `user_id` | user_id | total  | goods_id | goods_name | price |
+------+----------------------+---------+--------+----------+------------+-------+
|    1 |                  100 |     100 |  66.60 |        5 | 5号商品    |  7.40 |
|    2 |                  100 |     100 |  37.98 |        4 | 4号商品    | 18.99 |
|    3 |                  100 |     100 |  17.00 |        3 | 3号商品    |  3.40 |
|    1 |                  101 |     101 | 151.92 |        4 | 4号商品    | 18.99 |
|    2 |                  101 |     101 |  68.00 |        3 | 3号商品    |  3.40 |
|    3 |                  101 |     101 |  51.80 |        5 | 5号商品    |  7.40 |
|    1 |                  102 |     102 | 436.77 |        4 | 4号商品    | 18.99 |
|    2 |                  102 |     102 | 173.40 |        3 | 3号商品    |  3.40 |
|    3 |                  102 |     102 | 162.80 |        5 | 5号商品    |  7.40 |
|    1 |                  103 |     103 | 732.60 |        5 | 5号商品    |  7.40 |
|    2 |                  103 |     103 | 417.78 |        4 | 4号商品    | 18.99 |
|    3 |                  103 |     103 |  30.60 |        3 | 3号商品    |  3.40 |
|    1 |                  104 |     104 | 436.77 |        4 | 4号商品    | 18.99 |
|    2 |                  104 |     104 | 325.60 |        5 | 5号商品    |  7.40 |
|    3 |                  104 |     104 | 100.10 |        1 | 1号商品    |  1.30 |
+------+----------------------+---------+--------+----------+------------+-------+

参考文章: 我的mysql如何分组取top10？

<?php
$host = '127.0.0.1';
$dbname = 'yang';
$port = 3306;

$db = new PDO("mysql:host=$host;dbname=$dbname;port=$port", 'root', '12345');

$goods_info = [
    ['id' => 1, 'name' => '1号商品', 'price' => 1.30],
    ['id' => 2, 'name' => '2号商品', 'price' => 1.15],
    ['id' => 3, 'name' => '3号商品', 'price' => 3.40],
    ['id' => 4, 'name' => '4号商品', 'price' => 18.99],
    ['id' => 5, 'name' => '5号商品', 'price' => 7.40],
];

function insert($goods_info, PDO &$db, $start)
{
    $sql = 'insert into tb_user_consume(user_id,goods_id,goods_name,price,num,total) values';
    for ($i = $start; $i < $start + 50000; $i++) {
        foreach ($goods_info as $info) {
            $num = mt_rand(0, 1000);
            $total = $num * $info['price'];
            $sql .= sprintf("(%d,%d,\"%s\",%f,%d,%f),", $i, $info['id'], $info['name'], $info['price'], $num, $total);
        }
    }
    $sql = substr($sql, 0, -1);
    //echo $sql;

    $db->prepare($sql)->execute();
}

// 批量添加测试数据
//for ($j = 1000000; $j < 2000000; $j += 50000) {
//    insert($goods_info,$db,$j);
//}

// 执行时间
$start = time();
select($db);
echo "cost:".(time()-$start)."\n";

function select(PDO &$db){
    for ($i = 1000000; $i < 2000000; $i++) {
        $sql = 'SELECT * FROM `tb_user_consume` WHERE user_id = '.$i.' ORDER BY total DESC LIMIT 3; ';
        $ret = $db->query($sql)->fetchAll();
        //var_dump($ret);
    }
}

MySQL php

2019-03-21 作者：菜鸟DJ 0
编程技能

解一道字符串变化题

0x01. 做一道字符串变换的题目

给定一组字符串例按照设定一个行数，以从上到下，从左到右进行Z字形排列。
比如输入的字符串为『ABCEDFGHIJKLMN』，行数设为3的时候，排列如下

[A] [ ] [E] [ ] [I] [ ] [M]
[B] [D] [F] [H] [J] [L] [N]
[C] [ ] [G] [ ] [K] [ ] [ ]

之后，你的输出需要从左往右逐行读取，产生一个新的字符串，比例如『AEIMBDFHJLNCGK』
请设计一个这样的字符串变换函数

0x02. 分析解题思路

拿到这个题目，从最直观的方向入手就是，按照题目示例中的排序方式给逐个字符串扫描，排列到对应规则的位置上。
通俗来说，按照坐标系走（x轴从左到右，y轴从上到下）我们可以分析出下面的坐标点。
A(0,0)
B(0,1)
C(0,2)
D(1,1)
E(2,0)
F(2,1)
G(2,2)
H(3,1)
…
仔细观察排列关系之后，我们不难发现题中说的排列方式按照Z字形其实是一个误导，对程序而言A->G，E->K实际上不是一个可以重复循环处理的方案，我们需要把这种排列切割为A->D，E->H，这样的处理方式可以使程序能够重复循环处理。
可以得到如下伪代码

*p = str[0]
while *p != '\0'{
    if (i<row){
        ... set value
    }else{
        ... set value
    }
    loop++
}

具体代码实现可以拉到文末。

程序解题到这里，其实本题基本上已经解决了，但是如果我们更深层的思考下，这种解题过程是否可以值得更优化下，一定要使用二维数据来填值吗？从题目的立意来看，无非是将字符串重组，既然说到重组无非就是一个权重变化的过程，那么我们可否设计一个方程来计算这种权重呢？ 其实上面的解题里对应的二维数组也是一个权重的表现。二维坐标对应到一维的权重里。

按照x+y*10的思路去做。
A(0,0)->0
B(0,1)->10
C(0,2)->20
D(1,1)->11
E(2,0)->2
F(2,1)->12
G(2,2)->22
H(3,1)->13

按照解题思路一的分块重复的思路，我们惊奇的发现，后面的循环只是对前面的对应位置加2，这就很棒棒哒，只要算出第一块排列的位置，后面的权重就很好计算了。
转换公式x+y*10 中的系数10肯定不是一个好的系数，对于row 大于10的情况就非常容易两个位置出现转换的权重一致的情况，所以在设计公式的时候我们需要把10替换为字符串长度len，这样就可以保证唯一权重。

0x03. 解题代码

php版本，包含2种思路的解题

<?php

$para = getopt("s:n:");

$row = $para["n"] ?? 0;
if ($row < 2) {
    exit("请输入大于2的行数");
}
$str = $para['s'] ?? '';
if (strlen($str) <= 0) {
    exit("请输入要排序的字符串");
}

$output = null;
$len = strlen($str);
$pos = 0;
$i = 0;
$j = 0;
$loop = 1;
while ($pos < $len) {
    for ($i; $i < $row; $i++) {
        if ($pos == $len) {
            break;
        }
//        echo "$i,$j," . $str[$pos] . "\n";

        $output[$i][$j] = $str[$pos];
        $pos++;

    }
//    echo "| \n";
    $i -= 2;
    $j++;
    for ($j; $j < $row * $loop - 1; $j++) {
        if ($pos == $len) {
            break;
        }
//        echo "$i,$j," . $str[$pos] . "\n";
        $output[$i][$j] = $str[$pos];
        $pos++;
        $i--;
        if ($i < 0) {
            $i = 0;
            $pos--;
            break;
        }
    }
    $loop++;
//    echo "- \n";

}

$output2 = '';
for ($n = 0; $n < $row; $n++) {
    for ($m = 0; $m < $j; $m++) {
        echo isset($output[$n][$m]) ? "[" . $output[$n][$m] . "] " : "[ ] ";
        if (isset($output[$n][$m])) {
            $output2 .= $output[$n][$m];
        }
    }
    echo "\n";
}
echo "$output2.\n";

$output3 = [];
$n = 2 * ($row - 1);
$cnt = ceil((float)$len/$n);
$pos = 0;
for ($k = 0; $k < $cnt; $k++) {
    for ($l = 0; $l < $n; $l++) {
        if ($l < $row) {
            $output3[$l * $len + $k * ($row - 1)] = $str[$pos];
            $pos++;
        } else {
            $output3[($n - $l) * $len + $l - $row + 1 + $k * ($row - 1)] = $str[$pos];
            $pos++;
        }
        if ($pos >= $len) {
            break;
        }
    }
}
ksort($output3);

$output3 = array_values($output3);
$len = count($output3);
for ($i = 0; $i < $len; $i++) {
    echo $output3[$i];
}
echo "\n";

C++版本（限于能力问题，C++版本是练手的，写的不好还望大家指出改进）

#include <iostream>
#include <cmath>

int main(int argc, const char *argv[])
{
    int row, len;
    char str[100];
    std::cout << "请输入行数" << std::endl;
    std::cin >> row;
    if (row < 2) {
        std::cout << "请输入大于2的行数";
        exit(1);
    }

    std::cout << "请输入需要排序字符串" << std::endl;
    std::cin >> str;
    len = (int)strlen(str);
    if (len <= 0) {
        std::cout << "请输入需要排序字符串" << std::endl;
    }

    int b = 2 * (row - 1);
    int cnt = ceil((float)len / b);
//    printf("len=%d,b=%d,cnt=%d\n",len,b,cnt);
//    printf("%f\n",ceil(10.0/3));

    int pos = 0;
    char output3[100 * 100] = {};
    for (int k = 0; k < cnt; k++) {
        for (int l = 0; l < b; l++) {
            if (l < row) {
                output3[l * len + k * (row - 1)] = str[pos];
//                printf("竖排:%d,%d,%d,%c\n", k, l, l * len + k * (row - 1), str[pos]);
                pos++;
            } else {
                output3[(b - l) * len + l - row + 1 + k * (row - 1)] = str[pos];
//                printf("斜排:%d,%d,%d,%c\n", k, l, (b - l) * len + l - row + 1 + k * (row - 1), str[pos]);
                pos++;
            }
//            printf("len=%d,pos=%d,k=%d,l=%d\n",len,pos,k,l);
            if (pos >= len) {
                break;
            }
        }
    }
//    len = (int)strlen(output3);
    for (int i = 0; i < 100 * 100; i++) {
        if (output3[i] != '\0') {
            printf("%c", output3[i]);
        }
    }
    printf("\n");
    return 0;
}

C++ php 字符串 编程

2/2

天气

分类目录

热门文章

记一次和流氓软件战斗的过程0 comments
手机app https抓包步骤一揽0 comments
记一下RabblitMQ的安装和RPC的工作模式0 comments
聊一聊快排算法0 comments
MAC & WIN 平台效率工具清单0 comments
你真的会用MySQL里的max函数吗？0 comments
MySQL 分组之后如何取Top(N)?0 comments
B站代码解读 — LRUCache0 comments
解搜索二维矩阵题0 comments
解一道字符串变化题0 comments

微信公众号：菜鸟公园

微信公众号
微信公众号：菜鸟公园
隐私政策