问题描述
这是我当前拥有的最近12天的代码
Here is the code I currently have that gives me the last 12 days
SELECT *
FROM table
WHERE analysis_date >= current_date - interval '12' day;
analysis_date是表中的日期列。我了解为什么这不起作用,因为它没有考虑工作日。我该如何重写它,以便获得最近12个工作日的间隔?
analysis_date is the date column in the table. I understand why this isn't working because it's not accounting for business days. How can I rewrite this so that I get an interval of the last 12 business days?
我尝试在线搜索并发现
extract(dow from (date))
但是我找不到需要工作日间隔的示例。任何帮助,将不胜感激。
But I couldn't find an example where I need a weekday interval. Any help would be appreciated.
推荐答案
这可以通过CTE解决:
This can be solved with a CTE:
WITH business_days_back AS (
WITH RECURSIVE bd(back_day, go_back) AS (
-- Go back to the previous Monday, allowing for current_date in the weekend
SELECT CASE extract(dow from current_date)
WHEN 0 THEN current_date - 6
WHEN 6 THEN current_date - 5
ELSE current_date - extract(dow from current_date)::int + 1
END,
CASE extract(dow from current_date)
WHEN 0 THEN 7
WHEN 6 THEN 7
ELSE 12 - extract(dow from current_date)::int + 1
END
UNION
-- Go back by the week until go_back = 0
SELECT CASE
WHEN go_back >= 5 THEN back_day - 7
WHEN go_back > 0 THEN back_day - 2 - go_back
END,
CASE
WHEN go_back >= 5 THEN go_back - 5
WHEN go_back > 0 THEN 0
END
FROM bd
)
SELECT back_day FROM bd WHERE go_back = 0
)
SELECT * FROM my_table WHERE analysis_date >= (SELECT * FROM business_days_back);
一些解释:
- 内部CTE从工作到上一个星期一开始,以补偿在周末的
current_date
。 - 然后,递归项通过返回整周(日历日期为
back_day-7
和go_back-5
个工作日),直到go_back = 0
。 - 外部CTE返回
back_day
日期,其中go_back = 0
。因此,这是一个标量查询,您可以将其用作过滤器表达式中的子查询。
- The inner CTE starts off by working back to the previous Monday, compensating for a
current_date
that falls on a weekend day. - The recursive term then adds rows by going back full weeks (
back_day - 7
for the calendar date andgo_back - 5
for the business days) untilgo_back = 0
. - The outer CTE returns the
back_day
date wherego_back = 0
. This is therefore a scalar query and you can use it as a sub-query in a filter expression.
您可以更改只需在初始 SELECT 12
和 7
即可回顾工作日/ code>在内部CTE中。不过请记住,由于内部CTE的初始 SELECT
是相同的,因此该值应使其回到上周一,否则查询将失败。
You can change the number of business days to look back by simply changing the numbers 12
and 7
in the initial SELECT
in the inner CTE. Keep in mind, though, that the value should be such that it goes back to the previous Monday or the query will fail, due to the same initial SELECT
of the inner CTE.
一种更加灵活(并且可能更快*)的解决方案是使用以下功能:
A far more flexible (and probably faster*) solution is to use the following function:
CREATE FUNCTION business_days_diff(from_date date, diff int) RETURNS date AS $$
-- This function assumes Mon-Fri business days
DECLARE
start_dow int;
calc_date date;
curr_diff int;
weekend int;
BEGIN
-- If no diff requested, return the from_date. This may be a non-business day.
IF diff = 0 THEN
RETURN from_date;
END IF;
start_dow := extract(dow from from_date)::int;
calc_date := from_date;
IF diff < 0 THEN -- working backwards
weekend := -2;
IF start_dow = 0 THEN -- Fudge initial Sunday to the previous Saturday
calc_date := calc_date - 1;
start_dow := 6;
END IF;
IF start_dow + diff >= 1 THEN -- Stay in this week
RETURN calc_date + diff;
ELSE -- Work back to Monday
calc_date := calc_date - start_dow + 1;
curr_diff := diff + start_dow - 1;
END IF;
ELSE -- Working forwards
weekend := 2;
IF start_dow = 6 THEN -- Fudge initial Saturday to the following Sunday
calc_date := calc_date + 1;
start_dow := 0;
END IF;
IF start_dow + diff <= 5 THEN -- Stay in this week
RETURN calc_date + diff;
ELSE -- Work forwards to Friday
calc_date := calc_date + 5 - start_dow;
curr_diff := diff - 5 + start_dow;
END IF;
END IF;
-- Move backwards or forwards by full weeks
calc_date := calc_date + (curr_diff / 5) * 7;
-- Process any remaining days, include weekend
IF curr_diff % 5 != 0 THEN
RETURN calc_date + curr_diff % 5 + weekend;
ELSE
RETURN calc_date;
END IF;
END; $$ LANGUAGE plpgsql STRICT IMMUTABLE;
此函数可以使用任何日期进行计算,并可以计算未来的任何天数( diff
的正值或过去( diff
的负值),包括当周内的差异。并且由于它以标量形式返回工作日日期,因此在查询中的使用非常简单:
This function can take any date to calculate from and any number of days into the future (positive value of diff
) or the past (negative value of diff
), including diffs within the current week. And since it returns the business day date as a scalar, use in your query is very straightforward:
SELECT *
FROM table
WHERE analysis_date >= business_days_diff(current_date, -12);
除此之外,您还可以传递表中的字段并执行以下类似的时髦操作:
Apart from that, you can also pass in fields from your table and do funky stuff like:
SELECT t1.some_value - t2.some_value AS value_diff
FROM table t1
JOIN table t2 ON t2.analysis_date = business_days_diff(t1.analysis_date, -12);
即
请注意,此功能假设星期一至星期五工作日为一周。
Note that this function assumes a Monday-Friday business day week.
这篇关于如何在Postgresql中获得动态的12个工作日视图?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!