vvivekiyer commented on issue #10318: URL: https://github.com/apache/pinot/issues/10318#issuecomment-1449070682
**POSTGRES: Numeric DataType and AVG Return Type** Postgres supports some variable size datatypes (where the number of digits before and after decimal point is fixed and can be controlled by the user) like NUMERIC and DECIMAL. These are typically used for financial data. The precision and scale values determine the number of digits before/after the decimal. For AVG aggregation function, Postgres has the following return type: > avg ( smallint ) → numeric > avg ( integer ) → numeric > avg ( bigint ) → numeric > avg ( numeric ) → numeric > avg ( real ) → double precision > avg ( double precision ) → double precision > avg ( interval ) → interval For the cases where Postgres returns a numeric datatype (for all integer types above), the precision and scale of the result value could be different from the input values. It is calculated based on the input values as follows: > precision = max(precision(input_values) + ceil(log10(count(input_values))), scale(input_values) + decimal_places) **Pinot: AVG Return Type** - I think the closest support we have to variable sized data-types is BIG_DECIMAL. Based on my reading, BIG_DECIMAL can support larger than DOUBLE values, I don't think it can handle exact PRECISION and SCALE during storage. Precision and Scale support is added through specific aggregation functions like SUMPRECISION, etc - Additionally, during the planning stage, we do not have access to the column values. So, we can't calculate precision/scale values like Postgres does for NUMERIC datatype. Given these 2 limitations above, I'm wondering if it's possible to mimic Postgres behavior. Please let me know if I'm missing something @walterddr @somandal At the very least, I think we need to change AVG return type to at least handle BIG_DECIMAL instead of returning a blanket double for everything. > INT, LONG, FLOAT, DOUBLE -> return DOUBLE > BIG_DECIMAL -> return BIG_DECIMAL -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@pinot.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@pinot.apache.org For additional commands, e-mail: commits-h...@pinot.apache.org
