背景

背景就是师傅让我写一个推荐算法，来推荐我们组空闲的移动设备给需要使用的人。我们的痛点在于：
1、 不知道谁手头的手机空闲，往往挨个轮询借手机；
2、 不知道自己的需求要用什么设备去测试；
针对这两个痛点，我们给出了基于语义分析的机型推荐系统和基于用户行为的设备推荐系统。暂时我们第一个阶段只做设备推荐系统。

---

数据收集以及清洗：

作为一个推荐系统，我们首先要确定数据来源。我们使用了日常设备登记的平台——设备登录系统的后台数据库。选择了其中的login表作为数据来源。然后，就到了令我痛不欲生的数据清理过程。。。

源数据分析

首先我来了一句

select count(username) from login;

好家伙，有4000多个用户？我们组人口流动这么大？4000人缩减到30？WTF？那我们看看到底有哪一些人吧。

select distinct(username) from login;

我去，怎么这么多莫名奇妙的玩意啊？什么用户名后加一些莫名奇妙的东西的数据？什么web JavaScript?老哥，你们当初数据都不校验就直接扔数据库的吗？你的后台能力很强。
但又能怎么办呢？洗呗，我洗，我洗，我洗洗洗。
怎么洗呢？

数据清洗

我发现好像系统有过几个版本，一种username后加一个\n的，一种加-%的，还有加Customer Header的。来来来，update login set username=substring(username,'\\',1)了解一下。但是手动update总是令人头疼，怎么破？Python啊，pymysql啊。

核心思想就是：
1、 将所有username按照字符串的顺序进行order by，这样相同开头的字符串就会在一起

select username from  login order by username

2、 提取最长公共子串，可以采用归并的想法。def getCommonStr(str1, str2)，最后获得commonStr,然后用下面的代码：

sql_statement = 'update login set username=\'%s\' where username like \'%s%%\''
cusor.excute(sql_statement % (common_str, common_str))
cusor.commit()

然后就基本清洗了所有用户名的数据。

数据分析

数据分析相对简单，就是一些常规的pandas,matplotlib的操作。但是这些数据没有离散话，源数据中username，device_code都是varchar类型的，如果需要离散化，需要在读取数据的时候维护两个dict或者两个set两个list。后来我就想干脆就在login表上进行分解操作吧，将原表分解为devices和users。
sql代码如下:

insert into devices(code) select distinct(code) from login where code like 'TKMB%';
insert into users(username) select distinct(username) from login where code like 'TKMB%';

然后更新原表就出了大问题。

原表更新

第一种方案：

Database changed
mysql> insert into my_login_table (user_id, device_id, applogintime)
    -> (select tmp_table.user_id, devices.id, tmp_table.applogintime
    -> from (select users.id as user_id, login.applogintime as applogintime, login.code as code from users, login where users.username = login.username and login.code like 'TKMB%') as tmp_table, devices
    ->     where devices.code = tmp_table.code);
Query OK, 584591 rows affected (2 hours 21 min 12.56 sec)
Records: 584591  Duplicates: 0  Warnings: 0

好家伙，两个小时，再看看效果

select count(*) from my_login_table;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    27
Current database: ptest

+----------+
| count(*) |
+----------+
|  1169182 |
+----------+
1 row in set (0.29 sec)

多了一倍的数据量。有问题有问题。
这时候我想是不是应该建立索引并采用连接？
果断尝试：

mysql> alter table devices add index unique(code);
ERROR 1064 (42000): 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 'unique(code)' at line 1
mysql> alter table devices add unique devices_index(code);
Query OK, 0 rows affected (0.43 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table users add unique users_index(username);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> delete * from my_login_table;
ERROR 1064 (42000): 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 '* from my_login_table' at line 1
mysql> delete from my_login_table;
Query OK, 1169182 rows affected (4.10 sec)

mysql> show index from devices;
+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| devices |          0 | PRIMARY       |            1 | id          | A         |         412 |     NULL | NULL   |      | BTREE      |         |               |
| devices |          0 | devices_index |            1 | code        | A         |         412 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> insert into my_login_table (user_id, device_id, applogintime)
    -> (select tmp_table.user_id, devices.id, tmp_table.applogintime
    -> from (select users.id as user_id, login.applogintime as applogintime, login.code as code from users inner join login
    ->     on users.username = login.username) as tmp_table inner join devices
    ->     on devices.code = tmp_table.code);
Query OK, 584591 rows affected (2.93 sec)
Records: 584591  Duplicates: 0  Warnings: 0

3秒，完胜！！！

原因分析

内连接的中间过程数据规模不是两张表的笛卡尔乘积；而多表where查询采用两张表的笛卡尔积，在where的时候才过滤数据。