mysql 分类汇总_Mysql查询例1:分类统计

Mysql查询例1

本文仅仅是在实际工作中需求(分析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的播放次大于播放人数，说明有人多次看过此视频。