Нотация Jooq для невложенности с несколькими массивами

Я хочу смешать внешнюю информацию часовой пояс для каждого местоположения, чтобы отфильтровать строки, которые имеют отметку времени без значений часового пояса.

Как я могу выразить в Jooq этот фрагмент SQL (Postgresql)

WITH 
    now(locaton, value) AS (
        SELECT 
            * 
        FROM unnest(
            ARRAY[1,2]::BIGINT[],
            ARRAY['2017-05-28 09:30','2017-05-28 10:30']::TIMESTAMP[])
    ) 
SELECT * 
FROM now;

Менее желательный, но все еще рабочий фрагмент:

WITH 
    now(locaton, value) AS (
        SELECT 
            unnest(ARRAY[1,2]::BIGINT[]) as location,
            unnest(ARRAY['2017-05-28 09:30','2017-05-28 10:30']::TIMESTAMP[]) as value
    ) 
SELECT * 
FROM now;

Вероятно, более правильный (заблокированный Добавить поддержку предложения SQL Standard WITH ORDINALITY #5799 )

SELECT location, value
FROM 
  unnest(ARRAY[1,2]::BIGINT[]) WITH ORDINALITY as location
  JOIN unnest(ARRAY['2017-05-28 09:30','2017-05-28 10:30']::TIMESTAMP[]) WITH ORDINALITY as value 
  USING (ordinality);

Получить

location    |        value
------------+---------------------
         1  | 2017-05-28 09:30:00
         2  | 2017-05-28 10:30:00
(2 rows)

postgresql java unnest jooq
person Alex Dvoretsky    schedule 29.05.2017    source источник
comment
О, действительно хорошее решение с использованием WITH ORDINALITY. Возможно, этот вопрос должен получить более высокий приоритет.   -  person Lukas Eder    schedule 29.05.2017

Ответы (1)


arrow_upward
1
arrow_downward

Ну, я решил это с помощью простого SQL

DSL
    .with("now", "location", "value")
    .as(
        create
            .select(
                DSL.field("location"),
                DSL.field("value")
            )
            .from(
                DSL
                    .table(
                        "UNNEST(?::BIGINT[]) WITH ORDINALITY",
                        DSL.val(Arrays.asList(1L, 2L).toArray())
                    )
                    .as("location")
                    .join(
                        DSL
                            .table(
                                "UNNEST(?::TIMESTAMP[]) WITH ORDINALITY",
                                DSL.val(
                                    Arrays.asList(
                                        LocalDateTime.of(2017, 05, 28, 9, 30),
                                        LocalDateTime.of(2017, 05, 28, 10, 30)
                                    ).toArray()
                                )
                            )
                            .as("value")
                    )
                    .using(
                        DSL.field("ordinality")
                    )
            )
    )
    .select()
    .from(DSL.table("now"));
person Alex Dvoretsky    schedule 29.05.2017
comment
Вы все еще можете использовать DSL.unnest(), а затем обернуть результат в простой SQL как таковой: DSL.table("{0} WITH ORDINALITY", DSL.unnest(...)) - person Lukas Eder; 29.05.2017
comment
Предлагаемое улучшение заменяет строку array_table вместо параметра массива. - person Alex Dvoretsky; 30.05.2017