https://pinot.apache.org/ logo
Join Slack
Powered by
Hi team, I am using pinot for data analytics. Belo...
# general
s
Hi team, I am using pinot for data analytics. Below is a request sample- SELECT date, SUM(revenue) AS Revenue, SUM(cost) AS Cost FROM table WHERE date BETWEEN 1696118400000 AND 1698710399000 GROUP BY date HAVING SUM(cost) > 1000 Here stats i am getting is expected. but i need grand totals also to show on grid. for that i am firing separate query on pinot and doing union of both responses. --Grand total request SELECT SUM(revenue) AS Revenue, SUM(cost) AS Cost FROM table WHERE date BETWEEN 1696118400000 AND 1698710399000 HAVING SUM(cost) > 1000 Expectation is on hitting for grand total, filters(Having clause) should be applied. but its not the case. I understand logically there are constraints. Is there any workaround to show grand totals from pinot with a measure filter in request? I can scan entire data in response 1 and sum it up. but that will be too costly as response can be of 10s of MBs also of each request.
m
This should be possible in v2 engine to sum only the groups Cost > 1000 cc: @Rong R
s
We have v2 engine instance also(Pinot 1.0). I checked response of group by Requests on that. There is no addition info i could find. Can you guide please ?
r
hmm. i am not sure i understand the requirement. the 2 queries listed above has different result schema
s
Hey Rong, Consider a reporting screen where group by DIM wise aggregated measures are being shown and last element of grid is grand totals of request(basically DIM field is blank and aggregations of all measures with applied filters in request) For this we are hitting pinot with 2 diff requests(1 of stats and other for grand total) -----Request 1 SELECT date, SUM(revenue) AS Revenue, SUM(cost) AS Cost FROM table WHERE date BETWEEN 1696118400000 AND 1698710399000 GROUP BY date HAVING SUM(cost) > 1000 --Request 2(for Grand total) SELECT Null As date SUM(revenue) AS Revenue, SUM(cost) AS Cost FROM table WHERE date BETWEEN 1696118400000 AND 1698710399000 HAVING SUM(cost) > 1000 As filters has to be same for these. How to get expected results from request 2 ?
r
what's the columns of this grid?
s
date - DIM Revenue, cost - measures
r
and what would be the last row 's date value?
s
image.png
for ref this kind of response
its a dummy data but last row’s date will be null
r
ok. i understand better now. and what was your requirement? is it to just run 1 pinot query?
or is the performance of both queries are not up to the expectation?
s
i am ok to run 2 queries and union them on API layer. but i am expecting correct results
so for 2nd query of grand total, my having clause is not being respected
i am getting totals same as without any filters
r
oh i see!
and is this from multi-stage or single stage?
s
single stage
r
and is running it in multi-stage result correctly?
s
but on multi stage test env also i tried… results are same
r
interesting. what's the discrepancy you found? was it b/c the 1st query gives you a bunch of rows but the results from the 2nd query is not the same as summing up the 1st query result?
s
so second query gives response summing up the first query without any having clause(expectation is summing up first query with applying having filter) .
r
ok.
so you want a correct query for the 2nd one
yes?
s
yes
r
Copy code
WITH tmp AS (
  SELECT
    date,
    SUM(revenue) AS Revenue,
    SUM(cost) AS Cost
  FROM table
  WHERE date BETWEEN 1696118400000 AND 1698710399000
  GROUP BY date
  HAVING SUM(cost) > 1000
)
SELECT 
  SUM(Revenue) AS Revenue, 
  SUM(Cost) AS Cost
FROM tmp
s
CTEs are supported in single stage ?
r
multi-stage
s
got it
anyway to achieve this result in single stage also ?
r
nope
s
got it, thanks a lot
r
👍
Open in Slack
PreviousNext
Powered by