Administrace komentářů

CREATE TABLE admiralmarkets.m1_chfjpy
(
  datetime timestamp without time zone NOT NULL,
  open double precision,
  high double precision,
  low double precision,
  close double precision,
  volume integer,
  CONSTRAINT m1_chfjpy_pkey PRIMARY KEY (datetime)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE admiralmarkets.m1_chfjpy
  OWNER TO matlab;

SELECT
	w.time_in_hours,
	avg( case when r1=1 then open end ) as open,
	max(m2) as High,
	min(m1) as Low,
	avg( case when r2=1 then close  end) as Close,
	sum(w.volume) as volume,
	count(*) as bar_count,
	max(case when r1=1 then datetime end) as time_open,
	max(case when m2=High then datetime end) as time_high,
	max(case when m1=Low then datetime end) as time_low,
	max(case when r2=1 then datetime end ) as time_close
	--w.time_in_hours+1*interval '1 hour' as time_end
	--- skewness--
	--(sqrt(count(*)*(count(*)-1)))/(count(*)-2) as s0
FROM
	(
SELECT
datetime,
	High,
	Low,
	Open,
	Close,
	volume,
	datetime- extract (minute
FROM
	datetime) * INTERVAL '1 minute' as time_in_hours,
	
	min(low) over (partition BY datetime-extract (minute
FROM
	datetime) * INTERVAL '1 minute' ) as m1,
	max(high) over (partition BY datetime-extract (minute
FROM
	datetime) * INTERVAL '1 minute' ) as m2,
	rank() over (partition BY datetime-extract (minute
FROM
	datetime) * INTERVAL '1 minute'
ORDER BY
	datetime) as r1,
	rank() over (partition BY datetime-extract (minute
FROM
	datetime) * INTERVAL '1 minute'
ORDER BY
	datetime desc ) as r2
FROM
	admiralmarkets.m1_usdjpy
	
	) as w
GROUP BY 1 

"GroupAggregate  (cost=576914.91..659539.99 rows=200 width=84) (actual time=4357.579..6267.656 rows=17391 loops=1)"
"  ->  WindowAgg  (cost=576914.91..605316.25 rows=1032776 width=44) (actual time=4357.530..5175.552 rows=1032776 loops=1)"
"        ->  Sort  (cost=576914.91..579496.85 rows=1032776 width=44) (actual time=4357.523..4566.744 rows=1032776 loops=1)"
"              Sort Key: ((m1_usdjpy.datetime - (date_part('minute'::text, m1_usdjpy.datetime) * '00:01:00'::interval))), m1_usdjpy.datetime"
"              Sort Method: external sort  Disk: 90824kB"
"              ->  WindowAgg  (cost=381803.08..410204.42 rows=1032776 width=44) (actual time=2841.311..3573.644 rows=1032776 loops=1)"
"                    ->  Sort  (cost=381803.08..384385.02 rows=1032776 width=44) (actual time=2841.305..3024.597 rows=1032776 loops=1)"
"                          Sort Key: ((m1_usdjpy.datetime - (date_part('minute'::text, m1_usdjpy.datetime) * '00:01:00'::interval))), m1_usdjpy.datetime"
"                          Sort Method: external sort  Disk: 82752kB"
"                          ->  WindowAgg  (cost=186691.25..215092.59 rows=1032776 width=44) (actual time=1334.691..2102.177 rows=1032776 loops=1)"
"                                ->  Sort  (cost=186691.25..189273.19 rows=1032776 width=44) (actual time=1334.668..1564.490 rows=1032776 loops=1)"
"                                      Sort Key: ((m1_usdjpy.datetime - (date_part('minute'::text, m1_usdjpy.datetime) * '00:01:00'::interval)))"
"                                      Sort Method: external merge  Disk: 62568kB"
"                                      ->  Seq Scan on m1_usdjpy  (cost=0.00..19980.76 rows=1032776 width=44) (actual time=0.039..534.610 rows=1032776 loops=1)"
"Total runtime: 6313.651 ms"