N1QL query- every day report

SQL++
1

Hi N1QL World,
I’m not a developer-rather a designer who wants to get some insights.
as sessions run in millions per day, I don’t want to store data unnecesarily.
I want to record some transactions in a new data store, say data store1. this contains daily details of sessions and subscriber msisdn. So store1 looks like this - date-time, sessionid and msisdn.
I want to run a job(job1) every night to derive unique count of sessionid and unique count of msisdn.
I want to store the result to second store- date-time, count(sessionid), count(msisdn).
Then a second job(job2) runs every month to get sum of session id and sum from msisdn.

I’m looking for N1QL efficient query for job1 and job2 for above scenario as today date/calendar month is constantly changing.

Thanks
Ash

2

You can use aggregate queries
STORE datetime in ISO-8601 format https://docs.couchbase.com/server/5.5/n1ql/n1ql-language-reference/datefun.html

CREATE INDEX ix1 ON store1(SUBSTR(datetime,0,10), sessionid, msisdn);

INSERT INTO store2(KEY UUID(), VALUE _doc) 
SELECT  {"day":SUBSTR(datetime,0,10),  "scount":count(sessionid), "mcount":count(msisdn)} AS _doc
FROM store1 AS s
WHERE SUBSTR(datetime,0,10) = "2018-10-01"
GROUP BY SUBSTR(datetime,0,10);

CREATE INDEX ix2 ON store2(day,scount, ncount);

SELECT SUBSTR(day,0,8) AS month, SUM(scount) AS ssum, SUM(mcount) AS m sum

FROM store2 
WHERE day IS NOT NULL
GROUP BY SUBSTR(day,0,8);

Check out https://blog.couchbase.com/understanding-index-grouping-aggregation-couchbase-n1ql-query/