全栈攻城狮

Appearance

游戏数据分析与反作弊 ​

1. 游戏数据分析体系 ​

1.1 数据采集 ​

数据采集是游戏数据分析的基石，决定了分析的上限。游戏数据采集涵盖客户端日志、服务端日志、自定义事件、页面埋点等多种方式。

1.1.1 采集方式 ​

客户端日志

客户端日志采集玩家的设备信息、操作行为、性能数据和异常信息。通过 SDK 嵌入游戏客户端，将日志上报至服务端。

java
// 客户端日志采集示例
public class ClientLogger {
    private static final String TAG = "GameAnalytics";
    private LogQueue logQueue = new LogQueue(1024); // 内存队列，批量上报
    
    public void logEvent(String eventName, Map<String, Object> params) {
        LogEntry entry = new LogEntry();
        entry.setEventName(eventName);
        entry.setParams(params);
        entry.setTimestamp(System.currentTimeMillis());
        entry.setUserId(UserManager.getInstance().getUserId());
        entry.setSessionId(SessionManager.getCurrentSessionId());
        entry.setDeviceId(DeviceInfoUtil.getDeviceId());
        entry.setLevel(UserManager.getInstance().getPlayerLevel());
        logQueue.enqueue(entry);
        
        // 本地缓存，防止数据丢失
        LocalCache.save(entry);
    }
    
    // 关键事件打点
    public void onLogin() {
        Map<String, Object> params = new HashMap<>();
        params.put("login_type", getLoginType());
        params.put("is_new_device", isNewDevice());
        params.put("channel", ChannelUtil.getChannel());
        logEvent("login", params);
    }
    
    public void onPurchase(String itemId, int amount, String currency, double price) {
        Map<String, Object> params = new HashMap<>();
        params.put("item_id", itemId);
        params.put("amount", amount);
        params.put("currency", currency);
        params.put("price", price);
        params.put("total_spent", UserManager.getInstance().getTotalRecharge());
        logEvent("purchase", params);
    }
    
    public void onLevelUp(int newLevel) {
        Map<String, Object> params = new HashMap<>();
        params.put("new_level", newLevel);
        params.put("total_play_time", SessionManager.getTotalPlayTime());
        params.put("main_quest_progress", QuestManager.getMainQuestProgress());
        logEvent("level_up", params);
    }
}

服务端日志

服务端日志由游戏服务器直接产生，记录玩家在服务器上的所有操作。相比客户端日志，服务端日志更可靠，不可被玩家篡改。

java
// 服务端日志记录示例
@Component
public class ServerLogCollector {
    private final Logger logger = LoggerFactory.getLogger("GameServerLog");
    private final KafkaTemplate<String, String> kafkaTemplate;
    
    public void recordPlayerAction(PlayerAction action) {
        ServerLogEntry entry = ServerLogEntry.builder()
            .eventId(UUID.randomUUID().toString())
            .eventType(action.getType())
            .userId(action.getUserId())
            .roleId(action.getRoleId())
            .serverId(action.getServerId())
            .timestamp(System.currentTimeMillis())
            .ip(action.getClientIp())
            .actionDetail(JSON.toJSONString(action.getDetail()))
            .build();
        
        // 写入本地日志文件
        logger.info(JSON.toJSONString(entry));
        
        // 发送到消息队列
        kafkaTemplate.send("game-server-log", entry.getUserId(), JSON.toJSONString(entry));
    }
}

自定义事件

针对特定的业务场景定义事件，例如关卡开始、关卡结束、BOSS 击杀、装备强化等。

yaml
# 自定义事件定义示例
events:
  quest_start:
    description: "任务开始"
    properties:
      - name: quest_id
        type: string
      - name: quest_type
        type: string    # main/side/daily/activity
      - name: player_level
        type: int
  
  quest_complete:
    description: "任务完成"
    properties:
      - name: quest_id
        type: string
      - name: duration_seconds
        type: int
      - name: rewards
        type: array<object>
        properties:
          - name: item_id
            type: string
          - name: quantity
            type: int
  
  boss_kill:
    description: "BOSS击杀"
    properties:
      - name: boss_id
        type: string
      - name: boss_level
        type: int
      - name: party_size
        type: int
      - name: kill_duration
        type: int
      - name: damage_dealt
        type: long
      - name: is_first_kill
        type: boolean

页面埋点 / 全埋点 / 可视化埋点

  • 页面埋点：在游戏特定页面（商城、活动页、充值页）手动埋入代码，追踪 PV/UV、点击热力、转化路径。
  • 全埋点：通过 SDK 自动采集玩家所有交互事件，无需手动编码。适用于探索性分析阶段。
  • 可视化埋点：通过运营后台界面圈选页面元素，系统自动生成埋点代码。降低埋点成本，提升运营效率。
java
// 全埋点 SDK 自动采集示例
public class AutoTracker {
    
    // 自动采集点击事件
    @Autowired
    private TrackerService trackerService;
    
    @Around("@annotation(com.game.sdk.TrackClick)")
    public Object trackClick(ProceedingJoinPoint pjp) throws Throwable {
        Signature sig = pjp.getSignature();
        MethodSignature msig = (MethodSignature) sig;
        TrackClick annotation = msig.getMethod().getAnnotation(TrackClick.class);
        
        Object result = pjp.proceed();
        
        // 自动采集事件信息
        Map<String, Object> props = new HashMap<>();
        props.put("element_id", annotation.elementId());
        props.put("element_type", annotation.elementType());
        props.put("page_name", annotation.pageName());
        props.put("click_result", result != null ? "success" : "fail");
        
        trackerService.track("ui_click", props);
        return result;
    }
}

服务器端 SDK

对于非游戏核心战斗场景（如官网、社区、客服系统），使用服务器端 SDK 直接上报数据。

python
# 服务器端 SDK 数据上报示例
import requests
import json
import time

