def test_timestamp_scalar_in_filter(alltypes, translate):
table = alltypes
expr = (table.filter([
table.timestamp_col < (ibis.timestamp('2010-01-01') + ibis.week(3)),
table.timestamp_col < (ibis.now() + ibis.day(10))
]).count())
expr.execute()
SELECT *,
avg(`float_col`) OVER (PARTITION BY `year` ORDER BY UNIX_MICROS(`timestamp_col`) RANGE BETWEEN 4 PRECEDING AND 2 PRECEDING) AS `win_avg`
FROM `{}.testing.functional_alltypes`""".format(project_id) # noqa: E501
assert result == expected
@pytest.mark.parametrize(('preceding', 'value'), [
(5, 5),
(ibis.nanosecond(), 0.001),
(ibis.microsecond(), 1),
(ibis.second(), 1000000),
(ibis.minute(), 1000000 * 60),
(ibis.hour(), 1000000 * 60 * 60),
(ibis.day(), 1000000 * 60 * 60 * 24),
(2 * ibis.day(), 1000000 * 60 * 60 * 24 * 2),
(ibis.week(), 1000000 * 60 * 60 * 24 * 7),
])
def test_trailing_range_window(alltypes, preceding, value, project_id):
t = alltypes
w = ibis.trailing_range_window(preceding=preceding,
order_by=t.timestamp_col)
expr = t.mutate(win_avg=t.float_col.mean().over(w))
result = expr.compile()
expected = """\
SELECT *,
avg(`float_col`) OVER (ORDER BY UNIX_MICROS(`timestamp_col`) RANGE BETWEEN {} PRECEDING AND CURRENT ROW) AS `win_avg`
FROM `{}.testing.functional_alltypes`""".format( # noqa: E501
value, project_id)
assert result == expected