Jiahe Ling

← All notes

SQL基础

目录

本笔记默认掌握基础的SQL语法，仅讨论SQL常见问题的算法逻辑，如果需要详细学习SQL，请查看Hive或者Spark部分的笔记。

1. SQL顺序

语法顺序

  1. SELECT: 指定要查询的列
  2. DISTINCT: 去除结果集中的重复行
  3. FROM: 指定查询的表
  4. JOIN, ON: 连接多个表并ON指定连接条件
  5. WHERE: 过滤记录
  6. GROUP BY: 对结果集进行分组
  7. HAVING: 过滤分组后的结果集
  8. ORDER BY: 对结果集进行排序
  9. LIMIT: 限制结果集返回的行数

逻辑执行顺序

SQL查询语句在逻辑上被解析和执行的顺序，它反映了查询语句的结构和语义.

  1. FROM
  2. JOIN, ON
  3. WHERE
  4. GROUP BY
  5. HAVING
  6. SELECT
  7. DISTINCT
  8. ORDER BY
  9. LIMIT

物理执行顺序

2. TopN问题

TopN问题概述

TopN问题是指从数据表中查询出前N个最大或最小的数据记录（一旦题目中出现：按照指标X取前10名、前50%、后25%、第1名等等字眼，基本可以定位该问题为TopN问题）。

TopN问题可以分为以下2类：

  1. 全局TopN：从整个数据表中查询前N个最大或最小的记录。
  2. 分组TopN：按照某个字段分组后，在每个组内查询前N个最大或最小的记录。

窗口函数和窗口帧

窗口函数

  1. ROW_NUMBER()：为结果集中的每一行分配一个唯一且连续的序号。即使排序字段的值相同，不同行仍然会获得不同的序号。 
    SELECT *, 
     ROW_NUMBER() OVER (
         PARTITION BY dept
         ORDER BY salary DESC
     ) AS rn
FROM employee
    • PARTITION BY dept：每个部门分别排名。
    • ORDER BY salary DESC：部门内部按照工资从高到低排序
    • 适合严格取每组前 N 条记录，例如每个部门工资最高的 3 名员工。

  2. RANK()：为结果集中的每一行分配排名。排序值相同的行会获得相同排名，后续排名会跳号。

    RANK() OVER (
    PARTITION BY dept
    ORDER BY salary DESC
) AS rk

  3. DENSE_RANK()：为结果集中的每一行分配排名。排序值相同的行会获得相同排名，但后续排名不会跳号。

    DENSE_RANK() OVER (
    PARTITION BY dept
    ORDER BY salary DESC
) AS dr
  4. NTILE(n)：将窗口内的数据按照 ORDER BY 后的结果尽可能平均地划分成 n 个桶，并为每行分配桶号 1 ~ n。例如，想取排名靠前的约 25%，可以将数据分成 4 个桶，取第 1 个桶： 
    SELECT *,
     NTILE(4) OVER (
         ORDER BY score DESC
     ) AS bucket
 FROM student

    NTILE() 是按照行数尽量均匀分桶，如果不能均分，较小编号的桶通常会多分一行。

  5. PERCENT_RANK()：计算当前行在窗口中的百分比排名，返回值范围为 0 ~ 1，表示当前行在整个排序结果中的相对位置。其计算思想可以理解为：(rank - 1) / (总行数 - 1)

    PERCENT_RANK() OVER (
    ORDER BY score DESC
) AS pct_rank

窗口函数的基本结构

窗口函数() OVER (
    PARTITION BY ...
    ORDER BY ...
    ROWS / RANGE BETWEEN ... AND ...
)
  1. OVER()：使用窗口计算（空OVER()表示把整个数据集作为一个窗口）。 
     SUM(amount) OVER (
     PARTITION BY user_id
 )

    与普通 GROUP BY 不同，窗口函数不会把多行合并成一行。

    例如原数据：

     user_id   amount
 A         100
 A         200
 A         300

    使用：

     SUM(amount) OVER (
     PARTITION BY user_id
 )

    得到：

     user_id   amount   total_amount
 A         100      600
 A         200      600
 A         300      600

    但使用：

    SELECT user_id, SUM(amount) AS total_amount
FROM table
GROUP BY user_id

    得到：

     user_id   total_amount
 A         600

  2. PARTITION BY：用于把数据划分成多个独立的分区，每个分区单独计算。

    例如，先按照部门分组，每个部门内部再按照工资从高到低排名。

     ROW_NUMBER() OVER (
     PARTITION BY dept
     ORDER BY salary DESC
 )

    如果不写 PARTITION BY，则表示所有数据放在同一个窗口中进行全局排名。

     ROW_NUMBER() OVER (
     ORDER BY salary DESC
 )
  3. ORDER BY：规定分区内部的数据顺序。 
     ROW_NUMBER() OVER (
     PARTITION BY dept
     ORDER BY salary DESC
 )

    像下面这些函数通常都非常依赖 ORDER BY：

     ROW_NUMBER()
 RANK()
 DENSE_RANK()
 NTILE()
 PERCENT_RANK()
 LAG()
 LEAD()

窗口帧（Window Frame）

定义

窗口帧是：在已经通过 PARTITION BY 确定分区、通过 ORDER BY 确定顺序后，针对当前这一行，进一步规定这次计算具体使用哪些行。

例如：