class ServerSDK:
    def __init__(self, app_id, app_secret, endpoint):
        self.app_id = app_id
        self.app_secret = app_secret
        self.endpoint = endpoint
        self.batch_events = []
        
    def track(self, event_name, user_id, properties=None):
        """上报事件"""
        event = {
            "app_id": self.app_id,
            "event": event_name,
            "user_id": user_id,
            "time": int(time.time() * 1000),
            "properties": properties or {},
            "$device_id": self._get_device_id(user_id)
        }
        # 加入批量队列
        self.batch_events.append(event)
        if len(self.batch_events) >= 50:
            self.flush()
    
    def flush(self):
        """批量上报"""
        if not self.batch_events:
            return
        data = {
            "app_id": self.app_id,
            "sign": self._sign(),
            "events": self.batch_events
        }
        try:
            resp = requests.post(
                self.endpoint + "/batch",
                json=data,
                timeout=3,
                headers={"Content-Type": "application/json"}
            )
            if resp.status_code == 200:
                self.batch_events = []
        except Exception as e:
            # 失败重试，写入本地文件
            self._save_to_local(data)

1.1.2 日志上报协议 ​

HTTP/gRPC 上报

protobuf
// gRPC 日志上报协议定义
syntax = "proto3";

package game.analytics;

service LogService {
    // 单条上报
    rpc ReportLog(LogEntry) returns (LogResponse);
    // 批量上报
    rpc ReportLogBatch(stream LogEntry) returns (LogBatchResponse);
}

message LogEntry {
    string event_id = 1;
    string user_id = 2;
    string role_id = 3;
    int32 server_id = 4;
    string event_name = 5;
    int64 timestamp = 6;
    map<string, string> properties = 7;
    string device_id = 8;
    string session_id = 9;
    string ip = 10;
    string user_agent = 11;
    string channel = 12;
}

message LogResponse {
    int32 code = 1;
    string message = 2;
}

message LogBatchResponse {
    int32 code = 1;
    int32 accepted_count = 2;
    repeated string failed_ids = 3;
}

批量上报与实时上报

特性批量上报实时上报
延迟分钟级秒级
吞吐量高中
资源消耗低高
适用场景行为日志、事件追踪实时监控、反作弊告警
java
// 批量上报实现
@Component
public class BatchReporter {
    private final BlockingQueue<LogEntry> queue = new LinkedBlockingQueue<>(10000);
    private final ExecutorService executor = Executors.newSingleThreadExecutor();
    
    @PostConstruct
    public void init() {
        executor.submit(() -> {
            List<LogEntry> batch = new ArrayList<>(500);
            while (true) {
                // 每 5 秒或满 500 条上报
                try {
                    Thread.sleep(5000);
                    queue.drainTo(batch, 500);
                    if (!batch.isEmpty()) {
                        reportBatch(batch);
                        batch.clear();
                    }
                } catch (Exception e) {
                    log.error("Batch report failed", e);
                }
            }
        });
    }
    
    public void add(LogEntry entry) {
        queue.offer(entry);
    }
    
    private void reportBatch(List<LogEntry> batch) {
        // HTTP 批量上报
        httpClient.post("https://analytics.game.com/batch")
            .body(batch)
            .execute();
    }
}

采样策略

对于海量日志（如战斗帧数据、位置数据），采用采样策略降低数据量。

java
public class Sampler {
    
    // 按比例采样
    public static boolean sampleByRate(double rate) {
        return ThreadLocalRandom.current().nextDouble() < rate;
    }
    
    // 按用户 ID 哈希采样
    public static boolean sampleByUserHash(String userId, int modulus, int target) {
        return Math.abs(userId.hashCode()) % modulus == target;
    }
    
    // 分层采样：低等级玩家 10%，高等级玩家 100%
    public static boolean stratifiedSample(int playerLevel, double lowLevelRate, int threshold) {
        if (playerLevel >= threshold) {
            return true; // 高等级全量
        }
        return sampleByRate(lowLevelRate);
    }
    
    // 自适应采样
    public static boolean adaptiveSample(String eventName, long currentCount, long threshold) {
        if (currentCount < threshold) {
            return true; // 数据量小全量
        }
        // 数据量大后逐步降低采样率
        double rate = Math.max(0.01, 1.0 - Math.log10(currentCount - threshold + 1) * 0.1);
        return sampleByRate(rate);
    }
}

1.1.3 数据 ETL ​

sql
-- 数据清洗示例：去除异常数据
INSERT OVERWRITE TABLE ods_game_event PARTITION(dt='${dt}')
SELECT
    event_id,
    user_id,
    role_id,
    server_id,
    event_name,
    timestamp,
    properties,
    device_id,
    session_id
FROM raw_game_event
WHERE dt = '${dt}'
    AND user_id IS NOT NULL
    AND user_id != ''
    AND LENGTH(user_id) < 64
    AND timestamp > 0
    AND timestamp < UNIX_TIMESTAMP() * 1000
    AND server_id > 0;

数据格式统一

python
# 数据格式统一 ETL 示例
def normalize_event(raw_event):
    """统一不同来源的事件格式"""
    normalized = {
        "event_id": raw_event.get("event_id") or str(uuid.uuid4()),
        "event_name": raw_event.get("event_name", raw_event.get("event", "")),
    }
    
    # 统一时间格式（毫秒时间戳）
    raw_time = raw_event.get("timestamp", raw_event.get("time", raw_event.get("ts", 0)))
    if isinstance(raw_time, str):
        normalized["timestamp"] = int(date_parser(raw_time).timestamp() * 1000)
    elif raw_time < 1000000000000:  # 秒级转毫秒
        normalized["timestamp"] = raw_time * 1000
    else:
        normalized["timestamp"] = raw_time
    
    # 用户 ID 统一
    normalized["user_id"] = raw_event.get("user_id") or raw_event.get("uid", "")
    normalized["role_id"] = raw_event.get("role_id") or raw_event.get("rid", "")
    
    # 属性字段展开
    props = raw_event.get("properties", raw_event.get("params", {}))
    if isinstance(props, str):
        props = json.loads(props)
    normalized["properties"] = props
    
    return normalized

