N1QL pivot data

SQL++
1

Hi,

I have a requirement to create some reports that will represent hourly data. Want to represent count of orders in the following format:

    hour ---> 
Date|1  |2  |3  |4...
4/1 |125|150|100|175 ...
4/2 |125|150|100|175 ...

As of now, I am able to write a query with group by and date_part_millis to generate the required data in the below format:

dt	            |hr	|order count
"2021-03-28"	|0	|1
"2021-03-28"	|1	|34
"2021-03-28"	|2	|65

How do i go about pivoting the hr field to make it a column instead of a row. In SQL we have Pivot functions. Is there an equivalent in N1QL? Wondering if window functions and Over Partition By is the answer. Any samples?

Here’s my current query:

SELECT COUNT(1) AS `order count`, dt, hr
FROM orderV1
LET dt = MILLIS_TO_STR(received_date_time,"1111-11-11"),
    hr = DATE_PART_MILLIS(received_date_time, "hour")
WHERE dt BETWEEN "2021-03-28" AND "2021-03-29"
GROUP BY dt, hr
ORDER BY dt, hr;

Btw, I am running this in Analytics node not N1QL but either one works for now…

2 
   SELECT d.dt, (OBJECT TO_STR(v.hr):v.cnt FOR v IN arr END).*
    FROM (SELECT COUNT(1) AS cnt, dt, hr
                 FROM orderV1
                 LET dt = MILLIS_TO_STR(received_date_time,"1111-11-11"),
                          hr = DATE_PART_MILLIS(received_date_time, "hour")
                WHERE dt BETWEEN "2021-03-28" AND "2021-03-29"
                GROUP BY dt, hr) AS d
    GROUP BY d.dt
    LETTING arr = ARRAY_AGG({d.hr, d.cnt});

If you need to generate 0 for missing date checkout Analytics N1QL Query: GROUP BY `dates` base on the given date values

3

Analytics does not currently support OBJECT-FOR-IN-END expression. If you’re using Analytics then you could do something like this:

SELECT dt, 
  IF_MISSING( (SELECT VALUE cnt FROM cnts WHERE hr = 0)[0], 0) AS `0`,
  IF_MISSING( (SELECT VALUE cnt FROM cnts WHERE hr = 1)[0], 0) AS `1`,
  /* copy above for hr = 2, 3, 4, ... 22 */
  IF_MISSING( (SELECT VALUE cnt FROM cnts WHERE hr = 23)[0], 0) AS `23`
FROM orderV1
LET dt = MILLIS_TO_STR(received_date_time,"1111-11-11"),
    hr = DATE_PART_MILLIS(received_date_time, "hour")
GROUP BY dt GROUP AS g
LET cnts = (SELECT hr, COUNT(*) cnt FROM g GROUP BY hr)
ORDER BY dt
4

Thanks, this is awesome. I wish n1ql and analytics will be in sync one day when it comes to using the same queries in both services.