쿼리 서비스의 Adobe 정의 SQL 함수
여기에서 Adobe 정의 함수(ADF라고 함)는 Adobe Experience Platform Query Service에 사전 작성된 함수로, 일반적인 비즈니스 관련 작업을 수행할 수 있도록 도와줍니다 Experience Event 데이터. 여기에는 다음의 함수가 포함됩니다. 세션화 및 속성 Adobe Analytics에서 발견된 것과 비슷합니다.
이 문서는에서 사용할 수 있는 Adobe 정의 함수에 대한 정보를 제공합니다. Query Service.
창 함수 window-functions
대부분의 비즈니스 논리는 고객의 접점을 수집하고 시간별로 주문해야 합니다. 이 지원은에서 제공합니다. Spark 창 함수 형식의 SQL. 창 함수는 표준 SQL의 일부이며 다른 여러 SQL 엔진에서 지원됩니다.
창 함수는 합계를 갱신하고 정렬된 하위 집합의 각 행에 대해 단일 항목을 반환합니다. 가장 기본적인 집계 함수는 SUM()
. SUM()
는 행을 가져와서 합계 하나를 제공합니다. 대신 신청하시면 SUM()
이를 창 함수로 변환하면 각 행의 누적 합계가 표시됩니다.
의 대다수 Spark SQL 도우미는 해당 행의 상태가 추가된 상태에서 창의 각 행을 업데이트하는 창 함수입니다.
쿼리 구문
OVER ({PARTITION} {ORDER} {FRAME})
{PARTITION}
PARTITION BY endUserIds._experience.mcid.id
{ORDER}
ORDER BY timestamp
{FRAME}
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
세션화
을 사용하여 작업할 때 Experience Event 웹 사이트, 모바일 애플리케이션, 대화형 음성 응답 시스템 또는 기타 고객 상호 작용 채널에서 시작된 데이터로, 관련 활동 기간에 이벤트를 그룹화할 수 있는 경우 도움이 됩니다. 일반적으로 제품 조사, 청구서 결제, 계좌 잔액 확인, 애플리케이션 작성 등과 같은 활동을 유도하는 특정 의도가 있습니다.
이러한 그룹화 또는 데이터 세션화는 이벤트를 연결하여 고객 경험에 대한 더 많은 컨텍스트를 발견하는 데 도움이 됩니다.
Adobe Analytics의 세션화에 대한 자세한 내용은 컨텍스트 인식 세션.
쿼리 구문
SESS_TIMEOUT({TIMESTAMP}, {EXPIRATION_IN_SECONDS}) OVER ({PARTITION} {ORDER} {FRAME})
{TIMESTAMP}
{EXPIRATION_IN_SECONDS}
내의 매개변수에 대한 설명 OVER()
함수는에서 찾을 수 있습니다. 창 기능 섹션.
예제 쿼리
SELECT
endUserIds._experience.mcid.id as id,
timestamp,
SESS_TIMEOUT(timestamp, 60 * 30)
OVER (PARTITION BY endUserIds._experience.mcid.id
ORDER BY timestamp
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS session
FROM experience_events
ORDER BY id, timestamp ASC
LIMIT 10
결과
id | timestamp | session
----------------------------------+-----------------------+--------------------
100080F22A45CB40-3A2B7A8E11096B6 | 2018-01-18 06:55:53.0 | (0,1,true,1)
100080F22A45CB40-3A2B7A8E11096B6 | 2018-01-18 06:56:51.0 | (58,1,false,2)
100080F22A45CB40-3A2B7A8E11096B6 | 2018-01-18 06:57:47.0 | (56,1,false,3)
100080F22A45CB40-3A2B7A8E11096B6 | 2018-01-18 06:58:27.0 | (40,1,false,4)
100080F22A45CB40-3A2B7A8E11096B6 | 2018-01-18 06:59:22.0 | (55,1,false,5)
100080F22A45CB40-3A2B7A8E11096B6 | 2018-02-03 01:16:23.0 | (1361821,2,true,1)
100080F22A45CB40-3A2B7A8E11096B6 | 2018-02-03 01:17:17.0 | (54,2,false,2)
100080F22A45CB40-3A2B7A8E11096B6 | 2018-02-03 01:18:06.0 | (49,2,false,3)
100080F22A45CB40-3A2B7A8E11096B6 | 2018-02-03 01:18:39.0 | (33,2,false,4)
100080F22A45CB40-3A2B7A8E11096B6 | 2018-02-03 01:19:10.0 | (31,2,false,5)
(10 rows)
제공된 샘플 쿼리에 대한 결과는에 제공됩니다. session
열. 다음 session
열은 다음 구성 요소로 구성됩니다.
({TIMESTAMP_DIFF}, {NUM}, {IS_NEW}, {DEPTH})
{TIMESTAMP_DIFF}
{NUM}
PARTITION BY
/ 창 함수.{IS_NEW}
{DEPTH}
SESS_START_IF
이 쿼리는 현재 타임스탬프 및 지정된 표현식을 기반으로 현재 행에 대한 세션 상태를 반환하고 현재 행으로 새 세션을 시작합니다.
쿼리 구문
SESS_START_IF({TIMESTAMP}, {TEST_EXPRESSION}) OVER ({PARTITION} {ORDER} {FRAME})
{TIMESTAMP}
{TEST_EXPRESSION}
application.launches > 0
.내의 매개변수에 대한 설명 OVER()
함수는에서 찾을 수 있습니다. 창 기능 섹션.
예제 쿼리
SELECT
endUserIds._experience.mcid.id AS id,
timestamp,
IF(application.launches.value > 0, true, false) AS isLaunch,
SESS_START_IF(timestamp, application.launches.value > 0)
OVER (PARTITION BY endUserIds._experience.mcid.id
ORDER BY timestamp
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS session
FROM experience_events
ORDER BY id, timestamp ASC
LIMIT 10
결과
id | timestamp | isLaunch | session
----------------------------------+-----------------------+----------+--------------------
100080F22A45CB40-3A2B7A8E11096B6 | 2018-01-18 06:55:53.0 | true | (0,1,true,1)
100080F22A45CB40-3A2B7A8E11096B6 | 2018-01-18 06:56:51.0 | false | (58,1,false,2)
100080F22A45CB40-3A2B7A8E11096B6 | 2018-01-18 06:57:47.0 | false | (56,1,false,3)
100080F22A45CB40-3A2B7A8E11096B6 | 2018-01-18 06:58:27.0 | true | (40,2,true,1)
100080F22A45CB40-3A2B7A8E11096B6 | 2018-01-18 06:59:22.0 | false | (55,2,false,2)
100080F22A45CB40-3A2B7A8E11096B6 | 2018-02-03 01:16:23.0 | false | (1361821,2,false,3)
100080F22A45CB40-3A2B7A8E11096B6 | 2018-02-03 01:17:17.0 | false | (54,2,false,4)
100080F22A45CB40-3A2B7A8E11096B6 | 2018-02-03 01:18:06.0 | false | (49,2,false,5)
100080F22A45CB40-3A2B7A8E11096B6 | 2018-02-03 01:18:39.0 | false | (33,2,false,6)
100080F22A45CB40-3A2B7A8E11096B6 | 2018-02-03 01:19:10.0 | false | (31,2,false,7)
(10 rows)
제공된 샘플 쿼리에 대한 결과는에 제공됩니다. session
열. 다음 session
열은 다음 구성 요소로 구성됩니다.
({TIMESTAMP_DIFF}, {NUM}, {IS_NEW}, {DEPTH})
{TIMESTAMP_DIFF}
{NUM}
PARTITION BY
/ 창 함수.{IS_NEW}
{DEPTH}
SESS_END_IF
이 쿼리는 현재 타임스탬프와 지정된 표현식을 기반으로 현재 행에 대한 세션 상태를 반환하고 현재 세션을 종료하고 다음 행에서 새 세션을 시작합니다.
쿼리 구문
SESS_END_IF({TIMESTAMP}, {TEST_EXPRESSION}) OVER ({PARTITION} {ORDER} {FRAME})
{TIMESTAMP}
{TEST_EXPRESSION}
application.launches > 0
.내의 매개변수에 대한 설명 OVER()
함수는에서 찾을 수 있습니다. 창 기능 섹션.
예제 쿼리
SELECT
endUserIds._experience.mcid.id AS id,
timestamp,
IF(application.applicationCloses.value > 0 OR application.crashes.value > 0, true, false) AS isExit,
SESS_END_IF(timestamp, application.applicationCloses.value > 0 OR application.crashes.value > 0)
OVER (PARTITION BY endUserIds._experience.mcid.id
ORDER BY timestamp
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS session
FROM experience_events
ORDER BY id, timestamp ASC
LIMIT 10
결과
id | timestamp | isExit | session
----------------------------------+-----------------------+----------+--------------------
100080F22A45CB40-3A2B7A8E11096B6 | 2018-01-18 06:55:53.0 | false | (0,1,true,1)
100080F22A45CB40-3A2B7A8E11096B6 | 2018-01-18 06:56:51.0 | false | (58,1,false,2)
100080F22A45CB40-3A2B7A8E11096B6 | 2018-01-18 06:57:47.0 | true | (56,1,false,3)
100080F22A45CB40-3A2B7A8E11096B6 | 2018-01-18 06:58:27.0 | false | (40,2,true,1)
100080F22A45CB40-3A2B7A8E11096B6 | 2018-01-18 06:59:22.0 | false | (55,2,false,2)
100080F22A45CB40-3A2B7A8E11096B6 | 2018-02-03 01:16:23.0 | false | (1361821,2,false,3)
100080F22A45CB40-3A2B7A8E11096B6 | 2018-02-03 01:17:17.0 | false | (54,2,false,4)
100080F22A45CB40-3A2B7A8E11096B6 | 2018-02-03 01:18:06.0 | false | (49,2,false,5)
100080F22A45CB40-3A2B7A8E11096B6 | 2018-02-03 01:18:39.0 | false | (33,2,false,6)
100080F22A45CB40-3A2B7A8E11096B6 | 2018-02-03 01:19:10.0 | false | (31,2,false,7)
(10 rows)
제공된 샘플 쿼리에 대한 결과는에 제공됩니다. session
열. 다음 session
열은 다음 구성 요소로 구성됩니다.
({TIMESTAMP_DIFF}, {NUM}, {IS_NEW}, {DEPTH})
{TIMESTAMP_DIFF}
{NUM}
PARTITION BY
/ 창 함수.{IS_NEW}
{DEPTH}
경로 지정
경로 지정을 사용하여 고객의 참여도를 이해하고, 경험의 의도된 단계가 설계대로 작동하는지 확인하고, 고객에게 영향을 미칠 수 있는 잠재적인 문제점을 식별할 수 있습니다.
다음 ADF는 이전 및 다음 관계에서 경로 지정 보기를 설정할 수 있도록 지원합니다. 이전 페이지와 다음 페이지를 만들거나 여러 이벤트를 단계별로 진행하여 경로 지정을 만들 수 있습니다.
이전 페이지
창 내에서 정의된 단계 수만큼 떨어진 특정 필드의 이전 값을 결정합니다. 이 예제에서 WINDOW
함수는 다음 프레임으로 구성됩니다. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
현재 행 및 모든 후속 행을 보도록 ADF를 설정합니다.
쿼리 구문
PREVIOUS({KEY}, {SHIFT}, {IGNORE_NULLS}) OVER ({PARTITION} {ORDER} {FRAME})
{KEY}
{SHIFT}
{IGNORE_NULLS}
{KEY}
값은 무시해야 합니다. 기본적으로 값은 입니다. false
.내의 매개변수에 대한 설명 OVER()
함수는에서 찾을 수 있습니다. 창 기능 섹션.
예제 쿼리
SELECT endUserIds._experience.mcid.id, timestamp, web.webPageDetails.name
PREVIOUS(web.webPageDetails.name, 3)
OVER(PARTITION BY endUserIds._experience.mcid.id
ORDER BY timestamp
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS previous_page
FROM experience_events
ORDER BY endUserIds._experience.mcid.id, timestamp ASC
결과
id | timestamp | name | previous_page
-----------------------------------+-----------------------+-------------------------------------+-----------------------------------------------------
457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 17:15:28.0 | |
457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 17:53:05.0 | Home |
457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 17:53:45.0 | Kids | (Home)
457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 19:22:34.0 | | (Kids)
457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 20:01:12.0 | Home |
457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 20:01:57.0 | Kids | (Home)
457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 20:03:36.0 | Search Results | (Kids)
457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 20:04:30.0 | Product Details: Pemmican Power Bar | (Search Results)
457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 20:05:27.0 | Shopping Cart: Cart Details | (Product Details: Pemmican Power Bar)
457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 20:06:07.0 | Shopping Cart: Shipping Information | (Shopping Cart: Cart Details)
(10 rows)
제공된 샘플 쿼리에 대한 결과는에 제공됩니다. previous_page
열. 다음 내의 값 previous_page
열은 {KEY}
ADF에서 사용됩니다.
다음 페이지
정의된 단계 수만큼 떨어진 특정 필드의 다음 값을 결정합니다. 이 예제에서 WINDOW
함수는 다음 프레임으로 구성됩니다. ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
현재 행 및 모든 후속 행을 보도록 ADF를 설정합니다.
쿼리 구문
NEXT({KEY}, {SHIFT}, {IGNORE_NULLS}) OVER ({PARTITION} {ORDER} {FRAME})
{KEY}
{SHIFT}
{IGNORE_NULLS}
{KEY}
값은 무시해야 합니다. 기본적으로 값은 입니다. false
.내의 매개변수에 대한 설명 OVER()
함수는에서 찾을 수 있습니다. 창 기능 섹션.
예제 쿼리
SELECT endUserIds._experience.aaid.id, timestamp, web.webPageDetails.name,
NEXT(web.webPageDetails.name, 1, true)
OVER(PARTITION BY endUserIds._experience.aaid.id
ORDER BY timestamp
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
AS next_page
FROM experience_events
ORDER BY endUserIds._experience.aaid.id, timestamp ASC
LIMIT 10
결과
id | timestamp | name | previous_page
-----------------------------------+-----------------------+-------------------------------------+---------------------------------------
457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 17:15:28.0 | | (Home)
457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 17:53:05.0 | Home | (Kids)
457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 17:53:45.0 | Kids | (Home)
457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 19:22:34.0 | | (Home)
457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 20:01:12.0 | Home | (Kids)
457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 20:01:57.0 | Kids | (Search Results)
457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 20:03:36.0 | Search Results | (Product Details: Pemmican Power Bar)
457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 20:04:30.0 | Product Details: Pemmican Power Bar | (Shopping Cart: Cart Details)
457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 20:05:27.0 | Shopping Cart: Cart Details | (Shopping Cart: Shipping Information)
457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 20:06:07.0 | Shopping Cart: Shipping Information | (Shopping Cart: Billing Information)
(10 rows)
제공된 샘플 쿼리에 대한 결과는에 제공됩니다. previous_page
열. 다음 내의 값 previous_page
열은 {KEY}
ADF에서 사용됩니다.
간격
간격(Time-between)을 사용하면 이벤트가 발생하기 전이나 후의 특정 기간 내에 잠재 고객 행동을 탐색할 수 있습니다.
이전 일치 항목 사이의 시간
이 쿼리는 이전 일치 이벤트가 표시된 이후 시간 단위를 나타내는 숫자를 반환합니다. 일치하는 이벤트가 없으면 null을 반환합니다.
쿼리 구문
TIME_BETWEEN_PREVIOUS_MATCH(
{TIMESTAMP}, {EVENT_DEFINITION}, {TIME_UNIT})
OVER ({PARTITION} {ORDER} {FRAME})
{TIMESTAMP}
{EVENT_DEFINITION}
{TIME_UNIT}
내의 매개변수에 대한 설명 OVER()
함수는에서 찾을 수 있습니다. 창 기능 섹션.
예제 쿼리
SELECT
page_name,
SUM (time_between_previous_match) / COUNT(page_name) as average_minutes_since_registration
FROM
(
SELECT
endUserIds._experience.mcid.id as id,
timestamp, web.webPageDetails.name as page_name,
TIME_BETWEEN_PREVIOUS_MATCH(timestamp, web.webPageDetails.name='Account Registration|Confirmation', 'minutes')
OVER(PARTITION BY endUserIds._experience.mcid.id
ORDER BY timestamp
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS time_between_previous_match
FROM experience_events
)
WHERE time_between_previous_match IS NOT NULL
GROUP BY page_name
ORDER BY average_minutes_since_registration
LIMIT 10
결과
page_name | average_minutes_since_registration
-----------------------------------+------------------------------------
|
Account Registration|Confirmation | 0.0
Seasonal | 5.47029702970297
Equipment | 6.532110091743119
Women | 7.287081339712919
Men | 7.640918580375783
Product List | 9.387459807073954
Unlimited Blog|February | 9.954545454545455
Product Details|Buffalo | 13.304347826086957
Unlimited Blog|June | 770.4285714285714
(10 rows)
제공된 샘플 쿼리에 대한 결과는에 제공됩니다. average_minutes_since_registration
열. 다음 내의 값 average_minutes_since_registration
열은 현재 이벤트와 이전 이벤트 간의 시간 차이입니다. 시간 단위는 이전에 의 {TIME_UNIT}
.
다음 일치 사이의 시간
이 쿼리는 다음 일치 이벤트 이후의 시간 단위를 나타내는 음수를 반환합니다. 일치하는 이벤트를 찾을 수 없으면 null이 반환됩니다.
쿼리 구문
TIME_BETWEEN_NEXT_MATCH({TIMESTAMP}, {EVENT_DEFINITION}, {TIME_UNIT}) OVER ({PARTITION} {ORDER} {FRAME})
{TIMESTAMP}
{EVENT_DEFINITION}
{TIME_UNIT}
내의 매개변수에 대한 설명 OVER()
함수는에서 찾을 수 있습니다. 창 기능 섹션.
예제 쿼리
SELECT
page_name,
SUM (time_between_next_match) / COUNT(page_name) as average_minutes_until_order_confirmation
FROM
(
SELECT
endUserIds._experience.mcid.id as id,
timestamp, web.webPageDetails.name as page_name,
TIME_BETWEEN_NEXT_MATCH(timestamp, web.webPageDetails.name='Shopping Cart|Order Confirmation', 'minutes')
OVER(PARTITION BY endUserIds._experience.mcid.id
ORDER BY timestamp
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
AS time_between_next_match
FROM experience_events
)
WHERE time_between_next_match IS NOT NULL
GROUP BY page_name
ORDER BY average_minutes_until_order_confirmation DESC
LIMIT 10
결과
page_name | average_minutes_until_order_confirmation
-----------------------------------+------------------------------------------
Shopping Cart|Order Confirmation | 0.0
Men | -9.465295629820051
Equipment | -9.682098765432098
Product List | -9.690661478599221
Women | -9.759459459459459
Seasonal | -10.295
Shopping Cart|Order Review | -366.33567364956144
Unlimited Blog|February | -615.0327868852459
Shopping Cart|Billing Information | -775.6200495367711
Product Details|Buffalo | -1274.9571428571428
(10 rows)
제공된 샘플 쿼리에 대한 결과는에 제공됩니다. average_minutes_until_order_confirmation
열. 다음 내의 값 average_minutes_until_order_confirmation
열은 현재 이벤트와 다음 이벤트 간의 시간 차이입니다. 시간 단위는 이전에 의 {TIME_UNIT}
.
다음 단계
여기에 설명된 함수를 사용하여 자신의 쿼리에 액세스하기 위한 쿼리를 작성할 수 있습니다 Experience Event 데이터 세트 Query Service. 에서 쿼리 작성에 대한 자세한 내용 Query Service에 대한 설명서를 참조하십시오. 쿼리 만들기.
추가 리소스
다음 비디오는 Adobe Experience Platform 인터페이스 및 PSQL 클라이언트에서 쿼리를 실행하는 방법을 보여 줍니다. 또한 이 비디오에서는 Adobe 정의 함수 사용 및 CREATE TABLE AS SELECT(CTAS) 사용 시 XDM 개체의 개별 속성과 관련된 예제를 사용합니다.