1.1.4 用户设备信息 ​

java
// Android 设备信息采集
public class AndroidDeviceInfoCollector {
    
    public static DeviceInfo collect(Context context) {
        DeviceInfo info = new DeviceInfo();
        
        // UUID
        info.setUuid(DeviceUUID.getUUID(context));
        
        // 设备 ID
        info.setDeviceId(Settings.Secure.getString(context.getContentResolver(), 
            Settings.Secure.ANDROID_ID));
        
        // 渠道 ID
        try {
            ApplicationInfo appInfo = context.getPackageManager()
                .getApplicationInfo(context.getPackageName(), PackageManager.GET_META_DATA);
            info.setChannelId(appInfo.metaData.getString("CHANNEL_ID"));
        } catch (Exception e) {
            info.setChannelId("unknown");
        }
        
        // 广告 ID (GAID)
        try {
            AdvertisingIdClient.Info adInfo = AdvertisingIdClient.getAdvertisingIdInfo(context);
            info.setGaid(adInfo.getId());
            info.setLimitAdTracking(adInfo.isLimitAdTrackingEnabled());
        } catch (Exception e) {
            info.setGaid(null);
        }
        
        // OAID (匿名设备标识符)
        try {
            String oaid = OaidHelper.getOAID(context);
            info.setOaid(oaid);
        } catch (Exception e) {
            info.setOaid(null);
        }
        
        // IMEI (需要 READ_PHONE_STATE 权限)
        if (PermissionChecker.hasPermission(context, Manifest.permission.READ_PHONE_STATE)) {
            TelephonyManager tm = (TelephonyManager) context.getSystemService(Context.TELEPHONY_SERVICE);
            if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.O) {
                info.setImei(tm.getImei());
            } else {
                info.setImei(tm.getDeviceId());
            }
        }
        
        // AndroidID
        info.setAndroidId(Settings.Secure.getString(
            context.getContentResolver(), Settings.Secure.ANDROID_ID));
        
        return info;
    }
}
objc
// iOS 设备信息采集 (IDFA)
#import <AdSupport/AdSupport.h>
#import <AppTrackingTransparency/AppTrackingTransparency.h>

- (DeviceInfo *)collectDeviceInfo {
    DeviceInfo *info = [[DeviceInfo alloc] init];
    
    // UUID
    info.uuid = [[[UIDevice currentDevice] identifierForVendor] UUIDString];
    
    // IDFA
    if (@available(iOS 14, *)) {
        [ATTrackingManager requestTrackingAuthorizationWithCompletionHandler:^(ATTrackingManagerAuthorizationStatus status) {
            if (status == ATTrackingManagerAuthorizationStatusAuthorized) {
                info.idfa = [[ASIdentifierManager sharedManager] advertisingIdentifier].UUIDString;
            } else {
                info.idfa = nil;
            }
        }];
    } else {
        if ([[ASIdentifierManager sharedManager] isAdvertisingTrackingEnabled]) {
            info.idfa = [[ASIdentifierManager sharedManager] advertisingIdentifier].UUIDString;
        }
    }
    
    return info;
}

隐私合规

java
// 隐私合规数据采集
public class PrivacyCompliantCollector {
    
    // 隐私策略状态
    public enum ConsentStatus {
        NOT_ASKED,
        GRANTED,
        DENIED,
        EXPIRED
    }
    
    private ConsentStatus analyticsConsent = ConsentStatus.NOT_ASKED;
    
    // 请求用户同意
    public void requestConsent(Activity activity) {
        PrivacyDialog dialog = new PrivacyDialog(activity);
        dialog.setTitle("数据采集授权");
        dialog.setMessage("为了提升游戏体验，我们需要采集游戏行为数据。"
            + "我们不会收集您的敏感个人信息。您可以在设置中随时关闭。");
        dialog.setPositiveButton("同意", () -> {
            analyticsConsent = ConsentStatus.GRANTED;
            startAnalytics();
        });
        dialog.setNegativeButton("拒绝", () -> {
            analyticsConsent = ConsentStatus.DENIED;
        });
        dialog.show();
    }
    
    // 根据同意状态决定是否采集
    public boolean shouldCollect(String dataType) {
        if (analyticsConsent != ConsentStatus.GRANTED) {
            return false;
        }
        // 敏感数据类型单独控制
        if ("imei".equals(dataType) || "idfa".equals(dataType)) {
            return additionalAdConsent;
        }
        return true;
    }
}

1.2 数据仓库分层 ​

1.2.1 分层设计 ​

游戏数据仓库采用标准的分层架构，从原始数据到业务应用逐层加工。

+-------------------------------------------------------+
|                      ADS 应用层                        |
|      玩家价值分层 | 流失预测 | LTV 报表 | 活动分析      |
+-------------------------------------------------------+
|                      DWS 汇总层                        |
|      玩家日汇总 | 道具汇总 | 充值汇总 | 活跃汇总       |
+-------------------------------------------------------+
|                      DWD 明细层                        |
|      事件事实表 | 快照事实表 | 状态事实表               |
+-------------------------------------------------------+
|                      ODS 操作层                        |
|      原始日志 | 服务端日志 | 业务库 Binlog             |
+-------------------------------------------------------+

ODS（操作数据层）

保留原始数据，不做或少做加工。

sql
-- ODS 层建表示例
CREATE TABLE ods_game_event (
    event_id        STRING COMMENT '事件ID',
    user_id         STRING COMMENT '用户ID',
    role_id         STRING COMMENT '角色ID',
    server_id       INT COMMENT '服务器ID',
    event_name      STRING COMMENT '事件名称',
    event_time      BIGINT COMMENT '事件时间(毫秒)',
    properties      MAP<STRING, STRING> COMMENT '事件属性',
    device_id       STRING COMMENT '设备ID',
    session_id      STRING COMMENT '会话ID',
    ip              STRING COMMENT 'IP地址',
    channel         STRING COMMENT '渠道',
    client_version  STRING COMMENT '客户端版本',
    country         STRING COMMENT '国家',
    province        STRING COMMENT '省份',
    city            STRING COMMENT '城市',
    isp             STRING COMMENT '运营商',
    dt              STRING COMMENT '分区日期(yyyyMMdd)'
)
PARTITIONED BY (dt STRING)
STORED AS ORC
TBLPROPERTIES (
    "orc.compress" = "SNAPPY",
    "orc.bloom.filter.columns" = "user_id,event_name",
    "partition.retention.days" = "90"
);

