https://pinot.apache.org/ logo
#general
Title
# general
o

Oliver

09/14/2020, 9:59 AM
Dear all, thank you very much for building Apache Pinot - it is indeed a great tool! One question that came up during our evaluation of Pinot is how to handle year over year (or period over period) comparisons in Pinot (or in the Viz Tooling). How would you typically do this? In SQL, one normally would either use WINDOW functions (like LAG with PARTITION OVER) oder self-JOINS. Any advice is highly appreciated 🙂 THANKS!
🙏 2
👍 1
k

Kenny Bastani

09/14/2020, 1:35 PM
Good question. Yes, there's an easy solution here. Time granularity queries will aggregate your data period to period for comparison. Let me get an example.
Copy code
SELECT EVENT_TYPE,
       DATETIMECONVERT(EPOCH_TIMESTAMP, '1:MILLISECONDS:EPOCH', '1:MILLISECONDS:EPOCH', '1:DAYS'),
       COUNT(*)
FROM "stormEvents"
WHERE EVENT_TYPE IN ('Hail',
                     'Thunderstorm Wind')
GROUP BY EVENT_TYPE,
         DATETIMECONVERT(EPOCH_TIMESTAMP, '1:MILLISECONDS:EPOCH', '1:MILLISECONDS:EPOCH', '1:DAYS')
ORDER BY COUNT(*) DESC
LIMIT 50000;
I used this query for the following chart:
For the period granularity, check out our documentation. For ingestion and query transformations: https://docs.pinot.apache.org/users/user-guide-query/supported-transformations#datetime-functions
o

Oliver

09/14/2020, 1:43 PM
Hi Kenny, thanks, this looks great! one follow-up question: to get out the time series, this feels pretty straight forward 🙂 What about period over period comparison kpis - e.g., I want to report the 2 bold KPIs in a table: Sales 2018: 100m Sales 2019: 150m Sales uplift: +50% Similar for Week over previous year's week. Do you solve this in the VIz tool or directly in SQL?
k

Kenny Bastani

09/14/2020, 1:44 PM
Ah I see.
I use the visualization tool directly. Our SQL support has feature parity for anything that is immutable (read-only) queries.
There are a few different ways to do this with transforms. You can do a granularity ingest transformation that creates a dynamic column for datetime in your rows.
I meant to paste this screenshot from table configuration.
Let me know if you're still struggling, we can jump into a private DM and figure it out.
o

Oliver

09/14/2020, 1:55 PM
Thanks for the offer - maybe one last question here: Where would you do the transformation to divide (150/100-1) ?
k

Kenny Bastani

09/14/2020, 2:06 PM
*DIV(col1, col2)*
o

Oliver

09/14/2020, 2:08 PM
ah, ok got it. With the configuration ^^ both figures would appear in the same row?
Otherwise to get the figures into 1 row, in traditional sql it is a bit more complicated (with self-joins or using window function like lag)
k

Kishore G

09/14/2020, 3:05 PM
Oliver, you need to use post aggregation function to perform what you are looking for. You might want to look at ThirdEye tool as well, it does all the things you are looking for