Aggregating weekly data in N1QL? In MySQL I would use YEARWEEK()

SQL++
,
1

How do I generate a weekly aggregates using N1QL?

The result I am looking for:

count || date
100 || 2020-11-02
300 || 2020-11-09
400 || 2020-11-16

In MySQL I would use YEARWEEK() to come up with the results above, but in N1QL I am lost because I have been looking at the documentations and looking through various forums but I never found the answer.

I am hoping there’s a way in N1QL because I’m trying to avoid using VIEW to make it simple for my team.

2 
SELECT COUNT(1) AS count,
                  DATE_PART_STR(d.date,"week")  AS week
FROM default AS d
WHERE ........
GROUP BY DATE_PART_STR(d.date,"week") ;

It gives 0 to 52 (52 week partial). Also checkout iso_week.

Check out https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/datefun.html

  SELECT COUNT(1) AS count, dt
    FROM default AS d
    LET dt = DATE_ADD_STR(d.date, 
{"Sunday":0,"Monday":-1,"Tuesday":-2,"Wednesday":-3,"Thursday":-4,"Friday":-5,"Saturday":-6}.[WEEKDAY_STR(d.date)],"day")
    WHERE ........
    GROUP BY dt ;

Track MB-37371

1 Like
3

Thank you for the answer! For those who uses the date format 8601 ISO UTC (like my case) the code would be changed to:

SELECT COUNT(1) AS count, dt
    FROM default AS d
    LET dt = DATE_ADD_STR(SUBSTR(d.date, 0, 10), 
{"Sunday":0,"Monday":-1,"Tuesday":-2,"Wednesday":-3,"Thursday":-4,"Friday":-5,"Saturday":-6}.[WEEKDAY_STR(SUBSTR(d.date, 0, 10))],"day")
    WHERE ........
    GROUP BY dt ;