How to get Punch Card Data from contributions date?

@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?

Try this.

SELECT RAW ARRAY_AGG([hour,(0 + 7) - dday, cnt]) 
   FROM (SELECT dday, hour, COUNT(1) cnt
      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) AS d;
1 Like

I get the following error:

[
  {
    "code": 5020,
    "msg": "Error updating initial GROUP value. - cause: Aggregate count(1) not found."
  }
]

and , I also need this:
the default value is 0 if the time has not contributions record.

Updated previous post. need more clarification on default value is 0 if the time has not contributions record.

for example, I don’t have a record at 0:00, 1:00,2:00,3:00 on Sunday ,the output data should include

[0,0,0],[0,1,0],[0,2,0],[0,3,0]

with value 0

and the length of output array is always 7*24 = 168

Let me think.
Can u use DATE_PART_STR() the following gives day of week
"dow" gives 0-6 Sunday as 0
"iso_dow" gives 1-7 Sunday as 7

select DATE_PART_STR(now_str(),“dow”);

https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/datefun.html#story-h2-15

1 Like

it works for getting week as following N1QL.

SELECT RAW ARRAY_AGG([hour, week, cnt]) 
  FROM (
        SELECT hour,DATE_PART_STR(day,"dow") AS week ,COUNT(1) AS cnt
          FROM default 
          LET  day = STR_TO_TZ(STR_TO_UTC(SUBSTR(created_at,0,19)), "Asia/Shanghai"),
               hour = DATE_PART_STR(STR_TO_TZ(STR_TO_UTC(SUBSTR(created_at,0,19)), "Asia/Shanghai"),'hour')
         WHERE DATE_DIFF_STR(STR_TO_TZ("2017-04-09T00:00:00.000Z","Asia/Shanghai"), day, 'day') < 7
           AND DATE_DIFF_STR(STR_TO_TZ("2017-04-09T00:00:00.000Z","Asia/Shanghai"), day, 'day') > -1
         GROUP BY DATE_PART_STR(day,"dow"),hour
      ) AS d;

Second arm of UNION ALL i have default array only 3 elements. You need to extend that 168 elements. After that you can post final query if it works.

SELECT RAW ARRAY_AGG([hour, week, cnt])
  FROM (
        SELECT week, hour, SUM(cnt) AS cnt
        FROM (
                SELECT hour,DATE_PART_STR(day,"dow") AS week, 1 AS cnt
                FROM default
                LET  day = STR_TO_TZ(STR_TO_UTC(SUBSTR(created_at,0,19)), "Asia/Shanghai"),
                     hour = DATE_PART_STR(STR_TO_TZ(STR_TO_UTC(SUBSTR(created_at,0,19)), "Asia/Shanghai"),'hour')
                WHERE DATE_DIFF_STR(STR_TO_TZ("2017-04-09T00:00:00.000Z","Asia/Shanghai"), day, 'day') < 7
                 AND DATE_DIFF_STR(STR_TO_TZ("2017-04-09T00:00:00.000Z","Asia/Shanghai"), day, 'day') > -1
             UNION ALL
                 SELECT RAW {"hour":d1[0], "week":d1[1], "cnt":d1[2]} FROM (SELECT RAW [[0,0,0],[0,1,0],[0,2,0]]) AS d2 UNNEST d2 AS d1
             ) AS d
         GROUP BY week,hour
      ) AS d;
1 Like

it is cool.
can we get 168 elements by ARRAY_RANGE function?

SELECT RAW ARRAY_AGG([hour, week, cnt])
  FROM (
        SELECT week, hour, SUM(cnt) AS cnt
        FROM (
                SELECT hour,DATE_PART_STR(day,"dow") AS week, 1 AS cnt
                FROM default
                LET  day = STR_TO_TZ(STR_TO_UTC(SUBSTR(created_at,0,19)), "Asia/Shanghai"),
                     hour = DATE_PART_STR(STR_TO_TZ(STR_TO_UTC(SUBSTR(created_at,0,19)), "Asia/Shanghai"),'hour')
                WHERE DATE_DIFF_STR(STR_TO_TZ("2017-04-09T00:00:00.000Z","Asia/Shanghai"), day, 'day') < 7
                 AND DATE_DIFF_STR(STR_TO_TZ("2017-04-09T00:00:00.000Z","Asia/Shanghai"), day, 'day') > -1
             UNION ALL
                SELECT RAW d1 FROM (SELECT RAW ARRAY_FLATTEN(ARRAY (ARRAY {"hour":h, "week":w, "cnt":0} FOR h IN ARRAY_RANGE(0,24) END)  FOR w IN ARRAY_RANGE(0,7) END,1)) AS d2 UNNEST d2 AS d1
             ) AS d
         GROUP BY week,hour
      ) AS d;
1 Like

Thank you very much.it is wonderful!