Производные таблицы JOOQ

Я пытаюсь выразить следующий SQL в JOOQ. Однако у меня либо возникают серьезные проблемы с типами, использующими производные таблицы, либо я получаю что-то, что компилируется, но при этом происходит сбой на уровне SQL или даже в JAVA. Может кто-нибудь дать мне представление, как правильно использовать производные таблицы в этом контексте?

SELECT
    id,
    ROUND(num_realized / num_requirements, 2) AS realized_percent,
    ROUND(num_requirements / max_req, 2) AS activity_percent
FROM (
    SELECT
        requirement.project_id AS id,
        COUNT(requirement.id) AS num_requirements,
        COUNT(requirement.realized) AS num_realized
    FROM
        requirement
    GROUP BY
        requirement.project_id) AS stats
    CROSS JOIN (
        SELECT
            MAX(num_requirements) AS max_req
        FROM (
            SELECT
                requirement.project_id AS id,
                COUNT(requirement.id) AS num_requirements,
                COUNT(requirement.realized) AS num_realized
            FROM
                requirement
            GROUP BY
                requirement.project_id) AS stats) AS req_max 

Оператор отлично работает при применении в SQL, но я не могу получить это выражение в JOOQ.

Моя последняя попытка использовала

Table<Record3<Integer, Integer, Integer>> stats =
DSL.select(
    REQUIREMENT.PROJECT_ID.as("id"),
    DSL.count(REQUIREMENT.ID).as("num_requirements"),
    DSL.count(REQUIREMENT.REALIZED).as("num_realized")
).from(REQUIREMENT).groupBy(REQUIREMENT.PROJECT_ID).asTable("stats");

Table<Record2<Integer, Integer>> req_max =
    DSL.select(
        stats.field(0),
        DSL.min(stats.field(1))
    )
    .from(stats).asTable("req_max");

Однако я получаю сообщение об ошибке: несовместимые типы:

Table<Record2<CAP#1,CAP#2>> cannot be converted to Table<Record2<Integer,Integer>>

Я попробовал несколько разных техник, включая определение типа данных и использование .field (String, Datatype) вместо использования «Records», но что бы я ни делал, он либо не компилируется, либо завершается с ошибкой при выполнении с неизвестной ошибкой.

Буду рад любой помощи.

Всего 1 ответ


Использование оконных функций вместо

В общем, следует избегать самостоятельных присоединений, когда вы можете. Во многих случаях оконные функции могут решить проблему гораздо более элегантно, чем агрегации во вложенных запросах. Если бы вы использовали MySQL 8, ваш запрос мог бы быть переписан как:

SELECT
    requirement.project_id AS id,
    ROUND(COUNT(requirement.realized) / COUNT(requirement.id), 2) AS realized_percent,
    ROUND(COUNT(requirement.id) / MAX(COUNT(requirement.id)) OVER(), 2) AS activity_percent
FROM
    requirement
GROUP BY
    requirement.project_id    

Обратите внимание на оконную функцию MAX(..) OVER() , которая может агрегировать обычные функции агрегирования, как описано здесь . Я знаю, что вы используете MySQL 5.7, который пока не поддерживает оконные функции, но для полноты картины этот ответ требует решения на основе оконных функций - возможно, в качестве мотивации для обновления :-)

Многие сложные jOOQ-запросы можно упростить, прежде всего упрощая базовый SQL-запрос.

Проблема производной таблицы, с которой вы столкнулись

Проблема в том, что вы используете stats.field(0) и stats.field(1) . Подпись метода

Field<?> field​(int index)

Нет никакого способа, которым jOOQ мог бы обеспечить вам безопасность типов при доступе к столбцам таблицы по индексу, поэтому тип возвращаемого значения - Field<?> , Где тип столбца - это универсальный подстановочный знак. Здесь есть несколько решений:

  1. Избегайте безопасности типа, если вам это не нужно. Вы всегда можете объявить свою таблицу Table<?> req_max . Только из вашего примера, я не уверен, что вам нужна безопасность типов здесь
  2. Извлеките ваши полевые ссылки как локальные переменные. Вместо встраивания, например, столбца id в таблицу stats , почему бы и нет:

    Field<Integer> id = REQUIREMENT.PROJECT_ID.as("id");
    Field<Integer> numRequirements = DSL.count(REQUIREMENT.ID).as("num_requirements");
    Field<Integer> numRealized = DSL.count(REQUIREMENT.REALIZED).as("num_realized");
    

    и затем используйте это так:

    var stats =
    DSL.select(id, numRequirements, numRealized)
       .from(REQUIREMENT)
       .groupBy(REQUIREMENT.PROJECT_ID)
       .asTable("stats");
    
    var reqMax =
    DSL.select(stats.field(id), DSL.max(stats.field(numRequirements)))
       .from(stats)
       .asTable(reqMax);
    

Есть идеи?

10000