DWD（数据明细层）

对 ODS 层数据进行清洗、去重、格式统一、维度退化。

sql
-- DWD 层事实表
CREATE TABLE dwd_event_fact (
    event_id        STRING COMMENT '事件ID',
    user_id         STRING COMMENT '用户ID',
    role_id         STRING COMMENT '角色ID',
    server_id       INT COMMENT '服务器ID',
    event_name      STRING COMMENT '事件名称',
    event_time      BIGINT COMMENT '事件时间(毫秒)',
    event_date      STRING COMMENT '事件日期(yyyyMMdd)',
    event_hour      INT COMMENT '事件小时',
    
    -- 退化维度
    player_level    INT COMMENT '玩家等级',
    vip_level       INT COMMENT 'VIP等级',
    platform        STRING COMMENT '平台',
    channel         STRING COMMENT '渠道',
    
    -- 事件属性展开
    item_id         STRING COMMENT '道具ID',
    item_quantity   INT COMMENT '道具数量',
    cost_amount     DECIMAL(18,2) COMMENT '消费金额',
    currency_type   STRING COMMENT '货币类型',
    
    -- 技术字段
    device_type     STRING COMMENT '设备型号',
    os_version      STRING COMMENT '操作系统版本',
    network_type    STRING COMMENT '网络类型',
    ip              STRING COMMENT 'IP地址',
    country         STRING COMMENT '国家',
    province        STRING COMMENT '省份',
    dt              STRING COMMENT '分区日期'
)
PARTITIONED BY (dt STRING)
STORED AS ORC;

DWS（数据汇总层）

按日、周、月粒度的玩家汇总数据。

sql
-- DWS 层玩家日汇总表
CREATE TABLE dws_player_daily_agg (
    user_id          STRING COMMENT '用户ID',
    role_id          STRING COMMENT '角色ID',
    server_id        INT COMMENT '服务器ID',
    dt               STRING COMMENT '日期',
    
    -- 活跃指标
    login_count      INT COMMENT '登录次数',
    total_online_sec BIGINT COMMENT '在线时长(秒)',
    session_count    INT COMMENT '会话次数',
    
    -- 游戏进度
    current_level    INT COMMENT '当前等级',
    exp_gained       BIGINT COMMENT '获得经验',
    quest_completed  INT COMMENT '完成任务数',
    dungeon_cleared  INT COMMENT '通关副本数',
    
    -- 消费指标
    recharge_amount  DECIMAL(18,2) COMMENT '充值金额',
    recharge_count   INT COMMENT '充值次数',
    consumption_amount DECIMAL(18,2) COMMENT '消耗金额',
    
    -- 社交指标
    friend_count     INT COMMENT '好友数',
    guild_activity   INT COMMENT '公会活跃度',
    pvp_battle_count INT COMMENT 'PVP战斗次数',
    
    -- 资源变化
    gold_balance     BIGINT COMMENT '金币余额',
    diamond_balance  BIGINT COMMENT '钻石余额',
    gold_gained      BIGINT COMMENT '获得金币',
    gold_spent       BIGINT COMMENT '消耗金币',
    diamond_gained   BIGINT COMMENT '获得钻石',
    diamond_spent    BIGINT COMMENT '消耗钻石',
    
    -- 设备信息
    device_id        STRING COMMENT '设备ID',
    platform         STRING COMMENT '平台',
    channel          STRING COMMENT '渠道'
)
PARTITIONED BY (dt STRING)
STORED AS ORC;

ADS（应用数据层）

面向业务应用的报表数据。

sql
-- ADS 层 LTV 报表
CREATE TABLE ads_ltv_report (
    install_date     STRING COMMENT '安装日期',
    channel          STRING COMMENT '渠道',
    platform         STRING COMMENT '平台',
    country          STRING COMMENT '国家',
    
    new_user_count   BIGINT COMMENT '新增用户数',
    
    -- LTV 累计
    ltv_d1           DECIMAL(18,4) COMMENT '第1天LTV',
    ltv_d3           DECIMAL(18,4) COMMENT '第3天LTV',
    ltv_d7           DECIMAL(18,4) COMMENT '第7天LTV',
    ltv_d14          DECIMAL(18,4) COMMENT '第14天LTV',
    ltv_d30          DECIMAL(18,4) COMMENT '第30天LTV',
    
    -- 留存率
    retention_d1     DECIMAL(5,4) COMMENT '次日留存',
    retention_d3     DECIMAL(5,4) COMMENT '3日留存',
    retention_d7     DECIMAL(5,4) COMMENT '7日留存',
    retention_d14    DECIMAL(5,4) COMMENT '14日留存',
    retention_d30    DECIMAL(5,4) COMMENT '30日留存',
    
    -- ARPU
    arpu_d7          DECIMAL(18,4) COMMENT '7日ARPU',
    arpu_d30         DECIMAL(18,4) COMMENT '30日ARPU',
    
    -- ARPPU
    arppu_d7         DECIMAL(18,4) COMMENT '7日ARPPU',
    arppu_d30        DECIMAL(18,4) COMMENT '30日ARPPU',
    
    dt               STRING COMMENT '统计日期'
)
PARTITIONED BY (dt STRING)
STORED AS ORC;

1.2.2 宽表设计 ​

宽表将多个维度和指标合并到一张表中，减少 JOIN 操作，提升查询性能。

