--------------------------------------------------------
--  File created - Tuesday-June-24-2014   
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Type STRING_TABLE
--------------------------------------------------------

  CREATE OR REPLACE EDITIONABLE TYPE "STRING_TABLE" as table of varchar2(500);

/
--------------------------------------------------------
--  DDL for Table CITIES
--------------------------------------------------------

  CREATE TABLE "CITIES" 
   (	"ID" NUMBER(2,0), 
	"NAME" VARCHAR2(50), 
	"LATTITUDE" NUMBER(5,2), 
	"LONGITUDE" NUMBER(5,2)
   ) ;
--------------------------------------------------------
--  DDL for Table FOOTBALL_REGIONS
--------------------------------------------------------

  CREATE TABLE "FOOTBALL_REGIONS" 
   (	"ID" NUMBER(2,0), 
	"NAME" VARCHAR2(30), 
	"CONTINENT" VARCHAR2(2)
   ) ;
--------------------------------------------------------
--  DDL for Table MATCH_RESULTS
--------------------------------------------------------

  CREATE TABLE "MATCH_RESULTS" 
   (	"GROUP1" VARCHAR2(1), 
	"HOME" NUMBER(1,0), 
	"AWAY" NUMBER(1,0), 
	"HOME_GOALS" NUMBER(2,0), 
	"AWAY_GOALS" NUMBER(2,0), 
	"SDM_ID" NUMBER(2,0), 
	"LOCAL_START_TIME" DATE, 
	"SCORING_PROCESS" VARCHAR2(20), 
	"WEATHER_CATEGORY" VARCHAR2(20), 
	"ID" NUMBER(2,0)
   ) ;
--------------------------------------------------------
--  DDL for Table MATCH_TAGS
--------------------------------------------------------

  CREATE TABLE "MATCH_TAGS" 
   (	"TAG_ID" NUMBER(4,0), 
	"MATCH_ID" NUMBER
   ) ;
--------------------------------------------------------
--  DDL for Table STADIUMS
--------------------------------------------------------

  CREATE TABLE "STADIUMS" 
   (	"ID" NUMBER(2,0), 
	"CTY_ID" NUMBER(2,0), 
	"NAME" VARCHAR2(50)
   ) ;
--------------------------------------------------------
--  DDL for Table TAGS
--------------------------------------------------------

  CREATE TABLE "TAGS" 
   (	"ID" NUMBER(4,0), 
	"TAG" VARCHAR2(200)
   ) ;
