如何在AWS Athena中统计GA4数组字段的session_id数量

最终查询语句 #

SELECT COUNT(DISTINCT new_event_params.value.int_value) AS session_count
FROM "01_bronze_ga4".ga4_bigquery AS params
CROSS JOIN UNNEST(event_params) AS new_event_params
WHERE 
    -- 筛选2023年的所有数据（event_date格式为YYYYMMDD，前4位为年份）
    SUBSTRING(params."event_date", 1, 4) = '2023'
    AND params.event_timestamp = 1673094862853604
    AND params.event_name = 'session_start'
    -- 只保留ga_session_id参数条目
    AND new_event_params.key = 'ga_session_id'

核心步骤说明 #

• 过滤目标参数：通过new_event_params.key = 'ga_session_id'从拆分后的event_params行中，精准筛选出存储会话ID的条目
• 提取会话ID值：根据你提供的数据结构，ga_session_id的实际值存储在value.int_value字段中，直接提取该字段即可获取有效会话ID
• 统计去重会话数：使用COUNT(DISTINCT ...)统计唯一会话ID的数量，避免同一会话被重复计数
• 优化日期过滤：将原查询的LIKE '2023%'调整为SUBSTRING(...,1,4) = '2023'，更精准匹配2023年全年数据，同时提升查询效率

内容的提问来源于stack exchange，提问作者Peterson Silva