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

У меня есть таблица a в Vertica, содержащая item_id, прикрепленную к code во время from_date

create table a (
    code      varchar(16),
    item_id   int,
    from_date timestamptz
);

и таблица b, содержащая code для данной даты t

create table b (
    code    varchar(16),
    t       date
);

Мне нужно получить a.item_id из b.code на дату b.t. Данные выглядят так:

insert into a values ('abc', 1, '2013-01-01 10:00:00 EST'::timestamptz);
insert into a values ('abc', 2, '2013-01-05 11:00:00 EST'::timestamptz);
insert into a values ('abc', 3, '2013-01-09 9:00:00 EST'::timestamptz);

insert into b values ('abc', '2013-01-06'::date);

Я попытался использовать Vertica INTERPOLATE в ЛЕВОМ ПРИСОЕДИНЯЙТЕСЬ, чтобы получить предыдущую пару (code, item_id) в момент времени t с

select
    b.code, b.t, a.item_id
from  b
left join a on a.code = b.code and b.t INTERPOLATE PREVIOUS VALUE a.t;

Это дает мне ошибку типа Interpolated predicates can accept arguments of the same type only.

Поэтому я попытался преобразовать b.t в временную метку

select
    b.code, b.t, a.item_id
from b
left join a on a.code = b.code
    and (b.t::timestamptz) INTERPOLATE PREVIOUS VALUE a.t;

Это дает мне синтаксическую ошибку Syntax error at or near "INTERPOLATE".

Без INTERPOLATE выражение SQL работает, но не соединяется с предыдущей строкой.

Можно ли преобразовать мою дату в метку времени в JOIN с INTERPOLATE?

Если нет, есть ли другой подход, который позволил бы мне получить текущий item_id во время t?


sql timestamp vertica
person synapski    schedule 25.07.2013    source источник
comment
вы используете MySQL, sql-сервер?   -  person    schedule 25.07.2013
comment
нет, я использую Vertica.   -  person synapski    schedule 25.07.2013
comment
Vertica использует PostgreSQL, по крайней мере, для целей синтаксиса и типов данных. Если у вас когда-нибудь возникнет вопрос о том, как работают определенные типы данных, поиск того, как это работает с PostgreSQL, почти всегда работает для того же вопроса в Vertica. Надеюсь, что это поможет в будущем.   -  person geoffrobinson    schedule 26.07.2013
comment
@geoffrobinson К сожалению, интерполированное JOIN характерно для Vertica, и преобразование даты в отметку времени работает без интерполяции.   -  person synapski    schedule 26.07.2013

Ответы (2)


arrow_upward
1
arrow_downward

Я нашел хакерский обходной путь, обманув интерпретатор и сделав преобразование даты в отметку времени в подзапросе SELECT:

select
    b.code, b.t, a.item_id
from b
left join (select a.t::date, a.code, a.item from a) a on a.code = b.code
    and b.t INTERPOLATE PREVIOUS VALUE a.t;
person synapski    schedule 26.07.2013
comment
Преобразование даты в отметку времени является ошибкой в ​​Vertica. Этот обходной путь — способ сделать преобразование прямо сейчас. - person synapski; 02.08.2013

arrow_upward
0
arrow_downward 

select b.code, b.t, a.item_id
from b
left join a on a.code = b.code and b.t = cast(a.t as date);

Но это решение может быть неправильным, если в таблице «a» более 1 строки, где t содержит разное время в одну и ту же дату.

Например,

insert into a values ('abc', 4, '2013-01-06 8:00:00 EST'::timestamptz);
insert into a values ('abc', 5, '2013-01-06 9:00:00 EST'::timestamptz);

В этом случае вы должны решить, какой из этих рядов вам нужен. Скажем, вам нужна последняя строка:

select b.code, b.t, a.item_id
from b
left join (
    select a1.code, a1.item_id, a2.dt
    from a a1
    inner join (
        select code, cast(t as date) dt, max(t) as max_t
        from a
        group by code, cast(t as date)
    ) a2 on a1.code = a2.code and a1.t = a2.max_t
) a on a.code = b.code and b.t = a.dt;

Решение может отличаться в зависимости от вашей СУБД.

person GriGrim    schedule 25.07.2013
comment
Мне нужно присоединить дату «2013-01-06» к предыдущей отметке времени в таблице a, то есть «2013-01-05 11:00:00 EST», поэтому использование = не сработает. Вот почему мне нужно интерполировать item_id для даты «2013-01-06» (я использую Vertica). - person synapski; 26.07.2013