时间戳

首页 > 解决方案 > 如何从 Oracle 中选择最近三个星期五的值？

oracle - 如何从 Oracle 中选择最近三个星期五的值？

问题描述

我有一个如下表，记录每分钟的值。我需要选择返回一周中最后三个特定日期从 12:00 到 18:00 的值，例如最后 3 个星期五，或最后 3 个星期一总是从 12:00 到 18 点，我该怎么做?

     timestamp         | value
01/06/2020 00:00          10
01/06/2020 00:01          05
01/06/2020 00:02          14
01/06/2020 00:03          20
01/06/2020 00:04          15
01/06/2020 00:05          06
.
.
.

标签： oracle

解决方案

您可以使用：

SELECT *
FROM   table_name
WHERE  -- Where there are 4 days since the start of the ISO-week (Monday)
       -- so this would find all Fridays
       TRUNC( "timestamp" ) - TRUNC( "timestamp", 'IW' ) = 4
       -- Where the time interval since midnight of that day is between
       -- 12:00 and 18:00.
AND    ( "timestamp" - TRUNC( "timestamp" ) ) DAY TO SECOND
       BETWEEN INTERVAL '12:00' HOUR TO MINUTE
       AND     INTERVAL '18:00' HOUR TO MINUTE
       -- Where the timestamp is after 14 days before the start of this week
       -- (i.e. in this week or the previous 2 weeks).
AND    "timestamp" >= TRUNC( SYSDATE, 'IW' ) - INTERVAL '14' DAY
       -- Where the timestamp is before 14 days before the start of this week
       -- (i.e. in this week or the previous 2 weeks).
AND    "timestamp" <  TRUNC( SYSDATE, 'IW' ) + INTERVAL '7' DAY

对于一些示例数据：

CREATE TABLE table_name ("timestamp", value) AS
SELECT DATE '2020-06-18' + INTERVAL '00:00' HOUR TO MINUTE, 10 FROM DUAL UNION ALL
SELECT DATE '2020-06-19' + INTERVAL '01:00' HOUR TO MINUTE, 20 FROM DUAL UNION ALL
SELECT DATE '2020-06-19' + INTERVAL '15:00' HOUR TO MINUTE, 30 FROM DUAL UNION ALL
SELECT DATE '2020-06-20' + INTERVAL '02:00' HOUR TO MINUTE, 40 FROM DUAL UNION ALL
SELECT DATE '2020-06-21' + INTERVAL '03:00' HOUR TO MINUTE, 50 FROM DUAL UNION ALL
SELECT DATE '2020-06-22' + INTERVAL '04:00' HOUR TO MINUTE, 60 FROM DUAL UNION ALL
SELECT DATE '2020-06-23' + INTERVAL '05:00' HOUR TO MINUTE, 70 FROM DUAL UNION ALL
SELECT DATE '2020-06-24' + INTERVAL '06:00' HOUR TO MINUTE, 80 FROM DUAL UNION ALL
SELECT DATE '2020-06-25' + INTERVAL '07:00' HOUR TO MINUTE, 90 FROM DUAL UNION ALL
SELECT DATE '2020-06-26' + INTERVAL '08:00' HOUR TO MINUTE, 11 FROM DUAL UNION ALL
SELECT DATE '2020-06-27' + INTERVAL '09:00' HOUR TO MINUTE, 21 FROM DUAL UNION ALL
SELECT DATE '2020-06-28' + INTERVAL '10:00' HOUR TO MINUTE, 31 FROM DUAL UNION ALL
SELECT DATE '2020-06-29' + INTERVAL '11:00' HOUR TO MINUTE, 41 FROM DUAL UNION ALL
SELECT DATE '2020-06-30' + INTERVAL '12:00' HOUR TO MINUTE, 51 FROM DUAL UNION ALL
SELECT DATE '2020-07-01' + INTERVAL '13:00' HOUR TO MINUTE, 61 FROM DUAL UNION ALL
SELECT DATE '2020-07-02' + INTERVAL '14:00' HOUR TO MINUTE, 71 FROM DUAL UNION ALL
SELECT DATE '2020-07-03' + INTERVAL '15:00' HOUR TO MINUTE, 81 FROM DUAL;

输出：

时间戳 | 价值
:----------------- | ----:
2020-06-19 15:00:00 | 30
2020-07-03 15:00:00 | 81

db<>在这里摆弄

推荐阅读