计算每天访问次数

5.7官方文档 300页

---

章节名：3.6.8 Calculating Visits Per Day

BIT_COUNT()函数 与 BIT_OR()函数

1. 文档原题

• 计算每天访问次数

CREATE TABLE t1 (year YEAR, month INT UNSIGNED,day INT UNSIGNED);
INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),
(2000,2,2),(2000,2,23),(2000,2,23);
SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1 GROUP BY year,month;

• 运行效果

mysql> SELECT * FROM t1;
+------+-------+------+
| year | month | day  |
+------+-------+------+
| 2000 |     1 |    1 |
| 2000 |     1 |   20 |
| 2000 |     1 |   30 |
| 2000 |     2 |    2 |
| 2000 |     2 |   23 |
| 2000 |     2 |   23 |
+------+-------+------+
6 rows in set (0.00 sec)

mysql> SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1 GROUP BY year,month;
+------+-------+------+
| year | month | days |
+------+-------+------+
| 2000 |     1 |    3 |
| 2000 |     2 |    2 |
+------+-------+------+
2 rows in set (0.00 sec)

• 换一种解决办法

mysql> SELECT year,month,COUNT(DISTINCT day) AS day FROM t1 GROUP BY year,month;
+------+-------+-----+
| year | month | day |
+------+-------+-----+
| 2000 |     1 |   3 |
| 2000 |     2 |   2 |
+------+-------+-----+
2 rows in set (0.00 sec)

• 关于BIT_COUNT()函数 与 BIT_OR()函数说明
• 引用自：https://www.cnblogs.com/yuyangDataAnalysis/archive/2011/07/09/2101748.html

1、BIT_COUNT( expr )：返回 expr 的二进制表达式中”1“的个数。

     例如：29 = 11101 则：BIT_COUNT（29）= 4；

2、BIT_OR( expr )：返回 expr 中所有比特的bitwise OR。
    计算执行的精确度为64比特(BIGINT) 。
　  例如：上面例子中，2000年02月中有一条2号的记录两条23号的记录，所以"1<<day"表示出来就是 “1＜＜2”和“1＜＜23”，得到二进制数 100 和 100000000000000000000000 。
　  然后再OR运算。即 100 OR 10000000000000000000000 OR 10000000000000000000000 = 100000000000000000000100；
　  这样再用BIT_COUNT处理得出的值就是2，自动去除了重复的日期。

• 额外验证
• 数字格式超过范围后，会失效

mysql> select * from t1;
+------+-------+--------+
| year | month | day    |
+------+-------+--------+
| 2000 |     1 |      1 |
| 2000 |     1 |     20 |
| 2000 |     1 |     30 |
| 2000 |     2 |      2 |
| 2000 |     2 |     23 |
| 2000 |     2 |     23 |
| 2003 |     2 |     22 |
| 2003 |     2 |     22 |
| 2003 |     2 |     22 |
| 2003 |     2 |     22 |
| 2003 |     2 | 444444 |
| 2003 |     2 | 444444 |
| 2003 |     2 | 444444 |
| 2003 |     2 | 444444 |
| 2003 |     2 | 444444 |
| 2003 |     2 | 444444 |
+------+-------+--------+
16 rows in set (0.00 sec)

mysql> SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1 GROUP BY year,month;
+------+-------+------+
| year | month | days |
+------+-------+------+
| 2000 |     1 |    3 |
| 2000 |     2 |    2 |
| 2003 |     2 |    1 |
+------+-------+------+
3 rows in set (0.00 sec)

mysql> SELECT year,month,COUNT(DISTINCT day) AS day FROM t1 GROUP BY year,month;
+------+-------+-----+
| year | month | day |
+------+-------+-----+
| 2000 |     1 |   3 |
| 2000 |     2 |   2 |
| 2003 |     2 |   2 |
+------+-------+-----+
3 rows in set (0.00 sec)