sql
-- 玩家日宽表
CREATE TABLE dws_player_daily_wide (
    -- 维度字段
    user_id          STRING,
    role_id          STRING,
    server_id        INT,
    dt               STRING,
    platform         STRING,
    channel          STRING,
    install_date     STRING,
    first_recharge_date STRING,
    
    -- 玩家属性
    player_level     INT,
    vip_level        INT,
    total_recharge   DECIMAL(18,2),
    total_consumption DECIMAL(18,2),
    register_days    INT,
    
    -- 当日行为
    login_count      INT,
    online_seconds   BIGINT,
    pvp_battles      INT,
    dungeons_cleared INT,
    quests_completed INT,
    
    -- 当日消费
    recharge_amount  DECIMAL(18,2),
    recharge_count   INT,
    gold_income      BIGINT,
    gold_expense     BIGINT,
    diamond_income   BIGINT,
    diamond_expense  BIGINT,
    
    -- 累计指标
    lifetime_recharge   DECIMAL(18,2),
    lifetime_consumption DECIMAL(18,2),
    lifetime_login_days INT,
    last_active_days_ago INT,   -- 上次活跃距今天数
    
    -- 标签字段
    player_segment   STRING COMMENT '玩家分层标签',
    churn_risk       STRING COMMENT '流失风险',
    payment_intent   STRING COMMENT '付费意愿',
    
    -- 技术字段
    device_id        STRING,
    ip_address       STRING,
    country          STRING
)
PARTITIONED BY (dt STRING)
STORED AS ORC;

1.2.3 分区策略与数据生命周期 ​

sql
-- 分区策略示例
-- 按日期分区，每天一个分区
PARTITIONED BY (dt STRING)

-- 多级分区（减少小文件数）
PARTITIONED BY (year STRING, month STRING, day STRING)

-- 按事件名分区（适合事件表的快速过滤）
PARTITIONED BY (dt STRING, event_name STRING)
yaml
# 数据生命周期配置
data_lifecycle:
  ods_log:
    raw_logs: 7 days            # 原始日志保留7天
    compressed_logs: 30 days    # 压缩后保留30天
  
  dwd_fact:
    event_fact: 90 days         # 事件事实表90天
    snapshot_fact: 180 days     # 快照事实表180天
  
  dws_agg:
    daily_agg: 365 days         # 日汇总保留1年
    weekly_agg: 2 years         # 周汇总保留2年
    monthly_agg: 5 years        # 月汇总保留5年
  
  ads_report:
    permanent: true             # 业务报表永久保留
    
  cold_storage:
    enable: true
    archive_after_days: 180     # 180天后转为冷存储
    cold_storage_engine: "OSS/Archive"

冷热分离

sql
-- 冷热数据分离查询
-- 热数据（最近30天）使用高性能存储
SELECT * FROM dwd_event_fact 
WHERE dt >= DATE_FORMAT(DATE_SUB(CURRENT_DATE, 30), 'yyyyMMdd')
  AND user_id = 'target_user';

-- 冷数据（超过30天）使用归档存储
SELECT * FROM dwd_event_fact_archive
WHERE dt < DATE_FORMAT(DATE_SUB(CURRENT_DATE, 30), 'yyyyMMdd')
  AND user_id = 'target_user';

1.2.4 维度表与事实表 ​

玩家维表

sql
CREATE TABLE dim_player (
    user_id          STRING COMMENT '用户ID',
    role_id          STRING COMMENT '角色ID',
    role_name        STRING COMMENT '角色名',
    server_id        INT COMMENT '服务器ID',
    
    -- 注册信息
    register_time    BIGINT COMMENT '注册时间',
    register_date    STRING COMMENT '注册日期',
    register_channel STRING COMMENT '注册渠道',
    register_ip      STRING COMMENT '注册IP',
    register_device  STRING COMMENT '注册设备ID',
    
    -- 当前状态
    current_level    INT COMMENT '当前等级',
    current_vip      INT COMMENT '当前VIP等级',
    total_recharge   DECIMAL(18,2) COMMENT '累计充值',
    total_consumption DECIMAL(18,2) COMMENT '累计消费',
    last_login_time  BIGINT COMMENT '最后登录时间',
    last_logout_time BIGINT COMMENT '最后登出时间',
    
    -- 属性标签
    gender           INT COMMENT '性别',
    age_group        STRING COMMENT '年龄段',
    country          STRING COMMENT '国家',
    city             STRING COMMENT '城市',
    
    -- 玩家分层
    player_segment   STRING COMMENT '玩家分层',
    lifecycle_stage  STRING COMMENT '生命周期阶段',
    payment_tier     STRING COMMENT '付费档次',
    
    -- 元数据
    etl_time         TIMESTAMP COMMENT 'ETL时间',
    version          INT COMMENT '版本号'
)
STORED AS ORC
TBLPROPERTIES ("transactional" = "true");  -- 支持缓慢变化维更新

道具维表

sql
CREATE TABLE dim_item (
    item_id          STRING COMMENT '道具ID',
    item_name        STRING COMMENT '道具名称',
    item_type        STRING COMMENT '道具类型: equipment/consumable/material/card',
    item_quality     INT COMMENT '品质: 1-白色 2-绿色 3-蓝色 4-紫色 5-橙色',
    item_subtype     STRING COMMENT '子类型: weapon/armor/accessory/potion',
    
    -- 经济属性
    base_price       DECIMAL(18,2) COMMENT '基准价格',
    sell_price       DECIMAL(18,2) COMMENT '出售价格',
    max_stack        INT COMMENT '最大堆叠数',
    is_tradeable     BOOLEAN COMMENT '是否可交易',
    is_bind_on_pickup BOOLEAN COMMENT '是否拾取绑定',
    
    -- 道具来源
    obtain_source    ARRAY<STRING> COMMENT '获取来源: [shop,drop,craft,quest]',
    drop_rate        DECIMAL(10,6) COMMENT '掉落概率',
    
    -- 分类属性
    category_path    STRING COMMENT '分类路径',
    tags             ARRAY<STRING> COMMENT '标签',
    
    etl_time         TIMESTAMP
)
STORED AS ORC;

