Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- declare
- symbolz character varying(10);
- i int;
- buy numeric;
- sell numeric;
- total_1min numeric;
- total_1h numeric;
- total_24h numeric;
- total_1m_vs_total_24h numeric;
- total_1h_vs_total_24h numeric;
- total_week numeric;
- total_percent_net_vol_1h_vs_24h numeric;
- total_percent_net_vol_1min_vs_vo_l24h numeric;
- total_percent_net_vol_1min_vs_1h numeric;
- total_full_1min numeric;
- total_full_24h numeric;
- total_full_1h numeric;
- tablenamefor_full text = 'public.ticker2' ;
- tablenamefor text = 'ticker2' ;
- tablenamefor_col text = 'symbol';
- tablename1 text;
- curs1 refcursor;
- len int;
- ttt text;'
- BEGIN
- EXECUTE 'SELECT reltuples::bigint AS EstimatedCount FROM pg_class
- WHERE oid = '||quote_literal(tablenamefor_full)||'::regclass' into len;
- FOR i IN 1..len LOOP OPEN curs1 FOR EXECUTE 'SELECT '||quote_ident(tablenamefor_col)||
- ' from '||quote_ident(tablenamefor)||
- ' order by '||quote_ident(tablenamefor_col)||' ASC';
- FETCH ABSOLUTE i FROM curs1 INTO symbolz;
- tablename1 = 'ctbh_trades_'||lower(symbolz);
- IF NOT EXISTS (SELECT * FROM pg_tables WHERE TABLENAME=tablename1) THEN
- NULL;
- ELSE
- EXECUTE 'SELECT COALESCE(SUM(quantity),0) sum
- FROM '||quote_ident(tablename1)||'
- WHERE '||quote_ident(tablename1)||'.timestamp >=
- CURRENT_TIMESTAMP - interval ''1 minutes'' and '||quote_ident(tablename1)||'.side = ''buy''' into buy;
- EXECUTE 'SELECT COALESCE(SUM(quantity),0) sum
- FROM '||quote_ident(tablename1)||'
- WHERE '||quote_ident(tablename1)||'.timestamp >=
- CURRENT_TIMESTAMP - interval ''1 minutes'' and '||quote_ident(tablename1)||'.side = ''sell''' into sell;
- total_1min = buy - sell;
- total_full_1min = buy + sell;
- EXECUTE 'SELECT COALESCE(SUM(quantity),0) sum
- FROM '||quote_ident(tablename1)||'
- WHERE '||quote_ident(tablename1)||'.timestamp >=
- CURRENT_TIMESTAMP - interval ''1 hour'' and '||quote_ident(tablename1)||'.side = ''buy''' into buy;
- EXECUTE 'SELECT COALESCE(SUM(quantity),0) sum
- FROM '||quote_ident(tablename1)||'
- WHERE '||quote_ident(tablename1)||'.timestamp >=
- CURRENT_TIMESTAMP - interval ''1 hour'' and '||quote_ident(tablename1)||'.side = ''sell''' into sell;
- total_1h = buy - sell;
- total_full_1h = buy + sell;
- EXECUTE 'SELECT COALESCE(SUM(quantity),0) sum
- FROM '||quote_ident(tablename1)||'
- WHERE '||quote_ident(tablename1)||'.timestamp >=
- CURRENT_TIMESTAMP - interval ''24 hour'' and '||quote_ident(tablename1)||'.side = ''buy''' into buy;
- EXECUTE 'SELECT COALESCE(SUM(quantity),0) sum
- FROM '||quote_ident(tablename1)||'
- WHERE '||quote_ident(tablename1)||'.timestamp >=
- CURRENT_TIMESTAMP - interval ''24 hour'' and '||quote_ident(tablename1)||'.side = ''sell''' into sell;
- total_24h = buy - sell;
- total_full_24h = buy + sell;
- total_1m_vs_total_24h = total_1min - total_24h;
- total_1h_vs_total_24h = total_1h - total_24h;
- EXECUTE 'SELECT COALESCE(SUM(quantity),0) sum
- FROM '||quote_ident(tablename1)||'
- WHERE '||quote_ident(tablename1)||'.timestamp >=
- CURRENT_TIMESTAMP - interval ''168 hour'' and '||quote_ident(tablename1)||'.side = ''buy''' into buy;
- EXECUTE 'SELECT COALESCE(SUM(quantity),0) sum
- FROM '||quote_ident(tablename1)||'
- WHERE '||quote_ident(tablename1)||'.timestamp >=
- CURRENT_TIMESTAMP - interval ''168 hour'' and '||quote_ident(tablename1)||'.side = ''sell''' into sell;
- total_week = buy - sell;
- total_percent_net_vol_1h_vs_24h = round(COALESCE((total_1h * 100) / NULLIF((total_1h - total_24h),0), 0), 2);
- total_percent_net_vol_1min_vs_vo_l24h = round(COALESCE((total_1min * 100) / NULLIF((total_1min - total_24h),0), 0) ,2);
- total_percent_net_vol_1min_vs_1h = round(COALESCE((total_1min * 100) / NULLIF((total_1min - total_1h),0), 0) , 2);
- UPDATE ctbh_atotal SET net_vol_1min = total_1min, net_vol_1h = total_1h,
- timestamp = current_timestamp, net_vol_24h = total_24h, net_vol1min_vs_vol_24h = total_1m_vs_total_24h,
- net_vol1h_vs_vol_24h = total_1h_vs_total_24h, net_vol_week = total_week,
- percent_net_vol_1h_vs_vol_24h = total_percent_net_vol_1h_vs_24h,
- percent_net_vol_1min_vs_vo_l24h = total_percent_net_vol_1min_vs_vo_l24h,
- percent_net_vol_1min_vs_1h = total_percent_net_vol_1min_vs_1h,
- total_vol_1min = total_full_1min, total_vol_24h = total_full_24h WHERE symbol = symbolz;
- IF found THEN
- ELSE
- INSERT INTO
- ctbh_atotal(symbol,net_vol_1min,net_vol_1h,timestamp,net_vol_24h,
- net_vol1min_vs_vol_24h,net_vol1h_vs_vol_24h,
- net_vol_week,percent_net_vol_1h_vs_vol_24h,
- percent_net_vol_1min_vs_vo_l24h,percent_net_vol_1min_vs_1h,
- total_vol_1min,total_vol_24h)
- VALUES (symbolz, total_1min, total_1h, current_timestamp, total_24h, total_1m_vs_total_24h,
- total_1h_vs_total_24h, total_week, total_percent_net_vol_1h_vs_24h,
- total_percent_net_vol_1min_vs_vo_l24h, total_percent_net_vol_1min_vs_1h,
- total_full_1min, total_full_24h);
- END IF;
- END IF;
- CLOSE curs1;
- END LOOP;
- END;'
Add Comment
Please, Sign In to add comment