數(shù)據(jù)分析不僅僅是冷冰冰的數(shù)字和統(tǒng)計(jì)結(jié)果，創(chuàng)造力在其中扮演著重要的角色。創(chuàng)造力能夠?yàn)槲覀儚臄?shù)據(jù)集中提取最大化的價(jià)值。

以包含網(wǎng)站事件的表格為例，通過對(duì)用戶ID、事件名稱和時(shí)間戳等信息的分析，我們可以揭示出用戶行為和趨勢(shì)，進(jìn)而實(shí)現(xiàn)諸如監(jiān)測(cè)用戶參與度、衡量用戶增長、繪制客戶旅程以及個(gè)性化用戶體驗(yàn)等多種目標(biāo)。接下來，我們深入探討如何利用這些數(shù)據(jù)來回答關(guān)鍵問題，并展示數(shù)據(jù)背后的故事。

該表格由三列組成：

• user_id：顯示觸發(fā)事件的唯一用戶
• event_name：指示觸發(fā)的事件，例如查看、點(diǎn)擊、注冊(cè)、結(jié)賬、購買等
• timestamp：記錄事件發(fā)生的時(shí)間點(diǎn)

盡管數(shù)據(jù)有限，但可以用于多種目的，包括：

• 監(jiān)測(cè)用戶參與度
• 衡量用戶增長
• 繪制客戶旅程
• 個(gè)性化用戶體驗(yàn)

具體而言，本文將演示如何使用這些數(shù)據(jù)回答以下問題：

• 整體用戶會(huì)話和每個(gè)會(huì)話的事件趨勢(shì)如何？
• 使用情況是由新用戶還是回頭用戶推動(dòng)的？
• 每周使用率增長率是多少？

1. 公共表達(dá)式和窗口函數(shù)

在開始之前，理解公共表達(dá)式（CTEs）和窗口函數(shù)的概念是有必要的。通過利用這些強(qiáng)大的功能，可以使用SQL進(jìn)行更高級(jí)的分析。

CTE是一個(gè)命名的臨時(shí)結(jié)果集，可以在同一查詢中引用它，就像引用其他任何表一樣。這有助于將復(fù)雜查詢分解為較小、邏輯上的步驟。它們還可以通過減少復(fù)雜連接的需求并允許數(shù)據(jù)庫引擎緩存中間結(jié)果來提高查詢性能。

以下是一個(gè)簡(jiǎn)單的CTE示例，計(jì)算每個(gè)用戶的網(wǎng)站訪問次數(shù)。主查詢引用了user_visits CTE來進(jìn)行進(jìn)一步的聚合，這次是計(jì)算返回用戶的數(shù)量。

WITH user_visits AS (
  SELECT user_id, COUNT(DISTINCT visit_date) AS num_visits
  FROM my_table
  GROUP BY user_id
)
SELECT COUNT(*) AS num_returning_users
FROM user_visits
WHERE num_visits > 1;

窗口函數(shù)非常適用于執(zhí)行復(fù)雜任務(wù)，例如移動(dòng)平均值和滾動(dòng)總和。它們通過根據(jù)一個(gè)或多個(gè)列（例如日期或user_id）將數(shù)據(jù)分組為多個(gè)子集，并獨(dú)立地對(duì)每個(gè)子集執(zhí)行計(jì)算來實(shí)現(xiàn)這一目的。

窗口函數(shù)（如LAG、LEAD、RANK和ROW_NUMBER()）還需要指定數(shù)據(jù)分區(qū)的順序。下面的窗口函數(shù)使用LAG來計(jì)算當(dāng)前行與前一行之間的時(shí)間差（以秒為單位）。

SELECT 
  user_id, 
  event_name, 
  timestamp, 
  TIMESTAMPDIFF(SECOND, LAG(timestamp) OVER (PARTITION BY user_id ORDER BY timestamp), timestamp) AS time_diff
FROM my_table;

2. 監(jiān)測(cè)用戶參與度

企業(yè)使用參與度指標(biāo)來了解用戶如何與其產(chǎn)品和/或服務(wù)進(jìn)行交互。例如，每位用戶的會(huì)話數(shù)增加可能是用戶滿意度的積極指標(biāo)。這些指標(biāo)還可以洞察不同營銷渠道的效果，比如從一個(gè)渠道獲得的用戶是否比其他渠道更活躍。

為了回答關(guān)于會(huì)話和每個(gè)會(huì)話的事件的問題，將在原始數(shù)據(jù)集中添加一個(gè)新的列。該列將顯示特定用戶的會(huì)話編號(hào)。