REM INSERTING into CITIES
SET DEFINE OFF;
Insert into CITIES (ID,NAME,LATTITUDE,LONGITUDE) values (1,'Rio de Janeiro',23,43);
Insert into CITIES (ID,NAME,LATTITUDE,LONGITUDE) values (2,'Manaus',3,60);
Insert into CITIES (ID,NAME,LATTITUDE,LONGITUDE) values (3,'Natal',5,35);
Insert into CITIES (ID,NAME,LATTITUDE,LONGITUDE) values (4,'Brasilia',15,48);
Insert into CITIES (ID,NAME,LATTITUDE,LONGITUDE) values (5,'Sao Paulo',23,46);
Insert into CITIES (ID,NAME,LATTITUDE,LONGITUDE) values (6,'Fortaleza',3,38);
Insert into CITIES (ID,NAME,LATTITUDE,LONGITUDE) values (7,'Cuiaba',15,56);
Insert into CITIES (ID,NAME,LATTITUDE,LONGITUDE) values (8,'Belo Horizonte',20,44);
Insert into CITIES (ID,NAME,LATTITUDE,LONGITUDE) values (9,'Curitiba',25,49);
Insert into CITIES (ID,NAME,LATTITUDE,LONGITUDE) values (10,'Porto Alegre',30,51);
Insert into CITIES (ID,NAME,LATTITUDE,LONGITUDE) values (11,'Recife',8,35);
Insert into CITIES (ID,NAME,LATTITUDE,LONGITUDE) values (12,'Salvador',13,38);
REM INSERTING into FOOTBALL_REGIONS
SET DEFINE OFF;
Insert into FOOTBALL_REGIONS (ID,NAME,CONTINENT) values (1,'Middle East','AS');
Insert into FOOTBALL_REGIONS (ID,NAME,CONTINENT) values (2,'Africa','AF');
Insert into FOOTBALL_REGIONS (ID,NAME,CONTINENT) values (3,'Far East','AS');
Insert into FOOTBALL_REGIONS (ID,NAME,CONTINENT) values (4,'South America','AM');
Insert into FOOTBALL_REGIONS (ID,NAME,CONTINENT) values (5,'North America','AM');
Insert into FOOTBALL_REGIONS (ID,NAME,CONTINENT) values (6,'Central America','AM');
Insert into FOOTBALL_REGIONS (ID,NAME,CONTINENT) values (7,'North Western Europe','EU');
Insert into FOOTBALL_REGIONS (ID,NAME,CONTINENT) values (8,'Eastern Europe','EU');
Insert into FOOTBALL_REGIONS (ID,NAME,CONTINENT) values (9,'Southern Europe','EU');
Insert into FOOTBALL_REGIONS (ID,NAME,CONTINENT) values (10,'Pacific','PC');
REM INSERTING into MATCH_RESULTS
SET DEFINE OFF;
Insert into MATCH_RESULTS (GROUP1,HOME,AWAY,HOME_GOALS,AWAY_GOALS,SDM_ID,LOCAL_START_TIME,SCORING_PROCESS,WEATHER_CATEGORY,ID) values ('A',4,1,1,4,4,to_timestamp('23-JUN-2014 17:00:00','DD-MON-RR HH.MI.SSXFF AM'),'10111',null,33);
Insert into MATCH_RESULTS (GROUP1,HOME,AWAY,HOME_GOALS,AWAY_GOALS,SDM_ID,LOCAL_START_TIME,SCORING_PROCESS,WEATHER_CATEGORY,ID) values ('A',2,3,1,3,11,to_timestamp('23-JUN-2014 17:00:00','DD-MON-RR HH.MI.SSXFF AM'),'1110',null,34);
Insert into MATCH_RESULTS (GROUP1,HOME,AWAY,HOME_GOALS,AWAY_GOALS,SDM_ID,LOCAL_START_TIME,SCORING_PROCESS,WEATHER_CATEGORY,ID) values ('B',2,3,2,0,5,to_timestamp('23-JUN-2014 13:00:00','DD-MON-RR HH.MI.SSXFF AM'),'00',null,36);
Insert into MATCH_RESULTS (GROUP1,HOME,AWAY,HOME_GOALS,AWAY_GOALS,SDM_ID,LOCAL_START_TIME,SCORING_PROCESS,WEATHER_CATEGORY,ID) values ('B',4,1,0,3,5,to_timestamp('23-JUN-2014 13:00:00','DD-MON-RR HH.MI.SSXFF AM'),'111',null,35);
Insert into MATCH_RESULTS (GROUP1,HOME,AWAY,HOME_GOALS,AWAY_GOALS,SDM_ID,LOCAL_START_TIME,SCORING_PROCESS,WEATHER_CATEGORY,ID) values ('C',1,2,3,0,8,to_timestamp('14-JUN-2014 10:00:00','DD-MON-RR HH.MI.SSXFF AM'),'000',null,5);
Insert into MATCH_RESULTS (GROUP1,HOME,AWAY,HOME_GOALS,AWAY_GOALS,SDM_ID,LOCAL_START_TIME,SCORING_PROCESS,WEATHER_CATEGORY,ID) values ('C',3,4,2,1,11,to_timestamp('14-JUN-2014 22:00:00','DD-MON-RR HH.MI.SSXFF AM'),'100',null,6);
Insert into MATCH_RESULTS (GROUP1,HOME,AWAY,HOME_GOALS,AWAY_GOALS,SDM_ID,LOCAL_START_TIME,SCORING_PROCESS,WEATHER_CATEGORY,ID) values ('C',1,3,2,1,4,to_timestamp('19-JUN-2014 13:00:00','DD-MON-RR HH.MI.SSXFF AM'),'001',null,21);
Insert into MATCH_RESULTS (GROUP1,HOME,AWAY,HOME_GOALS,AWAY_GOALS,SDM_ID,LOCAL_START_TIME,SCORING_PROCESS,WEATHER_CATEGORY,ID) values ('C',4,2,0,0,3,to_timestamp('19-JUN-2014 22:00:00','DD-MON-RR HH.MI.SSXFF AM'),null,null,22);
Insert into MATCH_RESULTS (GROUP1,HOME,AWAY,HOME_GOALS,AWAY_GOALS,SDM_ID,LOCAL_START_TIME,SCORING_PROCESS,WEATHER_CATEGORY,ID) values ('A',1,2,3,1,5,to_timestamp('12-JUN-2014 17:00:00','DD-MON-RR HH.MI.SSXFF AM'),'1000',null,1);
Insert into MATCH_RESULTS (GROUP1,HOME,AWAY,HOME_GOALS,AWAY_GOALS,SDM_ID,LOCAL_START_TIME,SCORING_PROCESS,WEATHER_CATEGORY,ID) values ('A',3,4,1,0,3,to_timestamp('13-JUN-2014 13:00:00','DD-MON-RR HH.MI.SSXFF AM'),'0',null,2);
Insert into MATCH_RESULTS (GROUP1,HOME,AWAY,HOME_GOALS,AWAY_GOALS,SDM_ID,LOCAL_START_TIME,SCORING_PROCESS,WEATHER_CATEGORY,ID) values ('A',1,3,0,0,6,to_timestamp('17-JUN-2014 16:00:00','DD-MON-RR HH.MI.SSXFF AM'),'0',null,17);
Insert into MATCH_RESULTS (GROUP1,HOME,AWAY,HOME_GOALS,AWAY_GOALS,SDM_ID,LOCAL_START_TIME,SCORING_PROCESS,WEATHER_CATEGORY,ID) values ('A',4,2,0,4,2,to_timestamp('18-JUN-2014 18:00:00','DD-MON-RR HH.MI.SSXFF AM'),'1111',null,18);
Insert into MATCH_RESULTS (GROUP1,HOME,AWAY,HOME_GOALS,AWAY_GOALS,SDM_ID,LOCAL_START_TIME,SCORING_PROCESS,WEATHER_CATEGORY,ID) values ('B',1,2,1,5,12,to_timestamp('13-JUN-2014 16:00:00','DD-MON-RR HH.MI.SSXFF AM'),'011111',null,3);
Insert into MATCH_RESULTS (GROUP1,HOME,AWAY,HOME_GOALS,AWAY_GOALS,SDM_ID,LOCAL_START_TIME,SCORING_PROCESS,WEATHER_CATEGORY,ID) values ('B',3,4,3,1,7,to_timestamp('13-JUN-2014 18:00:00','DD-MON-RR HH.MI.SSXFF AM'),'0010',null,4);
Insert into MATCH_RESULTS (GROUP1,HOME,AWAY,HOME_GOALS,AWAY_GOALS,SDM_ID,LOCAL_START_TIME,SCORING_PROCESS,WEATHER_CATEGORY,ID) values ('B',1,3,0,2,1,to_timestamp('18-JUN-2014 16:00:00','DD-MON-RR HH.MI.SSXFF AM'),'11',null,19);
Insert into MATCH_RESULTS (GROUP1,HOME,AWAY,HOME_GOALS,AWAY_GOALS,SDM_ID,LOCAL_START_TIME,SCORING_PROCESS,WEATHER_CATEGORY,ID) values ('B',4,2,2,3,10,to_timestamp('18-JUN-2014 13:00:00','DD-MON-RR HH.MI.SSXFF AM'),'10011',null,20);
REM INSERTING into MATCH_TAGS
SET DEFINE OFF;
Insert into MATCH_TAGS (TAG_ID,MATCH_ID) values (1,1);
Insert into MATCH_TAGS (TAG_ID,MATCH_ID) values (1,3);
Insert into MATCH_TAGS (TAG_ID,MATCH_ID) values (3,3);
Insert into MATCH_TAGS (TAG_ID,MATCH_ID) values (3,20);
REM INSERTING into STADIUMS
SET DEFINE OFF;
Insert into STADIUMS (ID,CTY_ID,NAME) values (1,1,'Estádio do Maracanã');
Insert into STADIUMS (ID,CTY_ID,NAME) values (4,4,'Estádio Nacional Mané Garrincha');
Insert into STADIUMS (ID,CTY_ID,NAME) values (5,5,'Arena de São Paulo');
Insert into STADIUMS (ID,CTY_ID,NAME) values (6,6,'Estádio Castelão');
Insert into STADIUMS (ID,CTY_ID,NAME) values (8,8,'Estádio Mineirão');
Insert into STADIUMS (ID,CTY_ID,NAME) values (10,10,'Estádio Beira-Rio');
Insert into STADIUMS (ID,CTY_ID,NAME) values (2,2,'Arena da Amazônia');
Insert into STADIUMS (ID,CTY_ID,NAME) values (12,12,'Arena Fonte Nova');
Insert into STADIUMS (ID,CTY_ID,NAME) values (7,7,'Arena Pantanal');
Insert into STADIUMS (ID,CTY_ID,NAME) values (3,3,'Arena das Dunas');
Insert into STADIUMS (ID,CTY_ID,NAME) values (9,9,'Arena da Baixada');
Insert into STADIUMS (ID,CTY_ID,NAME) values (11,11,'Arena Pernambuco');
REM INSERTING into TAGS
SET DEFINE OFF;
Insert into TAGS (ID,TAG) values (1,'referential integrity');
Insert into TAGS (ID,TAG) values (2,'hattrick');
Insert into TAGS (ID,TAG) values (3,'super goal');


