1.6.5 BIT_COUNT和BIT_OR
下面的示例演示如何使用位组函数计算用户访问Web页面的每个月的天数:
CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,
day INT(2) UNSIGNED ZEROFILL);
INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),
(2000,2,23),(2000,2,23);
示例表包含用户访问页面的年月日值。要确定每个月发生的访问次数,请使用此查询:
mysql> SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
-> GROUP BY year,month;
+------+-------+------+
| year | month | days |
+------+-------+------+
| 2000 | 01 | 3 |
| 2000 | 02 | 2 |
+------+-------+------+
2 rows in set (0.00 sec)
这里使用 BIT_COUNT 和 BIT_OR,非常优雅的解决了这个问题。我们分析下这个过程。
我们先看下面的语句,先忽略掉 GROUP BY year, month
mysql> SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1;
+------+-------+------+
| year | month | days |
+------+-------+------+
| 2000 | 01 | 5 |
+------+-------+------+
1 row in set (0.00 sec)
BIT_COUNT( expr ):返回 expr 的二进制表达式中”1“的个数。
BIT_OR( expr ):返回 expr 中所有比特的位 OR 操作。
mysql> select year,month, day, 1<<day FROM t1;
+------+-------+------+------------+
| year | month | day | 1<<day |
+------+-------+------+------------+
| 2000 | 01 | 01 | 2 | 二进制 10 = 1 << 01
| 2000 | 01 | 20 | 1048576 | 二进制 100000000000000000000 = 1 << 20
| 2000 | 01 | 30 | 1073741824 | 二进制 1000000000000000000000000000000 = 1 << 30
| 2000 | 02 | 02 | 4 | 二进制 100 = 1 << 02
| 2000 | 02 | 23 | 8388608 | 二进制 100000000000000000000000 = 1 << 23
| 2000 | 02 | 23 | 8388608 | 二进制 100000000000000000000000 = 1 << 23
+------+-------+------+------------+
6 rows in set (0.00 sec)
使用 BIT_OR 对所有结果进行 OR 操作。
mysql> select year,month, day, BIT_OR(1<<day) FROM t1;
+------+-------+------+----------------+
| year | month | day | BIT_OR(1<<day) |
+------+-------+------+----------------+
| 2000 | 01 | 01 | 1083179014 | 二进制 100 0000 1001 0000 0000 0000 0000 0110
+------+-------+------+----------------+
1 row in set (0.00 sec)
使用 BIT_COUNT 统计返回表达式中 “1” 的个数。
mysql> select year,month, day, BIT_COUNT(BIT_OR(1<<day)) FROM t1;
+------+-------+------+---------------------------+
| year | month | day | BIT_COUNT(BIT_OR(1<<day)) |
+------+-------+------+---------------------------+
| 2000 | 01 | 01 | 5 | 表达式中出现了5个 “1”
+------+-------+------+---------------------------+
1 row in set (0.00 sec)
如果语句中有 GROUP BY ,则在 GROUP BY 簇中,进行了如上的操作。