1. 前言：SQL在字节跳动数据驱动中的核心地位

在字节跳动，数据是驱动一切业务决策和产品选代的基石。作为公司内部最核心的数据分析工具之一，SQL（Structured QueryLanguage）扮演着至关重要的角色。无论是日常的业务监控、用户行为分析、广告效果评估，还是复杂的数据挖掘和模型训练，SQL都是数据工程师和分析师们不可或缺的利器。字节跳动拥有海量的用户数据和复杂的业务场景，例如抖音、今日头条等产品的用户增长分析穿山甲等广告平台的投放效果优化，以及各类创新业务的探索，都离不开高效、准确的 SQL查询与分析。因此，掌握扎实的SQL技能，并能够结合具体业务场景灵活运用，是每一位字节跳动数据从业者的基本素养。

本手册旨在总结字节跳动在SQL使用方面的最佳实践和常见业务场景下的应用案例，为内部员工提供一份实用、硬核的SQL指导，帮助大家更好地利用数据赋能业务，提升工作效率和分析深度。字节跳动内部拥有庞大的数据生态，涵盖了用户行为日志、广告投放数据、内容元数据、社交互动数据等多种类型，这些数据存储在分布式数据仓库（如 Apache Hive、Google BigQuery）和各类数据库（如MySQL）中。SQL作为与这些数据系统交互的标准语言，其重要性不言而喻。

2. SQL 基础与字节跳动最佳实践

2.1. 字节跳动常用 SQL 语法与规范

在字节跳动，我们主要使用类SQL语言（如 Hive SQL、Spark SQL）进行大规模数据处理和分析。虽然不同的执行引擎可能在语法细节上略有差异，但核心的 SQL标准是共通的。我们强调编写清晰、易读、高效的 SQL 代码。

在命名规范方面，表名、字段名应使用有意义的英文单词或缩写，并采用下划线分隔，例如user_behavior_log
对于临时表或子查询，可以使用 tmp_ 作为前缀
在代码风格上，建议使用缩进和换行来组织复杂的查询逻辑，关键字（如 SELECT,FROM,WHERE, GROUP BY, ORDER BY）建议大写，列名和表名使用小写
注释是良好编程习惯的体现，对于复杂的逻辑或关键步骤，应添加必要的注释说明。
此外，字节跳动内部可能会有针对特定数据仓库或平台的SQL编写规范，例如分区表的使用、特定UDF（UserDefined Function）的调用方式等，需要严格遵守；例如，在处理大规模数据集时，应**充分利用分区裁剪（Partition Pruning）和谓词下推（Predicate Pushdown）**等优化特性，避免全表扫描，提升查询效率
在引用字段时，建议始终使用表别名或表名前缀，以避免歧义和提高可读性

2.2.性能优化：高效 SQL 编写技巧

在字节跳动这样数据量巨大的环境中，SQL查询的性能优化至关重要。

首先，理解数据分布和表结构是优化的前提。需要了解表的分区方式、索引情况、数据倾斜等信息。
其次，避免不必要的计算和数据移动。例如，在 JOIN 操作前，尽量通过 WHERE 条件过滤掉不需要的数据，减少参与 JOIN 的数据量。
- 对于聚合操作，可以先对子查询进行聚合，再与其他表JOIN，而不是先JOIN 再聚合。
合理使用JOIN类型，例如，当一个小表与大表JOIN时，可以考虑使用MAPJOIN（BroadcastJoin）将小表广播到所有节点，避免 Shuffle。
- 对于大表与大表的 JOIN，需要注意是否存在数据倾斜问题，可以通过倾斜键处理（如加盐）或调整 JOIN 策略来解决。
利用窗口函数替代自连接或子查询，窗口函数通常比自连接或复杂的子查询更高效，尤其是在进行组内排序、计算累计值等场景。
注意数据类型的匹配，避免在JOIN或 WHERE 条件中进行隐式类型转换，这可能导致索引失效或计算错误。
减少数据重复读取，对于需要多次使用的中间结果，可以将其写入临时表或使用CTE（Common Table Expression）
最后，关注执行计划，通过分析SQL的执行计划，可以了解查询的瓶颈所在，例如是否存在数据倾斜、是否有效利用了索引等。字节跳动内部提供了一些性能分析工具，可以帮助定位和解决SQL性能问题。

2.3.窗口函数在复杂分析中的应用

窗口函数是SQL中非常强大且常用的功能，尤其在字节跳动复杂的业务分析场景中，能够极大地简化查询逻辑并提升效率。

窗口函数允许我们在不减少原表行数的情况下，对数据进行分组、排序并进行各种计算。

