如果行不存在，请按日期和第二天选择？-Java 学习之路

我想知道在从一行日期进行日期比较时是否可以同时运行IN条件和>条件（仅当在IN中找不到）？

例如，如果2018-01-01不存在，它将拉出日期2018-01-02的下一个可用行，它应该为我提供的随机日期数组执行此操作 . （没有范围，因为这将引入我不感兴趣的日期 . ）

例：

create table trade (
    id serial primary key,
    year int,
    month int,
    "data" json
);

insert into trade ("year", "month", "data") VALUES (
  2018, 1, '{"2": {"low": 19, "high": 21, "open": 20, "close": 20, "volume": 1000}, "3": {"low": 19, "high": 21, "open": 20, "close": 20, "volume": 1000}}'::json
);

insert into trade ("year", "month", "data") VALUES (
  2018, 2, '{"1": {"low": 19, "high": 21, "open": 20, "close": 20, "volume": 1000}, "2": {"low": 19, "high": 21, "open": 20, "close": 20, "volume": 1000}}'::json

SELECT
 t.prices,
 make_date("year", "month", t.day::int) as date
FROM
trade
JOIN json_each(trade.data) t(day, prices) ON TRUE
WHERE
make_date("year", "month", t.day::int) IN ('2018-01-1', '2018-01-03')
);

希望它返回1950-01-03,2018-01-03和2018-01-02的价格（自2018-01-01以来不存在）

我正在开发一个函数，当我提供我感兴趣的日期时，它会产生1/1比率的结果，如果它们不存在，它将返回下一个可用日期 .

2 回答

假设间隙可能大于一天，则以下函数似乎是最简单有效的解决方案：

create or replace function trade_on_dates(variadic date[])
returns table (prices json, date date) 
language plpgsql stable as $$
declare
    d date;
begin
    foreach d in array $1 loop
        return query select
            t.prices,
            make_date("year", "month", t.day::int) as date
        from trade
        join json_each(trade.data) t(day, prices) on true
        where make_date("year", "month", t.day::int) >= d
        order by 2
        limit 1;
    end loop;
end $$;

select *
from trade_on_dates('2018-01-01', '2018-01-03');

                              prices                              |    date    
------------------------------------------------------------------+------------
 {"low": 19, "high": 21, "open": 20, "close": 20, "volume": 1000} | 2018-01-02
 {"low": 19, "high": 21, "open": 20, "close": 20, "volume": 1000} | 2018-01-03
(2 rows)

回复于 2024-05-17T18:08:19+08:00

您可以使用generate_series检查给定范围内的所有日期 .

SELECT
 t.prices,
 make_date("year", "month", t.day::int) as date
FROM
trade
JOIN json_each(trade.data) t(day, prices) ON TRUE
WHERE
("year"|| lpad( "month"::text,2,'0') || lpad(t.day,2,'0') )::DATE -- a simulation of 
                                                                 --your make_date function.

IN ( select 
           generate_series(DATE '2018-01-1',
                   DATE '2018-01-03',INTERVAL '1 DAY') ::DATE
);

Demo

Edit

假设我想在1950-01-01,2001-01-01和2002-01-04等日期等

您可以在 UNION ALL 中使用多个范围

..
IN ( select 
           generate_series(DATE '2018-01-1',
                   DATE '2018-01-03',INTERVAL '1 DAY') ::DATE
                   UNION ALL
     select 
           generate_series(DATE '1950-01-01',
                   DATE '1950-01-03',INTERVAL '1 DAY') ::DATE    
                   UNION ALL
     select 
           generate_series(DATE '2002-01-01',
                   DATE '2002-01-04',INTERVAL '1 DAY') ::DATE                  

)
..
..

回复于 2024-05-17T18:08:19+08:00

如果行不存在，请按日期和第二天选择？

2 回答

相关问题