Mysql查询例1:分类统计

Mysql查询例1

（源自：杨志刚 博客 老杨博客 http://yangzhigang.cublog.cn）

 

本文仅仅是在实际工作中需求(分析WMS日志)的一个简化例子。

 

// 数据库（log），表(table1)，表数据为：

mysql> select * from log.table1;
+----------------+-------------+------------+
| c_ip           | cs_uri_stem | c_playerid |
+----------------+-------------+------------+
| 192.168.12.100 | a.wmv       | A          |
| 192.168.12.100 | a.wmv       | A          |
| 192.168.12.100 | a.wmv       | A          |
| 192.168.12.100 | b.wmv       | B          |
| 192.168.12.111 | b.wmv       | C          |
| 192.168.12.111 | b.wmv       | D          |
+----------------+-------------+------------+

表描述：

c_ip:代表浏览视频的客户机IP地址；

cs_uri_stem:代表用户所看视频的文件名；

c_playerid:代表用户看视频所产生的ID号（同一IP看同一文件的ID可能相同，同一用户看不同视频的ID不可能相同，不同用户看不同视频ID也不可能相同；

//需求：统计cs_uri_stem有几种数据可能，每种可能值c_playerid有值的个数。
（预期：有a.wmv和b.wmv两种值，a.wmv的c_playerid有一个值[A]，b.wmv的c_playerid有三个值[B,C,D]）

mysql> select cs_uri_stem,count(distinct c_playerid) from log.table1 group by cs_uri_stem;
+-------------+----------------------------+
| cs_uri_stem | count(distinct c_playerid) |
+-------------+----------------------------+
| a.wmv       |                          1 |
| b.wmv       |                          3 |
+-------------+----------------------------+
2 rows in set (0.00 sec)

 

按个数由多到少排序

mysql> select cs_uri_stem,count(distinct c_playerid) from log.table1 group by cs_uri_stem order by count(distinct c_playerid) desc;
+-------------+----------------------------+
| cs_uri_stem | count(distinct c_playerid) |
+-------------+----------------------------+
| b.wmv       |                          3 |
| a.wmv       |                          1 |
+-------------+----------------------------+
2 rows in set (0.00 sec)

 

简化表格，并取最大值

mysql> select cs_uri_stem,count(distinct c_playerid) as playeridnum from log.table1 group by cs_uri_stem order by  playeridnum desc limit 1;
+-------------+-------------+
| cs_uri_stem | playeridnum |
+-------------+-------------+
| b.wmv       |           3 |
+-------------+-------------+
1 row in set (0.00 sec)

 

同样可以统计一下每个WMV文件被多少个用户(c_ip)浏览过。

mysql> select cs_uri_stem,count(distinct c_ip) as c_ip_num from log.table1 group by cs_uri_stem order by c_ip_num desc ;
+-------------+----------+
| cs_uri_stem | c_ip_num |
+-------------+----------+
| b.wmv       |        2 |
| a.wmv       |        1 |
+-------------+----------+
2 rows in set (0.00 sec)

 

对视频文件(cs_uri_stem)分类统计，查出每个视频文件被播放的次数以及每个视频被多少个用户浏览过，显示在一张表中。

mysql> select cs_uri_stem,count(distinct c_playerid) as playeridnum,count(distinct c_ip) as c_ip_num from log.table1 group by cs_uri_stem order by c_ip_num desc ;
+-------------+-------------+----------+
| cs_uri_stem | playeridnum | c_ip_num |
+-------------+-------------+----------+
| b.wmv       |           3 |        2 |
| a.wmv       |           1 |        1 |
+-------------+-------------+----------+
2 rows in set (0.00 sec)

 

结果显示b.wmv的播放次大于播放人数，说明有人多次看过此视频。