create or replace type string_table as table of varchar2(500);

 with  tag_filter as
 ( select string_tbl('recent','comeback') selected_tags
   from   dual
 )
 , filter_tags as
 ( select column_value filter_tag
   from   table((select selected_tags from tag_filter))
 )   
select *
from filter_tags


FILTER_TAG                                       
--------------------------------------------------
recent                                             
comeback       


========================================================================
filter out matches that do not have all matching tags
filter out selected tags

 with  tag_filter as
 ( select string_table('recent','comeback') selected_tags
   from   dual
 )
 , filter_tags as
 ( select column_value filter_tag
   from   table((select selected_tags from tag_filter))
 )   
 , matches as
( select mr.id
  ,      mr.home_goals
  ,      mr.away_goals
  ,      t1.country home_country
  ,      t2.country away_country
  ,      t1.fifa_rank home_fifa_rank
  ,      t2.fifa_rank away_fifa_rank
  ,      r1.name home_region
  ,      r1.continent home_continent
  ,      r2.name away_region
  ,      r2.continent away_continent
  ,      mr.local_start_time
  ,      mr.scoring_process
  ,      cty.lattitude
  from   match_results mr
         join
         stadiums sdm
         on (mr.sdm_id = sdm.id)
         join
         cities cty
         on (sdm.cty_id = cty.id)
         join
         teams t1
         on ( mr.home = t1.seq_in_group
              and
              mr.group1 = t1.group1
            )
         join
         teams t2
         on ( mr.away= t2.seq_in_group
              and
              t1.group1 = t2.group1
            )
         join
         football_regions r1
         on (t1.rgn_id = r1.id)
         join
         football_regions r2
          on (t2.rgn_id = r2.id)
)
, scoring_process (match_id, intermediate_score, iteration) as
( select id, substr(scoring_process,1,1), 1
  from   match_results
  union all
  select id,substr(scoring_process,1,iteration+1), iteration+1
  from   match_results mr
         join
         scoring_process sp
         on (sp.match_id = mr.id and length(scoring_process)>= iteration + 1)
)
, tagged as
(select t.tag
 ,      mt.match_id
 from   match_tags mt
        join
        tags t
        on (mt.tag_id = t.id)
 union all
 select 'Surprise' tag
 ,      m.id match_id
 from   matches m
 where (m.home_goals > m.away_goals and m.home_fifa_rank - m.away_fifa_rank > 5)
        or
       (m.home_goals < m.away_goals and m.home_fifa_rank - m.away_fifa_rank < -5)
        or
       (m.home_goals = m.away_goals and abs(m.home_fifa_rank - m.away_fifa_rank)  > 10)
 union all      
 select 'Intercontinental' tag
 ,      m.id match_id
 from   matches m
 where (m.home_continent != m.away_continent)
 union all      
 select 'Derby' tag
 ,        m.id match_id
 from   matches m
 where (m.home_region = m.away_region)
 union all      
 select case
        when lattitude < 10 then 'north'
        when lattitude > 23 then 'south'
        else 'central' end tag
 ,      m.id match_id
 from   matches m
 union all      
 select  case to_char(m.LOCAL_START_TIME, 'HH24')
         when '13' then 'early'
         when '22' then 'late'
         end tag
 ,       m.id match_id
 from    matches m
 where   to_char(m.LOCAL_START_TIME, 'HH24') in ('13','22')
 union all      
 select   'goalless tie' tag
 ,       m.id match_id
 from    matches m
 where   home_goals + away_goals=0
 union all      
 select  'recent' tag
 ,       m.id match_id
 from    ( select m.*
           ,      row_number() over (order by m.local_start_time desc) rnk
           from   matches m
        ) m
 where   rnk < 6
 union all      
 select  'exciting' tag
 ,        m.id match_id
 from     matches m
 where    home_goals + away_goals > 4
 union all
 select  'comeback' tag
 ,        match_id
 from     ( select ms.match_id
            from   ( select sp.*
                     ,      length(translate('1'||intermediate_score, '10','1')) - length(translate('0'||intermediate_score, '01','0')) running_score
                     from   scoring_process sp
                   ) ms          
            having max(running_score)> 0 and min(running_score) < 0
            group
            by     ms.match_id
          )         
)
, match_tag_sets as
( select match_id
  ,      cast(collect(substr(tag,1,25)) as string_table) tagset     
  from   tagged
         join
         matches m
         on (m.id = tagged.match_id)
  group
  by     match_id
)
, selected_matches as
( select match_id
  from   match_tag_sets mts
         cross join
         tag_filter  tf     
  where  tf.selected_tags SUBMULTISET  mts.tagset
)
select home_country ||'-'||away_country lineup
,      home_goals ||'-'||away_goals score
,      tags
from   ( select match_id
         ,      listagg(lower(tag),',') within group (order by tag) tags
         from   tagged
                join
                selected_matches
                using (match_id)   
         cross join
         tag_filter  tf     
         where  tag not member of tf.selected_tags
         group
         by     match_id
       ) mt
       join
       matches m
       on (m.id = mt.match_id)

