group_concat

2019独角兽企业重金招聘Python工程师标准>>>

有两个表:

一个用户表

select * from users;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | Alfred    |
|  2 | Bob       |
|  3 | Christine |
|  4 | Dan       |
|  5 | Eddie     |
+----+-----------+

一个订单表

mysql> select * from orders;
+----+---------+---------------------+
| id | user_id | created_at          |
+----+---------+---------------------+
|  1 |       1 | 2018-06-01 13:00:00 |
|  2 |       1 | 2018-06-02 11:00:00 |
|  3 |       2 | 2018-06-02 17:00:00 |
|  4 |       3 | 2018-06-02 18:00:00 |
|  5 |       4 | 2018-06-03 09:00:00 |
|  6 |       5 | 2018-06-03 09:00:00 |
+----+---------+---------------------+

现在要设计一条SQL语句，用来统计每天首次下单的用户数量，查询结果如下:

+------------+-------+
| date       | count |
+------------+-------+
| 2018-06-01 |     1 |
| 2018-06-02 |     2 |
| 2018-06-03 |     2 |
+------------+-------+

第一反应是先按日期分组，再count一下组内的记录条数，如下:

select date(o.created_at) as date, count(distinct o.user_id) as count from orders as o group by date(o.created_at);

结果如下:

+------------+-------+
| date       | count |
+------------+-------+
| 2018-06-01 |     1 |
| 2018-06-02 |     3 |
| 2018-06-03 |     2 |
+------------+-------+

发现2018-06-02这天的计数，比结果多了1，这是因为user_id为1的这个用户在06-01和06-02都下了订单，首次下单只能算在06-01，不能算在06-02这天，所以关键是去重。

改进:

select date(tmp.date) as date, count(distinct tmp.user_id) as count from (select distinct user_id, date(orders.created_at) as date from orders) as tmp  group by tmp.date order by tmp.date asc;

这里用一个子查询，先从order表里面进行distinct操作，再进行count操作。然而结果依然是:

+------------+-------+
| date       | count |
+------------+-------+
| 2018-06-01 |     1 |
| 2018-06-02 |     3 |
| 2018-06-03 |     2 |
+------------+-------+

原因是子查询中distinct语句会将后面的字段全部连起来，加上了日期，那么每一条记录都是distinct的了，所以把全部记录都查出来了，并没有起到去重的目的。而如果单独distinct user_id的话，又达不到统计的效果。

 

最后，用了group_concat函数，这个函数是将相同的记录的某个字段的值拼接起来，当然，拼接的时候是可以带上顺序的。这样，很多条记录就合并成一条记录。然后再用substring_index函数，从这条记录的字段中，取出想要的结果。

select date(tmp.m_date) as date, count(distinct tmp.user_id) as count from (select user_id,substring_index(group_concat(created_at order by created_at),',',1) as m_date from orders group by user_id) as tmp group by date asc;

结果：

+------------+-------+
| date       | count |
+------------+-------+
| 2018-06-01 |     1 |
| 2018-06-02 |     2 |
| 2018-06-03 |     2 |
+------------+-------+

达到结果。

 

这种语句在做统计工作的时候非常好用。

转载于:https://my.oschina.net/u/3412738/blog/2872194