Spec-Zone .ru
спецификации, руководства, описания, API

3.6.8. Calculating Visits Per Day

The following example shows how you can use the bit group functions to calculate the number of days per month a user has visited a Web page.

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);

The example table contains year-month-day values representing visits by users to the page. To determine how many different days in each month these visits occur, use this query:

SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1       GROUP BY year,month;

Which returns:

+------+-------+------+| year | month | days |+------+-------+------+| 2000 |    01 |    3 || 2000 |    02 |    2 |+------+-------+------+

The query calculates how many different days appear in the table for each year/month combination, with automatic removal of duplicate entries.