<< Click to Display Table of Contents >> Current:  Create Data Set  Integrated Application

❖@query implementation

•Functions of @query

The functions that can be implemented via @query are as follows:

1. If you want to dynamically query some information, for example, if you want to query the user information in Jingdong and Taobao or the information about the users who purchase toothpaste and toothbrush both, you can query the data dynamically by means of @query.

 

2. @ query can decrease the amount of new data sets. For example, in order to associate 20 different data sets, you do not need to create 20 data sets. Instead, you can dynamically transfer different values to @query to associate different data sets, which greatly decreases the number of data sets.

 

•Definition of @query

@query is a dynamic data set. It can be defined by defining the connection information for the database. Different SQL statements can be dynamically received by writing ?{@sql} to SQL statements.

➢For example:

1) Define  ?{@sql}  to receive the SQL statement with the name of aa.

2) Create a SQL Data Set with the name of query2 which is used to pass parameters.

select ID,MARKET,STATE from COFFEE_CHAIN where ID in ?{a} OR ID in ?{b}

As shown in the following figure:

3) Create Script Data Set.

//It needs to be set as true so that the Meta Columns which are static rather than dynamic will not be used.

param["_DYNAMIC_SQL_"]=true;

//Condition 1

param["sql"] = "select * from COFFEE_CHAIN where STATE in ?{state}"; 

param["state"] = ["Ohio","Florida","Texas"];

var grid = execute(this, SQL, "aa");

// Convert it to an array.

var size = grid.size(-1); //size is the function is the number of rows

var arr = grid!=null ? toArray(grid,0) : []; // Convert to an array.

// Condition 2

param["sql"] = "select * from ORDERS where STATE in ?{state1}"; 

param["state1"] = ["CA","NJ"];

var grid1=execute(this, SQL, "aa"); 

var size1 = grid1.size(-1);

var arr1 = grid1!=null ? toArray(grid1,0) : []; // Convert to an array.

// Transfer the two parameters of different conditions.

param["a"]=arr;

param["b"]=arr1;

var grid2=execute(this, SQL, "query2");

// Return the data of datagrid.

grid2;

4. Refresh Metadata and preview to see the required data.

 

❖JOIN and SUB QUERY that can implement data sets in a data mart

The Data Mart Data Set does not support JOIN and SUB QUERY. The data sets need to be split and then connected via the Script Data Set.

JOIN implementation method: There are two different queries query1 and query2, which can be joined through the common fields between them. Reserved fields should be written.

SUB QUERY implementation method: Create query1 and then query2. Access query1 via query2 to implement the functions of SUB QUERY.

➢For example:

UV indicators and average daily UV indicators of all websites. This example includes the JOIN and SUB QUERY implemented by Data Mart Data Set.

The detailed SQL statements are as follows:

Select ta.Site_ID, ta.UV, tb.AvgUV

From (

  Select Site_ID, count(distinct panel_id) as UV

  From webdata

  group by site_id 

)ta inner join

(

  Select Site_ID, Avg(UV)as AvgUV

  From (

    Select Site_ID, Dwd_date, count(distinct panel_id) as UV

    From webdata

  Group By Site_ID, dwd_date 

)tb on ta.Site_ID = tb.Site_ID

 

The operation modes in Yonghong Z-Suite are as follows:

1. Create a query Select Site_ID, Dwd_date, count(distinct panel_id) as UV from webdata group by Site_ID, Dwd_date

2. Create query1,

Select Site_ID, Avg(UV)as AvgUV from query1 group by Site_ID

3. Create query2,

Select Site_ID, count(distinct panel_id) as UV From webdata group by Site_id

4.Use Script Data Set to union query and query2.

var lt = execute(this, SQL, "query");

var rt = execute(this, SQL, "query2"); 

var lkeys = [0];

var rkeys = [0]; 

var lcols = [0,1]; 

var rcols = [1];

join(this, FINAL_JOIN | LEFT_MAIN, LEFT_JOIN, lt, rt, lkeys, rkeys, lcols, rcols);

 

Refresh Metadata and preview to see the required data.