@vsr1 @geraldss
the out put data looks like this:
[[0,0,0],[0,1,0],[0,2,0],[0,3,0],[0,4,0],[0,5,0],[0,6,0],[0,7,0],[0,8,0],[0,9,1],[0,10,3],[0,11,5],[0,12,4],[0,13,7],[0,14,1],[0,15,2],[0,16,3],[0,17,4],[0,18,3],[0,19,1],[0,20,1],[0,21,1],[0,22,0],[0,23,0],[1,0,0],[1,1,0],[1,2,0],[1,3,0],[1,4,0],[1,5,0],[1,6,0],[1,7,1],[1,8,0],[1,9,1],[1,10,1],[1,11,9],[1,12,5],[1,13,6],[1,14,6],[1,15,6],[1,16,10],[1,17,1],[1,18,0],[1,19,6],[1,20,1],[1,21,2],[1,22,0],[1,23,0],[2,0,0],[2,1,0],[2,2,0],[2,3,0],[2,4,0],[2,5,0],[2,6,0],[2,7,1],[2,8,0],[2,9,1],[2,10,3],[2,11,5],[2,12,3],[2,13,9],[2,14,5],[2,15,5],[2,16,11],[2,17,1],[2,18,0],[2,19,3],[2,20,1],[2,21,0],[2,22,0],[2,23,0],[3,0,1],[3,1,0],[3,2,0],[3,3,0],[3,4,0],[3,5,0],[3,6,0],[3,7,0],[3,8,0],[3,9,4],[3,10,2],[3,11,7],[3,12,10],[3,13,1],[3,14,13],[3,15,8],[3,16,7],[3,17,9],[3,18,1],[3,19,2],[3,20,0],[3,21,0],[3,22,0],[3,23,0],[4,0,0],[4,1,0],[4,2,0],[4,3,0],[4,4,0],[4,5,0],[4,6,0],[4,7,1],[4,8,2],[4,9,0],[4,10,4],[4,11,3],[4,12,6],[4,13,5],[4,14,7],[4,15,8],[4,16,5],[4,17,6],[4,18,4],[4,19,3],[4,20,1],[4,21,0],[4,22,0],[4,23,0],[5,0,0],[5,1,0],[5,2,0],[5,3,0],[5,4,0],[5,5,0],[5,6,0],[5,7,1],[5,8,1],[5,9,0],[5,10,6],[5,11,4],[5,12,8],[5,13,3],[5,14,9],[5,15,2],[5,16,5],[5,17,2],[5,18,1],[5,19,0],[5,20,8],[5,21,0],[5,22,0],[5,23,0],[6,0,0],[6,1,0],[6,2,0],[6,3,0],[6,4,0],[6,5,0],[6,6,0],[6,7,0],[6,8,0],[6,9,0],[6,10,1],[6,11,2],[6,12,0],[6,13,0],[6,14,0],[6,15,0],[6,16,4],[6,17,2],[6,18,5],[6,19,1],[6,20,0],[6,21,1],[6,22,0],[6,23,0]]
collection is a multi-dimensional array where
the first index is day of the week,
the second index is the hour of the day ,
and the third index is number of contributions:
the default value is 0 if the time has not contributions record.
the input data is the record of contributions with created_at
field, the value is looks like
2017-02-02 09:54:34 UTC
now I have get the following data
[
{
"res": [
5,
6,
2
]
},
{
"res": [
7,
6,
1
]
},
{
"res": [
19,
6,
1
]
},
{
"res": [
20,
6,
1
]
},
{
"res": [
9,
5,
1
]
},
{
"res": [
11,
5,
4
]
},
{
"res": [
17,
5,
1
]
},
{
"res": [
3,
4,
1
]
},
{
"res": [
19,
4,
1
]
},
{
"res": [
21,
4,
1
]
},
{
"res": [
2,
3,
1
]
},
{
"res": [
3,
3,
3
]
},
{
"res": [
4,
3,
1
]
},
{
"res": [
8,
3,
1
]
},
{
"res": [
9,
3,
1
]
},
{
"res": [
14,
3,
1
]
},
{
"res": [
20,
2,
1
]
},
{
"res": [
4,
1,
1
]
},
{
"res": [
6,
1,
2
]
},
{
"res": [
7,
1,
2
]
},
{
"res": [
8,
1,
1
]
},
{
"res": [
9,
1,
6
]
},
{
"res": [
10,
1,
1
]
},
{
"res": [
19,
1,
1
]
}
]
by this N1QL
SELECT [hour,(0 + 7) - dday,COUNT(1)] AS res
FROM default
LET dday = DATE_DIFF_STR(STR_TO_TZ("2017-04-09T00:00:00.000Z","Asia/Shanghai"), STR_TO_TZ(STR_TO_UTC(SUBSTR(created_at,0,19)), "Asia/Shanghai"), 'day') ,
hour = DATE_PART_STR(STR_TO_TZ(STR_TO_UTC(SUBSTR(created_at,0,19)), "Asia/Shanghai"),'hour')
WHERE dday < 7 AND dday > -1
GROUP BY dday,hour
ORDER BY dday,hour
where 0 is the weekday (Sunday).
any suggestion?