SUM(amount) OVER (
    PARTITION BY user_id
    ORDER BY dt
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

假设当前分区中有：

第1行   100
第2行   200
第3行   300
第4行   400 <- 当前行
第5行   500

当前在第 4 行时：

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

表示只取：

第2行   200
第3行   300
第4行   400  <- 当前行

因此：

SUM = 200 + 300 + 400 = 900

所以可以理解为：

常见窗口帧关键字
SUM(amount) OVER (
    PARTITION BY user_id
    ORDER BY dt
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

窗口函数和窗口帧的关系

并不是所有窗口函数都需要显式指定窗口帧!

不需要窗口帧

排名类函数以及前后行定位函数通常不需要显式指定 ROWS 或 RANGE，常见函数包括：

ROW_NUMBER()
RANK()
DENSE_RANK()
NTILE()
PERCENT_RANK()
LAG()
LEAD()

可以简单理解为：ROW_NUMBER() / RANK() 等函数用于判断“当前行排第几”，LAG() / LEAD() 用于寻找“当前行前面或后面的某一行”，它们都不是对一段窗口数据进行聚合，因此通常不需要 ROWS / RANGE。

需要窗口帧

窗口帧主要用于需要对“一段数据范围”进行计算的窗口函数，常见函数包括：

SUM()
AVG()
COUNT()
MIN()
MAX()
FIRST_VALUE()
LAST_VALUE()

SUM()、AVG()、COUNT()、MIN()、MAX() 这类聚合函数需要根据业务需求确定当前行计算时使用哪些行。例如，计算每个用户按照日期的累计消费金额（ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 表示从当前分区的第一行一直计算到当前行，因此可以实现累计求和）：

SUM(amount) OVER (
    PARTITION BY user_id
    ORDER BY dt
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS total_amount

FIRST_VALUE() / LAST_VALUE() 也需要特别注意窗口帧，因为它们获取的是当前窗口帧中的第一个值或最后一个值，而不一定是整个 PARTITION 的第一个值或最后一个值。尤其是 LAST_VALUE()，如果希望获取整个分区真正的最后一个值，可以显式指定完整窗口帧：

LAST_VALUE(price) OVER (
    PARTITION BY sku_id
    ORDER BY dt
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_price

全局TopN问题

定义

全局TopN问题是指在所有数据中找出排名前 N 的记录，通常用于需要获取整体排名靠前的数据场景。

答案

ORDER BY + LIMIT N 是最常见的全局TopN问题解决方案：

SELECT student_id, student_name, math_score
FROM student_achievement_info
ORDER BY math_score DESC
LIMIT 10

执行

底层执行引擎一般会尝试做优化，但优化效果会受到执行引擎版本、参数配置、limit 大小、SQL 复杂度和数据量的影响。

Hive、Spark 等执行引擎一般会尝试做 TopN 优化，例如先在局部阶段取每个分区的 TopN，再将局部候选结果汇总，最后计算全局 TopN。这样可以显著减少最终排序阶段的数据量。

分组TopN问题

定义

分组TopN问题是指在每个分组中找出排名前 N 的记录，通常用于需要获取各个组内排名靠前的数据场景。

解法

子查询 + 排序开窗函数 + where过滤

  1. TopN：求每个班级中，数学成绩排名前10的学生信息：

     SELECT year, class_name, student_id, student_name, math_score
 FROM (
     SELECT year,  class_name, student_id, 
         student_name, 
         math_score, 
         ROW_NUMBER() OVER (
                 PARTITION BY year, class_name 
                 ORDER BY math_score DESC
         ) AS rk
     FROM student_achievement_info
 ) as t
 WHERE rk <= 10
  2. 百分比：求每个班级中，数学成绩排名前30%的学生信息 
     SELECT year, class_name, student_id, student_name, math_score
 FROM (
     SELECT year, class_name, student_id,
         student_name, 
         math_score, 
         PERCENT_RANK() OVER (
                 PARTITION BY year, class_name 
                 ORDER BY math_score DESC
         ) AS prk
     FROM student_achievement_info
 ) as t
 WHERE prk <= 0.30

  3. 分组与整体/个体：空OVER()指定全数据整体直接取值，其他情况使用GROUP BY分组算出个体的值，然后结合窗口函数或子查询进行进一步处理

    • 求每个班级中，数学成绩排名前10的学生信息，以及这些学生和全校第一名的数学成绩差距

      使用窗口函数 MAX() OVER () 获取全数据最高分，并结合 ROW_NUMBER() 过滤每个班级排名前10的学生

        SELECT student_id, student_name, math_score, class_name, (max_math_score - math_score) AS score_diff
  FROM (
      SELECT student_id, 
          student_name, 
          math_score, 
          class_name,
          ROW_NUMBER() OVER (
                  PARTITION BY class_name 
                  ORDER BY math_score DESC
          ) AS rk,
          MAX(math_score) OVER () AS max_math_score
      FROM student_achievement_info
  ) as t
  WHERE rk <= 10
    • 在学生每科成绩得分表中，选出每年，每个年级的总得分第一名和他的成绩

      GROUP BY 求每个人总分，ROW_NUMBER() 结合 rn = 1 过滤

        SELECT year,
      class,
      name,
      total_score
  FROM (
      SELECT year,
          class,
          name,
          total_score,
          ROW_NUMBER() OVER (
              PARTITION BY year, class
              ORDER BY total_score DESC
          ) AS rn
      FROM (
          SELECT year,
              class,
              name,
              SUM(score) AS total_score
          FROM ods_game_dev.topn_scores
          GROUP BY year, class, name
      ) t1
  ) t2
  WHERE rn = 1
  4. Top1: 求平均分Top1的班级名称
    • ROW_NUMBER() 结合 rn = 1 过滤 
        SELECT class
  FROM (
      SELECT class,
          AVG(math_score) AS avg_score,
          ROW_NUMBER() OVER (
              ORDER BY AVG(math_score) DESC
          ) AS rn
      FROM score_list
      GROUP BY class
  ) as t
  WHERE rn = 1
    • FIRST_VALUE() 结合窗口函数 
        SELECT class,
      AVG(math_score) AS avg_score,
      FIRST_VALUE(class) OVER (
          ORDER BY AVG(math_score) DESC
      ) AS top_class
  FROM score_list
  GROUP BY class

  5. 最高最低：在学生成绩得分表中，查询每一科目成绩最高和最低分数的学生

     SELECT subject, name, score
 FROM (
     SELECT subject,
         name,
         score,
         score_rk_desc,
         score_rk_asc
     FROM (
         SELECT subject,
             name,
             score,
             ROW_NUMBER() OVER (
                 PARTITION BY subject
                 ORDER BY score DESC
             ) AS score_rk_desc,
             ROW_NUMBER() OVER (
                 PARTITION BY subject
                 ORDER BY score ASC
             ) AS score_rk_asc
         FROM ods_game_dev.topn_scores
     ) t1
     WHERE score_rk_desc = 1
     OR score_rk_asc = 1
 ) t2;

3. 连续登陆问题

连续登陆问题概述

连续登录问题的核心在于日期连续，一般题目中出现 “求XXX连续N天登录” 这种字眼时，往往就是一道连续登陆日期的题目。

通常需要注意以下三点：

常用函数

lag()

获取当前行的上一条记录。

lag(dt, 1) over (
    partition by user_id
    order by dt
)

含义：

datediff()

计算两个日期之间相差的天数。例如：datediff('2024-12-06', '2024-12-05') = 1

datediff(dt, last_dt)

因此：

datediff = 1 -> 日期连续
datediff > 1 -> 日期中断

date_sub()

将日期向前减指定天数。例如：12-01 - 1天 = 11-30

date_sub(dt, rn)

连续日期的 dt 和 rn 都同时 +1，所以：连续的一组日期，其 date_sub(dt, rn) 结果相同。

因此可以用它构造连续区间的分组标识 sub_dt。

常规解法

SELECT
    user_id,
    sub_dt,
    COUNT(*) AS continuous_days
FROM (
    SELECT 
        user_id,
        dt,
        DATE_SUB(
            dt,
            ROW_NUMBER() OVER (
                PARTITION BY user_id
                ORDER BY dt
            )
        ) AS sub_dt
    FROM (
        SELECT DISTINCT
            user_id,
            dt
        FROM user_login_table
    ) t0
) t1
GROUP BY user_id, sub_dt
HAVING COUNT(*) >= 3;
  1. 去重：连续登录统计的是天数，同一天登录多次只能算一天。因此先保证：一个 user_id + dt 只有一条记录
SELECT DISTINCT
    user_id,
    dt
FROM user_login_table
  1. ROW_NUMBER() 开窗排序：对每个用户的登录日期排序：
ROW_NUMBER() OVER (
    PARTITION BY user_id
    ORDER BY dt
) AS rn

例如：

user_id dt rn
1 12-01 1
1 12-02 2
1 12-03 3
1 12-05 4
1 12-06 5
  1. 构造日期差标识
DATE_SUB(dt, rn) AS sub_dt

得到：

user_id dt rn sub_dt
1 12-01 1 11-30
1 12-02 2 11-30
1 12-03 3 11-30
1 12-05 4 12-01
1 12-06 5 12-01
  1. GROUP BY 聚合统计
GROUP BY user_id, sub_dt

分组：

SELECT
    user_id,
    sub_dt,
    count(*) as continuous_days
FROM t
GROUP BY
    user_id,
    sub_dt;

每个分组就是一段连续登录区间，count(*) 即该段的连续登录天数。

其他解法

SELECT
    user_id,
    group_id,
    COUNT(*) AS continuous_days
FROM (
    SELECT
        user_id,
        dt,
        SUM(tmp_lab) OVER (
            PARTITION BY user_id
            ORDER BY dt
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS group_id
    FROM (
        SELECT
            user_id,
            dt,
            CASE
                WHEN login_date_diff = 1 THEN 0
                ELSE 1
            END AS tmp_lab
        FROM (
            SELECT
                user_id,
                dt,
                DATEDIFF(
                    dt,
                    LAG(dt, 1) OVER (
                        PARTITION BY user_id
                        ORDER BY dt
                    )
                ) AS login_date_diff
            FROM (
                SELECT DISTINCT
                    user_id,
                    dt
                FROM user_login_table
            ) t0
        ) t1
    ) t2
) t3
GROUP BY user_id, group_id
HAVING COUNT(*) >= 3;
  1. 去重：同一天登录多次只能算一天，因此先保证一个 user_id + dt 只有一条记录。
SELECT DISTINCT
    user_id,
    dt
FROM user_login_table
  1. LAG() + DATEDIFF() 计算相邻登录日期差：先通过 LAG() 获取用户上一次登录日期，再通过 DATEDIFF() 计算当前日期与上一次登录日期相差多少天。
DATEDIFF(
    dt,
    LAG(dt, 1) OVER (
        PARTITION BY user_id
        ORDER BY dt
    )
) AS login_date_diff

例如：

user_id dt last_dt login_date_diff
1 12-01 NULL NULL
1 12-02 12-01 1
1 12-03 12-02 1
1 12-05 12-03 2
1 12-06 12-05 1

其中：

login_date_diff = 1 -> 和上一条记录连续
login_date_diff != 1 -> 连续中断
  1. 构造中断标识：通过 CASE WHEN 判断当前记录是否开启了一段新的连续登录区间。
CASE
    WHEN login_date_diff = 1 THEN 0
    ELSE 1
END AS tmp_lab

得到：

user_id dt login_date_diff tmp_lab
1 12-01 NULL 1
1 12-02 1 0
1 12-03 1 0
1 12-05 2 1
1 12-06 1 0

其中：

tmp_lab = 0 -> 延续上一段
tmp_lab = 1 -> 开启新的一段
  1. SUM() 累计构造分组标识：对 tmp_lab 进行累计求和，每遇到一次中断，分组编号就 +1。
SUM(tmp_lab) OVER (
    PARTITION BY user_id
    ORDER BY dt
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS group_id

得到：

user_id dt tmp_lab group_id
1 12-01 1 1
1 12-02 0 1
1 12-03 0 1
1 12-05 1 2
1 12-06 0 2

因此：

group_id = 1 -> 12-01 ~ 12-03
group_id = 2 -> 12-05 ~ 12-06
  1. GROUP BY 聚合统计
GROUP BY user_id, group_id
HAVING COUNT(*) >= 3

每个 user_id + group_id 就是一段连续登录区间，COUNT(*) 即该段的连续登录天数。

间隔连续登陆（其他解法plus）

问题描述

设置一个间隔天数 k，如果用户在 k 天内登录，则视为连续登录（例子：假设用户在上次登录后2天内登录就视作连续登陆，一个用户在 12.1，12.3，12.5，12.6 共4天都登录了游戏，则视为连续 6 天登录）。

解决方案

SELECT
    user_id,
    sum_tmp_lab,
    DATEDIFF(MAX(dt), MIN(dt)) + 1 AS continuous_days
FROM (
    SELECT
        user_id,
        dt,
        SUM(tmp_lab) OVER (
            PARTITION BY user_id
            ORDER BY dt
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS sum_tmp_lab
    FROM (
        SELECT
            user_id,
            dt,
            CASE
                WHEN login_date_diff <= 2 THEN 0
                ELSE 1
            END AS tmp_lab
        FROM (
            SELECT
                user_id,
                dt,
                DATEDIFF(
                    dt,
                    LAG(dt, 1) OVER (
                        PARTITION BY user_id
                        ORDER BY dt
                    )
                ) AS login_date_diff
            FROM (
                SELECT DISTINCT
                    user_id,
                    dt
                FROM user_login_table
            ) t0
        ) t1
    ) t2
) t3
GROUP BY user_id, sum_tmp_lab
HAVING DATEDIFF(MAX(dt), MIN(dt)) + 1 >= 6;
  1. 去重：保证一个用户一天只有一条登录记录。
SELECT DISTINCT
    user_id,
    dt
FROM user_login_table
  1. LAG() + DATEDIFF() 计算相邻两次登录的日期差。
DATEDIFF(
    dt,
    LAG(dt, 1) OVER (
        PARTITION BY user_id
        ORDER BY dt
    )
) AS login_date_diff

例如：

dt 上次登录 login_date_diff
12-01 NULL NULL
12-03 12-01 2
12-05 12-03 2
12-06 12-05 1
  1. CASE WHEN 判断是否超过允许的间隔。

普通连续登录：

CASE
    WHEN login_date_diff = 1 THEN 0
    ELSE 1
END

允许间隔一天：

CASE
    WHEN login_date_diff <= 2 THEN 0
    ELSE 1
END AS tmp_lab

也就是：

日期差 <= 2 -> 仍然连续 -> 0
日期差 > 2  -> 连续中断 -> 1
  1. SUM() 累计生成连续区间编号。
SUM(tmp_lab) OVER (
    PARTITION BY user_id
    ORDER BY dt
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS sum_tmp_lab

例如：

dt login_date_diff tmp_lab sum_tmp_lab
12-01 NULL 1 1
12-03 2 0 1
12-05 2 0 1
12-06 1 0 1

因此这 4 条记录属于同一个连续区间。

  1. GROUP BY 聚合计算连续天数。
GROUP BY user_id, sum_tmp_lab

普通连续登录可以直接：

COUNT(*)

但是间隔连续登录不能使用 COUNT(*)。

因为：

登录日期：12-01、12-03、12-05、12-06

COUNT(*) = 4

但题目认为这一段其实算作连续登陆6天；所以需要计算日期跨度（12-06 - 12-01 + 1 = 6天）：

DATEDIFF(MAX(dt), MIN(dt)) + 1

因此间隔连续登录的核心区别是：

普通连续登录：
login_date_diff = 1
COUNT(*)

间隔一天也连续：
login_date_diff <= 2
DATEDIFF(MAX(dt), MIN(dt)) + 1

题型分类

TBD

4. 行转列/列转行问题

行转列问题概述

行转列和列转行的核心都是改变数据的组织形式 / 粒度。 行转列通常是把原来分散在多行中的同一主体数据进行聚合，整理到更少的行、更多的列或一个聚合字段中；列转行则相反，把原来保存在一行中的一个或多个字段拆开，生成多条明细记录。

行转列通常涉及 GROUP BY + 聚合操作，而列转行通常涉及把字段“炸开”的操作。

常用函数（列转行）

1. explode()

explode() 是最常用的炸裂函数 / UDTF（User-Defined Table-Generating Function，表生成函数）之一。UDTF 的特点是可以让原表中的一行数据生成多行数据。对于 ARRAY，explode() 会把数组中的每个元素展开成一行；对于 MAP，会把每个键值对展开成 key + value 两列。posexplode()和 explode(array) 类似，但会额外返回数组元素的位置索引，索引从 0 开始。

例子

id name hobbies
1 Amy ['game','swim']
2 Bob ['run']
3 Jack ['movie','game','music']

需要特别注意数据膨胀：如果原表有 100 万行，每行数组平均有 20 个元素，炸裂后理论上可能产生约 2000 万行，因此炸裂之后再做 JOIN / GROUP BY / DISTINCT 可能明显增加计算量，实际写 SQL 时通常应该先完成可以提前做的过滤。

另一个注意点是空数组或 NULL：普通炸裂没有元素可输出时，原记录可能不会保留；如果业务要求即使数组为空也保留原记录，Hive / Spark SQL 中通常使用 LATERAL VIEW OUTER explode(...)。

SELECT
    id,
    name,
    hobby
FROM person
LATERAL VIEW OUTER EXPLODE(hobbies) tmp AS hobby;

LATERAL VIEW

LATERAL VIEW 不是函数，而是配合 UDTF 使用的一种 SQL 语法。它会把 UDTF 应用到源表的每一行，让当前行生成一行或多行结果，然后把这些结果重新和当前源表行组合起来形成一个虚表。原笔记中的表述就是：将 UDTF 应用到源表的每行数据，把每行转换成一行或多行，并将输出结果与该源行连接起来。 因此 explode() 和 LATERAL VIEW 要分开理解：explode() 负责把什么炸出来，LATERAL VIEW 负责把炸出的结果和原表哪一行对应起来。

2. SPLIT()

SPLIT() 用于按照指定分隔规则拆分字符串，拆分后的返回结果是一个 ARRAY 数组。在列转行问题中，它经常负责先完成 STRING → ARRAY，再把数组交给 explode() 展开成多行。

语法
SPLIT(str, pattern)

例如原表：

id category
1 Action,Comedy,Drama
2 Comedy,Romance

执行：

SELECT
    id,
    split(category, ',') AS category_array
FROM movie;

结果可以理解为：

id category_array
1 ['Action','Comedy','Drama']
2 ['Comedy','Romance']

常用函数（行转列）

1. collect_list()

collect_list(expr) 是一个聚合函数（多行聚合），作用是把一个分组中的多行 expr 收集起来，返回一个 ARRAY，而且保留重复元素。

例如订单明细表：

user_id product
1 A
1 B
1 A
2 C
2 D

执行：

SELECT
    user_id,
    collect_list(product) AS products
FROM orders
GROUP BY user_id;

逻辑结果：

user_id products
1 ['A','B','A']
2 ['C','D']

可以看到，用户 1 的商品 A 出现两次，collect_list() 会把这两次都保留下来。因此如果重复次数具有业务含义，比如一个用户真实发生了两次 click、一个商品被购买了三次、一个订单出现了多条同 SKU 记录，就应该使用 collect_list()，而不是自动去重。

这类数组之后还可以继续交给 size() 统计长度、array_contains() 判断是否出现某个元素、transform() 做元素转换、explode() 再拆回明细，因此 collect_list() 更准确的理解是：把一个分组内的明细保存为数组结构，供后续复杂数据处理。

不要依赖 collect_list() 默认的元素顺序。该函数是非确定性的，因为结果顺序取决于输入行顺序，而经过 shuffle 后行顺序可能发生变化。 可以进一步写 sort_array(collect_list(score))来得到稳定的顺序。

2. collect_set()

collect_set(expr) 同样是聚合函数，也是把一个分组中的多行数据收集成一个 ARRAY，但它会自动去重，只保留唯一元素。原笔记中的定义是“收集并返回一个唯一元素的集合”。 因此选择 collect_list() 还是 collect_set()，关键不在于哪个函数更好，而在于重复值有没有业务意义。

例如：

user_id product
1 A
1 B
1 A
1 C

分别使用：

SELECT
    user_id,
    collect_list(product) AS product_list,
    collect_set(product) AS product_set
FROM orders
GROUP BY user_id;

逻辑上可以理解为：

user_id product_list product_set
1 ['A','B','A','C'] ['A','B','C']

所以如果问题是“用户所有购买记录”，两次 A 都应该保留，适合 collect_list()；如果问题是“用户买过哪些不同商品”，只关心 A 是否出现过，适合 collect_set()。同样地，用户行为 login、click、click、pay 中，“完整行为明细”使用 collect_list(event)，“出现过哪些不同事件类型”使用 collect_set(event)。

collect_set() 同样不能依赖结果顺序。如果需要按照数组元素自身稳定排序，一般继续使用sort_array(collect_set(product))。

3. sort_array()

sort_array(array[, ascendingOrder]) 是数组排序函数：输入一个数组，根据数组元素本身的自然顺序排序，最后仍然返回一个数组。原笔记基于 Spark 3.4.4 的说明是：该函数既支持升序也支持降序；对于 double / float，NaN 大于任何非 NaN 元素；升序时 NULL 放在数组开头，降序时 NULL 放在数组末尾。

例如原表：

user_id scores
1 [90,70,85]
2 [60,100,80]

执行：

SELECT
    user_id,
    sort_array(scores, true) AS score_asc,
    sort_array(scores, false) AS score_desc
FROM user_score;

结果：

user_id score_asc score_desc
1 [70,85,90] [90,85,70]
2 [60,80,100] [100,80,60]

所以 sort_array() 的数据粒度完全没有变化：仍然是一行、仍然是一个数组，只是数组内部元素的排列顺序发生变化。它完全不局限于行转列，任何已经存在的数组字段都可以直接排序。

最常见的组合之一是配合 collect_list() / collect_set()。

sort_array() 只能根据数组元素自己排序，而不能像普通 ORDER BY 一样任意指定另一个字段。

4. concat_ws()

concat_ws(sep[, str | array(str)]+) 是字符串拼接函数，作用是使用指定的 sep 作为分隔符，把多个字符串或一个字符串数组拼成最终的字符串；原笔记还特别指出，它在拼接时会跳过 NULL。 ws 可以直接理解为 with separator，所以它和 concat() 最大区别就是可以指定分隔符。

5. CASE

CASE 是 SQL 中的条件表达式，作用是根据不同条件返回不同的值，可以理解成 if / else if / else。它本身不是聚合函数，也不只用于行转列；整个 CASE ... END 最终会计算成一个值，因此可以和 SELECT、聚合函数、ORDER BY 等组合使用。

语法

CASE 主要有两种写法。

NULL 判断：判断空值必须使用 IS NULL / IS NOT NULL，不能使用 = NULL / != NULL。简单型 CASE col WHEN NULL 也不能可靠匹配 NULL，因为本质仍类似 col = NULL。

常见用法

可出现的位置**：因为 CASE ... END 本质是一个表达式，所以可以出现在很多需要“值”的位置，例如 SELECT、SUM/MAX/COUNT 内部、ORDER BY 等。

SELECT CASE WHEN ... THEN ... END AS new_col

SUM(CASE WHEN ... THEN ... END)

ORDER BY CASE WHEN ... THEN ... END

6. TRANSFORM()

TRANSFORM() 是一个数组高阶函数，用于遍历 ARRAY 中的每个元素，对每个元素执行指定的转换逻辑，最后返回一个新的数组。转换后的数组长度与原数组相同，只是数组中的元素被重新处理。

transform(array, element -> expression)
语法
常见用法

transform() 只能处理 ARRAY 类型，不能直接处理普通字符串、数字等非数组字段；transform() 处理后仍然返回数组，而且通常与原数组元素数量相同。

列转行解法

列转行就是把一行中的多个值拆成多行，核心思路是：split() + 炸裂函数 explode()。

常用模板：

SELECT
    原表字段,
    new_col
FROM table_name
LATERAL VIEW explode(
    split(待拆字段, '分隔符')
) tmp AS new_col;

例如原表：

title category
Movie A Action,Comedy,Drama

SQL：

SELECT
    title,
    category_new
FROM movie
LATERAL VIEW explode(split(category, ',')) tmp AS category_new;

结果：

title category_new
Movie A Action
Movie A Comedy
Movie A Drama

如果原字段（假设 hobbies = ['game','swim','movie']）已经是数组，则不需要 split():

SELECT id, name, hobby
FROM person LATERAL VIEW explode(hobbies) tmp AS hobby;

行转列解法

行转列本质上是对多行数据进行聚合：原来同一个主体的信息分散在多行，最终需要整理到更少的行或一行中。因此通常首先要确定最终一行代表什么，也就是找到 GROUP BY 的分组字段，再根据目标结果选择聚合方式。原资料把常见聚合方式归纳为两类：

  1. 常规聚合函数 MAX()/SUM() + CASE WHEN
  2. 数组聚合函数 collect_list()/collect_set()（通常再配合 concat_ws()）

行转列常见的三种解决思路：

做题时可以按照下面的顺序判断：

  1. 先看最终一行代表什么，确定 GROUP BY 字段
  2. 再看原来的多行最终怎么保存：
    • 多个值收进一个字段：collect_list/set + concat_ws
    • 不同类型变成不同列：CASE WHEN + MAX/SUM
  3. (optional) 如果没有字段可以 GROUP BY，先人工构造一个用于聚合

例题 1：GROUP BY + collect_set() + concat_ws()

把星座和血型相同的人归到一起，姓名之间使用 | 分隔。

例如原表 constellation：

name constellation_name blood_type
孙悟空 白羊座 A
猪八戒 白羊座 A
宋宋 白羊座 B
大海 射手座 A
凤姐 射手座 A

目标结果：

constellation_name blood_type name
白羊座 A 孙悟空\|猪八戒
白羊座 B 宋宋
射手座 A 大海\|凤姐

  1. 最终一行代表什么（确定分组字段）：一个“星座 + 血型”组合

     GROUP BY constellation_name, blood_type

  2. 多行保存形式（多个值收进一个字段）：同一个组合中可能有多个人，需要先把组内姓名聚合起来，再用 | 拼接。资料给出的 SQL 是：

     SELECT
     constellation_name,
     blood_type,
     concat_ws('|', collect_set(name)) AS name
 FROM constellation
 GROUP BY constellation_name, blood_type;

这里三个操作职责不同：GROUP BY 决定哪些行属于同一组，collect_set() 把组内多行姓名收集成数组，concat_ws() 再把数组拼成字符串（concat_ws() 本身不是聚合函数，因此不能直接聚合多行，必须先由 collect_set() 等函数完成组内聚合）。

例题 2：GROUP BY + SUM() + CASE WHEN

把 Products 表转换成 Result 表，将不同 store 从行变成不同列。

原表：

product_id store price
0 store1 95
0 store2 100
0 store3 105
1 store1 70
1 store3 80

目标：

product_id store1 store2 store3
0 95 100 105
1 70 NULL 80

  1. 最终一行代表什么（确定分组字段）：最终是一种商品一行

     GROUP BY product_id

  2. 多行保存形式（不同类型变成不同列）：在聚合之前，需要先解决“95 应该进入 store1 列、100 应该进入 store2 列”的问题，因此使用 CASE WHEN：

     CASE WHEN store = 'store1' THEN price ELSE NULL END

    三列一起处理后，可以把原数据理解成：

    product_id store1 store2 store3
    0 95 NULL NULL
    0 NULL 100 NULL
    0 NULL NULL 105
    1 70 NULL NULL
    1 NULL NULL 80

  3. 最后再按 product_id 聚合，就可以把这些行压成一行。资料使用的 SQL 是：

     SELECT
     product_id,
     SUM(CASE WHEN store = 'store1' THEN price ELSE NULL END) AS store1,
     SUM(CASE WHEN store = 'store2' THEN price ELSE NULL END) AS store2,
     SUM(CASE WHEN store = 'store3' THEN price ELSE NULL END) AS store3
 FROM Products
 GROUP BY product_id;

CASE WHEN 负责“分类放位置 / 造列”，GROUP BY + SUM() 负责“把同一个 product 的多行合并成一行”（SUM 只会把非 NULL 的值加起来，但这里的 SUM() 主要充当聚合桥梁，并不是重点在把多个 store 的价格加在一起）。

例题 3：没有聚合字段

输出每个大洲的姓名，每个大洲一列，并且姓名按照字典顺序排列。

例如原表 student：

name continent
Jack America
Jane America
Xi Asia
Pascal Europe

目标类似：

America Europe Asia
Jack Pascal Xi
Jane NULL NULL

  1. 人工造 id： 原表没有一个类似 product_id 的字段告诉我们哪些记录应该出现在目标结果的同一行。

     WITH a AS (
     SELECT
         ROW_NUMBER() OVER (
             PARTITION BY continent
             ORDER BY name
         ) AS id,
         name,
         continent
     FROM student
 )

    中间结果可以理解成：

    id name continent
    1 Jack America
    2 Jane America
    1 Pascal Europe
    1 Xi Asia

    这样就人为建立了对应关系：各大洲的第 1 个姓名都是 id = 1，各大洲的第 2 个姓名都是 id = 2。之后就可以按照这个人工 id 做条件聚合。

  2. 多行保存形式（不同类型变成不同列）：使用 MAX(CASE WHEN ...) 把不同大洲的姓名放到对应列中（聚合成列）。

     SELECT
     MAX(CASE continent WHEN 'America' THEN name ELSE NULL END) AS America,
     MAX(CASE continent WHEN 'Europe'  THEN name ELSE NULL END) AS Europe,
     MAX(CASE continent WHEN 'Asia'    THEN name ELSE NULL END) AS Asia
 FROM a
 GROUP BY id;

先判断目标结果中哪些数据应该处于同一行；如果原表没有这样的对应关系，就先人工构造出来，再使用普通的行转列方法。

有序行转列解法

有序行转列是把多行聚合成数组/字符串的同时，还要求结果按照指定字段排序。

正常行转列用collect_list(value)不能保证最终顺序，因为 collect_list() / collect_set() 的结果顺序在发生 shuffle 后可能不确定。

所以核心思路是：不要直接对待输出字段排序，而是把“排序字段”和“待输出字段”绑定在一起 -> 排序 -> 再取出真正需要的字段。

方法 1：拼接排序字段 + sort_array() + transform()

先用concat(delivery_time, customer_id) 把排序字段放在待输出字段前面，然后collect_list() 聚合成数组，再用 sort_array() 排序，最后用 transform() 去掉前面的排序字段，只保留真正的目标字段。

完整写法：

SELECT
    rider_id,
    concat_ws(
        ',',
        transform(
            sort_array(collect_list(time_customer)),
            x -> substr(x, 20)
        )
    ) AS customer_list
FROM (
    SELECT
        rider_id,
        concat(delivery_time, customer_id) AS time_customer
    FROM ods_game_dev.t_delivery_orders
) t
GROUP BY rider_id;

其中：

方法 2：STRUCT 绑定字段 + array_sort()

比字符串拼接更直接的方法，是用struct(delivery_time, customer_id)把排序字段和目标字段组成一个 STRUCT，并把排序字段放在前面。接着，用 array_sort() 对数组进行排序。

SELECT
    rider_id,
    array_sort(
        collect_list(
            struct(delivery_time, customer_id)
        )
    ).customer_id AS customer_id_list
FROM ods_game_dev.t_delivery_orders
GROUP BY rider_id;

用排序字段和最终字段组成 STRUCT，排序字段放前面，排序完成后再通过 .字段名 提取真正需要的数据。

5. 同时在线问题

同时在线问题概述

同时在线问题可以定义为：给定每个用户的进入时间和离开时间，统计某个时间点或某个时间段内，有多少用户同时处于某些状态（比如，”在线/活跃/等待”），以及一些衍生问题（比如最多同时在线用户数等问题）。

同时在线问题解法

题目描述

用户行为日志表 tb_user_log 记录了用户浏览文章的行为，每条记录包含用户进入文章的时间 in_time 和离开文章的时间 out_time。

表中主要字段如下：

字段 含义
id 记录 ID
uid 用户 ID
article_id 文章 ID
in_time 用户进入文章的时间
out_time 用户离开文章的时间
sign_in 是否签到

其中，article_id = 0 表示用户处于非文章内容页面，例如 App 首页、活动页等，因此统计文章在线人数时需要排除这些记录。

问题要求

统计每篇文章在任意时刻的最大同时在线人数，如果同一时刻有进入也有离开，先记录用户增加再记录减少，结果按最大人数降序排列。

输出字段 含义
article_id 文章 ID
max_uv 最大同时在线人数

解决办法

  1. 标记时间：开始阅读文章的时间点记为1，结束阅读文章的时间点记为-1，然后将两部分数据 union all 起来

     WITH t1 AS (
     SELECT article_id, in_time AS dt, 1 AS num
     FROM tb_user_log
     WHERE article_id != 0 -- 只选择正在观看的记录

     UNION ALL

     SELECT article_id, out_time AS dt, -1 AS num
     FROM tb_user_log
     WHERE article_id != 0 -- 只选择正在观看的记录
 )
  2. 使用开窗函数对人数进行累加：这里需要按照事件时间来对数据进行排序，然后对在线人数进行累计计数

    当同一时刻即有人开始阅读，又有人结束阅读时，是先统计进入的还是退出的 （这个属于边界情况，写之前一定要确认好）。

     WITH t2 AS (
     SELECT 
         article_id,
         sum(num) OVER (
             PARTITION BY article_id
             ORDER BY dt ASC, num DESC
             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
         ) AS cnt
     FROM t1
 )

  3. 找出最终的结果：根据题目要求，筛选出最终结果即可，这里直接求出来最大的在线人数即可。

     SELECT article_id, MAX(cnt) AS max_uv
 FROM t2
 GROUP BY article_id
 ORDER BY max_uv DESC;

示例：假设文章 9001 有两位用户访问

原始数据：

article_id in_time out_time
9001 10:00 10:10
9001 10:05 10:15

经过第 1 步拆分事件后：

article_id dt num
9001 10:00 +1
9001 10:05 +1
9001 10:10 -1
9001 10:15 -1

经过第 2 步按时间累计后：

article_id dt num cnt
9001 10:00 +1 1
9001 10:05 +1 2
9001 10:10 -1 1
9001 10:15 -1 0

经过第 3 步取最大值：

article_id max_uv
9001 2

因此文章 9001 的最大同时在线人数为 2。

同时在线问题分类

  1. 用户表 & 订单表
  2. 按类细分

6. 留存问题

留存问题的核心在于如何保持用户的长期活跃与粘性，它直接反映了产品吸引力、用户满意度和运营效能。在业务题中，只要出现“留存率”、“次日留存”等关键词，即可迅速定位为留存问题。

留存问题核心参数

留存问题常用函数

  1. DATE_FORMAT()：统一日期粒度，登录时间如果带时分秒，通常先转成日期，再按“日期 + 用户”去重。

     date_format(log_time, 'yyyy-MM-dd')

  2. MIN() / ROW_NUMBER()：找首次登录日期，如果题目考察新用户留存，需要先确定每个用户第一次登录的日期。

     MIN(login_date)

    或者：

     ROW_NUMBER() OVER (
     PARTITION BY user_id
     ORDER BY login_date
 )

    然后取 rk = 1。资料明确说明这两种方式都可以用于计算首次登录日期。

  3. DATEDIFF()：计算两个日期相差多少天，是留存判断最核心的函数。

     datediff(后续登录日期, 基准日期)

  4. IF() / CASE WHEN：给留存打标

     IF(datediff(b.login_date, a.login_date) = 1, 1,0)

    即满足留存条件打 1，不满足打 0。

  5. MAX()：把同一个用户的多条留存记录压成一个标签，一个基准用户 LEFT JOIN 后可能对应多条后续登录记录，因此可以：

     MAX(
     IF(datediff(b.login_date, a.login_date) = 1, 1, 0)
 )

    只要该用户有一次满足条件，最终就是 1。资料的高效写法也是先按“日期 + 用户”聚合，并用 MAX() 得到用户级留存标签。

  6. COUNT(DISTINCT) / SUM() / COUNT()：计算留存率

    • 方法一：

        COUNT(DISTINCT IF(flag = 1, user_id, NULL))
  COUNT(DISTINCT user_id)

    • 方法二：先保证日期 + 用户只有一行，再：

        SUM(flag) / COUNT(1)

      这种方式可以避免大量使用 COUNT(DISTINCT)，在大数据场景下一般效率更高。

留存问题解法

留存问题基本固定为三步走：

  1. 确定基准用户群体
  2. 为留存打标
  3. 计算留存率

1. 确定基准用户群体

先判断题目到底要追踪哪批用户：

所以第一步最重要的问题是： 最终要观察的是哪一批用户后续有没有回来？

如果原始表一天内同一用户有多条行为记录，应先：

GROUP BY 日期, user_id

保证每天每个用户只有一条，否则后续 JOIN 容易造成数据膨胀。资料将这一点作为留存题的重要注意事项。

2. 基准用户 LEFT JOIN 后续活跃记录并打标

基准用户作为主表，LEFT JOIN 后续全量活跃数据，再用 DATEDIFF() 判断是否符合留存条件。使用 LEFT JOIN，是为了让**后续没有回来的人仍然保留在基准用户中。

基本结构：

FROM base_user a
LEFT JOIN activity b
    ON a.user_id = b.user_id

然后：

IF(
    datediff(b.login_date, a.base_date) = 留存天数, 1, 0
) AS flag

同一用户可能关联到多条后续记录，因此通常再按：

GROUP BY base_date, user_id

并使用：

MAX(flag)

最终得到：

base_date user_id flag
01-01 A 1
01-01 B 0
01-01 C 1

3. 计算留存率

核心公式：留存率 = 符合留存条件的用户数 / 基准用户总数

SELECT
    base_date,
    SUM(flag) / COUNT(1) AS retention_rate
FROM t
GROUP BY base_date;

例子：计算每天新用户的次日留存率

原始登录表 user_login：

user_id login_date
A 2026-01-01
A 2026-01-02
A 2026-01-04
B 2026-01-01
B 2026-01-03
C 2026-01-02
C 2026-01-03

要求：计算每天首次登录用户的次日留存率。

  1. 找到每个用户首次登录日期

     WITH base_user AS (
     SELECT
         user_id,
         MIN(login_date) AS first_login_date
     FROM user_login
     GROUP BY user_id
 )

    得到：

    user_id first_login_date
    A 2026-01-01
    B 2026-01-01
    C 2026-01-02

    因此：

    text id 2026-01-01 新用户：A、B 2026-01-02 新用户：C

  2. LEFT JOIN 后续登录记录并打留存标签

     SELECT
     a.first_login_date,
     a.user_id,
     MAX(
         CASE
             WHEN datediff(b.login_date, a.first_login_date) = 1
             THEN 1
             ELSE 0
         END
     ) AS next_day_flag
 FROM base_user a
 LEFT JOIN user_login b
     ON a.user_id = b.user_id
 GROUP BY
     a.first_login_date,
     a.user_id;

    得到：

    first_login_date user_id next_day_flag
    2026-01-01 A 1
    2026-01-01 B 0
    2026-01-02 C 1

    解释：

     A：01-01 首次登录，01-02 又登录 -> 次日留存 = 1
 B：01-01 首次登录，01-02 没登录 -> 次日留存 = 0
 C：01-02 首次登录，01-03 又登录 -> 次日留存 = 1

    这里使用 MAX() 是因为一个用户可能关联到多条后续登录记录，只要其中一条满足次日条件，最终标签就是 1。

  3. 计算次日留存率

     WITH base_user AS (
     SELECT
         user_id,
         MIN(login_date) AS first_login_date
     FROM user_login
     GROUP BY user_id
 ),
 user_flag AS (
     SELECT
         a.first_login_date,
         a.user_id,
         MAX(
             CASE
                 WHEN datediff(b.login_date, a.first_login_date) = 1
                 THEN 1
                 ELSE 0
             END
         ) AS next_day_flag
     FROM base_user a
     LEFT JOIN user_login b
         ON a.user_id = b.user_id
     GROUP BY
         a.first_login_date,
         a.user_id
 )

 SELECT
     first_login_date,
     COUNT(1) AS new_user_num,
     SUM(next_day_flag) AS next_day_user_num,
     ROUND(SUM(next_day_flag) / COUNT(1), 2) AS next_day_retention
 FROM user_flag
 GROUP BY first_login_date;

    结果：

    first_login_date new_user_num next_day_user_num next_day_retention
    2026-01-01 2 1 0.50
    2026-01-02 1 1 1.00

7. 开窗/聚合函数（重点）

波峰波谷

掐头去尾

合并区间

空值填充

前后列转换

相互关注

无效搜索

相邻问题

8. 正则表达

正则表达式概述

相关函数

RLIKE

用于判断字符串是否匹配指定的正则表达式模式，若匹配则返回 true，否则返回 false。它本质上是一个布尔运算符，常用于 WHERE 子句中进行条件筛选（string_expression RLIKE regex_pattern）。

--- sample data
CREATE TABLE employees (
    id INT,
    name STRING
);
INSERT INTO employees VALUES
(1, 'John'),
(2, 'Alice'),
(3, 'Jack');
 
-- 使用 rlike 进行筛选
SELECT *
FROM employees
WHERE name rlike '^J.*';

REGEXP_EXTRACT

从输入字符串中按照正则表达式进行匹配，并返回指定组的匹配结果。这里的组是指正则表达式中用括号 () 括起来的部分，索引从 1 开始计数，若 index 为 0 则返回整个匹配的字符串（REGEXP_EXTRACT(STRING string_expression, STRING regex_pattern, INT index)）。

-- sample data
CREATE TABLE logs (
    log_id INT,
    log_content STRING
);
INSERT INTO logs VALUES
(1, '[2025-01-25 10:00:00] [登录] 用户 John 登录系统'),
(2, '[2025-01-25 11:00:00] [退出] 用户 Alice 退出系统');
 
-- 使用 regexp_extract 提取操作类型
SELECT
    log_id,
    log_content,
    REGEXP_EXTRACT(log_content, "\\[(.*?)\\]\\s*\\[(.*?)\\]", 1) AS second_bracket_content,
    REGEXP_EXTRACT(log_content, "\\[(.*?)\\]\\s*\\[(.*?)\\]", 2) AS second_bracket_content
FROM logs;

REGEXP_REPLACE

将输入字符串中匹配正则表达式的部分替换为指定的字符串（REGEXP_REPLACE(STRING string_expression, STRING regex_pattern, STRING replacement)）。

-- sample data
CREATE TABLE products (
    product_id INT,
    product_name STRING
);
INSERT INTO products VALUES
(1, 'iPhone 14'),
(2, 'iPad Pro 12.9');
 
-- 使用 regexp_replace 替换数字
SELECT
    product_id,
    regexp_replace(product_name, '[0-9]+', '') AS new_product_name
FROM products;

9. JSON字符串与解析

JSON介绍

什么是 JSON

JSON（JavaScript Object Notation）是一种轻量级的数据交换格式，本质上是用文本形式组织和表示结构化数据，方便数据存储、传输和解析。

数仓中常见于：

JSON 的两种基本形式

JSON 主要分为 JSON Object（对象） 和 JSON Array（数组）。

JSON 字符串与 SQL 数据类型

SQL 表中看到 '{"name":"Alice","age":30}' 这样的字段，通常是 JSON 字符串，它的类型是 STRING。

要进一步使用数组、结构体等操作，需要先解析：JSON STRING -> 解析 -> STRUCT / ARRAY / MAP。

解析之后，不同数据类型的访问方式不同：

数据类型 常见操作
STRUCT data.name
MAP data['name']
ARRAY explode(data) 等

JSON函数

1. GET_JSON_OBJECT()

用于根据 JSON 路径，从 JSON 字符串中提取指定元素。适合提取少量字段，尤其是嵌套字段。

语法
get_json_object(json_string, path)

例如：

{
  "name": "Alice",
  "scores": {
    "math": 85
  }
}
常见用法
注意点

2. JSON_TUPLE()

用于一次从 JSON 字符串中提取多个字段，适合多个同层字段的解析。相比多次调用 get_json_object()，只需要解析一次 JSON，效率通常更高。

语法
json_tuple(
    json_string,
    key1,
    key2,
    key3,
    ...
)

注意：key 不需要写 $.。

例如：

{
  "name":"Alice",
  "math_score":85,
  "english_score":90
}

SELECT
    json_tuple(
        student_info,
        'name',
        'math_score',
        'english_score'
    ) AS (name, math_score, english_score)
FROM student;

结果：

name math_score english_score
Alice 85 90
常见用法

原表：

student_info
{"name":"Alice","scores":{"math":85,"english":90}}

第一层解析后：

name scores_json
Alice {"math":85,"english":90}

第二层解析后：

name math_score english_score
Alice 85 90

3. FROM_JSON()

from_json() 用于把整个 JSON STRING 映射成 STRUCT / ARRAY / MAP 等结构化类型。

当 JSON 很复杂、嵌套层级较多，或者需要继续使用 explode()、数组函数、MAP 操作时，使用 from_json() 更合适。资料将它总结为对复杂 JSON “化繁为简”。

函数结构
from_json(json_str, schema)
Schema

常见映射：

JSON 结构 Schema
普通对象 struct<name:string,age:int>
嵌套对象 struct<user:struct<id:int,name:string>,score:double>
JSON 数组 array<struct<course:string,score:int>>
JSON 键值结构 map<string,int>

例如：

{
  "name":"Alice",
  "age":30
}

SELECT 
    from_json(
        json_str, 
        'struct<name:string,age:int>' 
    ) AS json_data 
FROM t;

得到：STRUCT<name:string, age:int>，之后可以json_data.name、json_data.age 直接访问。

常见用法
注意点

4. LATERAL VIEW JSON_TUPLE

用于在保留原表字段的同时，把 JSON 中多个字段解析成独立列。

语法
SELECT
    ...
FROM table_name LATERAL VIEW json_tuple(
    json_col,
    'key1',
    'key2'
) tmp AS col1, col2;

例如原表：

id user_info
1 {"name":"Alice","age":30}

SQL：

SELECT
    id,
    name,
    age
FROM user LATERAL VIEW json_tuple(
    user_info,
    'name',
    'age'
) tmp AS name, age;

结果：

id name age
1 Alice 30

注意它和 explode() 的作用不同：JSON的LATERAL VIEW 主要是把 JSON 中的多个 key 解析成多列，而 explode() 是把 ARRAY / MAP 拆成多行。

5. TO_JSON()（

to_json() 和 from_json() 方向相反，用于把结构化数据转换成 JSON 字符串；常和 STRUCT / ARRAY / MAP 等类型配合使用。

语法
to_json(struct_or_array_or_map)
常见用法
id name age
1 张三 20

可以先构造 MAP：

to_json(
    map(
        'id', CAST(id AS STRING),
        'name', CAST(name AS STRING),
        'age', CAST(age AS STRING)
    )
)

得到类似：

{"id":"1","name":"张三","age":"20"}
注意点

10. SQL高性能优化