您好,登錄后才能下訂單哦!
這篇文章將為大家詳細(xì)講解有關(guān)clickhouse數(shù)據(jù)模型中有序漏斗是什么,小編覺(jué)得挺實(shí)用的,因此分享給大家做個(gè)參考,希望大家閱讀完這篇文章后可以有所收獲。
前言
假設(shè)我們已經(jīng)得到了觸達(dá)支付購(gòu)買的路徑有 “首頁(yè)->詳情頁(yè)->購(gòu)買頁(yè)->支付“ 和 “搜索頁(yè)->詳情頁(yè)->購(gòu)買頁(yè)->支付“ 兩個(gè)主要路徑,但是我們不清楚哪條路徑轉(zhuǎn)化率高,那么這個(gè)時(shí)候漏斗分析就派上用場(chǎng)了
windowFunnel(window)(timestamp, cond1, cond2, ..., condN)
# 創(chuàng)建一張用戶行為表,至少包含時(shí)間、事件、用戶idCREATE TABLE test.action( `uid` Int32, `event_type` String, `time` datetime)ENGINE = MergeTree()PARTITION BY uidORDER BY xxHash42(uid)SAMPLE BY xxHash42(uid)SETTINGS index_granularity = 8192
insert into action values(1,'瀏覽','2020-01-02 11:00:00');
insert into action values(1,'點(diǎn)擊','2020-01-02 11:10:00');
insert into action values(1,'下單','2020-01-02 11:20:00');
insert into action values(1,'支付','2020-01-02 11:30:00');
insert into action values(2,'下單','2020-01-02 11:00:00');
insert into action values(2,'支付','2020-01-02 11:10:00');
insert into action values(1,'瀏覽','2020-01-02 11:00:00');
insert into action values(3,'瀏覽','2020-01-02 11:20:00');
insert into action values(3,'點(diǎn)擊','2020-01-02 12:00:00');
insert into action values(4,'瀏覽','2020-01-02 11:50:00');
insert into action values(4,'點(diǎn)擊','2020-01-02 12:00:00');
insert into action values(5,'瀏覽','2020-01-02 11:50:00');
insert into action values(5,'點(diǎn)擊','2020-01-02 12:00:00');
insert into action values(5,'下單','2020-01-02 11:10:00');
insert into action values(6,'瀏覽','2020-01-02 11:50:00');
insert into action values(6,'點(diǎn)擊','2020-01-02 12:00:00');
insert into action values(6,'下單','2020-01-02 12:10:00');
SELECT
user_id,
windowFunnel(1800)(time, event_type = '瀏覽', event_type = '點(diǎn)擊', event_type = '下單', event_type = '支付') AS level
FROM
(
SELECT
time,
event_type,
uid AS user_id
FROM action
)
GROUP BY user_id
┌─user_id─┬─level─┐
│ 3 │ 1 │
│ 2 │ 0 │
│ 5 │ 2 │
│ 1 │ 4 │
│ 6 │ 3 │
└─────────┴───────┘
SELECT level_index,count(1) FROM( SELECT user_id, arrayWithConstant(level, 1) levels, arrayJoin(arrayEnumerate( levels )) level_index FROM ( SELECT user_id, windowFunnel(1800)( time, event_type = '瀏覽', event_type = '點(diǎn)擊' , event_type = '下單', event_type = '支付' ) AS level FROM ( SELECT time, event_type , uid as user_id FROM test.action WHERE toDate(time) = '2020-01-02' ) GROUP BY user_id ))group by level_indexORDER BY level_index
關(guān)于“clickhouse數(shù)據(jù)模型中有序漏斗是什么”這篇文章就分享到這里了,希望以上內(nèi)容可以對(duì)大家有一定的幫助,使各位可以學(xué)到更多知識(shí),如果覺(jué)得文章不錯(cuò),請(qǐng)把它分享出去讓更多的人看到。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。