我有一行:
sum(purchases) over(partition by category order by value_day range between interval '1' month preceding and current row)
如果value_day = Aug 21
,它返回从7月21日到8月21日的总和,但我需要从7月22日到8日21日。
我该怎么做?
您可以使用表达式来定义窗口的起点。所以你可以
- 从当前日期减去一个月
- 添加一天
给予类似的东西:
sum ( purchases ) over (
partition by category
order by value_day
range between ( value_day - ( add_months ( value_day, -1 ) + 1 ) ) preceding
and current row
)
您可以:
- 使用两个窗口函数,其中一个函数添加上个月的所有内容,然后减去第二个函数,该函数刚好覆盖您不想包含的范围;或
- 使用相关的子查询,而不是窗口分析函数
SELECT t.*,
sum(purchases) over(
partition by category
order by value_day
range between interval '1' month preceding and current row
) -
COALESCE(
sum(purchases) over(
partition by category
order by value_day
range between interval '1' month preceding and interval '1' month preceding
),
0
) AS total1,
( SELECT SUM(s.purchases)
FROM table_name s
WHERE t.category = s.category
AND ADD_MONTHS(t.value_day, -1) + INTERVAL '1' DAY <= s.value_day
AND s.value_day <= t.value_day
) AS total2
FROM table_name t;
对于样本数据:
CREATE TABLE table_name (category, value_day, purchases) AS
SELECT 1, DATE '2022-01-01' + LEVEL - 1, LEVEL
FROM DUAL
CONNECT BY LEVEL <= 50;
输出:
CATEGORY VALUE_DAY 1 22年2月1日 32 >5271 02-FEB-22 33 581 22年2月3日 1 22年2月4日 1 22年2月5日 1 22年2月6日 1 22年2月7日 1 08-FEB-22 1 22年2月9日 1 10-FEB-22 1 22年2月11日 1 22年2月12日 1 22年2月13日 1 22年2月14日 1 22年2月15日 1 22年2月16日 1 22年2月17日 48 <1023>1 18-FEB-22 49 054<1054>1 22年2月19日