本节列出了在数据湖中查询历程步骤事件的几个常用示例。
利用此查询,可列出执行消息/操作时在历程中遇到的每个错误。
数据湖查询
SELECT _experience.journeyOrchestration.stepEvents.actionExecutionError, count(distinct _id) FROM journey_step_events
WHERE _experience.journeyOrchestration.stepEvents.nodeName=<'message-name'>
AND _experience.journeyOrchestration.stepEvents.actionExecutionError IS NOT NULL
AND _experience.journeyOrchestration.stepEvents.journeyVersionID = '<journey-version-id>'
GROUP BY _experience.journeyOrchestration.stepEvents.actionExecutionError
示例
SELECT _experience.journeyOrchestration.stepEvents.actionExecutionError, count(distinct _id) FROM journey_step_events
WHERE _experience.journeyOrchestration.stepEvents.nodeName='Message - 100KB Email with Gateway and Kafkav2'
AND _experience.journeyOrchestration.stepEvents.actionExecutionError IS NOT NULL
AND _experience.journeyOrchestration.stepEvents.journeyVersionID = '67b14482-143e-4f83-9cf5-cfec0fca3d26'
GROUP BY _experience.journeyOrchestration.stepEvents.actionExecutionError
此查询返回在历程中执行操作时发生的所有不同错误以及发生次数的计数。
数据湖查询
SELECT count(distinct _id) FROM journey_step_events
where
_experience.journeyOrchestration.stepEvents.journeyVersionID = '<journey-version-id>' AND
_experience.journeyOrchestration.stepEvents.profileID = '<profileID corresponding to the namespace used>'
示例
SELECT count(distinct _id) FROM journey_step_events
where
_experience.journeyOrchestration.stepEvents.journeyVersionID = 'ec9efdd0-8a7c-4d7a-a765-b2cad659fa4e' AND
_experience.journeyOrchestration.stepEvents.profileID = 'saurgarg@adobe.com'
结果应大于0。 此查询会返回用户档案进入历程的确切次数。
方法1: 如果消息的名称在历程中并非唯一(在多个位置使用)。
数据湖查询
SELECT count(distinct _id) FROM journey_step_events WHERE
_experience.journeyOrchestration.stepEvents.nodeID='<NodeId in the UI corresponding to the message>' AND
_experience.journeyOrchestration.stepEvents.actionExecutionError IS NULL AND
_experience.journeyOrchestration.stepEvents.journeyVersionID = '<journey-version-id>' AND
_experience.journeyOrchestration.stepEvents.profileID = '<profileID corresponding to the namespace used>'
示例
SELECT count(distinct _id) FROM journey_step_events WHERE
_experience.journeyOrchestration.stepEvents.nodeID='17ae65a1-02dd-439d-b54e-b56a78520eba' AND
_experience.journeyOrchestration.stepEvents.actionExecutionError IS NULL AND
_experience.journeyOrchestration.stepEvents.journeyVersionID = '67b14482-143e-4f83-9cf5-cfec0fca3d26' AND
_experience.journeyOrchestration.stepEvents.profileID = 'saurgarg@adobe.com'
结果应大于0。 此查询仅告知我们消息操作是否在历程端成功执行。
方法2: 如果消息的名称在历程中是唯一的。
数据湖查询
SELECT count(distinct _id) FROM journey_step_events WHERE
_experience.journeyOrchestration.stepEvents.nodeName='<NodeName in the UI corresponding to the message>' AND
_experience.journeyOrchestration.stepEvents.actionExecutionError IS NULL AND
_experience.journeyOrchestration.stepEvents.journeyVersionID = '<journey-version-id>' AND
_experience.journeyOrchestration.stepEvents.profileID = '<profileID corresponding to the namespace used>'
示例
SELECT count(distinct _id) FROM journey_step_events WHERE
_experience.journeyOrchestration.stepEvents.nodeID='Message- 100KB Email' AND
_experience.journeyOrchestration.stepEvents.actionExecutionError IS NULL AND
_experience.journeyOrchestration.stepEvents.journeyVersionID = '67b14482-143e-4f83-9cf5-cfec0fca3d26' AND
_experience.journeyOrchestration.stepEvents.profileID = 'saurgarg@adobe.com'
查询会返回所有消息的列表以及为所选用户档案调用的消息计数。
数据湖查询
SELECT _experience.journeyOrchestration.stepEvents.nodeName, count(distinct _id) FROM journey_step_events
WHERE _experience.journeyOrchestration.stepEvents.actionExecutionError IS NULL AND
_experience.journeyOrchestration.stepEvents.nodeType = 'action' AND
_experience.journeyOrchestration.stepEvents.profileID = '<profileID corresponding to the namespace used>' AND
timestamp > (now() - interval '30' day)
GROUP BY _experience.journeyOrchestration.stepEvents.nodeName
示例
SELECT _experience.journeyOrchestration.stepEvents.nodeName, count(distinct _id) FROM journey_step_events
WHERE _experience.journeyOrchestration.stepEvents.actionExecutionError IS NULL AND
_experience.journeyOrchestration.stepEvents.nodeType = 'action' AND
_experience.journeyOrchestration.stepEvents.profileID = 'saurgarg@adobe.com' AND
timestamp > (now() - interval '30' day)
GROUP BY _experience.journeyOrchestration.stepEvents.nodeName
查询会返回所有消息的列表以及为所选用户档案调用的消息计数。
数据湖查询
SELECT _experience.journeyOrchestration.stepEvents.journeyVersionName, count(distinct _id) FROM journey_step_events
WHERE _experience.journeyOrchestration.stepEvents.nodeType = 'start' AND
_experience.journeyOrchestration.stepEvents.profileID = '<profileID corresponding to the namespace used>' AND
timestamp > (now() - interval '30' day)
GROUP BY _experience.journeyOrchestration.stepEvents.journeyVersionName
示例
SELECT _experience.journeyOrchestration.stepEvents.journeyVersionName, count(distinct _id) FROM journey_step_events
WHERE _experience.journeyOrchestration.stepEvents.nodeType = 'start' AND
_experience.journeyOrchestration.stepEvents.profileID = 'saurgarg@adobe.com' AND
timestamp > (now() - interval '30' day)
GROUP BY _experience.journeyOrchestration.stepEvents.journeyVersionName
查询会返回所有历程名称的列表以及查询的用户档案进入历程的次数。
数据湖查询
SELECT DATE(timestamp), count(distinct _experience.journeyOrchestration.stepEvents.profileID) FROM journey_step_events
WHERE DATE(timestamp) > (now() - interval '<last x days>' day)
AND _experience.journeyOrchestration.stepEvents.journeyVersionID = '<journey-version-id>'
GROUP BY DATE(timestamp)
ORDER BY DATE(timestamp) desc
示例
SELECT DATE(timestamp), count(distinct _experience.journeyOrchestration.stepEvents.profileID) FROM journey_step_events
WHERE DATE(timestamp) > (now() - interval '100' day)
AND _experience.journeyOrchestration.stepEvents.journeyVersionID = '180ad071-d42d-42bb-8724-2a6ff0a109f1'
GROUP BY DATE(timestamp)
ORDER BY DATE(timestamp) desc
在定义的时间段内,查询会返回每天进入历程的用户档案数。 如果通过多个身份输入的用户档案,则计为两次。 如果启用了重新进入,则如果用户档案计数在不同的日期重新进入历程,则该计数可能会在不同的日期重复。
数据湖查询
select DATEDIFF (minute,
(select timestamp
where
_experience.journeyOrchestration.journey.versionID = '<journey-version-id>' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.status = 'queued') ,
(select timestamp
where
_experience.journeyOrchestration.journey.versionID = '<journey-version-id>' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.status = 'finished')) AS export_job_runtime;
示例
select DATEDIFF (minute,
(select timestamp
where
_experience.journeyOrchestration.journey.versionID = '180ad071-d42d-42bb-8724-2a6ff0a109f1' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.status = 'queued') ,
(select timestamp
where
_experience.journeyOrchestration.journey.versionID = '180ad071-d42d-42bb-8724-2a6ff0a109f1' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.status = 'finished')) AS export_job_runtime;
查询会返回区段导出作业排队和最终结束之间的时间差(以分钟为单位)。
数据湖查询
SELECT count(distinct _experience.journeyOrchestration.profile.ID) FROM journey_step_events
where
_experience.journeyOrchestration.journey.versionID = '<journey-version-id>' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode = 'ERROR_INSTANCE_DUPLICATION'
示例
SELECT count(distinct _experience.journeyOrchestration.profile.ID) FROM journey_step_events
where
_experience.journeyOrchestration.journey.versionID = '180ad071-d42d-42bb-8724-2a6ff0a109f1' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode = 'ERROR_INSTANCE_DUPLICATION'
该查询会返回历程丢弃的所有配置文件Id,因为它们是重复项。
数据湖查询
SELECT count(*) FROM journey_step_events
where
_experience.journeyOrchestration.journey.versionID = '<journey-version-id>' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode = 'ERROR_INSTANCE_BAD_NAMESPACE'
示例
SELECT count(*) FROM journey_step_events
where
_experience.journeyOrchestration.journey.versionID = '180ad071-d42d-42bb-8724-2a6ff0a109f1' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode = 'ERROR_INSTANCE_BAD_NAMESPACE'
查询会返回历程丢弃的所有配置文件Id,因为它们具有无效的命名空间或没有该命名空间的标识。
数据湖查询
SELECT count(*) FROM journey_step_events
where
_experience.journeyOrchestration.journey.versionID = '<journey-version-id>' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode = 'ERROR_INSTANCE_NO_IDENTITY_MAP'
示例
SELECT count(*) FROM journey_step_events
where
_experience.journeyOrchestration.journey.versionID = '180ad071-d42d-42bb-8724-2a6ff0a109f1' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode = 'ERROR_INSTANCE_NO_IDENTITY_MAP'
该查询会返回由于缺少身份映射,导致历程丢弃的所有配置文件Id。
数据湖查询
SELECT count(distinct _experience.journeyOrchestration.profile.ID) FROM journey_step_events
where
_experience.journeyOrchestration.journey.versionID = '<journey-version-id>' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode = 'ERROR_INSTANCE_NOT_A_TEST_PROFILE'
示例
SELECT count(distinct _experience.journeyOrchestration.profile.ID) FROM journey_step_events
where
_experience.journeyOrchestration.journey.versionID = '180ad071-d42d-42bb-8724-2a6ff0a109f1' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode = 'ERROR_INSTANCE_NOT_A_TEST_PROFILE'
查询会返回历程丢弃的所有配置文件Id,因为导出作业在测试模式下运行,但配置文件未将testProfile属性设置为true。
数据湖查询
SELECT count(distinct _experience.journeyOrchestration.profile.ID) FROM journey_step_events
where
_experience.journeyOrchestration.journey.versionID = '<journey-version-id>' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode = 'ERROR_INSTANCE_INTERNAL'
示例
SELECT count(distinct _experience.journeyOrchestration.profile.ID) FROM journey_step_events
where
_experience.journeyOrchestration.journey.versionID = '180ad071-d42d-42bb-8724-2a6ff0a109f1' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode = 'ERROR_INSTANCE_INTERNAL'
查询会返回由于某些内部错误而被历程丢弃的所有配置文件Id。
数据湖查询
SELECT
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode AS EVENT_CODE,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportSegmentID AS SEGMENT_ID,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.ID AS EXPORTJOB_ID,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.status AS EXPORTJOB_STATUS,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportCountTotal AS TOTAL_EXPORTED_PROFILES_COUNT,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportCountRealized AS SUCCESS_EXPORTED_PROFILES_COUNT,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportCountFailed AS FAILED_EXPORTED_PROFILES_COUNT
FROM
journey_step_events
WHERE
_experience.journeyOrchestration.journey.versionID = '<journey-version-id>' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventType = 'segmenttrigger-orchestrator'
它将返回与给定历程版本相关的所有服务事件。 我们可以遵循一系列操作:
我们还可以检测以下问题:
重要信息:如果此查询未返回任何事件,则可能是由于以下原因之一所致:
数据湖查询
SELECT
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode AS EVENT_CODE,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportSegmentID AS SEGMENT_ID,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.ID AS EXPORTJOB_ID,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.status AS EXPORTJOB_STATUS,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportCountTotal AS TOTAL_EXPORTED_PROFILES_COUNT,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportCountRealized AS SUCCESS_EXPORTED_PROFILES_COUNT,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportCountFailed AS FAILED_EXPORTED_PROFILES_COUNT
FROM
journey_step_events
WHERE
_experience.journeyOrchestration.journey.versionID = '<journey-version-id>' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventType = 'segmenttrigger-orchestrator' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode IN (
'ERROR_TOPIC_CREATION',
'ERROR_EXPORTJOB_APICALL',
'ERROR_EXPORTJOB_APICALL_TIMEOUT',
'ERROR_EXPORTJOB_FAILED'
)
数据湖查询
SELECT
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode AS EVENT_CODE,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportSegmentID AS SEGMENT_ID,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.ID AS EXPORTJOB_ID,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.status AS EXPORTJOB_STATUS,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportCountTotal AS TOTAL_EXPORTED_PROFILES_COUNT,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportCountRealized AS SUCCESS_EXPORTED_PROFILES_COUNT,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportCountFailed AS FAILED_EXPORTED_PROFILES_COUNT
FROM
journey_step_events
WHERE
_experience.journeyOrchestration.journey.versionID = '<journey-version-id>' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventType = 'segmenttrigger-orchestrator' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode IN (
'INFO_EXPORTJOB_SUCCEEDED',
'ERROR_EXPORTJOB_FAILED'
)
如果未返回任何记录,则表示:
数据湖查询
WITH
DISCARDED_EXPORTED_PROFILES AS (
SELECT
_experience.journeyOrchestration.serviceEvents.segmentExportJob.ID AS EXPORTJOB_ID,
count(distinct _experience.journeyOrchestration.profile.ID) AS DISCARDED_PROFILES_COUNT
FROM
journey_step_events
WHERE
_experience.journeyOrchestration.journey.versionID = '<journey-version-id>' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode IN (
'ERROR_INSTANCE_DUPLICATION',
'ERROR_INSTANCE_BAD_NAMESPACE',
'ERROR_INSTANCE_NO_IDENTITY_MAP',
'ERROR_INSTANCE_NOT_A_TEST_PROFILE',
'ERROR_INSTANCE_INTERNAL'
)
GROUP BY
_experience.journeyOrchestration.serviceEvents.segmentExportJob.ID
),
SEGMENT_EXPORT_METRICS AS (
SELECT
_experience.journeyOrchestration.serviceEvents.segmentExportJob.ID AS EXPORTJOB_ID,
sum(_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportCountTotal) AS TOTAL_EXPORTED_PROFILES_COUNT,
sum(_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportCountRealized) AS SUCCESS_EXPORTED_PROFILES_COUNT,
sum(_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportCountFailed) AS FAILED_EXPORTED_PROFILES_COUNT
FROM
journey_step_events
WHERE
_experience.journeyOrchestration.journey.versionID = '<journey-version-id>' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventType = 'segmenttrigger-orchestrator' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode IN (
'INFO_EXPORTJOB_SUCCEEDED',
'ERROR_EXPORTJOB_FAILED'
)
GROUP BY
_experience.journeyOrchestration.serviceEvents.segmentExportJob.ID
)
SELECT
sum(T2.TOTAL_EXPORTED_PROFILES_COUNT),
sum(T2.SUCCESS_EXPORTED_PROFILES_COUNT),
sum(T2.FAILED_EXPORTED_PROFILES_COUNT),
sum(T1.DISCARDED_PROFILES_COUNT)
FROM
DISCARDED_EXPORTED_PROFILES AS T1,
SEGMENT_EXPORT_METRICS AS T2
WHERE T1.EXPORTJOB_ID = T2.EXPORTJOB_ID
数据湖查询
WITH
DISCARDED_EXPORTED_PROFILES AS (
SELECT
_experience.journeyOrchestration.journey.versionID AS JOURNEYVERSION_ID,
count(distinct _experience.journeyOrchestration.profile.ID) AS DISCARDED_PROFILES_COUNT
FROM
journey_step_events
WHERE
_experience.journeyOrchestration.journey.versionID = '<journey-version-id>' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode IN (
'ERROR_INSTANCE_DUPLICATION',
'ERROR_INSTANCE_BAD_NAMESPACE',
'ERROR_INSTANCE_NO_IDENTITY_MAP',
'ERROR_INSTANCE_NOT_A_TEST_PROFILE',
'ERROR_INSTANCE_INTERNAL'
)
GROUP BY
_experience.journeyOrchestration.journey.versionID
),
SEGMENT_EXPORT_METRICS AS (
SELECT
_experience.journeyOrchestration.journey.versionID AS JOURNEYVERSION_ID,
sum(_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportCountTotal) AS TOTAL_EXPORTED_PROFILES_COUNT,
sum(_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportCountRealized) AS SUCCESS_EXPORTED_PROFILES_COUNT,
sum(_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportCountFailed) AS FAILED_EXPORTED_PROFILES_COUNT
FROM
journey_step_events
WHERE
_experience.journeyOrchestration.journey.versionID = '<journey-version-id>' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventType = 'segmenttrigger-orchestrator' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode IN (
'INFO_EXPORTJOB_SUCCEEDED',
'ERROR_EXPORTJOB_FAILED'
)
GROUP BY
_experience.journeyOrchestration.journey.versionID
)
SELECT
sum(T2.TOTAL_EXPORTED_PROFILES_COUNT),
sum(T2.SUCCESS_EXPORTED_PROFILES_COUNT),
sum(T2.FAILED_EXPORTED_PROFILES_COUNT),
sum(T1.DISCARDED_PROFILES_COUNT)
FROM
DISCARDED_EXPORTED_PROFILES AS T1,
SEGMENT_EXPORT_METRICS AS T2
WHERE T1.JOURNEYVERSION_ID = T2.JOURNEYVERSION_ID
此查询与上一个查询不同。
它会返回给定历程版本的整体量度,而不考虑可为其运行的作业(如果是定期历程,则业务事件会触发利用主题重复利用的事件)。
数据湖查询
SELECT count(distinct _experience.journeyOrchestration.profile.ID) FROM journey_step_events
where
_experience.journeyOrchestration.journey.versionID = '<journey-version-id>' AND
_experience.journeyOrchestration.serviceEvents.dispatcher.eventCode = 'ERROR_INSTANCE_WRONG_SEGMENT_REALIZATION'
示例
SELECT count(distinct _experience.journeyOrchestration.profile.ID) FROM journey_step_events
where
_experience.journeyOrchestration.journey.versionID = '180ad071-d42d-42bb-8724-2a6ff0a109f1' AND
_experience.journeyOrchestration.serviceEvents.dispatcher.eventCode = 'ERROR_INSTANCE_WRONG_SEGMENT_REALIZATION'
此查询会返回由于区段实现错误而被历程版本丢弃的所有配置文件Id。
数据湖查询
SELECT DATE(timestamp), count(distinct _id)
FROM journey_step_events
where
_experience.journeyOrchestration.stepEvents.journeyVersionID = '<journey-version-id>' AND
_experience.journeyOrchestration.stepEvents.nodeName = '<node-name-corresponding-to-business-event>' AND
_experience.journeyOrchestration.stepEvents.nodeType = 'start' AND
WHERE DATE(timestamp) > (now() - interval '<last x hours>' hour)
示例
SELECT DATE(timestamp), count(distinct _id)
FROM journey_step_events
where
_experience.journeyOrchestration.stepEvents.journeyVersionID = 'b1093bd4-11f3-44cc-961e-33925cc58e18' AND
_experience.journeyOrchestration.stepEvents.nodeName = 'TEST_MLTrainingSession' AND
_experience.journeyOrchestration.stepEvents.nodeType = 'start' AND
WHERE DATE(timestamp) > (now() - interval '6' hour)
数据湖查询
SELECT DATE(timestamp), count(distinct _experience.journeyOrchestration.stepEvents.journeyVersionID) FROM journey_step_events
WHERE DATE(timestamp) > (now() - interval '<last x days>' day)
GROUP BY DATE(timestamp)
ORDER BY DATE(timestamp) desc
示例
SELECT DATE(timestamp), count(distinct _experience.journeyOrchestration.stepEvents.journeyVersionID) FROM journey_step_events
WHERE DATE(timestamp) > (now() - interval '100' day)
GROUP BY DATE(timestamp)
ORDER BY DATE(timestamp) desc
在定义的时段内,查询会返回每天触发的唯一历程计数。 在多天触发的单个历程将每天计数一次。
数据湖查询
WITH
INSTANCES_PASSED_IN_ALL_NODES_WITH_DETAILS AS (
SELECT
STEP_EVENTS.timestamp AS TS,
STEP_EVENTS._experience.journeyOrchestration.stepEvents.nodeName AS NODE_NAME,
STEP_EVENTS._experience.journeyOrchestration.stepEvents.instanceID AS ID
FROM
journey_step_events AS STEP_EVENTS
WHERE
STEP_EVENTS._experience.journeyOrchestration.stepEvents.journeyVersionName = '<journey version name>'
),
INSTANCES_PASSED_IN_NODE_WITH_DETAILS AS (
SELECT
T1.TS AS TS,
T1.ID AS ID
FROM
INSTANCES_PASSED_IN_ALL_NODES_WITH_DETAILS AS T1
WHERE
T1.NODE_NAME = '<specific node name>' AND
<filter on time for profile in specific node>
),
INSTANCES_PASSED_IN_NEXT_NODES AS (
SELECT
T1.TS AS TS,
T1.ID AS ID
FROM
INSTANCES_PASSED_IN_ALL_NODES_WITH_DETAILS AS T1
WHERE
T1.NODE_NAME in (<list of next node names from the specific node>)
),
INSTANCES_PASSED_IN_NODE_NOT_PASSED_IN_NODES AS (
SELECT
distinct T1.ID AS ID
FROM
INSTANCES_PASSED_IN_NODE_WITH_DETAILS AS T1
EXCEPT
SELECT
distinct T1.ID AS ID
FROM
INSTANCES_PASSED_IN_NEXT_NODES AS T1
)
SELECT
DATE_FORMAT(T1.TS,'<date pattern>') AS DATETIME,
count(T1.ID) AS INSTANCES_COUNT
FROM
INSTANCES_PASSED_IN_NODE_WITH_DETAILS AS T1,
INSTANCES_PASSED_IN_NODE_NOT_PASSED_IN_NODES AS T2
WHERE
T1.ID = T2.ID
GROUP BY
DATETIME
ORDER BY
DATETIME DESC
示例
WITH
INSTANCES_PASSED_IN_ALL_NODES_WITH_DETAILS AS (
SELECT
STEP_EVENTS.timestamp AS TS,
STEP_EVENTS._experience.journeyOrchestration.stepEvents.nodeName AS NODE_NAME,
STEP_EVENTS._experience.journeyOrchestration.stepEvents.instanceID AS ID
FROM
journey_step_events AS STEP_EVENTS
WHERE
STEP_EVENTS._experience.journeyOrchestration.stepEvents.journeyVersionName = 'Journey20009'
),
INSTANCES_PASSED_IN_NODE_WITH_DETAILS AS (
SELECT
T1.TS AS TS,
T1.ID AS ID
FROM
INSTANCES_PASSED_IN_ALL_NODES_WITH_DETAILS AS T1
WHERE
T1.NODE_NAME = 'slack_bso_tests - test1' AND
T1.TS > (now() - interval '18 hour')
),
INSTANCES_PASSED_IN_NEXT_NODES AS (
SELECT
T1.TS AS TS,
T1.ID AS ID
FROM
INSTANCES_PASSED_IN_ALL_NODES_WITH_DETAILS AS T1
WHERE
T1.NODE_NAME in ('slack_bso_tests - test2')
),
INSTANCES_PASSED_IN_NODE_NOT_PASSED_IN_NODES AS (
SELECT
distinct T1.ID AS ID
FROM
INSTANCES_PASSED_IN_NODE_WITH_DETAILS AS T1
EXCEPT
SELECT
distinct T1.ID AS ID
FROM
INSTANCES_PASSED_IN_NEXT_NODES AS T1
)
SELECT
DATE_FORMAT(T1.TS,'yyyy/MM/dd HH:mm') AS DATETIME,
count(T1.ID) AS INSTANCES_COUNT
FROM
INSTANCES_PASSED_IN_NODE_WITH_DETAILS AS T1,
INSTANCES_PASSED_IN_NODE_NOT_PASSED_IN_NODES AS T2
WHERE
T1.ID = T2.ID
GROUP BY
DATETIME
ORDER BY
DATETIME DESC
数据湖查询
SELECT
DATE_FORMAT(STEP_EVENTS.timestamp,'yyyy/MM/dd HH:mm') AS DATETIME,
count(STEP_EVENTS._experience.journeyOrchestration.stepEvents.instanceID) AS EXITED_INSTANCES_COUNT
FROM
journey_step_events AS STEP_EVENTS
WHERE
STEP_EVENTS._experience.journeyOrchestration.stepEvents.journeyVersionName = '<journey version name>' AND
STEP_EVENTS._experience.journeyOrchestration.stepEvents.stepStatus in ('endStep', 'error', 'timedOut', 'cappingError') AND
<timestamp filter>
GROUP BY
DATETIME
ORDER BY
DATETIME DESC
示例
SELECT
DATE_FORMAT(STEP_EVENTS.timestamp,'yyyy/MM/dd HH:mm') AS DATETIME,
count(STEP_EVENTS._experience.journeyOrchestration.stepEvents.instanceID) AS EXITED_INSTANCES_COUNT
FROM
journey_step_events AS STEP_EVENTS
WHERE
STEP_EVENTS._experience.journeyOrchestration.stepEvents.journeyVersionName = 'Journey20009' AND
STEP_EVENTS._experience.journeyOrchestration.stepEvents.stepStatus in ('endStep', 'error', 'timedOut', 'cappingError') AND
STEP_EVENTS.timestamp > (now() - interval '22 hour')
GROUP BY
DATETIME
ORDER BY
DATETIME DESC
数据湖查询
SELECT
DATE_FORMAT(STEP_EVENTS.timestamp,'yyyy/MM/dd HH:mm') AS DATETIME,
STEP_EVENTS._experience.journeyOrchestration.stepEvents.nodeName AS NODE_NAME,
STEP_EVENTS._experience.journeyOrchestration.stepEvents.stepStatus AS EXIT_STATUS,
count(STEP_EVENTS._experience.journeyOrchestration.stepEvents.instanceID) AS EXITED_INSTANCES_COUNT
FROM
journey_step_events AS STEP_EVENTS
WHERE
STEP_EVENTS._experience.journeyOrchestration.stepEvents.journeyVersionName = '<journey version name>' AND
STEP_EVENTS._experience.journeyOrchestration.stepEvents.stepStatus in ('endStep', 'error', 'timedOut', 'cappingError') AND
<timestamp filter>
GROUP BY
DATETIME, NODE_NAME, EXIT_STATUS
ORDER BY
DATETIME DESC
示例
SELECT
DATE_FORMAT(STEP_EVENTS.timestamp,'yyyy/MM/dd HH:mm') AS DATETIME,
STEP_EVENTS._experience.journeyOrchestration.stepEvents.nodeName AS NODE_NAME,
STEP_EVENTS._experience.journeyOrchestration.stepEvents.stepStatus AS EXIT_STATUS,
count(STEP_EVENTS._experience.journeyOrchestration.stepEvents.instanceID) AS EXITED_INSTANCES_COUNT
FROM
journey_step_events AS STEP_EVENTS
WHERE
STEP_EVENTS._experience.journeyOrchestration.stepEvents.journeyVersionName = 'Journey20009' AND
STEP_EVENTS._experience.journeyOrchestration.stepEvents.stepStatus in ('endStep', 'error', 'timedOut', 'cappingError') AND
STEP_EVENTS.timestamp > (now() - interval '22 hour')
GROUP BY
DATETIME, NODE_NAME, EXIT_STATUS
ORDER BY
DATETIME DESC