View the results by using the SQL statements

The SQL statements

CREATE TEMPORARY TABLE IF NOT EXISTS filterTable AS 
SELECT * 
FROM data_for_group_table_1qa0e940jbiw9gocaber436q72enw5kh 
INNER JOIN 
	(SELECT DISTINCT dimension_id 
	FROM dimension_table  
	WHERE  horizontal_relationship_id IN 
		(SELECT relationship_id  FROM viewOfRelationshipTableWithNameAndValueStrings 
          WHERE name_string='Msgtype' AND value_string='Shopping')  
	AND  horizontal_relationship_id IN 
         (SELECT relationship_id  FROM viewOfRelationshipTableWithNameAndValueStrings 
          WHERE name_string='MsgOriginType' AND value_string='Mobile')  
	AND  vertical_relationship_id IN 
	     (SELECT DISTINCT relationship_id FROM viewOfRelationshipTableWithNameAndValueStrings    
          WHERE  relationship_ID IN 
		  (SELECT relationship_ID  FROM viewOfRelationshipTableWithNameAndValueStrings 
            WHERE  name_string='IOwnerHi' AND value_string='OWNER1')  
	      AND  relationship_ID IN 
		  (SELECT relationship_ID  FROM viewOfRelationshipTableWithNameAndValueStrings 
            WHERE  name_string='IOwnerMid' AND value_string='MID1')  
	GROUP BY relationship_id 
	HAVING COUNT(*) = 2)) AS infoTable 
USING (dimension_id)  
WHERE  data_for_group_table_1qa0e940jbiw9gocaber436q72enw5kh.dimension_id = infoTable.dimension_id;

The view of the relationship_table table

In the SQL statements in Figure 1, viewofRelationshipTableWithNameAndValueStrings is called to create a view of the relationship_table table that has the text strings for the name-value pair names and values instead of the identifiers that were assigned to the names and values. For more information about the relationship_table table, see relationship_table.

select 'relationship_table'.'relationship_id' AS 
'relationship_id','name_table'.'name_string' AS 
'name_string',
'value_table'.'value_string' AS 
'value_string','relationship_table'.'cardinality' AS 
'cardinality',
'relationship_table'.'relationship_type' AS 
'relationship_type' 
from (('relationship_table' join 'name_table' 
on('relationship_table'.'name_id' = 'name_table'.'name_id')) 
join 'value_table' on('relationship_table'.'value_id' = 'value_table'.'value_id'))

The filterTable table

The SQL statements in Figure 1 create the filterTable temporary table. The filterTable table contains the filtered data from the data_for_group_1qa0e940jbiw9gocaber436q72enw5kh table based on the specified horizontal and vertical name-value pair filters.

The filterTable table contains the following information:

The rows that correspond to a horizontal relationship type

          ⋮
		(SELECT relationship_id  FROM viewOfRelationshipTableWithNameAndValueStrings 
          WHERE name_string='Msgtype' AND value_string='Shopping')   
          ⋮
         (SELECT relationship_id  FROM viewOfRelationshipTableWithNameAndValueStrings 
          WHERE name_string='MsgOriginType' AND value_string='Mobile')  
          ⋮

The SQL statements select the relationship with the R4 relationship ID from the view of the relationship_table table because this horizontal relationship has MsgType=Shopping and MsgOriginType=Mobile. The relationship with the R5 relationship ID is also selected because this horizontal relationship has MsgType=Shopping and MsgOriginType=Mobile.

The rows that correspond to a vertical relationship type

The following SQL statements from Figure 1 select all the rows from the relationship_table table that correspond to a vertical relationship type with only the name-value pairs of IOwnerHi=OWNER1,IOwnerMid=MID1, which is accomplished by the HAVING COUNT(*) = 2 statement. If a vertical relationship type has more name-value pairs than those two name-value pairs, the row is not selected. When the statements are selecting vertical relationship type rows from the relationship_table table, the cardinality must match. This is an important difference between selecting vertical data and horizontal data.

          ⋮
	     (SELECT DISTINCT relationship_id FROM viewOfRelationshipTableWithNameAndValueStrings    
          WHERE  relationship_ID IN 
		  (SELECT relationship_ID  FROM viewOfRelationshipTableWithNameAndValueStrings 
            WHERE  name_string='IOwnerHi' AND value_string='OWNER1')   
            ⋮
		  (SELECT relationship_ID  FROM viewOfRelationshipTableWithNameAndValueStrings 
            WHERE  name_string='IOwnerMid' AND 
       value_string='MID1')  
	 ⋮
	HAVING COUNT(*) = 2))
       ⋮

The SQL statements select the relationship with the R3 relationship ID from the view of the relationship_table table because this vertical relationship has IOwnerHi=OWNER1 and IOwnerMid=MID1. The relationship with the R1 Relationship ID is not selected because it has IOwnerLo=LOW1 in addition to IOwnerHi=OWNER1 and IOwnerMid=MID1.

The dimension IDs that correspond to the horizontal and vertical relationship IDs

          ⋮
	(SELECT DISTINCT dimension_id 
	FROM dimension_table  
	WHERE  horizontal_relationship_id IN 
	⋮
	AND  horizontal_relationship_id IN 
         ⋮
	AND  vertical_relationship_id IN 

If a portion of the dimension_table table contains the following information in Table 2, the dimensions with the D2 and D4 dimension IDs are selected because these dimensions match the horizontal and vertical relationship that are selected; that is, the relationship with the R4, R3 and R5, R3 relationship IDs.

The rows that have the dimension IDs

The following SQL statements from Figure 1 select all of the rows from the data_for_group_table_1qa0e940jbiw9gocaber436q72enw5kh table that have the dimension IDs that are obtained from the previous SQL statements segment.

⋮
SELECT * 
FROM data_for_group_table_1qa0e940jbiw9gocaber436q72enw5kh 
⋮
USING (dimension_id)  
WHERE  data_for_group_table_1qa0e940jbiw9gocaber436q72enw5kh.dimension_id = infoTable.dimension_id;

View the average values of the FIND and SWBK metrics

The following SQL statements use the filterTable table to obtain the values of the FIND and SWBK metrics from the filtered data. These metric values are processed to obtain a weighted average based on the message count. The data is sorted by collection date and time.

SELECT CONCAT(collection_date, ' ', collection_time) as date,  (SUM( FIND) / SUM(message_count)) AS FIND, (SUM( SWBK) / SUM(message_count)) AS SWBK 
FROM  collection_table 
INNER JOIN 
	(SELECT collection_id,message_count, (FIND * message_count) AS FIND,(SWBK * message_count)
      AS SWBK FROM filterTable ) 
AS valTable  
USING (collection_id)
GROUP BY date;