Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Incorrect query to calculate a project's monthly cost #15

Open
lukaseder opened this issue Mar 25, 2019 · 0 comments
Open

Incorrect query to calculate a project's monthly cost #15

lukaseder opened this issue Mar 25, 2019 · 0 comments

Comments

@lukaseder
Copy link
Contributor

Thanks for doing this comparison.

I've stumbled upon this query here:

CommonTableExpression<Record4<Integer, String, BigDecimal, String>> project_info =
name("project_info")
.fields("project_pid", "project_name", "monthly_cost", "company_name")
.as(select(PROJECT.PID, PROJECT.NAME, EMPLOYEE.SALARY, COMPANY.NAME)
.from(PROJECT)
.join(PROJECTEMPLOYEE).on(PROJECT.PID.eq(PROJECTEMPLOYEE.PROJECT_PID))
.join(EMPLOYEE).on(PROJECTEMPLOYEE.EMPLOYEE_PID.eq(EMPLOYEE.PID))
.leftJoin(DEPARTMENT).on(EMPLOYEE.DEPARTMENT_PID.eq(DEPARTMENT.PID))
.leftJoin(COMPANY).on(DEPARTMENT.COMPANY_PID.eq(COMPANY.PID))
);
Field<BigDecimal> monthly_cost = project_info.field("monthly_cost", BigDecimal.class);
Field<?> project_name = project_info.field("project_name");
Field<?> company_name = project_info.field("company_name");
CommonTableExpression<Record2<Integer, BigDecimal>> project_cost =
name("project_cost")
.fields("project_pid", "total_cost")
.as(select(project_info.field("project_pid", Integer.class), sum(monthly_cost))
.from(project_info)
.groupBy(project_info.field("project_pid"))
);
Field<BigDecimal> total_cost = project_cost.field("total_cost", BigDecimal.class);
SelectSeekStep1<? extends Record4<?, BigDecimal, ?, BigDecimal>, ?> query = create
.with(project_info)
.with(project_cost)
.select(project_name, total_cost, company_name, sum(monthly_cost).as("company_cost"))
.from(project_info)
.join(project_cost).using(project_info.field("project_pid"))
.where(total_cost.greaterThan(new BigDecimal(totalCostBoundary)))
.groupBy(project_name, total_cost, company_name)
.orderBy(company_name);
return query.fetchInto(ProjectsWithCostsGreaterThanOutput.class);

I'm sure there's an easier way to write this with jOOQ (or with all other solutions, as the query does contain a bit of redundancy), but first, I'd like to point out that your using a many to many relationship between employee and project seems to hint at the query being wrong as an employee who is assigned to several projects will produce more than their actual costs. I suspect a weighted average should be used instead.

Once this has been done, I guess that by using window functions, you will achieve a much more straightforward (and faster!) solution in all frameworks.

I hope this helps

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant