DROP FUNCTION IF EXISTS get_cb_trademin;
CREATE OR REPLACE FUNCTION get_cb_trademin(
	tradedate text, 
	interval_min int,
	thres_cnt int,
	traintime text
)
returns table(unique_id character varying(40), trademin timestamp, lastprice double precision, d_flag int)
as $$
DECLARE
    sql_text TEXT;
BEGIN
    sql_text := '
    select code as unique_id,trademin,lastprice,case when trademin > '''|| traintime || ''' then 0 else 1 end as d_flag
      from
      (
        select code,trademin,lastprice,count(*) over(partition by code) as cnt
          from
          (
            select code,trademin,rank() over(partition by code,trademin order by tradetime desc) as rid,lastprice
              from
              (
                select code,tradetime,date_trunc(''hour'', tradetime) + ((floor(extract(''minute'' from tradetime)/'||interval_min||')*'||interval_min||') || '' min'')::interval as trademin,lastprice
                  from cb_data_pt
                 where tradedate = '''|| tradedate || '''
              ) t1
          ) t2  where rid = 1
      ) t3 where cnt > ' || thres_cnt;

	raise notice 'sql_text = %', sql_text;

    RETURN QUERY EXECUTE sql_text;
END $$ LANGUAGE plpgsql;


copy (select * from get_cb_trademin('2023-04-28', 1, 144, '2023-04-28 14:45:00') order by unique_id,trademin) to 'D:\\llm\\lamini\\cb_data_20230428_1min.csv' WITH CSV HEADER;
copy (select * from get_cb_trademin('2023-04-28', 5, 40, '2023-04-28 14:45:00') order by unique_id,trademin) to 'D:\\llm\\lamini\\cb_data_20230428_5min.csv' WITH CSV HEADER;
copy (select * from get_cb_trademin('2023-04-28', 15, 10, '2023-04-28 14:45:00') order by unique_id,trademin) to 'D:\\llm\\lamini\\cb_data_20230428_15min.csv' WITH CSV HEADER;


drop table if exists cb_data_1min_fcst_cmp;
create table cb_data_1min_fcst_cmp(
  unique_id varchar(40) DEFAULT NULL,
  trademin timestamp DEFAULT NULL,
  TimeGPT double precision DEFAULT NULL,
  TimeGPT_lo_90 double precision DEFAULT NULL,
  TimeGPT_lo_80 double precision DEFAULT NULL,
  TimeGPT_hi_80 double precision DEFAULT NULL,
  TimeGPT_hi_90 double precision DEFAULT NULL,
  lastprice double precision DEFAULT NULL,
  diff double precision DEFAULT NULL,
  diff_ratio double precision DEFAULT NULL
);


drop table if exists cb_data_5min_fcst_cmp;
create table cb_data_5min_fcst_cmp(
  unique_id varchar(40) DEFAULT NULL,
  trademin timestamp DEFAULT NULL,
  TimeGPT double precision DEFAULT NULL,
  TimeGPT_lo_90 double precision DEFAULT NULL,
  TimeGPT_lo_80 double precision DEFAULT NULL,
  TimeGPT_hi_80 double precision DEFAULT NULL,
  TimeGPT_hi_90 double precision DEFAULT NULL,
  lastprice double precision DEFAULT NULL,
  diff double precision DEFAULT NULL,
  diff_ratio double precision DEFAULT NULL
);