Create an MCPS Query
Creates a MCPSQuery object. The query object can be accessed by uniqueId once created.
MCPSQuery object by uniqueId is passed in /data/query/persist call.
| Request Method | Service URL |
|---|---|
|
|
/api-metadata/v1/{access-key}/metadata/mcpsqueries
|
| Request Headers |
|
Request URL
http://{AccountURL}/api-metadata/v1/e2a7a0df2410470d85ef69c8dad2bdb8/metadata/mcpsqueries
Example
Create MCPS query object.
{
"MCPSQuery": {
"tenantId": 100,
"name": "selectCustomer",
"versionTS": null,
"description": null,
"active": null,
"lastModifiedBy": null,
"createdTS": null,
"uniqueId": "selectCustomer",
"operation": {
"ctype": ".SetOperation",
"name": "TopOp",
"tenantId": 21,
"uniqueId": "selectCustomer",
"operands": [
{
"ctype": ".ObjectSet",
"tenantId": 100,
"name": "C1",
"versionTS": null,
"description": "C1",
"active": null,
"lastModifiedBy": null,
"createdTS": null,
"objectName": "customer",
"uniqueId": "selectCustomerQuery_customer",
"outputAttributes": [
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "SourceID",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "SourceCustomerID",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "SourceAccountID",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "SourceAssociateID",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "SourceSignUpOrganizationID",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "Email",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "AlternateEmails",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "Phone",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "MobilePhone",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "AlternatePhones",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "Gender",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "okToEmail",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "okToCall",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "okToText",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "okToNotify",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "okToMail",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "EmailFrequency",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "Properties",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "RegistrationTS",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "IsActive",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "Status",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "Type",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "FirstName",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "MiddleName",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "Suffix",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "Prefix",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "LastName",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "BirthDate",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "BirthMonth",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "BirthDay",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "BirthYear",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "Age",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "LoyaltyNumber",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "OriginalChannel",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "PrimaryLanguage",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "CreatedBy",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "ModifiedBy",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "CreatedTS",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "ModifiedTS",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "RowCreatedTS",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "RowModifiedTS",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "favoriteBrand",
"alias": null
}
],
"distinct": false,
"criteria": {
"ctype": ".Criteria",
"operator": "OR",
"operands": [
{
"ctype": ".Criteria",
"operator": "NOT_NULL",
"operands": [
{
"ctype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "ID",
"alias": null
}
]
}
]
},
"havings": null,
"groupBy": null,
"sortBy": null,
"occurrence": null,
"objectJoin": null,
"parameters": null
},
null
],
"operator": "UNION",
"distinct": false,
"outputAttributes": [
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "SourceID",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "SourceCustomerID",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "SourceAccountID",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "SourceAssociateID",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "SourceSignUpOrganizationID",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "Email",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "AlternateEmails",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "Phone",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "MobilePhone",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "AlternatePhones",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "Gender",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "okToEmail",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "okToCall",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "okToText",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "okToNotify",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "okToMail",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "EmailFrequency",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "Properties",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "RegistrationTS",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "IsActive",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "Status",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "Type",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "FirstName",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "LastName",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "BirthDate",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "BirthMonth",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "BirthDay",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "BirthYear",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "Age",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "LoyaltyNumber",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "OriginalChannel",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "PrimaryLanguage",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "CreatedBy",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "ModifiedBy",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "CreatedTS",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "ModifiedTS",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "RowCreatedTS",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "RowModifiedTS",
"alias": null
},
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "favoriteBrand",
"alias": null
}
],
"joinConditions": [
]
}
}
}
Example
Create MCPS object and select customers where:
(Gender = Male) and ((State_ in (CA, NY, IL)) or ((Order_Date between 8/1/2015-12/31/2015)) and ((Order SKU_Category = “Car”)) or ((SUM of Order_Basket_Amount between 1/1/2015 to 12/1/2015 is >$2000)) and (Country = US)) or ((AVE of order lines Line_Amount <$100 between 10/1/2015 and 12/31/2015) or (Has clicked on Web/Mobile Product_ID within (iTunes, iWatch) at least once between 3/1/2015 and 9/1/2015))
{
"MCPSQuery": {
"tenantId": 10,
"name": "OMC_QUERY4",
"uniqueId": "complex4",
"operation": {
"ctype": ".SetOperation",
"name": "Aud1",
"tenantId": 10,
"uniqueId": "complex4",
"operands": [{
"ctype": ".SetOperation",
"name": "C1",
"tenantId": 10,
"uniqueId": "complex4_c1",
"operands": [{
"ctype": ".SetOperation",
"name": "C4",
"tenantId": 10,
"uniqueId": "complex4_c4",
"operands": [{
"ctype": ".ObjectSet",
"tenantId": 10,
"name": "C2",
"description": "C2",
"objectName": "Customer",
"uniqueId": "complex4_c2",
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "C2",
"attributeName": "ID"
}],
"distinct": false,
"criteria": {
"ctype": ".Criteria",
"operator": "EQUALS",
"operands": [{
"ctype": ".ReferenceAttribute",
"tableName": "C2",
"attributeName": "Gender"
}, {
"ctype": ".StaticAttribute",
"data": "M"
}]
},
"objectJoin": {
"type": "LEFT",
"condition": [{
"left": {
"atype": ".ReferenceAttribute",
"tableName": "C2",
"attributeName": "ID"
},
"right": {
"atype": ".ReferenceAttribute",
"tableName": "A1",
"attributeName": "CustomerID"
}
}],
"targetSet": {
"ctype": ".ObjectSet",
"tenantId": 10,
"name": "A1",
"description": "A1",
"objectName": "Address",
"uniqueId": "complex4_a1",
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "A1",
"attributeName": "CustomerID"
}],
"distinct": false,
"criteria": {
"ctype": ".Criteria",
"operator": "AND",
"operands": [{
"ctype": ".Criteria",
"operator": "IN",
"operands": [{
"ctype": ".ReferenceAttribute",
"tableName": "A1",
"attributeName": "State"
}, {
"ctype": ".StaticAttribute",
"data": "CA"
}, {
"ctype": ".StaticAttribute",
"data": "NY"
}, {
"ctype": ".StaticAttribute",
"data": "IL"
}]
}, {
"ctype": ".Criteria",
"operator": "EQUALS",
"operands": [{
"ctype": ".ReferenceAttribute",
"tableName": "A1",
"attributeName": "Country"
}, {
"ctype": ".StaticAttribute",
"data": "US"
}]
}]
}
}
}
}],
"operator": "INTERSECTION",
"distinct": true,
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "C2",
"attributeName": "ID"
}],
"joinConditions": [{
"left": {
"atype": ".ReferenceAttribute",
"tableName": "C2",
"attributeName": "ID"
},
"right": {
"atype": ".ReferenceAttribute",
"tableName": "L1",
"attributeName": "c_CustomerID"
}
}]
}, {
"ctype": ".SetOperation",
"name": "OO1",
"tenantId": 10,
"uniqueId": "complex4_oo1",
"operands": [{
"ctype": ".ObjectSet",
"tenantId": 10,
"name": "OD",
"description": "OD",
"objectName": "Order",
"uniqueId": "complex4_od",
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "OD",
"attributeName": "CustomerID"
}],
"distinct": true,
"criteria": {
"ctype": ".Criteria",
"operator": "AND",
"operands": [{
"ctype": ".Criteria",
"operator": "BETWEEN",
"operands": [{
"ctype": ".ReferenceAttribute",
"tableName": "OD",
"attributeName": "OrderEntryTS"
}, {
"ctype": ".StaticAttribute",
"data": 1438412400
}, {
"ctype": ".StaticAttribute",
"data": 1454313600
}]
}, {
"ctype": ".Criteria",
"operator": "GREATER_EQUAL_THAN",
"operands": [{
"ctype": ".ReferenceAttribute",
"tableName": "OD",
"attributeName": "OrderEntryDate"
}, {
"ctype": ".StaticAttribute",
"data": "201508"
}]
}, {
"ctype": ".Criteria",
"operator": "LESS_EQUAL_THAN",
"operands": [{
"ctype": ".ReferenceAttribute",
"tableName": "OD",
"attributeName": "OrderEntryDate"
}, {
"ctype": ".StaticAttribute",
"data": "201602"
}]
}]
}
}, {
"ctype": ".SetOperation",
"name": "OSum",
"tenantId": 10,
"uniqueId": "complex4_osum",
"operands": [{
"ctype": ".ObjectSet",
"tenantId": 10,
"name": "USCust",
"description": "USCust",
"objectName": "Customer",
"uniqueId": "complex4_uscust",
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "USCust",
"attributeName": "ID"
}],
"distinct": false,
"objectJoin": {
"type": "LEFT",
"condition": [{
"left": {
"atype": ".ReferenceAttribute",
"tableName": "USCust",
"attributeName": "ID"
},
"right": {
"atype": ".ReferenceAttribute",
"tableName": "USAddr",
"attributeName": "CustomerID"
}
}],
"targetSet": {
"ctype": ".ObjectSet",
"tenantId": 10,
"name": "USAddr",
"description": "USAddr",
"objectName": "Address",
"uniqueId": "complex4_usaddr",
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "USAddr",
"attributeName": "CustomerID"
}],
"distinct": false,
"criteria": {
"ctype": ".Criteria",
"operator": "EQUALS",
"operands": [{
"ctype": ".ReferenceAttribute",
"tableName": "USAddr",
"attributeName": "Country"
}, {
"ctype": ".StaticAttribute",
"data": "US"
}]
}
}
}
}, {
"ctype": ".SetOperation",
"name": "SUMU",
"tenantId": 10,
"uniqueId": "complex4_sumu",
"operands": [{
"ctype": ".ObjectSet",
"tenantId": 10,
"name": "CustCat",
"description": "CustCat",
"objectName": "Customer",
"uniqueId": "complex4_custcat",
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "CustCat",
"attributeName": "ID"
}],
"distinct": true,
"objectJoin": {
"type": "LEFT",
"condition": [{
"left": {
"atype": ".ReferenceAttribute",
"tableName": "CustCat",
"attributeName": "ID"
},
"right": {
"atype": ".ReferenceAttribute",
"tableName": "OrderItemCat",
"attributeName": "CustomerID"
}
}],
"targetSet": {
"ctype": ".ObjectSet",
"tenantId": 10,
"name": "OrderItemCat",
"description": "OrderItemCat",
"objectName": "OrderItem",
"uniqueId": "complex4_orderitemcat",
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "OrderItemCat",
"attributeName": "CustomerID"
}],
"distinct": false,
"objectJoin": {
"type": "LEFT",
"condition": [{
"left": {
"atype": ".ReferenceAttribute",
"tableName": "OrderItemCat",
"attributeName": "ProductID"
},
"right": {
"atype": ".ReferenceAttribute",
"tableName": "P1",
"attributeName": "ID"
}
}],
"targetSet": {
"ctype": ".ObjectSet",
"tenantId": 10,
"name": "P1",
"description": "P1",
"objectName": "Product",
"uniqueId": "complex4_p1",
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "P1",
"attributeName": "ID"
}],
"distinct": false,
"criteria": {
"ctype": ".Criteria",
"operator": "EQUALS",
"operands": [{
"ctype": ".ReferenceAttribute",
"tableName": "P1",
"attributeName": "Name"
}, {
"ctype": ".StaticAttribute",
"data": "iCar"
}]
}
}
}
}
}
}, {
"ctype": ".ObjectSet",
"tenantId": 10,
"name": "O1",
"description": "O1",
"objectName": "Order",
"uniqueId": "complex4_o1",
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "O1",
"attributeName": "CustomerID",
"alias": "ID"
}],
"distinct": false,
"criteria": {
"ctype": ".Criteria",
"operator": "AND",
"operands": [{
"ctype": ".Criteria",
"operator": "BETWEEN",
"operands": [{
"ctype": ".ReferenceAttribute",
"tableName": "O1",
"attributeName": "OrderEntryTS"
}, {
"ctype": ".StaticAttribute",
"data": 1420099200
}, {
"ctype": ".StaticAttribute",
"data": 1451635140
}]
}, {
"ctype": ".Criteria",
"operator": "GREATER_EQUAL_THAN",
"operands": [{
"ctype": ".ReferenceAttribute",
"tableName": "O1",
"attributeName": "OrderEntryDate"
}, {
"ctype": ".StaticAttribute",
"data": "201501"
}]
}, {
"ctype": ".Criteria",
"operator": "LESS_EQUAL_THAN",
"operands": [{
"ctype": ".ReferenceAttribute",
"tableName": "O1",
"attributeName": "OrderEntryDate"
}, {
"ctype": ".StaticAttribute",
"data": "201512"
}]
}]
},
"havings": {
"ctype": ".Criteria",
"operator": "GREATER_THAN",
"operands": [{
"ctype": ".AggregateAttribute",
"type": "SUM",
"attribute": {
"atype": ".ReferenceAttribute",
"tableName": "O1",
"attributeName": "Total"
}
}, {
"ctype": ".StaticAttribute",
"data": 2000
}]
},
"groupBy": [{
"atype": ".ReferenceAttribute",
"tableName": "O1",
"attributeName": "CustomerID"
}]
}],
"operator": "UNION",
"distinct": true,
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "CustCat",
"attributeName": "ID",
"alias": "CustomerID"
}],
"joinConditions": [{
"left": {
"atype": ".ReferenceAttribute",
"tableName": "CustCat",
"attributeName": "ID"
},
"right": {
"atype": ".ReferenceAttribute",
"tableName": "O1",
"attributeName": "ID"
}
}]
}],
"operator": "INTERSECTION",
"distinct": true,
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "USCust",
"attributeName": "ID"
}],
"joinConditions": [{
"left": {
"atype": ".ReferenceAttribute",
"tableName": "USCust",
"attributeName": "ID"
},
"right": {
"atype": ".ReferenceAttribute",
"tableName": "SUMU",
"attributeName": "CustomerID"
}
}]
}],
"operator": "INTERSECTION",
"distinct": false,
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "OD",
"attributeName": "CustomerID",
"alias": "ID"
}],
"joinConditions": [{
"left": {
"atype": ".ReferenceAttribute",
"tableName": "OD",
"attributeName": "CustomerID"
},
"right": {
"atype": ".ReferenceAttribute",
"tableName": "OSum",
"attributeName": "ID"
}
}]
}],
"operator": "UNION",
"distinct": false,
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "C4",
"attributeName": "ID"
}],
"joinConditions": [{
"left": {
"atype": ".ReferenceAttribute",
"tableName": "C4",
"attributeName": "ID"
},
"right": {
"atype": ".ReferenceAttribute",
"tableName": "OO1",
"attributeName": "ID"
}
}]
}, {
"ctype": ".SetOperation",
"name": "C2",
"tenantId": 10,
"uniqueId": "complex4_c2",
"operands": [{
"ctype": ".ObjectSet",
"tenantId": 10,
"name": "CustAvg",
"description": "CustAvg",
"objectName": "Customer",
"uniqueId": "complex4_custavg",
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "CustAvg",
"attributeName": "ID"
}],
"distinct": true,
"objectJoin": {
"type": "LEFT",
"condition": [{
"left": {
"atype": ".ReferenceAttribute",
"tableName": "CustAvg",
"attributeName": "ID"
},
"right": {
"atype": ".ReferenceAttribute",
"tableName": "OrderItemAvg",
"attributeName": "CustomerID"
}
}],
"targetSet": {
"ctype": ".ObjectSet",
"tenantId": 10,
"name": "OrderItemAvg",
"description": "OrderItemAvg",
"objectName": "OrderItem",
"uniqueId": "complex4_orderitemavg",
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "OrderItemAvg",
"attributeName": "OrderID"
}, {
"atype": ".ReferenceAttribute",
"tableName": "OrderItemAvg",
"attributeName": "CustomerID"
}],
"distinct": false,
"criteria": {
"ctype": ".Criteria",
"operator": "AND",
"operands": [{
"ctype": ".Criteria",
"operator": "BETWEEN",
"operands": [{
"ctype": ".ReferenceAttribute",
"tableName": "OrderItemAvg",
"attributeName": "OrderEntryTS"
}, {
"ctype": ".StaticAttribute",
"data": 1443682800
}, {
"ctype": ".StaticAttribute",
"data": 1451635200
}]
}, {
"ctype": ".Criteria",
"operator": "GREATER_EQUAL_THAN",
"operands": [{
"ctype": ".ReferenceAttribute",
"tableName": "OrderItemAvg",
"attributeName": "OrderEntryDate"
}, {
"ctype": ".StaticAttribute",
"data": "201510"
}]
}, {
"ctype": ".Criteria",
"operator": "LESS_EQUAL_THAN",
"operands": [{
"ctype": ".ReferenceAttribute",
"tableName": "OrderItemAvg",
"attributeName": "OrderEntryDate"
}, {
"ctype": ".StaticAttribute",
"data": "201601"
}]
}]
},
"havings": {
"ctype": ".Criteria",
"operator": "LESS_EQUAL_THAN",
"operands": [{
"ctype": ".AggregateAttribute",
"type": "AVG",
"attribute": {
"atype": ".ReferenceAttribute",
"tableName": "OrderItemAvg",
"attributeName": "ExtendedPrice"
}
}, {
"ctype": ".StaticAttribute",
"data": 100
}]
},
"groupBy": [{
"atype": ".ReferenceAttribute",
"tableName": "OrderItemAvg",
"attributeName": "OrderID"
}, {
"atype": ".ReferenceAttribute",
"tableName": "OrderItemAvg",
"attributeName": "CustomerID"
}]
}
}
}, {
"ctype": ".ObjectSet",
"tenantId": 10,
"name": "CustClick",
"description": "CustClick",
"objectName": "Customer",
"uniqueId": "complex4_custclick",
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "CustClick",
"attributeName": "ID"
}],
"distinct": false,
"objectJoin": {
"type": "LEFT",
"condition": [{
"left": {
"atype": ".ReferenceAttribute",
"tableName": "CustClick",
"attributeName": "ID"
},
"right": {
"atype": ".ReferenceAttribute",
"tableName": "Click2",
"attributeName": "CustomerID"
}
}],
"targetSet": {
"ctype": ".ObjectSet",
"tenantId": 10,
"name": "Click2",
"description": "Click2",
"objectName": "Event",
"uniqueId": "complex4_click2",
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "Click2",
"attributeName": "CustomerID"
}],
"distinct": false,
"criteria": {
"ctype": ".Criteria",
"operator": "AND",
"operands": [{
"ctype": ".Criteria",
"operator": "EQUALS",
"operands": [{
"ctype": ".ReferenceAttribute",
"tableName": "Click2",
"attributeName": "Type"
}, {
"ctype": ".StaticAttribute",
"data": "ProductClick"
}]
}, {
"ctype": ".Criteria",
"operator": "AND",
"operands": [{
"ctype": ".Criteria",
"operator": "BETWEEN",
"operands": [{
"ctype": ".ReferenceAttribute",
"tableName": "Click2",
"attributeName": "EventTS"
}, {
"ctype": ".StaticAttribute",
"data": 1425196800
}, {
"ctype": ".StaticAttribute",
"data": 1441177140
}]
}, {
"ctype": ".Criteria",
"operator": "GREATER_EQUAL_THAN",
"operands": [{
"ctype": ".ReferenceAttribute",
"tableName": "Click2",
"attributeName": "EventDate"
}, {
"ctype": ".StaticAttribute",
"data": "201503"
}]
}, {
"ctype": ".Criteria",
"operator": "LESS_EQUAL_THAN",
"operands": [{
"ctype": ".ReferenceAttribute",
"tableName": "Click2",
"attributeName": "EventDate"
}, {
"ctype": ".StaticAttribute",
"data": "201509"
}]
}]
}]
},
"groupBy": [{
"atype": ".ReferenceAttribute",
"tableName": "Click2",
"attributeName": "CustomerID"
}],
"objectJoin": {
"type": "LEFT",
"condition": [{
"left": {
"atype": ".ReferenceAttribute",
"tableName": "Click2",
"attributeName": "ProductID"
},
"right": {
"atype": ".ReferenceAttribute",
"tableName": "P4",
"attributeName": "ID"
}
}],
"targetSet": {
"ctype": ".ObjectSet",
"tenantId": 10,
"name": "P4",
"description": "P4",
"objectName": "Product",
"uniqueId": "complex4_p4",
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "P4",
"attributeName": "ID"
}],
"distinct": false,
"criteria": {
"ctype": ".Criteria",
"operator": "IN",
"operands": [{
"ctype": ".ReferenceAttribute",
"tableName": "P4",
"attributeName": "Name"
}, {
"ctype": ".StaticAttribute",
"data": "iTunes"
}, {
"ctype": ".StaticAttribute",
"data": "iWatch"
}]
}
}
}
}
}
}],
"operator": "UNION",
"distinct": false,
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "CustAvg",
"attributeName": "ID"
}],
"joinConditions": [{
"left": {
"atype": ".ReferenceAttribute",
"tableName": "CustAvg",
"attributeName": "ID"
},
"right": {
"atype": ".ReferenceAttribute",
"tableName": "CustClick",
"attributeName": "ID"
}
}]
}],
"operator": "UNION",
"distinct": false,
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "ID"
}],
"joinConditions": [{
"left": {
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "ID"
},
"right": {
"atype": ".ReferenceAttribute",
"tableName": "C2",
"attributeName": "ID"
}
}]
}
}
}
SQL Example
Here's the same example above in SQL.
SELECT coalesce(C2.ID, C1.ID) AS ID
FROM
(SELECT coalesce(OO1.ID, C4.ID) AS ID
FROM
(SELECT DISTINCT C2.ID
FROM
(SELECT C2.ID
FROM
mcpsdw_Customer C2
JOIN (SELECT A1.CustomerID
FROM
mcpsdw_Address A1
WHERE ((A1.State IN ('CA', 'NY', 'IL')) AND (A1.Country = 'US'))
) A1 ON C2.ID = A1.CustomerID
WHERE (C2.Gender = 'M')
) C2
) C4
FULL OUTER JOIN (SELECT OD.CustomerID AS ID
FROM
(SELECT DISTINCT OD.CustomerID
FROM
mcpsdw_Order OD
WHERE ((OD.OrderEntryTS BETWEEN 1438412400 AND 1454313600) AND (OD.OrderEntryDate >= '201508') AND (OD.OrderEntryDate <= '201602'))
) OD
JOIN (SELECT DISTINCT USCust.ID
FROM
(SELECT USCust.ID
FROM
mcpsdw_Customer USCust
JOIN (SELECT USAddr.CustomerID
FROM
mcpsdw_Address USAddr
WHERE (USAddr.Country = 'US')
) USAddr ON USCust.ID = USAddr.CustomerID
) USCust
JOIN (SELECT DISTINCT coalesce(O1.ID, CustCat.ID) AS CustomerID
FROM
(SELECT DISTINCT CustCat.ID
FROM
mcpsdw_Customer CustCat
JOIN (SELECT OrderItemCat.CustomerID
FROM
mcpsdw_OrderItem OrderItemCat
JOIN (SELECT P1.ID
FROM
mcpsdw_Product P1
WHERE (P1.Name = 'iCar')
) P1 ON OrderItemCat.ProductID = P1.ID
) OrderItemCat ON CustCat.ID = OrderItemCat.CustomerID
) CustCat
FULL OUTER JOIN (SELECT O1.CustomerID AS ID
FROM
mcpsdw_Order O1
WHERE ((O1.OrderEntryTS BETWEEN 1420099200 AND 1451635140) AND (O1.OrderEntryDate >= '201501') AND (O1.OrderEntryDate <= '201512'))
GROUP BY O1.CustomerID
HAVING (SUM(O1.Total) > 2000)
) O1 ON CustCat.ID = O1.ID
) SUMU ON USCust.ID = SUMU.CustomerID
) OSum ON OD.CustomerID = OSum.ID
) OO1 ON C4.ID = OO1.ID
) C1
FULL OUTER JOIN (SELECT coalesce(CustClick.ID, CustAvg.ID) AS ID
FROM
(SELECT DISTINCT CustAvg.ID
FROM
mcpsdw_Customer CustAvg
JOIN (SELECT OrderItemAvg.OrderID, OrderItemAvg.CustomerID
FROM
mcpsdw_OrderItem OrderItemAvg
WHERE ((OrderItemAvg.OrderEntryTS BETWEEN 1443682800 AND 1451635200) AND (OrderItemAvg.OrderEntryDate >= '201510') AND (OrderItemAvg.OrderEntryDate <= '201601'))
GROUP BY OrderItemAvg.OrderID, OrderItemAvg.CustomerID
HAVING (AVG(OrderItemAvg.ExtendedPrice) <= 100)
) OrderItemAvg ON CustAvg.ID = OrderItemAvg.CustomerID
) CustAvg
FULL OUTER JOIN (SELECT CustClick.ID
FROM
mcpsdw_Customer CustClick
JOIN (SELECT Click2.CustomerID
FROM
mcpsdw_Event Click2
JOIN (SELECT P4.ID
FROM
mcpsdw_Product P4
WHERE (P4.Name IN ('iTunes', 'iWatch'))
) P4 ON Click2.ProductID = P4.ID
WHERE ((Click2.Type = 'ProductClick') AND ((Click2.EventTS BETWEEN 1425196800 AND 1441177140) AND (Click2.EventDate >= '201503') AND (Click2.EventDate <= '201509')))
GROUP BY Click2.CustomerID
) Click2 ON CustClick.ID = Click2.CustomerID
) CustClick ON CustAvg.ID = CustClick.ID
) C2 ON C1.ID = C2.ID
Example
Create MCPS abandoned cart object.
{
"MCPSQuery": {
"tenantId": 25,
"name": "AB_CART",
"uniqueId": "abcart",
"operation": {
"ctype": ".SetOperation",
"name": "AbCart",
"tenantId": 25,
"uniqueId": "abcart",
"operands": [
{
"ctype": ".ObjectSet",
"tenantId": 25,
"name": "C1",
"description": "C1",
"objectName": "Customer",
"uniqueId": "abcart_c1",
"outputAttributes": [
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "ID"
}
],
"distinct": false,
"objectJoin": {
"type": "LEFT",
"condition": [
{
"left": {
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "ID"
},
"right": {
"atype": ".ReferenceAttribute",
"tableName": "E1",
"attributeName": "CustomerID"
}
}
],
"targetSet": {
"ctype": ".ObjectSet",
"tenantId": 25,
"name": "E1",
"description": "E1",
"objectName": "Event",
"uniqueId": "abcart_e1",
"outputAttributes": [
{
"atype": ".ReferenceAttribute",
"tableName": "E1",
"attributeName": "CustomerID"
}
],
"distinct": false,
"criteria": {
"ctype": ".Criteria",
"operator": "AND",
"operands": [
{
"ctype": ".Criteria",
"operator": "EQUALS",
"operands": [
{
"ctype": ".ReferenceAttribute",
"tableName": "E1",
"attributeName": "Type"
},
{
"ctype": ".StaticAttribute",
"data": "AddToCart"
}
]
},
{
"ctype": ".Criteria",
"operator": "BETWEEN",
"operands": [
{
"ctype": ".ReferenceAttribute",
"tableName": "E1",
"attributeName": "EventTS"
},
{
"ctype": ".ParameterValue",
"identifier": "startWindow"
},
{
"ctype": ".ParameterValue",
"identifier": "endWindow"
}
]
}
]
},
"occurrence": {
"type": "LAST",
"partitionAttribute": {
"atype": ".ReferenceAttribute",
"tableName": "E1",
"attributeName": "CustomerID"
},
"limit": 1,
"count": 5,
"orderAttribute": {
"atype": ".ReferenceAttribute",
"tableName": "E1",
"attributeName": "EventTS"
}
},
"parameters": [
{
"identifier": "startWindow",
"type": "TIMESTAMP",
"data": {
"ctype": ".StaticAttribute",
"data": 1422748800000
}
},
{
"identifier": "endWindow",
"type": "TIMESTAMP",
"data": {
"ctype": ".StaticAttribute",
"data": 1448928000000
}
}
]
}
}
},
{
"ctype": ".ObjectSet",
"tenantId": 25,
"name": "C2",
"description": "C2",
"objectName": "Customer",
"uniqueId": "abcart_c2",
"outputAttributes": [
{
"atype": ".ReferenceAttribute",
"tableName": "C2",
"attributeName": "ID"
}
],
"distinct": false,
"objectJoin": {
"type": "LEFT",
"condition": [
{
"left": {
"atype": ".ReferenceAttribute",
"tableName": "C2",
"attributeName": "ID"
},
"right": {
"atype": ".ReferenceAttribute",
"tableName": "E2",
"attributeName": "CustomerID"
}
}
],
"targetSet": {
"ctype": ".ObjectSet",
"tenantId": 25,
"name": "E2",
"description": "E2",
"objectName": "Event",
"uniqueId": "abcart_e2",
"outputAttributes": [
{
"atype": ".ReferenceAttribute",
"tableName": "E2",
"attributeName": "CustomerID"
}
],
"distinct": false,
"criteria": {
"ctype": ".Criteria",
"operator": "GREATER_THAN",
"operands": [
{
"ctype": ".ReferenceAttribute",
"tableName": "E2",
"attributeName": "EventTS"
},
{
"ctype": ".ParameterValue",
"identifier": "idleTime"
}
]
},
"occurrence": {
"type": "LAST",
"partitionAttribute": {
"atype": ".ReferenceAttribute",
"tableName": "E2",
"attributeName": "CustomerID"
},
"limit": 1,
"count": 1,
"orderAttribute": {
"atype": ".ReferenceAttribute",
"tableName": "E2",
"attributeName": "EventTS"
}
},
"parameters": [
{
"identifier": "idleTime",
"type": "TIMESTAMP",
"data": {
"ctype": ".Criteria",
"operator": "MINUS",
"operands": [
{
"ctype": ".StaticAttribute",
"data": "$NOW"
},
{
"ctype": ".StaticAttribute",
"data": 2000000
}
]
}
}
]
}
}
}
],
"operator": "EXCLUSION",
"distinct": false,
"outputAttributes": [
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "ID"
}
],
"joinConditions": [
{
"left": {
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "ID"
},
"right": {
"atype": ".ReferenceAttribute",
"tableName": "C2",
"attributeName": "ID"
}
}
]
}
}
}
SQL Example
Here's the same example above in SQL.
SELECT C1.ID
FROM
(SELECT C1.ID
FROM
mcpsdw_Customer C1
JOIN (SELECT E1.CustomerID
FROM
(SELECT E1.CustomerID, DENSE_RANK() over(partition by E1.CustomerID ORDER BY E1.EventTS DESC) AS system_window, ROW_NUMBER() over(partition by E1.CustomerID ORDER BY E1.EventTS DESC) AS system_window_count
FROM
mcpsdw_Event E1
WHERE ((E1.Type = 'AddToCart') AND (E1.EventTS BETWEEN 1422748800000 AND 1448928000000))
) E1
WHERE ((E1.system_window <= 1) AND (E1.system_window_count <= 5))
) E1 ON C1.ID = E1.CustomerID
) C1
LEFT OUTER JOIN (SELECT C2.ID
FROM
mcpsdw_Customer C2
JOIN (SELECT E2.CustomerID
FROM
(SELECT E2.CustomerID, DENSE_RANK() over(partition by E2.CustomerID ORDER BY E2.EventTS DESC) AS system_window, ROW_NUMBER() over(partition by E2.CustomerID ORDER BY E2.EventTS DESC) AS system_window_count
FROM
mcpsdw_Event E2
WHERE (E2.EventTS > (unix_timestamp() - 2000000))
) E2
WHERE ((E2.system_window <= 1) AND (E2.system_window_count <= 1))
) E2 ON C2.ID = E2.CustomerID
) C2 ON C1.ID = C2.ID
WHERE ((C2.ID IS NULL))
Example
Create MCPS object of a master customer list that have orders with an average discount total greater than $100.
{
"MCPSQuery": {
"tenantId": 10,
"name": "selectCustomerWithOrderDiscount",
"versionTS": null,
"description": null,
"active": null,
"lastModifiedBy": null,
"createdBy": null,
"createdTS": null,
"uniqueId": "selectCustomerWithOrderDiscount",
"operation": {
"ctype": ".SetOperation",
"name": "windowing",
"tenantId": 10,
"uniqueId": "windowing",
"operands": [{
"ctype": ".ObjectSet",
"tenantId": 10,
"name": "C1",
"versionTS": null,
"description": "C1",
"active": null,
"lastModifiedBy": null,
"createdBy": null,
"createdTS": null,
"objectName": "Customer_MasterCustomer",
"uniqueId": "C1",
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "MasterCustomerID",
"alias": null
}, {
"atype": ".AggregateAttribute",
"type": "AVG",
"attribute": {
"atype": ".ReferenceAttribute",
"tableName": "O1",
"attributeName": "DiscountTotal",
"alias": null
},
"functionName": null,
"alias": "averageMcDiscountTotal"
}],
"distinct": false,
"criteria": null,
"havings": {
"ctype": ".Criteria",
"operator": "GREATER_THAN",
"operands": [{
"ctype": ".AggregateAttribute",
"type": "AVG",
"attribute": {
"atype": ".ReferenceAttribute",
"tableName": "O1",
"attributeName": "DiscountTotal",
"alias": null
},
"functionName": null,
"alias": "averageMcDiscountTotal"
}, {
"ctype": ".StaticAttribute",
"data": 100
}]
},
"groupBy": [{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "MasterCustomerID",
"alias": null
}],
"sortBy": null,
"occurrence": null,
"objectJoin": {
"type": "LEFT",
"condition": [{
"left": {
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "CustomerID",
"alias": null
},
"right": {
"atype": ".ReferenceAttribute",
"tableName": "O1",
"attributeName": "CustomerID",
"alias": null
}
}],
"targetSet": {
"ctype": ".ObjectSet",
"tenantId": 10,
"name": "O1",
"versionTS": null,
"description": "O1",
"active": null,
"lastModifiedBy": null,
"createdBy": null,
"createdTS": null,
"objectName": "Order",
"uniqueId": "O1",
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "O1",
"attributeName": "CustomerID",
"alias": null
}, {
"atype": ".ReferenceAttribute",
"tableName": "O1",
"attributeName": "DiscountTotal",
"alias": null
}],
"distinct": false,
"criteria": null,
"havings": null,
"groupBy": null,
"sortBy": null,
"occurrence": null,
"objectJoin": null,
"parameters": null
}
},
"parameters": null
}, null],
"operator": "INTERSECTION",
"distinct": false,
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "MasterCustomerID",
"alias": null
}, {
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "averageMcDiscountTotal",
"alias": null
}],
"joinConditions": []
},
"type": "DW"
}
}
SQL Example
Here's the same example above in SQL.
SELECT C1.MasterCustomerID, C1.averageMcDiscountTotal
FROM
(SELECT C1.MasterCustomerID, AVG(O1.DiscountTotal) AS averageMcDiscountTotal
FROM
mcpsdw_Customer_MasterCustomer C1
JOIN (SELECT O1.CustomerID, O1.DiscountTotal
FROM
mcpsdw_Order O1 ) O1 ON C1.CustomerID = O1.CustomerID GROUP BY C1.MasterCustomerID
HAVING (AVG(O1.DiscountTotal) > 100)
) C1