将整数列转换为Month类型MM

Question

我正在尝试使用以下代码在指定月份使用PRECEDING和FOLLOWING

WITH tmp1 AS (
SELECT location.city,  date.date, COUNT(*) OVER W 
FROM fact, crime, date, location 
WHERE fact.location_key = location.location_key and crime.crime_key = 
fact.crime_key and fact.date_key = date.date_key
WINDOW W AS (
PARTITION BY location.city, date.year
ORDER BY date.month
RANGE BETWEEN INTERVAL '1 month' PRECEDING
AND INTERVAL '1 month' FOLLOWING
))

我得到错误说

RANGE with offset PRECEDING/FOLLOWING is not supported for column type integer and offset type interval

我想这是因为我的date.month是整数类型

所以我尝试将我的date.month列转换为MM格式的日期类型

我试着跟随

ALTER TABLE date ALTER COLUMN month TYPE text 
using to_date(month, 'MM');

并给出错误

No function matches the given name and argument types. You might need to add explicit type casts.

我的问题是

如何将date.month转换为可与PROCEDING和FOLLOWING命令一起使用的类型？

我假设这个类型是date，格式是MM。这就是为什么我尝试了上面的代码

谢谢！

Answer 1

您不需要将month列转换为任何其他数据类型。窗口化子句，即rows between...，在您的情况下适用于给定的分区PARTITION BY location.city, date.year。您需要先将month添加到partition子句中，以便它在window子句中工作。也不要使用旧的连接方式。

WITH tmp1 AS (
SELECT location.city,  date.date, COUNT(*) OVER W 
FROM fact
join crime
on crime.crime_key = fact.crime_key
join date
on fact.date_key = date.date_key 
join location 
on fact.location_key = location.location_key and 
WINDOW W AS (
PARTITION BY location.city, date.year, date.month
ORDER BY date.month
RANGE BETWEEN 1 PRECEDING
AND 1 FOLLOWING
))

Answer 2

既然可以使用整数范围，为什么要使用区间范围？

WINDOW W AS (
PARTITION BY location.city, date.year
ORDER BY date.month
RANGE BETWEEN INTERVAL 1 PRECEDING AND
INTERVAL 1 FOLLOWING
)