下面的查詢首先使用名為sessions的CTE創(chuàng)建了一個(gè)名為new_session的列。使用LAG窗口函數(shù)，新會(huì)話被定義為行（事件）之間超過30分鐘的差異。這個(gè)新列包含布爾值，其中1表示新會(huì)話的開始，0表示現(xiàn)有會(huì)話的延續(xù)。

然后，session_ids CTE使用SUM窗口函數(shù)為每個(gè)事件分配session_id，通過對(duì)每個(gè)用戶的new_session值求和。

請(qǐng)注意，窗口函數(shù)放置在CASE語句內(nèi)部。這是因?yàn)長AG需要從先前的行中檢索數(shù)據(jù)。如果沒有先前的行，這在由用戶觸發(fā)的第一個(gè)事件中是這種情況，將返回NULL值。使用CASE WHEN，NULL將被替換為值1。

WITH sessions AS (
  SELECT user_id, event_name, timestamp,
    CASE
      -- 第一個(gè)事件總是開始一個(gè)新會(huì)話
      WHEN LAG(timestamp, 1) OVER (PARTITION BY user_id ORDER BY timestamp) IS NULL THEN 1
      -- 檢查事件之間是否超過30分鐘
      WHEN timestamp - LAG(timestamp, 1) OVER (PARTITION BY user_id ORDER BY timestamp) >= INTERVAL '30 minutes' THEN 1
      -- 否則，繼續(xù)當(dāng)前會(huì)話
      ELSE 0
    END AS new_session
  FROM my_table
), session_ids AS (
  SELECT user_id, event_name, timestamp, SUM(new_session) OVER (PARTITION BY user_id ORDER BY timestamp) AS user_session_id
  FROM sessions
)
SELECT user_id, event_name, timestamp, new_session, session_id
FROM session_ids
ORDER BY user_id, timestamp;

最終查詢?cè)赟ELECT語句中包含了new_session和user_session_id，你可以在下面看到它們作為新列：

通過這個(gè)表，現(xiàn)在可以計(jì)算每日總會(huì)話數(shù)。首先，我們需要?jiǎng)?chuàng)建一個(gè)新的global_session_id，它將以全局而不是用戶的級(jí)別區(qū)分會(huì)話。

這可以通過使用CONCAT(user_id, '-', session_id)來組合user_id和user_session_id來完成。例如，將user_id 001和user_session_id 1組合的結(jié)果將是一個(gè)新的全局session_id，即001-1。最后，通過按DATE(timestamp)分組計(jì)算不同的global_session_id的計(jì)數(shù)，可以得到每日會(huì)話的視圖。

SELECT
    DATE(timestamp) AS date,
    -- 將user_id和user_session_id連接起來，創(chuàng)建一個(gè)全局會(huì)話id 
    COUNT(DISTINCT CONCAT(user_id, '-', user_session_id)) AS unique_sessions
  FROM
    my_table
  GROUP BY
    DATE(timestamp)

利用global_session_id，我們還可以計(jì)算每個(gè)會(huì)話的事件數(shù)。在下面的查詢中，user_actions CTE按global_session_id和date分組事件，然后計(jì)算唯一事件的timestamps。這樣就可以得到每個(gè)日期上每個(gè)會(huì)話的事件數(shù)。

在主查詢中，我們計(jì)算不同的global_session_id的數(shù)量，從而得到每日會(huì)話的總數(shù)。然后，我們SUM(session_event_count)，得到每日事件的總數(shù)，然后將其除以每日會(huì)話數(shù)，得到每個(gè)會(huì)話的平均事件數(shù)。按日期分組可以得到每天每個(gè)會(huì)話的平均事件數(shù)。

WITH user_actions AS (
  SELECT
    CONCAT(user_id, '-', user_session_id) AS global_session_id,
    DATE(timestamp) AS date,
    -- 計(jì)算每個(gè)會(huì)話和日期的唯一事件數(shù)
    COUNT(DISTINCT timestamp) AS session_event_count
  FROM
    my_table
  GROUP BY
    CONCAT(user_id, '-', user_session_id), DATE(timestamp)
)
SELECT
  date,
  -- 通過計(jì)算不同的global_session_id的數(shù)量來計(jì)算總會(huì)話數(shù)
  COUNT(DISTINCT global_session_id) AS sessions,
  -- 求和所有會(huì)話中的事件數(shù)
  SUM(session_event_count) AS total_events,
  -- 將總事件數(shù)除以總會(huì)話數(shù)
  SUM(session_event_count) / COUNT(DISTINCT global_session_id) AS avg_events_per_session
