oracle 获取当前周之前的最后4周

bis0qfac 于 2022-11-03 发布在 Oracle

关注(0)|答案(2)|浏览(119)

我有一个包含日期值、会计年度、会计年度的表

DATE_VALUE  FISCAL_WEEK FISCAL_YEAR
24-DEC-21   52      2021
25-DEC-21   52      2021
26-DEC-21   52      2021
27-DEC-21   53      2021
28-DEC-21   53      2021
29-DEC-21   53      2021
30-DEC-21   53      2021
31-DEC-21   53      2021
01-JAN-22   53      2021
02-JAN-22   53      2021
03-JAN-22   1       2022
04-JAN-22   1       2022
05-JAN-22   1       2022
06-JAN-22   1       2022
07-JAN-22   1       2022
08-JAN-22   1       2022
09-JAN-22   1       2022
10-JAN-22   2       2022
11-JAN-22   2       2022
12-JAN-22   2       2022
13-JAN-22   2       2022
14-JAN-22   2       2022

该表涵盖整个2021和2022财年
我想取得本周之前的前4个会计周（FW）。假设本周是FW20 FY2022，我可以得到这个结果：

FISCAL_WEEK     FISCAL_YEAR
16              2022
17              2022
18              2022
19              2022

用于返回上述输出的代码为：

SELECT 
  * 
FROM 
  (
    WITH t AS (
      SELECT 
        fiscal_week - 1 lastweek, 
        fiscal_week - 5 week_x, 
        fiscal_year 
      FROM 
        TABLE 
      WHERE 
        Trunc(date_value) = Trunc(sysdate)
    ) 
    SELECT 
      DISTINCT fiscal_week, 
      t.fiscal_year 
    FROM 
      TABLE 
      OUTER JOIN t ON fiscal_week <> week_x 
    WHERE 
      to_char(fiscal_week) BETWEEN lastweek - 4 
      AND lastweek 
    ORDER BY 
      fiscal_week
  )

但是，如果当前周是FW04 FY2022，则上面的代码无法返回所需的输出。

FISCAL_WEEK     FISCAL_YEAR
53              2021
1               2022
2               2022
3               2022

同样，如果当前为FY03 FY2022，则输出为：

FISCAL_WEEK     FISCAL_YEAR
52              2021
53              2021
1               2022
2               2022

我需要怎样写代码来得到这个输出呢？也许case语句可以工作，但是我想看看是否有其他的解决方法？任何帮助都将不胜感激。
谢谢你，谢谢你

oracle

来源：https://stackoverflow.com/questions/74063684/get-the-last-4-weeks-prior-to-current-week

2条答案

按热度按时间

v9tzhpje1#

您可以将条件放在日期值而不是星期上以获得所需的输出，然后使用OFFSET 1跳过当前星期并仅提取后面4行。

WITH T AS
  (
    SELECT DISTINCT fiscal_week, fiscal_year 
    FROM TABLE_NAME 
    WHERE Trunc(date_value) <= Trunc(SYSDATE) 
    ORDER BY fiscal_year DESC, fiscal_week DESC
    OFFSET 1 ROWS 
    FETCH NEXT 4 ROWS ONLY
  )
SELECT fiscal_week, fiscal_year
FROM T ORDER BY fiscal_year, fiscal_week

请参见demo。

赞(0）回复(0）举报 2022-11-03

ujv3wf0j2#

不要使用FISCAL_WEEK和FISCAL_YEAR列;只需使用DATE列，并将其与基于ISO周开始的范围进行比较：

SELECT DISTINCT fiscal_week, fiscal_year
FROM   table_name
WHERE  "DATE" <  TRUNC(SYSDATE, 'IW')
AND    "DATE" >= TRUNC(SYSDATE, 'IW') - INTERVAL '28' DAY
ORDER BY fiscal_year, fiscal_week;

其中，对于示例数据：

Create Table table_name("DATE", FISCAL_WEEK, FISCAL_YEAR) AS
SELECT DATE '2021-12-24', 52, 2021 FROM DUAL UNION ALL
SELECT DATE '2021-12-25', 52, 2021 FROM DUAL UNION ALL
SELECT DATE '2021-12-26', 52, 2021 FROM DUAL UNION ALL
SELECT DATE '2021-12-27', 53, 2021 FROM DUAL UNION ALL
SELECT DATE '2021-12-28', 53, 2021 FROM DUAL UNION ALL
SELECT DATE '2021-12-29', 53, 2021 FROM DUAL UNION ALL
SELECT DATE '2021-12-30', 53, 2021 FROM DUAL UNION ALL
SELECT DATE '2021-12-31', 53, 2021 FROM DUAL UNION ALL
SELECT DATE '2022-01-01', 53, 2021 FROM DUAL UNION ALL
SELECT DATE '2022-01-02', 53, 2021 FROM DUAL UNION ALL
SELECT DATE '2022-01-03', 1, 2022 FROM DUAL UNION ALL
SELECT DATE '2022-01-04', 1, 2022 FROM DUAL UNION ALL
SELECT DATE '2022-01-05', 1, 2022 FROM DUAL UNION ALL
SELECT DATE '2022-01-06', 1, 2022 FROM DUAL UNION ALL
SELECT DATE '2022-01-07', 1, 2022 FROM DUAL UNION ALL
SELECT DATE '2022-01-08', 1, 2022 FROM DUAL UNION ALL
SELECT DATE '2022-01-09', 1, 2022 FROM DUAL UNION ALL
SELECT DATE '2022-01-10', 2, 2022 FROM DUAL UNION ALL
SELECT DATE '2022-01-11', 2, 2022 FROM DUAL UNION ALL
SELECT DATE '2022-01-12', 2, 2022 FROM DUAL UNION ALL
SELECT DATE '2022-01-13', 2, 2022 FROM DUAL UNION ALL
SELECT DATE '2022-01-14', 2, 2022 FROM DUAL UNION ALL
SELECT DATE '2022-02-14', 6, 2022 FROM DUAL;

如果SYSDATE为2022-01-17，则将输出：
| 会计_周|会计年度|
| - -|- -|
| 五十二人|小行星2021|
| 五十三个|小行星2021|
| 一个|小行星2022|
| 2个|小行星2022|
fiddle

赞(0）回复(0）举报 2022-11-03

我来回答

oracle 获取当前周之前的最后4周

2条答案

相关问题

热门标签

最新问答