N1QL to return Current Year and Previous Year on same Document

SQL++
1

Hi Team
I have N1QL query like below and my predicates are PatternDate in Subquery . This query returns funcroomrevcurrentyear but I want to return funcroomrevlastyear in same SELECT within Same document which should return the data for ‘2018-12-01’ AND ‘2018-12-31’ .
Any ability to do that in N1QL easily ? I just don’t want to have two documents .

SELECT
SUM(M.BlendedEventRevenueTotal) funcroomrevcurrentyear
FROM(
SELECT MAX(A.SnapshotDate) SnapshotDate,
A.ExternalBookingId ExternalBookingId,
A.PatternDate
FROM group360all A
WHERE A.PatternDate BETWEEN ‘2019-12-01’ AND ‘2019-12-31’
GROUP BY A.PatternDate, A.ExternalBookingId
) T
INNER JOIN group360all M
ON M.ExternalBookingId = T.ExternalBookingId
AND M.SnapshotDate = T.SnapshotDate
AND T.PatternDate = M.PatternDate

2 
SELECT (OBJECT TO_STRING(v.year) : v.total FOR v IN  result END).*
LET result = ( SELECT DATE_PART_STR(T.PatternDate, "year") AS year,
               SUM(M.BlendedEventRevenueTotal) total
                FROM ( SELECT MAX(A.SnapshotDate) AS SnapshotDate,
                             A.ExternalBookingId AS ExternalBookingId,
                             A.PatternDate
                       FROM group360all AS A
                       WHERE (A.PatternDate BETWEEN "2019-12-01" AND "2019-12-31")
                            OR (A.PatternDate BETWEEN "2018-12-01" AND "2018-12-31")
                       GROUP BY A.PatternDate, A.ExternalBookingId
                    ) T
                INNER JOIN group360all M ON M.ExternalBookingId = T.ExternalBookingId
                                            AND M.SnapshotDate = T.SnapshotDate
                                            AND T.PatternDate = M.PatternDate
                GROUP BY DATE_PART_STR(T.PatternDate, "year"));
1 Like
3

Thank you for prompt response… I got “msg”: “syntax error - at SUM”, so assume I need , before SUM in first SELECT …Still have below error : “msg”: “syntax error - at ;” .
Again my idea is to have funcroomrevcurrentyear , funcroomrevlastyear metrics returns as different column in same row in single document. I guess I can’t see that unless query syntax corrected …

Also I afraid of putting the OR in last year clause because it might return wrong results because of MAX(SnapshotDate) which could come as wrong . Not sure yet but guessing …

4

Tray again. You have GROUP BY PatternDate, So it will be fine
OBJECT … END converts ARRAY of objects into object .
This approach can expandable i.e n rows convert to n columns

Example:

 SELECT (OBJECT v.year: v.total FOR v IN result END).* 
LET result = [{"year":"2018", "total":100}, {"year":"2019", "total":50}];

   {
            "2018": 100,
            "2019": 50
        }

You can also try this

SELECT r1, r2
LET r1 = (SELECT current year)[0],
r2 = (SELECT previous year)[0]
;

1 Like
5

Query works now … But if I execute the query for 2019 Dec month it returns me : 33552 and for 2018 Dec month it returns : 75426
however when I execute modified query with OBJECT it returns ; 2018 correctly : 75426 but 2019 as : 147330 (this is not correct data as per my predicates)

6

correct date in the predicate it has 2019

1 Like
7

However if I comment out : /* OR (A.PatternDate BETWEEN “2018-12-01” AND “2019-12-31”) */"
it is returning 2019 with right data I want … Guessing I can’t do OR

8

change the year 2019 to 2018

9

I see … My bad … Yes it is correct now … Now I have other challenge … I have 20 metrics to be returned , 10 for this year and 10 for last year and the label for this year will be ‘revcurrentyear’ and last year will be ‘revlastyear’ So little confused with v.year … Guessing I need to try out below if this query doesn’t work :
SELECT r1, r2
LET r1 = (SELECT current year)[0],
r2 = (SELECT previous year)[0]
;

