For table Members:
+----------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+-------+
| S_Date | date | YES | | NULL | |
| memberID | varchar(5) | YES | | NULL | |
+----------+------------+------+-----+---------+-------+
I need to find the distribution of days active within a week and Create a table to show how many members are active for 1 day, 2days, 3days,…7days during 3/1-3/7.
#Days Active Count
1
2
3
4
5
I have tried bitwise operations and count operations but nothing works
if you want to count Days Active you can use DATEDIFF function to calculate the date diff days.then use COUNT and group by to get members are active.
I guess you can follow this query.
SET @StartDate := '2017-03-01';
SET @EndDate := '2017-03-07';
SELECT DATEDIFF(S_Date,@StartDate) + 1 'Days Active',
COUNT(1) 'Count'
FROM Members
WHERE S_Date BETWEEN @StartDate AND @EndDate
GROUP BY S_Date
sqlfiddle:http://sqlfiddle.com/#!9/aa6ea3/1
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加