CREATE TABLE "public"."stats" (
  "id" SERIAL, 
  "place_no" SMALLINT DEFAULT  0  NOT NULL, 
  "club_id" INTEGER DEFAULT  0  NOT NULL, 
  "currentin" BIGINT DEFAULT  0  NOT NULL, 
  "currentout" BIGINT DEFAULT  0  NOT NULL, 
  "last_query" TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL, 
  CONSTRAINT "stats_pkey" PRIMARY KEY("id"), 
  CONSTRAINT "stats_place_no_key" UNIQUE("place_no", "club_id", "last_query")
) WITHOUT OIDS;

CREATE RULE "stats_dup_ignore" AS ON INSERT TO "public"."stats" 
WHERE (EXISTS ( SELECT stats.place_no FROM stats WHERE stats.place_no = new.place_no AND stats.club_id = new.club_id AND stats.last_query = new.last_query))
DO INSTEAD NOTHING;

--для выброса 
CREATE TABLE "public"."get_ai_result" 
(
  "currentin" BIGINT, 
  "currentout" BIGINT, 
  "last_query" TIMESTAMP WITHOUT TIME ZONE
) WITHOUT OIDS;

--ХП


DROP FUNCTION get_ai(place integer, club integer, begin_val_ timestamp without time zone, end_val_ timestamp without time zone, minute_period integer);

CREATE OR REPLACE FUNCTION get_ai(place integer, club integer, begin_val_ timestamp without time zone, end_val_ timestamp without time zone, minute_period integer)
  RETURNS SETOF get_ai_result AS
$BODY$
DECLARE
	str text;
	tmp timestamp;
	begin_val timestamp;    
	end_val timestamp;    
	prev_last_datime timestamp;    
	prev_last_IN bigint;    
	prev_last_OUT bigint;  
	rec get_ai_result%ROWTYPE;
        ret_rec record;
	real_num bigint;
	dt2 timestamp;
BEGIN
	end_val = end_val_;    
	begin_val = begin_val_;    
	if (end_val < begin_val) then   
		tmp = end_val;
		end_val = begin_val;    
		begin_val = tmp;    
	end if;

	--первая запись
	select last_query into prev_last_datime from club_stats where club_id = club order by last_query asc limit  1 ;
	if (begin_val < prev_last_datime) then  
		begin_val = prev_last_datime; 
	end if;

	--последняя запись
	select last_query into prev_last_datime from club_stats where club_id = club order by last_query desc limit  1 ;
	if (end_val > prev_last_datime)  then
		end_val = prev_last_datime;  
	end if;

	prev_last_datime = (begin_val - ((minute_period::text||' minutes ' )::interval))::timestamp;
	prev_last_IN  = - 1 ;
	prev_last_OUT = - 1 ;

	real_num =  0 ; 	
	IF (minute_period =  0 ) THEN
		for ret_rec in select currentIN, currentOUT, last_query from stats where 
			place_no = place and club_id = club and last_query >= begin_val and last_query <= end_val order by last_query
		LOOP
			return next ret_rec;
			real_num = real_num +  1 ;
		END LOOP;
	ELSE
		FOR rec IN SELECT currentIN, currentOUT, last_query FROM stats where place_no = place and club_id = club and last_query >= begin_val and last_query <= end_val order by last_query
		LOOP 
			if ((EXTRACT(EPOCH FROM (rec.last_query - prev_last_datime)) >= minute_period* 60 ) and
			   (rec.currentIN <> prev_last_IN or rec.currentOUT <> prev_last_OUT)) then
				select rec.currentIN, rec.currentOUT, rec.last_query into ret_rec; return next ret_rec;
				prev_last_datime = rec.last_query;    
				prev_last_IN  = rec.currentIN;  
				prev_last_OUT = rec.currentOUT;  
				real_num = real_num+ 1 ;   
			end if;
		END LOOP;
	END IF;


	--компенсация до минимального количества записей (2 или 1) 
	if (real_num =  1 )  then  
		select last_query into dt2 from stats where  place_no = place and club_id = club and last_query >= begin_val and last_query <= end_val 
			order by last_query desc limit  1 ;
		if  (prev_last_datime is not null and  prev_last_datime <> dt2) then
			select prev_last_IN,prev_last_OUT,prev_last_datime into ret_rec; return next ret_rec;
			real_num = real_num +  1 ;
		end if;
	end if;
	if (real_num =  0 ) then   
		select  0 ::bigint,  0 ::bigint, begin_val into ret_rec; return next ret_rec;
		real_num = real_num +  1 ;
		if (end_val <> begin_val) then
			select  0 ::bigint,  0 ::bigint, end_val into ret_rec; return next ret_rec;
			real_num = real_num +  1 ;
		end if;
	end if;
 
	--добавление первой строкой (если смотреть в порядке даты) количества строк в результате   
	select real_num,real_num, timestamp '19000101' into ret_rec; return next ret_rec;

	RETURN;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;


--вызов ф-ции собссно - получить данные для аппарата 1 из зала 2 с 2000 по 2010 год с разбросом не менее 10 минут
select * from get_ai( 1 , 2 , '20000101', '20100101',  10 ) order by last_query ; 

select t1.* 
  from stats t1
 where club_id =  1  and place_no =  1  and exists(
               select null 
			     from stats t2
			    where t1.id != t2.id and club_id =  1  and place_no =  1 
			      and t2.last_query - t1.last_query > interval '1 minute'
			      and (t1.currentin != t2.currentin  or t1.currentout != t2.currentout )
			 )