10 
SELECT  cyear, pyear
LET cyear = (SELECT SUM(M.BlendedEventRevenueTotal) revenue
                FROM ( SELECT MAX(A.SnapshotDate) AS SnapshotDate,
                             A.ExternalBookingId AS ExternalBookingId,
                             A.PatternDate
                       FROM group360all AS A
                       WHERE (A.PatternDate BETWEEN "2019-12-01" AND "2019-12-31")
                       GROUP BY A.PatternDate, A.ExternalBookingId
                    ) T
                INNER JOIN group360all M ON M.ExternalBookingId = T.ExternalBookingId
                                            AND M.SnapshotDate = T.SnapshotDate
                                            AND T.PatternDate = M.PatternDate)[0],
   pyear = (SELECT SUM(M.BlendedEventRevenueTotal) revenue
                FROM ( SELECT MAX(A.SnapshotDate) AS SnapshotDate,
                             A.ExternalBookingId AS ExternalBookingId,
                             A.PatternDate
                       FROM group360all AS A
                       WHERE (A.PatternDate BETWEEN "2018-12-01" AND "2018-12-31")
                       GROUP BY A.PatternDate, A.ExternalBookingId
                    ) T
                INNER JOIN group360all M ON M.ExternalBookingId = T.ExternalBookingId
                                            AND M.SnapshotDate = T.SnapshotDate
                                            AND T.PatternDate = M.PatternDate)[0]
;

CREATE INDEX ix1 ON group360all(PatternDate, ExternalBookingId, SnapshotDate, BlendedEventRevenueTotal );
1 Like
11

Ohh Yes … I was about to post …but my resulted JSON was coming little weird which I don’t want …Looks like [0] did the trick … Appreciated it … thank you so much …

One last question : I have 2.34 s response time and I have Index on all Join columns plus predicates … Any chance to improved perf … Bucket has very few 6033 documents only

12

CREATE INDEX ix1 ON group360all(PatternDate, ExternalBookingId, SnapshotDate, BlendedEventRevenueTotal );

If using EE version try HASH Join

https://query-tutorial.couchbase.com/tutorial/#55 (you should give inner most subquery)

1 Like
13

Great … ms response time now… thanks for sharing the docs …
I had Primary Key Index but not Composite index …looks like that did the trick … But currently I have few predicates to filter in WHERE and this varies query by query based on user selections … I have 5 more predicates to come do you suggest to have Index like below : again those predicates may or may not apply in WHERE …

CREATE INDEX ix1 ON group360all(PatternDate, ExternalBookingId, SnapshotDate, BlendedEventRevenueTotal,status , segment , region , salesid );

Also I am curious if we need to have BlendedEventRevenueTotal in Index because I have 10 other metrics to be in as well … so like to know the value of adding that

14

Predicate fields at the start , and non predicate fields at the end. read the 3rd link you will have idea.

15

WOW ! I am excited on 6.5 INDEX Advisor … Good to know that beta features… Will be helpful for DBA’s .thanks

16

3rd link is very detailed. Any pointer where should I see ? No issue I will check that in details.
So are you suggesting if I have 5 predicates and 10 no predicates (selected measure) I should create One composite GSI with 15 attributes no matter how dynamically those predicates will apply ?

17

I am on 6.0 EE btw so I can use HASH join as well if you advise how

18

Example 9, 10 https://blog.couchbase.com/ansi-join-support-n1ql/

1 Like
19

Predicates will apply but it will not pass to indexer. It only pass the leading index keys. When they gap it stops. u can add that field with field IS NOT MISSING predicate.

20

Perfect …make sense … I still don’t get it that if I drop the measure this : BlendedEventRevenueTotal from Index whether I will get same perf or bad … Since I am not doing any joins or neither this column is part of predicates I am still confused if this is really needed… I will test it out to drop this and see performance.

to close this thread if somebody wants to get help to have YOY I did below in return : 100 * (r1.cyear -r2.pyear) / r2.pyear As YOY - in your previous query . This should get the YOY return % … Not a great deal I know but that’s what finally I want …
thank you again