活动维表

sql
CREATE TABLE dim_activity (
    activity_id      STRING COMMENT '活动ID',
    activity_name    STRING COMMENT '活动名称',
    activity_type    STRING COMMENT '活动类型: recharge/consumption/boss/gvg',
    
    start_time       BIGINT COMMENT '开始时间',
    end_time         BIGINT COMMENT '结束时间',
    server_ids       ARRAY<INT> COMMENT '参与服务器列表',
    
    -- 活动规则
    entry_condition  STRING COMMENT '参与条件(JSON)',
    reward_rules     STRING COMMENT '奖励规则(JSON)',
    rank_rules       STRING COMMENT '排名规则(JSON)',
    
    -- 运营信息
    operator         STRING COMMENT '运营负责人',
    budget           DECIMAL(18,2) COMMENT '活动预算',
    expected_arppu   DECIMAL(18,2) COMMENT '预期ARPPU',
    
    status           STRING COMMENT '状态: draft/published/running/ended',
    etl_time         TIMESTAMP
)
STORED AS ORC;

日期维表

sql
CREATE TABLE dim_date (
    date_key         STRING COMMENT '日期键 yyyyMMdd',
    date_value       DATE COMMENT '日期',
    year             INT COMMENT '年份',
    quarter          INT COMMENT '季度',
    month            INT COMMENT '月份',
    week             INT COMMENT '年内第几周',
    day_of_year      INT COMMENT '年内第几天',
    day_of_month     INT COMMENT '月内第几天',
    day_of_week      INT COMMENT '周内第几天(1-7)',
    is_weekend       BOOLEAN COMMENT '是否周末',
    is_holiday       BOOLEAN COMMENT '是否节假日',
    season           STRING COMMENT '季节',
    game_week        INT COMMENT '游戏运营周(开服第几周)',
    server_life_day  INT COMMENT '服务器开服天数'
)
STORED AS ORC;

事件事实表

sql
CREATE TABLE dwd_event_fact (
    event_id        STRING,
    user_id         STRING,
    role_id         STRING,
    server_id       INT,
    event_name      STRING,
    
    -- 时间维度
    event_time      BIGINT,
    dt              STRING,
    hour            INT,
    minute          INT,
    weekday         INT,
    
    -- 维度外键
    date_key        STRING COMMENT '日期维表FK',
    player_key      STRING COMMENT '玩家维表FK',
    item_key        STRING COMMENT '道具维表FK',
    activity_key    STRING COMMENT '活动维表FK',
    
    -- 事件度量
    duration_ms     BIGINT COMMENT '持续时间',
    item_count      INT COMMENT '道具数量',
    amount          DECIMAL(18,2) COMMENT '金额',
    count           INT COMMENT '计数',
    
    -- 上下文
    source          STRING COMMENT '事件来源',
    target          STRING COMMENT '事件目标',
    result          STRING COMMENT '事件结果',
    detail          STRING COMMENT '详细信息(JSON)',
    
    dt PARTITION
)
PARTITIONED BY (dt STRING)
STORED AS ORC;

累计快照事实表

sql
-- 玩家月度累计快照
CREATE TABLE dws_player_monthly_snapshot (
    user_id         STRING,
    role_id         STRING,
    server_id       INT,
    month_key       STRING COMMENT '月份 yyyyMM',
    
    -- 累计到当月的指标
    cum_login_days      INT COMMENT '累计登录天数',
    cum_recharge_amount DECIMAL(18,2) COMMENT '累计充值金额',
    cum_recharge_count  INT COMMENT '累计充值次数',
    cum_consumption     DECIMAL(18,2) COMMENT '累计消费',
    cum_dungeon_cleared INT COMMENT '累计通关副本',
    
    -- 当月指标
    month_login_days    INT COMMENT '本月登录天数',
    month_recharge      DECIMAL(18,2) COMMENT '本月充值',
    month_online_hours  DOUBLE COMMENT '本月在线小时',
    
    -- 月末状态
    end_level           INT COMMENT '月末等级',
    end_vip             INT COMMENT '月末VIP',
    end_gold_balance    BIGINT COMMENT '月末金币余额',
    end_diamond_balance BIGINT COMMENT '月末钻石余额',
    
    etl_time TIMESTAMP
)
PARTITIONED BY (month_key STRING)
STORED AS ORC;

1.2.5 数据仓库建模方法 ​

星型模型

事实表在中心，维度表呈辐射状围绕。查询性能好，适合 OLAP。

sql
-- 星型模型示例
-- 事实表
CREATE TABLE fact_recharge (
    recharge_id     STRING,
    user_id         STRING,
    date_key        STRING,    -- 关联 dim_date
    item_key        STRING,    -- 关联 dim_item
    channel_key     STRING,    -- 关联 dim_channel
    amount          DECIMAL(18,2),
    payment_method  STRING,
    status          STRING
);

-- 维度表
-- dim_date, dim_player, dim_item, dim_channel 分别在一侧

雪花模型

维度表进一步规范化，拆分为子维度表。节省存储空间，但查询需要更多 JOIN。

sql
-- 雪花模型示例
-- dim_player 进一步拆分
CREATE TABLE dim_player (
    user_id     STRING PRIMARY KEY,
    register_channel_key STRING,  -- 关联 dim_channel
    register_device_key  STRING,  -- 关联 dim_device
    location_key         STRING,  -- 关联 dim_location
    ...
);

CREATE TABLE dim_device (
    device_key      STRING PRIMARY KEY,
    device_model    STRING,
    os_type         STRING,
    os_version      STRING,
    screen_size     STRING,
    brand           STRING
);

CREATE TABLE dim_location (
    location_key    STRING PRIMARY KEY,
    country         STRING,
    province        STRING,
    city            STRING,
    isp             STRING
);

事实星座（事实星系）

多个事实表共享维度表，形成星座结构。