FROM
  user_actions
GROUP BY
  date;

3. 測(cè)量保留和增長

在高使用率的情況下，如果由新用戶推動(dòng)，可能會(huì)掩蓋用戶流失的問題。因此，留存率是了解用戶參與度的另一個(gè)重要指標(biāo)。通過分析user_session_id列，我們可以確定新用戶和老用戶的比例。

下面創(chuàng)建了兩個(gè)CTE來將計(jì)算分解為連續(xù)的部分。第一個(gè)CTE計(jì)算每日唯一用戶總數(shù)。第二個(gè)CTE計(jì)算每日唯一回頭用戶總數(shù)，使用user_session_id > 1來識(shí)別回頭用戶。

然后，將這些CTE使用日期列進(jìn)行連接，然后計(jì)算返回比率，即每日回頭用戶除以每日總用戶數(shù)。

WITH all_users AS (
-- 計(jì)算每日所有用戶數(shù)
SELECT 
COUNT(DISTINCT users_id) AS total_users, 
DATE(timestamp) AS date
FROM 
my_table
GROUP BY 
date),

returning_users AS (
-- 計(jì)算每日回頭用戶數(shù)
SELECT 
COUNT(DISTINCT users_id) AS returning_users, 
DATE(timestamp) AS date
FROM 
my_table
WHERE user_session_id > 1
GROUP BY 
date)

SELECT 
-- 連接CTE并將回頭用戶除以總用戶數(shù)
all_users.date,
total_users, 
returning_users,
ROUND((returning_users / all_users), 2) AS returning_ratio
FROM all_users
LEFT JOIN returning_users ON returning_users.date = all_users.date

除了衡量現(xiàn)有用戶的保留情況外，增長率對(duì)于提供用戶漏斗的更廣泛圖景也很有用。下面的查詢計(jì)算了每周的增長率，這對(duì)于評(píng)估短期營銷活動(dòng)是合適的，盡管相同的計(jì)算也可以在較長的時(shí)間段內(nèi)進(jìn)行。

首先，我們使用DATE_TRUNC函數(shù)將timestamp值提取為周的起始日期，時(shí)間間隔設(shè)置為week。接下來，我們計(jì)算DISTINCT user_id的數(shù)量，其中user_session_id = 1，表示這是用戶的第一次會(huì)話。這為我們提供了weekly_new_users，我們可以在窗口函數(shù)中使用它來計(jì)算累積用戶獲取。這里非常關(guān)鍵的是，對(duì)該窗口函數(shù)按week_start進(jìn)行排序，并設(shè)置范圍為UNBOUNDED PRECEDING AND CURRENT ROW，這將對(duì)當(dāng)前周和之前所有周的weekly_new_users值進(jìn)行求和。

最后，我們通過將當(dāng)前累積用戶減去先前累積用戶，并將結(jié)果除以先前累積用戶來計(jì)算每周的增長率。

WITH weekly_new_users AS (
-- 計(jì)算每周的新用戶數(shù)
  SELECT
    DATE_TRUNC('week', timestamp) AS week_start,
    COUNT(DISTINCT user_id) AS weekly_new_users
  FROM 
    my_table
  WHERE user_session_id = 1
  GROUP BY
    DATE_TRUNC('week', timestamp),

weekly_cumulative AS (
-- 對(duì)每周新用戶數(shù)進(jìn)行累加
  SELECT
    week_start,
    sum(weekly_new_users) OVER (ORDER BY week_start RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_users
  FROM
    weekly_new_users
)

-- 使用累積用戶計(jì)算每周增長率
SELECT
  DATE(week_start) AS week_start,
  cum_users,
  ROUND(((cum_users - LAG(cum_users) OVER (ORDER BY week_start))/LAG(cum_users) OVER (ORDER BY week_start)) * 100, 2) AS weekly_growth_rate,
FROM
  weekly_cumulative

4. 結(jié)論

雖然具體指標(biāo)的相關(guān)性取決于業(yè)務(wù)模型、行業(yè)和增長階段，但上述示例清楚地展示了SQL在提供業(yè)務(wù)洞察方面的強(qiáng)大和多功能性。通過將這些工具與創(chuàng)造性思維相結(jié)合，即使是基本的數(shù)據(jù)集也能夠?yàn)楦鞣N利益相關(guān)者提供價(jià)值。