常见的窗口函数包括聚合窗口函数（如 SUM,AVG,COUNT,MAX,MIN）、排序窗口函数（如ROW_NUMBER,RANK,DENSE_RANK）以及偏移窗口函数(如 LAG,LEAD）。例如

在用户行为分析中，我们经常需要计算每个用户的首次购买时间、最近一次购买时间、购买次数等，这时就可以使用 FIRST_VALUE,LAST_VALUECOUNT 配合 OVER(PARTITION BY user id ORDER BY order_time) 来实现。
在计算连续登录天数、连续观看视频等场景，LAG和LEAD函数可以帮助我们获取前一行或后一行的数据，从而判断连续性例如，通过比较当前登录日期与前一天(通过 LAG 获取)的登录日期可以判断用户是否连续登录。
窗口函数还可以用于解决Top N问题，例如找出每个品类下销售额最高的前10个商品，可以使用 ROW_NUMBER() OVER(PARTITION BY category_id ORDER BYsales amount DESC) 并筛选行号小于等于 10 的记录。

在字节跳动窗口函数被广泛应用于用户留存分析、活跃度分析、斗分析、RFM模型构建等多个方面。掌握窗口函数的使用，能够显著提升数据分析的深度和广度。

3.SQL在字节跳动核心业务场景的应用

3.1.用户增长分析

3.1.1.案例:用户留存率与活跃度分析

用户留存率和活跃度是衡量产品健康度和用户粘性的核心指标。在字节跳动，我们通过 SQL对这些指标进行精细化分析。

用户留存率通常指新用户在特定时间窗口后仍然活跃的比例，例如次日留存、7日留存、30 日留存。

计算留存率首先需要确定新增用户集合和活跃用户集合。SQL实现上，通常会先查询出每日的新增用户列表，然后与后续日期的活跃用户表进行关联。例如，要计算 2023年10月1日新增用户的7日留存率，我们会先筛选出2023年10月1日首次出现的用户ID，然后左连接2023年10月8日的活跃用户ID列表(活跃定义为有任意行为记录)，统计连接后非空的用户数除以新增用户总数。SQL中会用到 DISTINCT 去重，LEFTJOIN，以及日期函数(如DATE_ADD 或INTERVAL)来计算后续日期。

用户活跃度是指关注用户在特定周期内的活跃情况，如日活跃用户数(DAU)、周活跃用户数(WAU)、月活跃用户数(MAU)。

计算 DAU 通常是对用户行为日志表按日期分组，并统计当天的独立用户数(COUNT(DISTINCTuser_id))更细致的活跃度分析可能包括用户平均使用时长、平均启动次数等这些都需要对用户行为日志进行聚合计算。例如，在抖音用户行为分析中，可以通过分析用户观看历史和互动行为，计算用户在不同时间段的活跃度，如晚上19:00-24:00 是用户主要活跃时间。这些分析结果会指导产品迭代和运营策略，以提升用户留存和活跃。

3.1.2.案例:用户行为路径与漏斗分析

用户增长分析中，理解用户行为路径并进行漏斗分析是至关重要的环节。

通过 SQL对用户行为数据进行挖掘，可以清晰地描绘用户从接触产品到完成特定目标(如注册、付费、持续活跃)的完整路径，并识别出路径中的关键转化节点和流失节点。例如，在短视频产品中，分析师可能会关注用户从打开 App、浏览推荐流、点击视频、完整观看、点赞评论到分享的整个行为链条。

通过SQL查询，可以统计在每个步骤的用户数量，进而计算各步骤之间的转化率，形成转化漏斗。例如，可以分析从视频曝光到点击的转化率，从点击到完整播放的转化率，以及从播放到互动的转化率等。这些数据能够帮助产品团队发现用户体验的瓶颈，例如某个环节的转化率远低于预期，可能意味着该环节的设计存在问题或者用户引导不足。针对这些发现，团队可以进行针对性的优化，例如改进 U/UX 设计、调整内容推荐策略或优化新用户引导流程，从而提升整体用户增长效果。

字节跳动的用户增长团队会利用 SQL进行深度数据分析，搭建增长指标体系，并通过 A/B 测试等方法验证优化效果，持续驱动业务增长。

一个具体的SQL应用案例是分析用户在一天内连续观看某一类别四个视频的行为。假设存在一张用户行为表user_video_behavior，包含字段user_id(用户 ID)，video_id(视频ID)，category(视频类别)，和watch_time(观看时间点)。业务需求是提取出在一天内连续观看同一类别至少四个视频的用户ID 和视频ID。这个问题涉及到对用户行为序列的分析，是用户行为路径分析中的一个具体场景。

解决这类问题通常需要借助SQL的窗口函数。首先，需要按用户和观看时间对数据进行排序，然后对每个用户的观看记录，判断其连续观看的视频是否属于同一类别，并且数量达到四个。这可能需要通过计算行号、与前一行或后一行数据进行比较等操作来实现。例如，可以为每个用户的观看记录按照时间升序编号，然后计算同一类别视频的连续观看次数或者通过时间差判断观看行为是否连续。

这类分析有助于识别出对特定内容有深度兴趣的用户群体，为个性化推荐、内容运营以及用户分群提供数据支持。字节跳动在面试数据分析师时，也会考察此类SQL问题的解决能力，以评估候选人对用户行为分析和 SQL技能的掌握程度。

解决 “提取一天内连续观看某一类别4个视频的用户id和视频id” 这类问题，可以运用 SQL中的窗口函数进行高效处理。首先，我们需要明确数据表结构，假设表名为user_video log,包含字段：user_id(用户ID)，video_id(视频ID)，category(观看视频类别)watch_timestamp(观看时间点，精确到秒或毫秒)。解题思路通常分为以下几个步骤:

数据预处理与排序：首先，筛选出指定日期范围内的数据，并按用户 ID 和观看时间进行排序。这可以通过 WHERE 子句和 ORDERBY 子句实现。
利用窗口函数标记连续行为：核心步骤是使用窗口函数(如ROW NUMBER()，LAG()，LEAD())来识别连续的观看行为。可以为每个用户的观看记录按照观看时间升序编号。然后，通过LAG()函数获取前一条记录的观看类别和时间，或者通过LEAD()函数获取后一条记录的信息。通过比较当前记录与前一条或后一条记录的类别和时间差，可以判断观看行为是否连续且属于同一类别。
分组与筛选：根据上一步的标记，对用户和视频类别进行分组，并计算连续观看的视频数量如果某个用户在某一个类别下存在连续观看数量大于等于4的情况，则满足条件。
结果提取：最终提取出满足条件的用户ID和视频ID。

一个可能的SQL实现思路(伪代码，具体语法可能因数据库系统而异)：

WITH ranked_logs AS (
    SELECT
        user_id,
        video_id,
        category,
        watch_timestamp,
        LAG(category, 1) OVER (PARTITION BY user_id ORDER BY watch_timestamp) AS prev_category,
        LAG(watch_timestamp, 1) OVER (PARTITION BY user_id ORDER BY watch_timestamp) AS prev_watch_timestamp
    FROM user_video_log
    WHERE DATE(watch_timestamp) = '特定日期' -- 假设 watch_timestamp 含日期
),
consecutive_groups AS (
    SELECT
        user_id,
        video_id,
        category,
        watch_timestamp,
        SUM(
            CASE
                WHEN prev_category IS NULL THEN 1
                WHEN category = prev_category THEN 0
                ELSE 1
            END
        ) OVER (
            PARTITION BY user_id
            ORDER BY watch_timestamp
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS group_id
    FROM ranked_logs
),
consecutive_counts AS (
    SELECT
        user_id,
        video_id,
        category,
        group_id,
        COUNT(*) OVER (PARTITION BY user_id, category, group_id) AS consecutive_count
    FROM consecutive_groups
)
SELECT DISTINCT user_id, video_id
FROM consecutive_counts
WHERE consecutive_count >= 4;

在这个思路中，

ranked_logs CTE 用于获取前一条记录的类别和时间并为每条记录编号
consecutive_groups CTE 通过判断当前记录与前一条记录是否属于同一类别且时间间隔在一定范围内，来为连续的观看行为分配相同的 group_id。
consecutive_counts CTE 则计算每个 group_id 下的记录数量，即连续观看的次数。
最后，筛选出连续观看次数大于等于4的记录，并去重得到用户ID和视频ID。

这种类型的SQL查询在用户行为分析中非常常见，能够帮助分析师深入理解用户的兴趣偏好和内容消费习惯，为产品优化和个性化推荐提供数据洞察字节跳动在实际业务中，可能会对这类查询进行进一步的优化，例如考虑视频时长、是否完播等因素，以获得更精准的分析结果。

3.2.广告效果评估

3.2.1.案例:广告点击率（CTR）与转化率（CVR）计算

广告点击率（CTR）和转化率（CVR）是衡量广告效果最核心的两个指标。在字节跳动，我们通过 SQL对海量的广告曝光、点击和转化数据进行统计，以评估广告活动的表现。

**广告点击率（CTR）**的计算公式为:CTR=(广告点击次数 /广告曝光次数)*100%。

在SQL中，我们通常会有一张广告曝光日志表和一张广告点击日志表。计算 CTR 时，首先需要统计特定广告（或广告系列、渠道等维度）在一定时间范围内的总曝光次数和总点击次数。这可以通过对曝光日志表按广告 ID 分组并计数，然后左连接（或内连接，取决于数据完整性）点击日志表，同样按广告 ID 分组并计数点击事件。例如:

SELECT
    ad_id,
    COUNT(DISTINCT impression_id) AS impressions,
    COUNT(DISTINCT click_id) AS clicks,
    CASE
        WHEN COUNT(DISTINCT impression_id) = 0 THEN 0
        ELSE COUNT(DISTINCT click_id) * 1.0 / COUNT(DISTINCT impression_id)
    END AS ctr
FROM ad_impression_log
LEFT JOIN ad_click_log USING (ad_id, impression_id) -- 假设点击表包含 impression_id
WHERE date BETWEEN '2023-10-01' AND '2023-10-07'
GROUP BY ad_id;

广告转化率(CVR)的计算公式为：CVR=(转化次数/广告点击次数)*100%

转化可以是多种行为，如下载 App、注册、购买商品等。计算 CVR 需要广告点击日志表和转化行为日志表。与 CTR 计算类似先统计点击次数，然后通过用户ID 或点击ID 关联转化行为表，统计转化次数。例如，如果转化行为是购买，那么需要关联订单表，统计由特定广告点击带来的订单数。SQL查询会涉及到对点击日志和转化日志的 JOIN 操作，并按广告维度进行聚合。字节跳动内部的数据表结构可能更为复杂，可能包含多个事实表和维度表，需要根据具体的业务逻辑和数据模型来编写 SQL。例如，在分析用户兴趣和购买行为后，字节跳动能够为广告主推荐最合适的投放时机和平台，从而最大化广告的曝光和转化。这些 CTR和 CVR 数据是广告优化师进行投放策略调整的重要依据。

3.2.2.案例:A/B 测试效果评估与 SQL实现

A/B 测试是字节跳动进行产品选代和策略优化的重要手段，SQL在A/B 测试的效果评估中扮演着核心角色。A/B 测试的基本原理是将用户随机分配到不同的组(例如，对照组A和实验组 B)，对照组使用现有版本或策略，实验组使用新版本或新策略，然后通过比较两组在关键指标上的表现来判断新版本或新策略的效果。在字节跳动，A/B 测试广泛应用于广告投放策略优选、运营推送活动文案赛马等场景字节跳动内部拥有强大的 A/8 测试平台 DataTester(对外产品名为火山引擎 A/B 测试 DataTester，内部代号 Libra)，该平台支撑了公司内部海量的 A/B 实验需求，每日新增实验数量巨大，覆盖了广泛的业务线。SQL在 A/B 测试的各个环节都扮演着重要角色，包括实验设计数据收集、指标计算和结果分析。

在 A/B 测试的 SQL实现中，首先需要明确实验的目标和假设，并选择合适的关键指标。例如，在评估一个新推荐算法时，关键指标可能包括推荐商品的销售额、点击率和转化率。其次，需要确定实验的样本量和实验周期，确保结果具有统计学意义。SQL可以用于从用户表中随机抽样并分配到不同的实验组和对照组。在数据收集阶段，SQL用于从行为日志表中提取用户在实验期间的行为数据，如曝光、点击、购买等。例如，火山引擎 DataTester 平台在指标查询时，会将用户定义的算子转换为 SQL进行查询，其查询逻辑可能包括实时扫描事件表、根据用户首次进组时间过滤用户以及进行聚合运算。一个典型的 A/B 测试查询 SQL可能包含对事件表的扫描、用户分组的过滤以及最终指标的聚合计算，例如计算每个实验组的 UV、事件值的总和以及平方和，用于后续的统计检验

在广告投放的 A/B 测试中，常见的衡量指标是点击率(CTR)和转化率(CVR)。SQL查询需要能够准确地计算出实验组和对照组的这些指标。例如，可以构建一个 SQL查询，统计每个实验分组中广告的曝光次数、点击次数和转化次数，然后计算相应的 CTR和 CVR。为了判断实验结果的显著性，通常需要进行假设检验，如Z检验或T检验。SQL 可以用于计算检验统计量所需的中间数据，例如每个组的样本量、均值、方差等。例如，在评估两个方案(方案A和方案 B)的支付按钮点击率时，可以收集各实验组的人数、点击次数，计算点击率均值和方差，然后利用 SQL进行Z值的计算和P值的推导，最终判断哪个方案更优。

字节跳动的 A/B 测试平台 DataTester 能够支持复杂的实验设计和指标计算，并通过 SQL进行高效的数据处理和查询，从而帮助业务快速做出数据驱动的决策。字节跳动在 A/B 测试的实践中非常注重实验的严谨性和结果的可靠性，会设定明确的原假设和备择假设，选择合适的显著性水平，并根据指标的预期提升确定样本量和实验周期。

3.2.3.案例:广告评论情感分析与比例计算

在字节跳动的广告业务中，评估广告效果不仅依赖于点击率(CTR)、转化率(CVR)等量化指标，用户对广告的评论内容和情感倾向也是衡量广告质量和用户接受度的重要维度。通过分析广告评论，我们可以了解用户对广告创意的喜好、对产品或服务的疑虑、以及潜在的负面反馈，从而为广告优化提供有价值的洞察。SQL在此过程中扮演着数据提取和初步处理的关键角色。例如，我们可以编写 SQL查询，从评论表中筛选出针对特定广告(如“劳动节衬衫促销”)的评论，并关联用户表获取用户画像信息，以便进行更细致的用户分群分析。虽然 SQL本身不直接进行复杂的自然语言处理和情感分析(这通常需要借助 Python 等编程语言和专门的 NLP库)，但 SQL可以高效地完成数据清洗、筛选、聚合等预处理工作，为后续的深度分析奠定基础。

一个具体的应用场景是计算广告评论的比例，例如，计算不同广告在信息流(feed)和动态(moments)中的评论比例。假设我们有ads表(包含广告 ID 和广告名称)、feed comments 表(记录信息流中的广告评论，包含ad_id,user_id,comment_id等字段)和moments_comments表(记录动态中的广告评论，包含ad_iduser_id, comment_id 等字段)。我们可以通过 SQL的 JOIN 操作和聚合函数来实现这一目标。首先，我们需要分别统计每个广告在feed_comments和 moments_comments 表中的评论数量。这可以通过对 ad_id 进行分组并计数 comment_id 来实现。然后，将这两个结果集通过 ad_id 进行左连接或全外连接，以确保即使某个广告在其中一个平台没有评论也能被统计到。最后，计算评论比例。例如，可以计算信息流评论数占总评论数(信息流评论数+动态评论数)的比例。

SQL查询可能如下所示(具体表结构和字段名需根据实际情况调整)：

WITH feed_comment_counts AS (
    SELECT ad_id, COUNT(comment_id) AS feed_comment_count
    FROM feed_comments
    GROUP BY ad_id
),
moments_comment_counts AS (
    SELECT ad_id, COUNT(comment_id) AS moments_comment_count
    FROM moments_comments
    GROUP BY ad_id
)
SELECT
    a.ad_id,
    a.ad_name,
    COALESCE(f.feed_comment_count, 0) AS feed_comments,
    COALESCE(m.moments_comment_count, 0) AS moments_comments,
    CASE
        WHEN (COALESCE(f.feed_comment_count, 0) + COALESCE(m.moments_comment_count, 0)) = 0 THEN 0
        ELSE COALESCE(f.feed_comment_count, 0) * 1.0
             / (COALESCE(f.feed_comment_count, 0) + COALESCE(m.moments_comment_count, 0))
    END AS feed_comment_ratio,
    CASE
        WHEN (COALESCE(f.feed_comment_count, 0) + COALESCE(m.moments_comment_count, 0)) = 0 THEN 0
        ELSE COALESCE(m.moments_comment_count, 0) * 1.0
             / (COALESCE(f.feed_comment_count, 0) + COALESCE(m.moments_comment_count, 0))
    END AS moments_comment_ratio
FROM ads a
LEFT JOIN feed_comment_counts f ON a.ad_id = f.ad_id
LEFT JOIN moments_comment_counts m ON a.ad_id = m.ad_id;

在这个查询中，我们使用了公共表表达式(CTE)feed _comment_counts和moments_comment_counts 来分别计算每个广告在两个平台的评论数。然后，通过左连接将这两个CTE的结果连接到 ads 表，并使用 COALESCE 函数处理可能存在的 NULL值(即广告在某个平台没有评论的情况)。最后，通过 CASE WHEN 语句计算评论比例，并处理了除数为零的情况。通过这类 SQL分析，我们可以识别出哪些广告在哪些平台引发了更多的用户讨论，从而指导广告投放策略的优化。字节跳动在其广告业务中，会利用用户画像和行为数据精准定位目标用户群体，而广告评论作为用户行为的直接反馈其分析结果对于优化用户画像和提升广告投放精准度同样具有重要意义。

3.3.内容推荐优化

3.3.1.案例:视频内容好评率计算

在字节跳动的业务实践中，准确评估视频内容的质量和用户喜好对于优化推荐算法至关重要。好评率是衡量视频内容受欢迎程度的关键指标之一。一个常见的需求是计算特定时间段内，特定分类下视频的好评率。例如，在西瓜视频的“2020百大人气创作者“优质内容扶持项目中，需要统计 2020年11月01日至2020年11月30日期间创作的视频中，“科技“大类下“数码测评”子类的视频好评率。

好评率的计算公式直接影响评估的准确性和鲁棒性。一种被提及的计算方式是好评率 = 好评数/视频观看次数。

然而，在另一些场景或面试题中为了平滑数据，尤其是在评价数量较少时避免极端情况，可能会采用更复杂的公式，例如 好评率=(好评数 +3)/(总评论数 + 7)。这种调整类似于贝叶斯平均，旨在引入一个先验估计，使得在数据稀疏时，好评率不会过于偏向0或1，从而更稳定地反映内容的潜在质。

在具体的SQL实现层面，计算好评率通常需要关联用户行为表(如content_action_info)和视频详情表(如 dim_content)。用户行为表记录了用户对视频的各种互动，如点赞、差评、无评价等，同时包含视频ID(content_id)和创建时间(create_time)等信息。视频详情表则存储了视频的元数据，如创作者ID(creator_id)、内容类目(content_category)和子类目(content_sub_category)等。以计算 “科技” 大类下 “数码测评” 子类视频的好评率(按好评数 /视频观看次数计算)为例，SQL查询的核心逻辑如下：

数据筛选与关联：首先，通过 WHERE 子句限定时间范围(a.create time BETWEEN'2020-11-01'AND'2020-11-30')和内容分类(b.content category='科技' AND b.content_sub_category ='数码测评')。然后，通过 JOIN 操作将用户行为表content_actioninfo(别名 a)和视频详情表dim_content(别名 b)在content_id上关联起来。
指标计算 a. 总行为数(视频观看次数)：通过 COUNT(1) AS all_action 计算符合条件的总行为数量，这通常代表视频的观看次数。 b. 好评数：通过 SUM(CASE WHEN a.content action ='点赞'THEN 1ELSE 0 END) AS like_action 计算好评数量。这里使用 CASE 语句判断每条行为记录是否为“点赞”，如果是则计为1，否则为0，然后对所有记录的计数值求和。 c. 好评率：最终的好评率通过 SUM(CASE WHEN a.content action='点赞' THEN 1 ELSE 0 END)/COUNT(1) AS like_rate 计算得出，即好评数除以总行为数。

具体的 SQL语句示例如下：

SELECT
    COUNT(1) AS all_action,
    SUM(CASE WHEN a.content_action = '点赞' THEN 1 ELSE 0 END) AS like_cnt_1,
    SUM(CASE WHEN a.content_action = '点赞' THEN 1 ELSE 0 END) AS like_cnt_2
FROM content_action_info AS a
JOIN dim_content AS b ON a.content_id = b.content_id
WHERE b.content_category = '科技'
  AND b.content_sub_category = '数码测评'
  AND a.create_time BETWEEN '2020-11-01' AND '2020-11-30';

如果采用好评率 =(好评数 + 3)/(总评论数 + 7) 的计算方式SQL语句中的好评率计算部分需要相应调整。假设 like_action 代表原始好评数，all_action 代表原始总评论数(或某种行为总数，具体取决于业务定义)，则好评率计算应为(like_action+3)/(all_action+ 7)。需要注意的是，这里的 all_action 在原始示例中代表总观看次数，如果公式中的 “总评论数” 特指包含好评、中评、差评的评论总数那么 all_action 的定义可能需要调整，或者需要从其他字段获取总评论数。例如，如果存在一个字段记录了总评论数，或者可以通过对不同评价类型的计数求和得到总评论数，那么SQL语句需要相应修改，例如，如果 content_action 字段包含点赞、差评、无评价等多种值，并且总评论数是指有明确评价(点赞或差评)的数量，那么总评论数可以计算为 SUM(CASE WHEN a.content action IN('点赞''差评)THEN 1 ELSE 0 END)。然后，好评数依然是 SUM(CASE WHEN a.content_action ='点赞' THEN 1 ELSE 0 END)。最终的好评率SQL表达式将基于这些调整后的分子和分母进行计算。

在实际应用中，数据清洗和准确性至关重要。确保数据来源的合法性和可靠性，去除重复数据，处理缺失数据和错误数据，是进行准确好评率计算的前提。

3.3.2.案例:用户兴趣分析与内容匹配度评估

在字节跳动的个性化推荐系统中，准确理解用户兴趣并将其与合适的内容进行匹配是核心目标。SQL在此过程中主要用于处理和分析用户行为数据，以构建用户兴趣画像和评估内容匹配度。例如，在抖音平台上，通过对用户观看历史和互动行为的分析，字节跳动能够推荐用户感兴趣的视频内容，提高用户的观看时长和活跃度。

SQL在用户兴趣分析和内容匹配度评估中的应用包括：

用户行为数据聚合
a. 内容偏好统计：通过分析用户的观看、点赞、评论、分享、收藏等行为，统计用户对不同类别、标签、主题、创作者的内容的偏好程度。例如，可以计算用户对“体育”类视频的观看时长占比、点赞率等。
```
SELECT
    user_id,
    content_category,
    SUM(watch_duration) AS total_watch_duration_category,
    COUNT(DISTINCT video_id) AS videos_watched,
    SUM(CASE WHEN action_type = 'like' THEN 1 ELSE 0 END) AS likes_count,
    AVG(CASE WHEN action_type = 'like' THEN 1 ELSE 0 END) AS like_rate
FROM user_content_interaction_log
GROUP BY user_id, content_category;
```
b. 行为序列分析：利用窗口函数分析用户的行为序列，例如用户连续观看同一主题视频的频率、观看不同类型视频的切换模式等，以捕捉用户兴趣的动态变化。
内容特征提取：从内容元数据表中提取内容的特征,如类别、标签关键词、时长、创作者等。这些特征是进行内容匹配的基础，
兴趣画像构建与匹配度评估：基于用户行为聚合结果和内容特征可以构建用户兴趣画像(例如，用户对不同标签的偏好权重)。然后通过 SQL计算用户兴趣向量与内容特征向量之间的相似度(如余弦相似度)，作为内容匹配度的初步评估。虽然复杂的相似度计算可能依赖 UDF 或后续处理，但 SQL可以完成基础的数据准备和简单的相似度计算逻辑。
A/B 测试与效果评估：将基于新兴趣模型或匹配策略的推荐结果通过 A/B 测试进行验证。SQL用于计算不同实验组的推荐效果指标如点击率、观看时长、多样性等，从而评估内容匹配度的提升情况,通过上述 SQL分析，可以为推荐系统提供更精准的用户兴趣洞察，优化内容匹配算法，从而提升用户体验和平台的核心竞争力。字节跳动内部可能会结合更复杂的机器学习模型进行用户兴趣建模，但 SQL始终是数据准备、特征工程和效果评估中不可或缺的一环。

3.4.直播业务数据分析

3.4.1.案例:直播间实时在线人数与峰值分析

在字节跳动的直播业务中，实时监控和分析直播间在线人数及其峰值对于运营决策和资源调度至关重要。SQL虽然主要用于离线数据分析但在处理历史直播数据、计算关键指标以及为实时系统提供数据支持方面仍扮演重要角色。例如，可以通过 SQL分析历史直播数据，找出不同主播、不同内容类型的直播间在线人数规律，预测未来直播的峰值人数，从而提前进行服务器资源准备和网络带宽优化。

分析目标：

统计每个直播间在特定时间段内的平均在线人数、最高在线人数(峰值)以及峰值出现的时间
分析不同主播、不同直播分类的在线人数表现
识别高人气直播间和潜力主播

数据表结构假设：假设存在一张直播观看记录表live_watch_log，包含以下字段：

live id(直播ID)
user id(观看用户 ID)
watch start time(用户进入直播间时间)
watch end time(用户离开直播间时间)
anchor id(主播ID)
live_category(直播分类)

SQL实现思路：

生成时间序列：首先，需要生成一个连续的时间序列，覆盖直播的整个周期(例如，每分钟一个点)
计算每分钟在线人数:将时间序列与观看记录表进行关联，统计在每个时间点有多少用户正在观看直播。这通常涉及到判断 watch start time<= '当前分钟' AND(watch end time >= '当前分钟' OR watch end time IS NULL)。
聚合统计：对每个直播间，按直播ID 和时间粒度(如每分钟)进行聚合，计算在线人数。然后，再对每个直播ID 进行聚合，计算平均在线人数、最高在线人数。峰值出现的时间可以通过窗口函数或子查询获取。

-- 示例：计算每个直播间的平均在线人数、最高在线人数及峰值时间
WITH TimeSeries AS (
    -- 假设已知全局（或最大覆盖范围）直播开始和结束时间，生成分钟级时间序列
    -- 可替换 '直播开始时间' / '直播结束时间' 为实际时间，或改成从 live_info 取最小/最大时间
    SELECT
        explode(sequence(
            to_unix_timestamp('直播开始时间'),    -- 如 '2025-01-01 10:00:00'
            to_unix_timestamp('直播结束时间'),    -- 如 '2025-01-01 14:00:00'
            60                                     -- 步长：60 秒=1 分钟
        )) AS minute_ts
    FROM (SELECT 1) AS dummy
),
MinuteOnlineUsers AS (
    SELECT
        l.live_id,
        from_unixtime(t.minute_ts) AS live_minute,
        COUNT(DISTINCT w.user_id) AS online_users_count
    FROM TimeSeries t
    -- 假设 live_watch_log 记录用户进入/离开时间（或进入时间，若无离开时间可用一个会话时长估算）
    JOIN live_watch_log w
      ON from_unixtime(t.minute_ts) BETWEEN w.watch_start_time AND w.watch_end_time
    -- 假设 live_info 表包含直播信息
    JOIN live_info l
      ON w.live_id = l.live_id
    WHERE l.live_start_time <= from_unixtime(t.minute_ts)
      AND l.live_end_time   >= from_unixtime(t.minute_ts)
    GROUP BY l.live_id, t.minute_ts
),
LiveStats AS (
    SELECT
        live_id,
        AVG(online_users_count) AS avg_online_users,
        MAX(online_users_count) AS max_online_users
    FROM MinuteOnlineUsers
    GROUP BY live_id
),
PeakTime AS (
    SELECT
        live_id,
        live_minute AS peak_time,
        online_users_count,
        ROW_NUMBER() OVER (PARTITION BY live_id ORDER BY online_users_count DESC, live_minute ASC) AS rn
    FROM MinuteOnlineUsers
)
SELECT
    s.live_id,
    s.avg_online_users,
    s.max_online_users,
    p.peak_time,
    p.online_users_count AS peak_online_users
FROM LiveStats s
JOIN PeakTime p
  ON s.live_id = p.live_id
 AND p.rn = 1;

这个 SQL 查询首先通过 TimeSeries CTE 生成直播期间的分钟级时间戳。然后，MinuteOnlineUsers CTE 通过将时间序列与观看记录表关联，计算每个直播间在每一分钟的在线人数。接着，LiveStatsCTE 对每个直播间聚合计算平均在线人数和最高在线人数。最后，PeakTime CTE 利用窗口函数找出每个直播间在线人数最高的时间点(即峰值时间)，并从中选取排名第一的记录与 LiveStats 结果进行关联。通过这类分析，运营团队可以更好地了解直播效果，优化直播策略，并为资源分配提供数据依据。

4.进阶:SQL在字节跳动复杂数据分析中的应用

4.1.连续行为分析(如连续登录、连续观看)

连续行为分析是字节跳动数据驱动决策中的一个重要组成部分，广泛应用于用户增长、内容推荐、风险控制等多个业务场景。SQL作为核心的数据处理工具，在实现连续行为分析方面扮演着关键角色。例如，在用户增长领域，分析用户的连续登录天数、连续观看特定类型内容的次数等，有助于识别高价值用户、预测用户流失以及评估运营活动效果。在广告业务中，分析用户连续点击广告或连续与广告互动的行为，可以为广告主提供更精准的人群画像和投放策略。字节跳动在面试数据分析相关岗位时，也经常考察候选人利用 SQL解决连续行为分析问题的能力，例如“找出连续7天登录的用户”或“提取一天内连续观看某一类别4个视频的用户”等。

解决这类问题的核心思路是利用SQL的窗口函数(WindowFunctions)。窗口函数允许对查询结果集中的一组行执行计算，这组行与当前行有某种关系。常见的用于连续行为分析的窗口函数包括ROW_NUMBER()，LAG()，LEAD()，RANK()，DENSE_RANK()等。

以“找出连续7天登录的用户“为例，一种常见的解法是：

首先，对每个用户的登录日期进行去重和排序。
然后，使用 ROW NUMBER() 函数为每个用户的登录日期按升序编号。
接着，将登录日期减去对应的行号。如果用户是连续登录的，那么登录日期 -行号会得到一个固定的日期值。
最后，对这个固定的日期值进行分组计数，筛选出计数大于等于7的用户即可。

-- 示例：找出连续登录 7 天及以上的用户
WITH user_login_ranks AS (
    SELECT
        user_id,
        login_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rank
    FROM (
        SELECT DISTINCT user_id, login_date
        FROM user_login_log      -- 假设原始登录表
    ) AS distinct_logins
),
consecutive_groups AS (
    SELECT
        user_id,
        login_date,
        DATE_SUB(login_date, INTERVAL rank DAY) AS group_start_date
    FROM user_login_ranks
),
consecutive_counts AS (
    SELECT
        user_id,
        group_start_date,
        COUNT(*) AS consecutive_days
    FROM consecutive_groups
    GROUP BY user_id, group_start_date
)
SELECT DISTINCT user_id
FROM consecutive_counts
WHERE consecutive_days >= 7;

类似地，对于“提取一天内连续观看某一类别4个视频的用户“这个问题，也可以采用窗口函数的思路。首先按用户和观看时间排序，然后通过 LAG() 函数比较当前观看视频的类别和前一个视频的类别，以及观看时间差，来判断是否属于连续观看同一类别。通过累加符合条件的记录，可以统计出连续观看的次数。这类分析不仅限于登录和观看行为，还可以扩展到购买行为、分享行为、评论行为等多种用户行为序列的分析。

字节跳动内部的数据工程师和分析师需要熟练掌握这些高级SQL技巧，以应对复杂的数据分析需求，并从海量用户行为数据中挖掘有价值的洞察，驱动业务决策和产品迭代。例如，在内容推荐优化中，通过分析用户连续观看相似主题或同一创作者的视频序列可以更精准地把握用户的兴趣演化，从而提升推荐的相关性和用户满意度。

4.2.图计算与SQL的结合（如 Graph-Reward-SQL 框架简介）

在字节跳动，随着业务复杂度的提升和数据量的激增，传统SQL在处理某些特定场景时可能会遇到性能瓶颈或表达能力不足的问题。为了应对这些挑战，字节跳动积极探索 SQL与其他计算范式的结合，其中图计算与 SQL 的结合是一个重要的方向。

一个典型的例子是字节跳动内部提出的 Graph-Reward-SQL 框架 。该框架虽然主要目标是提升 Text-to-SQL 模型的训练效率，但其设计理念和技术实现中蕴含了对 SQL深层理解和扩展的思路，对于处理复杂数据分析场景具有借鉴意义。

Graph-Reward-SQL框架通过引入图匹配网络评分(GMNScore)和渐进式关系运算符树匹配(StepRTM)两大互补奖励模型，旨在更精准地评估 SQL语句的语义等价性和结构合理性从而优化模型训练过程。

Graph-Reward-SQL 框架的核心创新点在于其奖励模型的设计 GMNScore 通过图匹配网络直接评估 SQL 语句的功能等价性，无需实际执行查询即可捕捉深层语义，这相较于依赖执行结果的奖励机制显著提升了评估速度并降低了GPU内存占用。例如，对于WHEREage>34 和 WHERE age >= 35 这样的查询，虽然语法不同，但在特定数据分布下可能语义相近，GMNScore 能够更好地识别这类语义相似性，避免了传统执行准确率(EX)奖励可能存在的语义盲区。

另一方面，StepRTM 则专注于评估公共表表达式(CTE)中子查询的生成过程，通过渐进式奖励机制，引导模型生成结构更优、可读性更强的复杂 SQL查询。CTE作为一种将子查询定义为临时命名结果集的技术，能够显著提升复杂查询的可读性和模块化程度，允许在后续查询中多次引用，这对于编写和维护大型分析脚本至关重要。字节跳动将 CTE 引入奖励模型，表明其在内部实践中高度重视 SQL 代码的质量和可维护性。

该框架的提出，反映了字节跳动在 SQL 应用上的两个趋势：

追求更高效、更智能的 SQL生成与优化，以应对日益增长的数据分析需求
关注 SQL本身的可读性、可维护性和语义准确性，尤其是在处理复杂业务逻辑

虽然 Graph-Reward-SQL 主要应用于 Text-to-SQL 领域，但其背后的思想，如利用图结构进行语义分析、关注查询结构的渐进式优化、以及强调 CTE 等高级 SQL 特性的使用，对于数据工程师在日常工作中编写高质量、高效率的 SQL 代码具有指导意义。例如在用户行为分析、社交网络分析等场景，数据之间往往存在复杂的关联关系，传统的 JOIN 操作可能难以高效处理。如果能够借鉴图计算的思路，将数据模型化为图结构，并结合 SQL 进行查询，或许能开辟新的优化路径。字节跳动对这类前沿技术的探索和应用，体现了其在大数据处理和分析领域的领先地位和持续创新能力。

5. 总结与展望：SQL在字节跳动数据未来的角色

SQL作为字节跳动数据驱动文化的核心支柱，其地位在未来不仅不会削弱，反而会更加重要和深化。随着公司业务的持续扩张和数据量的指数级增长，对数据分析和洞察的需求将愈发迫切和精细化。SQL作为连接数据与业务、实现数据价值的关键工具，将持续在以下几个方面发挥不可替代的作用：

数据democratization(数据民主化)的基石：SQL的易学易用性使其成为赋能更广泛业务人员(而不仅仅是专业数据工程师)进行数据探索和自助分析的首选工具。字节跳动将持续投入于提升内部SQL工具的易用性和性能，降低数据使用门槛，让更多团队能够基于数据快速决策。
复杂分析与深度洞察的引擎：尽管新兴技术如 AI/ML发展迅速但 SQL在处理大规模结构化数据、进行复杂关联分析、构建核心业务指标等方面依然具有独特优势。窗口函数、CTE等高级 SQL特性将被更广泛地应用于解决用户行为分析、广告归因、内容推荐等领域的复杂问题。
数据仓库与数据湖的核心交互语言：无论是传统的数仓架构还是新兴的 Lakehouse 架构，SQL都是进行数据ETL、数据建、数据查询和分析的标准语言。字节跳动将持续优化其大数据平台(如基于 Spark/Flink 的 SQL引擎)的性能和功能，以支持更高效的数据处理。
与 AI/ML的深度融合：SQL将与机器学习框架更紧密地结合。例如，通过 SQL直接调用机器学习模型进行预测分析，或者将SQL查询的结果无缝对接到模型训练流程中。Graph-Reward-SQL这类探索也预示着 SQL本身将变得更加“智能”能够自动优化或生成更高效的查询。
实时数据处理与分析的关键：随着业务对实时性要求的提高，流处理SQL(如 Flink SQL)将在实时监控、实时推荐、实时风控等场景扮演更核心的角色。字节跳动将持续推动流批一体技术的发展，使得SQL能够统一处理历史和实时数据。

展望未来，SQL在字节跳动的角色将从单纯的数据查询工具，向更智能、更高效、更易用的数据分析和价值创造平台演进。数据工程师和分析师需要不断深化 SQL技能，掌握最新的 SQL特性和最佳实践，同时积极拥抱 SQL与其他技术的融合，以更好地应对未来的数据挑战，驱动业务持续创新和增长。

Griffin Chow / 字节 SQL 使用手册