Problem with percentileCont calculation

chri schneid

unread,

Jan 11, 2024, 8:05:15 AM1/11/24

to jooq...@googlegroups.com

Hi,

I'd like to calculate two 5% percentile from the table below:

WAREHOUSE, ITEM, BEST_BEFORE_DATE, MIN_DELIVERY_DATE, MAX_DELIVERY_DATE, ENTRY_REMAINING_TIME, EXIT_REMAINING_TIME

39 418 2023-09-03 2023-08-14 00:00:00.000 2023-08-18 00:00:00.000 20 16
39 418 2023-09-10 2023-08-19 00:00:00.000 2023-08-24 00:00:00.000 22 17
39 418 2023-09-16 2023-08-25 00:00:00.000 2023-08-26 00:00:00.000 22 21
39 418 2023-09-24 2023-08-31 00:00:00.000 2023-09-08 00:00:00.000 24 16
39 418 2023-09-29 2023-09-09 00:00:00.000 2023-09-09 00:00:00.000 20 20
39 418 2023-10-06 2023-09-14 00:00:00.000 2023-09-21 00:00:00.000 22 15
39 418 2023-10-16 2023-09-23 00:00:00.000 2023-09-23 00:00:00.000 23 23
39 418 2023-10-21 2023-09-23 00:00:00.000 2023-10-06 00:00:00.000 28 15
39 418 2023-11-05 2023-10-13 00:00:00.000 2023-10-16 00:00:00.000 23 20
39 418 2023-11-09 2023-10-18 00:00:00.000 2023-10-24 00:00:00.000 22 16
39 418 2023-11-11 2023-10-24 00:00:00.000 2023-10-25 00:00:00.000 18 17
39 418 2023-11-17 2023-10-28 00:00:00.000 2023-10-30 00:00:00.000 20 18
39 418 2023-11-25 2023-11-02 00:00:00.000 2023-11-11 00:00:00.000 23 14
39 418 2023-12-08 2023-11-15 00:00:00.000 2023-11-15 00:00:00.000 23 23
39 418 2023-12-09 2023-11-17 00:00:00.000 2023-11-23 00:00:00.000 22 16
39 418 2023-12-15 2023-11-23 00:00:00.000 2023-12-07 00:00:00.000 22 8
39 418 2023-12-22 2023-12-07 00:00:00.000 2023-12-09 00:00:00.000 15 13
39 418 2023-12-29 2023-12-09 00:00:00.000 2023-12-09 00:00:00.000 20 20

percentile=0.05;

List<ArrivalItemAvgEntryExitTimeEntity>items = context.
        selectDistinct( ARRIVAL_ITEMS_ENTRY_EXIT_RT.WAREHOUSE,
                ARRIVAL_ITEMS_ENTRY_EXIT_RT.ITEM,
                percentileCont(percentile).withinGroupOrderBy(ARRIVAL_ITEMS_ENTRY_EXIT_RT.ENTRY_REMAINING_TIME).as("AVERAGE_ENTRY_REMAINING_TIME"),
                percentileCont(percentile).withinGroupOrderBy(ARRIVAL_ITEMS_ENTRY_EXIT_RT.EXIT_REMAINING_TIME).as("AVERAGE_EXIT_REMAINING_TIME")). //
                from(ARRIVAL_ITEMS_ENTRY_EXIT_RT). //
                groupBy(ARRIVAL_ITEMS_ENTRY_EXIT_RT.WAREHOUSE). //
                fetchInto(ArrivalItemAvgEntryExitTimeEntity.class);

The expected results are:

AVERAGE_ENTRY_REMAINING_TIME: 17.55

AVERYGE_EXIT_REMAINING_TIME:  12.25

But it returns 0.0 for both calculated columns

I also tried a normal select instead of selectDistinct, but I got the same result.

Any hint what I'm doing wrong here?

thanks

Christian Schneider

Lukas Eder

unread,

Jan 11, 2024, 8:18:10 AM1/11/24

to jooq...@googlegroups.com

Thanks for your message.

Assuming ArrivalItemAvgEntryExitTimeEntity is a mutable POJO, then your column names (AVERAGE_...) and your attribute names (avg...) don't match.

I hope this helps,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAK%2BCNsaMo6qy-tOCQpLeV9MTru3UYFA%3D85CNZy9A%2BBXsXowVqQ%40mail.gmail.com.

chri schneid

unread,

Jan 11, 2024, 8:38:39 AM1/11/24

to jooq...@googlegroups.com

Hi Lukas,

yes, you're right - after changing the aliases to the attribute names the calculation is like expected.

Many thanks

Christian

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO7WMVx7mah2TC7NFqcjOKQPFJTe0yMSRrPEa_mdi86cpA%40mail.gmail.com.

Reply all

Reply to author

Forward