Mysql查询例1:分类统计

时间：2010-03-17 来源：yangzhigang

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的播放次大于播放人数，说明有人多次看过此视频。