sql
-- 事实星座：多个事实表共享维度
-- 共享维度：dim_date, dim_player, dim_server, dim_channel

-- 事实表1：充值事实
CREATE TABLE fact_recharge (
    recharge_id STRING,
    date_key    STRING,    -- 共享 dim_date
    user_id     STRING,    -- 共享 dim_player
    server_id   INT,       -- 共享 dim_server
    ...
);

-- 事实表2：关卡通关事实
CREATE TABLE fact_dungeon (
    dungeon_id  STRING,
    date_key    STRING,    -- 共享 dim_date
    user_id     STRING,    -- 共享 dim_player
    server_id   INT,       -- 共享 dim_server
    ...
);

-- 事实表3：社交行为事实
CREATE TABLE fact_social (
    social_id   STRING,
    date_key    STRING,    -- 共享 dim_date
    user_id     STRING,    -- 共享 dim_player
    ...
);

1.3 核心指标 ​

1.3.1 用户指标 ​

sql
-- DAU 计算
SELECT dt, COUNT(DISTINCT user_id) AS dau
FROM dwd_event_fact
WHERE event_name = 'login'
  AND dt >= '20260701'
  AND dt <= '20260707'
GROUP BY dt
ORDER BY dt;

-- WAU 计算
SELECT 
    DATE_FORMAT(DATE_SUB(dt, (DAYOFWEEK(dt) - 1) % 7), 'yyyyMMdd') AS week_start,
    COUNT(DISTINCT user_id) AS wau
FROM dwd_event_fact
WHERE event_name = 'login'
GROUP BY DATE_FORMAT(DATE_SUB(dt, (DAYOFWEEK(dt) - 1) % 7), 'yyyyMMdd');

-- MAU 计算
SELECT 
    DATE_FORMAT(dt, 'yyyyMM') AS month,
    COUNT(DISTINCT user_id) AS mau
FROM dwd_event_fact
WHERE event_name = 'login'
GROUP BY DATE_FORMAT(dt, 'yyyyMM');

-- DNU (日新增用户)
SELECT 
    dt,
    COUNT(DISTINCT p.user_id) AS dnu
FROM dim_player p
WHERE p.register_date = dt
  AND dt >= '20260701'
GROUP BY dt;

留存率

sql
-- 次日/7日/30日留存率
WITH install_cohort AS (
    SELECT 
        user_id,
        register_date AS install_date
    FROM dim_player
    WHERE register_date >= '20260701'
),
login_dates AS (
    SELECT DISTINCT 
        user_id,
        dt
    FROM dwd_event_fact
    WHERE event_name = 'login'
),
retention_calc AS (
    SELECT 
        i.install_date,
        COUNT(DISTINCT i.user_id) AS install_users,
        COUNT(DISTINCT CASE WHEN DATEDIFF(l.dt, i.install_date) = 1 THEN i.user_id END) AS retention_d1,
        COUNT(DISTINCT CASE WHEN DATEDIFF(l.dt, i.install_date) = 6 THEN i.user_id END) AS retention_d7,
        COUNT(DISTINCT CASE WHEN DATEDIFF(l.dt, i.install_date) = 29 THEN i.user_id END) AS retention_d30
    FROM install_cohort i
    LEFT JOIN login_dates l ON i.user_id = l.user_id
    GROUP BY i.install_date
)
SELECT 
    install_date,
    install_users,
    ROUND(retention_d1 / install_users, 4) AS retention_rate_d1,
    ROUND(retention_d7 / install_users, 4) AS retention_rate_d7,
    ROUND(retention_d30 / install_users, 4) AS retention_rate_d30
FROM retention_calc
ORDER BY install_date;

渠道留存

sql
-- 各渠道留存率对比
SELECT 
    p.register_channel,
    COUNT(DISTINCT p.user_id) AS install_users,
    ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(l.dt, p.register_date) = 1 THEN p.user_id END) 
        / COUNT(DISTINCT p.user_id), 4) AS retention_d1,
    ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(l.dt, p.register_date) = 6 THEN p.user_id END) 
        / COUNT(DISTINCT p.user_id), 4) AS retention_d7,
    ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(l.dt, p.register_date) = 29 THEN p.user_id END) 
        / COUNT(DISTINCT p.user_id), 4) AS retention_d30
FROM dim_player p
LEFT JOIN dwd_event_fact l 
    ON p.user_id = l.user_id 
    AND l.event_name = 'login'
    AND l.dt >= '20260701'
WHERE p.register_date >= '20260701'
GROUP BY p.register_channel;

1.3.2 行为指标 ​

sql
-- 游戏时长分布
SELECT 
    dt,
    CASE 
        WHEN total_online_sec < 600 THEN '0-10min'
        WHEN total_online_sec < 1800 THEN '10-30min'
        WHEN total_online_sec < 3600 THEN '30-60min'
        WHEN total_online_sec < 7200 THEN '1-2h'
        ELSE '2h+'
    END AS duration_bucket,
    COUNT(DISTINCT user_id) AS player_count
FROM dws_player_daily_agg
WHERE dt = '20260712'
GROUP BY dt, 
    CASE 
        WHEN total_online_sec < 600 THEN '0-10min'
        WHEN total_online_sec < 1800 THEN '10-30min'
        WHEN total_online_sec < 3600 THEN '30-60min'
        WHEN total_online_sec < 7200 THEN '1-2h'
        ELSE '2h+'
    END;

-- 登录频次
SELECT 
    player_segment,
    AVG(login_days_per_week) AS avg_login_days_per_week,
    AVG(sessions_per_day) AS avg_sessions_per_day
FROM (
    SELECT 
        user_id,
        p.player_segment,
        COUNT(DISTINCT dt) / 7 AS login_days_per_week,
        AVG(login_count) AS sessions_per_day
    FROM dws_player_daily_agg a
    JOIN dim_player p ON a.user_id = p.user_id
    WHERE a.dt >= DATE_SUK('20260712', 7)
    GROUP BY user_id, p.player_segment
) t
GROUP BY player_segment;