=======================================================================	   
select tag cloud for remaining matches - leaving out the selected tags:

 with  tag_filter as
 ( select string_table('recent','comeback') selected_tags
   from   dual
 )
 , filter_tags as
 ( select column_value filter_tag
   from   table((select selected_tags from tag_filter))
 )   
 , matches as
( select mr.id
  ,      mr.home_goals
  ,      mr.away_goals
  ,      t1.country home_country
  ,      t2.country away_country
  ,      t1.fifa_rank home_fifa_rank
  ,      t2.fifa_rank away_fifa_rank
  ,      r1.name home_region
  ,      r1.continent home_continent
  ,      r2.name away_region
  ,      r2.continent away_continent
  ,      mr.local_start_time
  ,      mr.scoring_process
  ,      cty.lattitude
  from   match_results mr
         join
         stadiums sdm
         on (mr.sdm_id = sdm.id)
         join
         cities cty
         on (sdm.cty_id = cty.id)
         join
         teams t1
         on ( mr.home = t1.seq_in_group
              and
              mr.group1 = t1.group1
            )
         join
         teams t2
         on ( mr.away= t2.seq_in_group
              and
              t1.group1 = t2.group1
            )
         join
         football_regions r1
         on (t1.rgn_id = r1.id)
         join
         football_regions r2
          on (t2.rgn_id = r2.id)
)
, scoring_process (match_id, intermediate_score, iteration) as
( select id, substr(scoring_process,1,1), 1
  from   match_results
  union all
  select id,substr(scoring_process,1,iteration+1), iteration+1
  from   match_results mr
         join
         scoring_process sp
         on (sp.match_id = mr.id and length(scoring_process)>= iteration + 1)
)
, tagged as
(select t.tag
 ,      mt.match_id
 from   match_tags mt
        join
        tags t
        on (mt.tag_id = t.id)
 union all
 select 'Surprise' tag
 ,      m.id match_id
 from   matches m
 where (m.home_goals > m.away_goals and m.home_fifa_rank - m.away_fifa_rank > 5)
        or
       (m.home_goals < m.away_goals and m.home_fifa_rank - m.away_fifa_rank < -5)
        or
       (m.home_goals = m.away_goals and abs(m.home_fifa_rank - m.away_fifa_rank)  > 10)
 union all      
 select 'Intercontinental' tag
 ,      m.id match_id
 from   matches m
 where (m.home_continent != m.away_continent)
 union all      
 select 'Derby' tag
 ,        m.id match_id
 from   matches m
 where (m.home_region = m.away_region)
 union all      
 select case
        when lattitude < 10 then 'north'
        when lattitude > 23 then 'south'
        else 'central' end tag
 ,      m.id match_id
 from   matches m
 union all      
 select  case to_char(m.LOCAL_START_TIME, 'HH24')
         when '13' then 'early'
         when '22' then 'late'
         end tag
 ,       m.id match_id
 from    matches m
 where   to_char(m.LOCAL_START_TIME, 'HH24') in ('13','22')
 union all      
 select   'goalless tie' tag
 ,       m.id match_id
 from    matches m
 where   home_goals + away_goals=0
 union all      
 select  'recent' tag
 ,       m.id match_id
 from    ( select m.*
           ,      row_number() over (order by m.local_start_time desc) rnk
           from   matches m
        ) m
 where   rnk < 6
 union all      
 select  'exciting' tag
 ,        m.id match_id
 from     matches m
 where    home_goals + away_goals > 4
 union all
 select  'comeback' tag
 ,        match_id
 from     ( select ms.match_id
            from   ( select sp.*
                     ,      length(translate('1'||intermediate_score, '10','1')) - length(translate('0'||intermediate_score, '01','0')) running_score
                     from   scoring_process sp
                   ) ms          
            having max(running_score)> 0 and min(running_score) < 0
            group
            by     ms.match_id
          )         
)
, match_tag_sets as
( select match_id
  ,      cast(collect(substr(tag,1,25)) as string_table) tagset     
  from   tagged
         join
         matches m
         on (m.id = tagged.match_id)
  group
  by     match_id
)
, selected_matches as
( select match_id
  from   match_tag_sets mts
         cross join
         tag_filter  tf     
  where  tf.selected_tags SUBMULTISET  mts.tagset
)
select substr(tag, 1, 25) tag
,      count(match_id) occurrences
from   tagged
                join
                selected_matches
                using (match_id)   
       cross join
       tag_filter  tf     
where  tag not member of tf.selected_tags
group
by     substr(tag, 1, 25)
order
by     occurrences desc