Create index optimize

SQL++
1

pls how to create optimize index for this query

SELECT aggregatedRes.qty, aggregatedRes.amt, aggregatedRes.dat, 
       aggregatedRes.sit, aggregatedRes.off, aggregatedRes.tk, aggregatedRes.datInt, aggregatedRes.crd, aggregatedRes.startDate, aggregatedRes.endDate 
FROM ( SELECT ARRAY_AGG(qty)[0] AS qty, ARRAY_AGG(amt)[0] AS amt, 
              dat, sit, off, tk, datInt, crd, 1672531200000 AS startDate,1689033600000 AS endDate 
       FROM ( SELECT ROUND ( SUM(content.qty), 8) as qty, content.dat, content.sit, 
                     content.off, content.tk, content.datInt, content.crd 
              FROM rxpReport 
              WHERE type = 'aD' AND content.a = 'TDCL' AND content.sD BETWEEN 1672531200000 AND 1689033600000 
              GROUP BY content.dat, content.sit, content.off, content.tk, content.datInt, content.crd 

             UNION 
           
              SELECT ROUND ( SUM(content.amt), 8) as amt, content.dat, content.sit, 
                           content.off, content.tk, content.datInt, content.crd 
             FROM rxpReport WHERE type = 'aD' AND content.a = 'TDCL' AND content.sD BETWEEN 1672531200000 AND 1689033600000 
            GROUP BY content.dat, content.sit, content.off, content.tk, content.datInt, content.crd) AS res 
       GROUP BY res.dat, res.sit, res.off, res.tk, res.datInt, res.crd) as aggregatedRes 
WHERE aggregatedRes.qty IS NOT NULL OR aggregatedRes.amt IS NOT NULL
2 
CREATE INDEX ix1 ON rxpReport(content.a, content.sD, content.dat, content.sit, content.off, content.tk, content.datInt, content.crd, content.amt, content.qty) WHERE type = 'aD';


SELECT content.dat, content.sit, content.off, content.tk, content.datInt, content.crd,
       qty, amt, 1672531200000 AS startDate,1689033600000 AS endDate
FROM rxpReport AS r
WHERE type = 'aD' AND content.a = 'TDCL' AND content.sD BETWEEN 1672531200000 AND 1689033600000
GROUP BY content.dat, content.sit, content.off, content.tk, content.datInt, content.crd
LETTING qty = ROUND ( SUM(content.qty), 8),
        amt = ROUND ( SUM(content.amt), 8)
HAVING qty IS NOT NULL OR amt IS NOT NULL;
1 Like
3

thks for reply so you optimize the query exact? and the ARRAY_AGG in qty and amt???

4

ARRAY_AGG(qty)[0] AS qty, ARRAY_AGG(amt)[0] AS amt,

It is exact. You can verify results.

Why do u need ARRAY_AGG()? My opinion it is not needed (You do UNION each produce sum of qty, amt and then again GROUP of ARRA_AGG()[0], this is same as without UNION do SUM of qty, amt at same time)

1 Like
5

yes it’s same results thks a lot
I’m newbie in couchbase

6

hello @vsr1
if now I have this query
SELECT aggregatedRes.qty, aggregatedRes.amt, aggregatedRes.startDate, aggregatedRes.endDate
FROM ( SELECT ARRAY_AGG(qty)[0] AS qty, ARRAY_AGG(amt)[0] AS amt, 1672876800000 AS startDate,1672963199000 AS endDate
FROM ( SELECT ROUND ( SUM(content.qty), 8) as qty
FROM rxpReport
WHERE type = ‘aD’ AND content.a = ‘FTEB’ AND content.sD BETWEEN 1672876800000 AND 1672963199000
UNION SELECT ROUND ( SUM(content.amt), 8) as amt
FROM rxpReport
WHERE type = ‘aD’ AND content.a = ‘FTEB’ AND content.sD BETWEEN 1672876800000 AND 1672963199000) AS res ) as aggregatedRes
WHERE aggregatedRes.qty IS NOT NULL OR aggregatedRes.amt IS NOT NULL
and want to do as you advise me
SELECT qty, amt, 1672704000000 AS startDate,1672790399000 AS endDate
FROM rxpReport
WHERE type = ‘aD’ AND content.a = ‘FTEB’ AND content.sD BETWEEN 1672704000000 AND 1672790399000
LETTING qty = ROUND ( SUM(content.qty), 8), amt = ROUND ( SUM(content.amt), 8)
HAVING qty IS NOT NULL OR amt IS NOT NULL
as you see it’s not work because missing Group By clause so I do resolve it? thks

7 
SELECT q.*
FROM (SELECT ROUND ( SUM(content.qty), 8) AS qty, ROUND ( SUM(content.amt), 8) AS amt, 1672704000000 AS startDate, 1672790399000 AS endDate
      FROM rxpReport AS r
      WHERE type = 'aD' AND content.a = 'FTEB' AND content.sD BETWEEN 1672704000000 AND 1672790399000) AS q
WHERE  qty IS NOT NULL OR amt IS NOT NULL;
1 Like
8

@vsr1 thks for reply, so it’s not possible to use the LETTING clause for this case? because I want to customize query to generate query from input’s values or Letting clause in for others query optimize time execution so want it for all query

9

HAVING/LETTING only allowed when GROUP BY present. SQL standard will not allow HAVING caluse for NON GROUP aggregates.
Non GROUP aggregate query must return 1 row by having can HAVING will filter that .
If you need uniform query

SELECT d.*
FROM (SELECT content.dat, content.sit, content.off, content.tk, content.datInt, content.crd,
            ROUND ( SUM(content.qty), 8) AS qty, ROUND ( SUM(content.amt), 8) AS amt, 
            1672531200000 AS startDate,1689033600000 AS endDate
      FROM rxpReport AS r
      WHERE type = 'aD' AND content.a = 'TDCL' AND content.sD BETWEEN 1672531200000 AND 1689033600000
      GROUP BY content.dat, content.sit, content.off, content.tk, content.datInt, content.crd) AS d
WHERE qty IS NOT NULL OR amt IS NOT NULL;
1 Like