-- 关卡通过率
SELECT 
    dungeon_id,
    dungeon_name,
    entry_count,
    clear_count,
    ROUND(clear_count / entry_count, 4) AS pass_rate,
    AVG(clear_duration) AS avg_clear_time_sec
FROM (
    SELECT 
        d.dungeon_id,
        d.dungeon_name,
        COUNT(CASE WHEN e.event_name = 'dungeon_entry' THEN 1 END) AS entry_count,
        COUNT(CASE WHEN e.event_name = 'dungeon_clear' THEN 1 END) AS clear_count,
        AVG(CASE WHEN e.event_name = 'dungeon_clear' THEN e.duration_ms / 1000 END) AS clear_duration
    FROM dwd_event_fact e
    JOIN dim_dungeon d ON e.item_id = d.dungeon_id
    WHERE e.event_name IN ('dungeon_entry', 'dungeon_clear')
      AND e.dt >= '20260701'
    GROUP BY d.dungeon_id, d.dungeon_name
) t
ORDER BY pass_rate;

-- 等级分布
SELECT 
    current_level,
    COUNT(DISTINCT user_id) AS player_count,
    ROUND(COUNT(DISTINCT user_id) / SUM(COUNT(DISTINCT user_id)) OVER(), 4) AS pct
FROM dws_player_daily_agg
WHERE dt = '20260712'
GROUP BY current_level
ORDER BY current_level;

1.3.3 消费指标 ​

ARPU / ARPPU / LTV

sql
-- ARPU (每用户平均收入)
SELECT 
    dt,
    SUM(recharge_amount) / COUNT(DISTINCT user_id) AS arpu
FROM dws_player_daily_agg
WHERE dt >= '20260701'
GROUP BY dt;

-- ARPPU (每付费用户平均收入)
SELECT 
    dt,
    SUM(recharge_amount) / COUNT(DISTINCT CASE WHEN recharge_amount > 0 THEN user_id END) AS arppu
FROM dws_player_daily_agg
WHERE dt >= '20260701'
GROUP BY dt;

-- LTV (生命周期价值)
WITH cohort AS (
    SELECT 
        user_id,
        register_date,
        MIN(dt) AS first_recharge_date
    FROM dim_player p
    LEFT JOIN dwd_event_fact e ON p.user_id = e.user_id AND e.event_name = 'recharge'
    WHERE p.register_date >= '20260701'
    GROUP BY user_id, register_date
)
SELECT 
    c.register_date,
    COUNT(DISTINCT c.user_id) AS new_users,
    -- 第1天 LTV
    ROUND(COALESCE(SUM(CASE WHEN DATEDIFF(f.dt, c.register_date) = 0 THEN f.amount END), 0) 
        / COUNT(DISTINCT c.user_id), 4) AS ltv_d1,
    -- 第7天 LTV
    ROUND(COALESCE(SUM(CASE WHEN DATEDIFF(f.dt, c.register_date) BETWEEN 0 AND 6 THEN f.amount END), 0) 
        / COUNT(DISTINCT c.user_id), 4) AS ltv_d7,
    -- 第30天 LTV
    ROUND(COALESCE(SUM(CASE WHEN DATEDIFF(f.dt, c.register_date) BETWEEN 0 AND 29 THEN f.amount END), 0) 
        / COUNT(DISTINCT c.user_id), 4) AS ltv_d30
FROM cohort c
LEFT JOIN dwd_event_fact f ON c.user_id = f.user_id AND f.event_name = 'recharge'
GROUP BY c.register_date
ORDER BY c.register_date;

LTV 分渠道

sql
-- 渠道 LTV
SELECT 
    p.register_channel,
    p.register_date,
    COUNT(DISTINCT p.user_id) AS new_users,
    ROUND(COALESCE(SUM(CASE WHEN DATEDIFF(f.dt, p.register_date) BETWEEN 0 AND 6 THEN f.amount END), 0) 
        / COUNT(DISTINCT p.user_id), 4) AS ltv_d7,
    ROUND(COALESCE(SUM(CASE WHEN DATEDIFF(f.dt, p.register_date) BETWEEN 0 AND 29 THEN f.amount END), 0) 
        / COUNT(DISTINCT p.user_id), 4) AS ltv_d30
FROM dim_player p
LEFT JOIN dwd_event_fact f 
    ON p.user_id = f.user_id 
    AND f.event_name = 'recharge'
WHERE p.register_date >= '20260701'
GROUP BY p.register_channel, p.register_date;

付费率与付费留存

sql
-- 付费率
SELECT 
    dt,
    COUNT(DISTINCT CASE WHEN recharge_amount > 0 THEN user_id END) AS paying_users,
    COUNT(DISTINCT user_id) AS active_users,
    ROUND(COUNT(DISTINCT CASE WHEN recharge_amount > 0 THEN user_id END) 
        / COUNT(DISTINCT user_id), 4) AS payment_rate
FROM dws_player_daily_agg
WHERE dt >= '20260701'
GROUP BY dt;

-- 付费留存（首次付费后持续付费情况）
WITH first_payment AS (
    SELECT 
        user_id,
        MIN(dt) AS first_pay_date
    FROM dwd_event_fact
    WHERE event_name = 'recharge'
    GROUP BY user_id
)
SELECT 
    DATEDIFF(f.dt, fp.first_pay_date) AS days_after_first_pay,
    COUNT(DISTINCT fp.user_id) AS cohort_users,
    COUNT(DISTINCT CASE WHEN f.amount > 0 THEN fp.user_id END) AS retained_payers,
    ROUND(COUNT(DISTINCT CASE WHEN f.amount > 0 THEN fp.user_id END) 
        / COUNT(DISTINCT fp.user_id), 4) AS payment_retention_rate
FROM first_payment fp
JOIN dws_player_daily_agg f ON fp.user_id = f.user_id
WHERE fp.first_pay_date >= '20260701'
GROUP BY DATEDIFF(f.dt, fp.first_pay_date)
ORDER BY days_after_first_pay;