第 5 章广电用户账单与订单数据查询进阶

开篇自学说明

本章是 Hive 查询从基础单表查询到业务化多维度分析的核心跨越，承接第 3 章的表结构创建、第 4 章的基础查询能力，聚焦广电两大核心业务数据 ——用户订单表 order_index、用户账单表 mmconsume_billevents，结合用户基本表 mediamatch_usermsg，解决更复杂的业务分析需求。

学完本章，你能独立完成以下核心业务分析：

对用户订单做消费类型分层、用户价值打标
统计用户年度 / 月度 / 周期消费的应付、实付总额
计算用户月均消费、优惠力度、产品生命周期等核心指标
打通用户 - 订单 - 账单三张表，做全维度的用户消费分析
对千万级订单数据做高效抽样统计，避免全表扫描的性能损耗

前置必备准备

已完成第 3 章的三张核心表创建与数据导入：
- order_index：用户产品订单表（核心字段：phone_no 用户编号、cost 订单金额、prodname 产品名称、effdate 生效时间、orderdate 订购时间等）
- mmconsume_billevents：用户月度账单表（核心字段：phone_no 用户编号、year_month 账单年月、should_pay 应收金额、favour_fee 优惠金额等）
- mediamatch_usermsg：用户基本信息表（核心字段：phone_no 用户编号、run_name 用户状态、addressoj 用户地址、open_time 开户时间等）
已掌握第 4 章的 SELECT、WHERE、GROUP BY、ORDER BY、LIMIT 等基础语法
进入 Hive 客户端后，先执行以下必备配置（提升查询体验）

hive

-- 切换到广电业务库
USE ZJSM;
-- 开启查询结果列名显示
SET hive.cli.print.header = true;
-- 列名仅显示字段名，不显示表名前缀
SET hive.resultset.use.unique.column.names=false;
-- 提示符显示当前所在数据库，避免走错库
SET hive.cli.print.current.db=true;

模块一：Hive 内置函数（进阶查询的核心工具）

Hive 内置函数是把高频的计算、转换、判断逻辑封装好的现成工具，不用自己写复杂逻辑，直接调用就能实现业务需求，是本章最基础、最核心的内容。

一、内置函数基础操作（自学必记）

遇到不会的函数，先查官方说明，比百度更权威、更准确

hive

-- 查看Hive所有内置函数，不知道有什么函数时先执行这个
SHOW FUNCTIONS;
-- 查看指定函数的详细用法、语法、示例
DESC FUNCTION EXTENDED 函数名;
-- 示例：查看CASE函数的完整用法
DESC FUNCTION EXTENDED CASE;

二、6 大类核心内置函数（按业务使用频率排序）

1. 条件函数 CASE WHEN + 类型转换函数 CAST（任务 5.1 核心）

这两个函数是数据分类打标的黄金搭档，90% 的用户分层、消费分类场景都会用到。

（1）CASE WHEN 条件函数

大白话理解：就是 SQL 里的 if-else 多分支判断，满足不同条件就返回不同的结果，专门用来给数据做分类、打标签。

两种核心语法

① 固定值匹配语法（适合字段值和固定值对比）

hive

CASE 待判断字段
  WHEN 匹配值1 THEN 返回结果1
  WHEN 匹配值2 THEN 返回结果2
  [ELSE 默认结果] -- 所有条件都不满足时返回
END [AS 列别名]

② 条件表达式语法（更灵活，适合区间判断、复杂条件，本章重点）

hive

CASE
  WHEN 条件表达式1 THEN 返回结果1
  WHEN 条件表达式2 THEN 返回结果2
  [ELSE 默认结果]
END [AS 列别名]

自学必守规则
1. 列别名必须加在END关键字之后，不能加在中间
2. 分支按从上到下的顺序执行，匹配到第一个条件就会终止判断，所以要把范围更小的条件放前面
3. 条件值的数据类型必须和判断字段的类型完全一致，否则会语法报错

（2）CAST 类型转换函数

大白话理解：强制把一个数据类型转换成另一个，解决「字符串类型的金额无法做数值计算 / 比较」的核心问题。

核心语法

hive

CAST(待转换的值 AS 目标数据类型)

广电业务高频场景
订单表的
```
cost
```
订单金额、账单表的
```
should_pay
```
应收金额，建表时都是 STRING 字符串类型，必须用
```
CAST(cost AS DOUBLE)
```
转换成数值类型，才能做大小比较、加减计算。

（3）核心业务实战（任务 5.1：统计订单的消费类型）

需求：按订单金额分 3 类：≥2000 元 = 高消费，1000-2000 元 = 中等消费，<1000 元 = 低消费；去重后按订单金额降序排列，消费类型列别名设为type。

hive

-- 任务5.1 完整可执行代码
SELECT 
  DISTINCT -- 去重，保证订单数据唯一
  phone_no AS `用户编号`,
  orderno AS `订单编号`,
  prodprcname AS `产品名称`,
  -- 1. 类型转换：把字符串类型的cost转成浮点型，用于计算和排序
  CAST(cost AS FLOAT) AS `订单金额`,
  -- 2. 条件函数：多分支判断，实现消费类型分类
  CASE
    WHEN CAST(cost AS DOUBLE) >= 2000.0 THEN '高消费'
    WHEN CAST(cost AS DOUBLE) >= 1000.0 AND CAST(cost AS DOUBLE) < 2000.0 THEN '中等消费'
    ELSE '低消费'
  END AS `type` -- 别名必须加在END之后
FROM order_index
-- 3. 按转换后的订单金额降序排列
ORDER BY `订单金额` DESC;

✅ 自学避坑点：

必须用 CAST 转换字符串类型的金额，否则字符串比较会出现「1000 < 200」的逻辑错误
DISTINCT 是对 SELECT 后面所有字段去重，不是只对第一个字段去重
多分支条件要注意区间闭合，避免出现「1000 元既不属于中等也不属于低消费」的逻辑漏洞

2. 字符函数（任务 5.2 核心）

专门用来处理字符串的截取、拼接、清洗、格式转换，是时间字段处理、文本数据清洗的核心工具。

函数	核心语法	大白话解释	广电业务高频场景
`SUBSTR`	`SUBSTR(字符串, 起始位置, 截取长度)`	从字符串的指定位置，截取指定长度的字符；省略长度则截取到末尾	从账单年月`year_month`中截取年份 / 月份
`CONCAT`	`CONCAT(字符串1, 字符串2, ...)`	把多个字符串拼接成一个新字符串	拼接用户省市区地址、年度 + 月度时间标识
`TRIM`	`TRIM(字符串)`	去掉字符串前后的空格	清洗产品名称、用户地址里的首尾空格，避免匹配失败
`LOWER/UPPER`	`LOWER(字符串)`/`UPPER(字符串)`	把字符串全转成小写 / 大写	统一产品名称的大小写，避免大小写不同导致匹配失败
`LENGTH`	`LENGTH(字符串)`	获取字符串的长度	校验用户编号、地址字段的合法性
regexp_replace(s, r, r)	`regexp_replace('foobar', 'oo	ar', '')`->`'fb'`	正则表达式替换
split(str, pat)	`split('a-b-c', '-')` -> `['a','b','c']`	分割字符串，返回数组

核心业务实战（任务 5.2：统计用户每年消费应付总额）

需求：从账单表的year_month字段（格式如 2022-01）中提取年份，统计每个用户每年的应付消费总金额。

hive

-- 任务5.2 完整可执行代码
SELECT 
  phone_no AS `用户编号`,
  -- 截取year_month的前4位，得到年份（比如2022-01 → 2022）
  SUBSTR(year_month, 1, 4) AS `账单年份`,
  -- 求和计算年度应付总额，先把字符串转成数值型
  SUM(CAST(should_pay AS DOUBLE)) AS `年度应付总额`
FROM mmconsume_billevents
-- 按用户编号+年份两个维度分组，保证每个用户每年一条统计结果
GROUP BY phone_no, SUBSTR(year_month, 1, 4);

✅ 自学避坑点：

Hive 的 SUBSTR 起始位置是从 1 开始，不是从 0 开始，这是和 Java/Python 最大的区别，新手很容易写错
GROUP BY 后面的字段，必须和 SELECT 后面的非聚合字段完全一致，否则会语法报错

3. 日期函数（任务 5.3 核心）

专门用来处理日期的提取、计算、格式转换，是用户消费周期、开户时长、账期统计的核心工具。

函数	核心语法	大白话解释	广电业务高频场景
`YEAR/MONTH/DAY`	`YEAR(日期字符串)`	从日期 / 年月字符串中提取年 / 月 / 日	从账单年月中提取年份、月份，做周期统计
`TRUNC`	`TRUNC(日期字符串, 格式)`	日期归零，格式支持 YEAR/MM，按年归零到当年 1 月 1 日，按月归零到当月 1 日	统计年度 / 月度累计消费，做时间维度的聚合
`DATEDIFF`	`DATEDIFF(结束日期, 开始日期)`	计算两个日期之间相隔的天数，结束日期早于开始日期则返回负数	计算产品生效周期、用户开户时长、订单处理时长
`DATE_ADD/DATE_SUB`	`DATE_ADD(开始日期, 天数)`	给日期增加 / 减少指定天数	计算产品到期时间、用户账期预警
`ADD_MONTHS`	`ADD_MONTHS(开始日期, 月数)`	给日期增加 / 减少指定月数	计算用户合约到期时间
current_date		返回当前日期
unix_timestamps		获取当前或指定时间的 Unix 时间戳
rom_unixtime		将 Unix 时间戳转为字符串

核心业务实战（任务 5.3：统计用户每月消费应付总额）

需求：统计每个用户每个月的应付消费总额，避免 2021 年 7 月和 2022 年 7 月的数据被合并统计。

hive

-- 任务5.3 完整可执行代码
SELECT 
  phone_no AS `用户编号`,
  -- 用日期函数提取年份、月份，比SUBSTR更规范、容错率更高
  YEAR(year_month) AS `账单年份`,
  MONTH(year_month) AS `账单月份`,
  SUM(CAST(should_pay AS DOUBLE)) AS `月度应付总额`
FROM mmconsume_billevents
-- 必须按用户+年份+月份3个维度分组，避免跨年同月份数据合并
GROUP BY phone_no, YEAR(year_month), MONTH(year_month);

✅ 自学避坑点：

分组维度必须覆盖年份 + 月份，只按月份分组会导致不同年份的同月份数据被合并，出现严重的业务错误
YEAR/MONTH 函数有日期容错机制，比如月份写 13 会自动进位到下一年，比 SUBSTR 手动截取更健壮

4. 数学函数（任务 5.4 核心）

用来做数值的四则运算、取整、精度处理，是账单金额、消费指标计算的核心工具。

函数 / 运算符	核心语法	大白话解释	广电业务高频场景
四则运算符 `+`/`-`/`*`/`/`	`字段1 - 字段2`	数值的加减乘除计算	实付金额 = 应收金额 - 优惠金额、优惠率 = 优惠金额 / 应收金额
`ROUND`	`ROUND(数值, 保留小数位数)`	四舍五入，保留指定小数位数	金额类数据固定保留 2 位小数，避免精度问题
`CEIL`	`CEIL(数值)`	向上取整，返回比数值大的最小整数	计算用户消费天数、套餐周期向上取整
`FLOOR`	`FLOOR(数值)`	向下取整，返回比数值小的最大整数	消费金额向下取整统计
abs	`abs(-10)` -> 10	绝对值

核心业务实战（任务 5.4：统计用户每月实际账单金额）

需求：实付金额 = 应收金额 - 优惠金额，计算 2022 年度用户月均实付账单金额，保留 2 位小数，取金额最高的前 20 个用户。

hive

-- 任务5.4 完整可执行代码
SELECT 
  phone_no AS `用户编号`,
  -- 1. 四则运算算实付金额，AVG求月均，ROUND保留2位小数
  ROUND(AVG(CAST(should_pay AS DOUBLE) - CAST(favour_fee AS DOUBLE)), 2) AS `月均实付金额`
FROM mmconsume_billevents
-- 按用户+年份分组
GROUP BY phone_no, YEAR(year_month)
-- 分组后筛选2022年的数据，聚合函数结果筛选必须用HAVING
HAVING YEAR(year_month) = 2022
-- 按月均金额降序排列
ORDER BY `月均实付金额` DESC
-- 只取前20名
LIMIT 20;

✅ 自学避坑点：

四则运算中如果有一个字段是 NULL，结果会变成 NULL，建议用NVL(字段, 0)把 NULL 值填充为 0，比如NVL(CAST(favour_fee AS DOUBLE), 0)
金额类数据必须用 ROUND 固定小数位数，避免浮点精度问题导致的金额显示异常
年份筛选必须用 HAVING，因为 YEAR (year_month) 在 GROUP BY 里，不能用 WHERE 筛选

额外补充

5. 条件判断函数 (Conditional Functions)

用于实现逻辑分支。

if(boolean, valueTrue, valueFalse): 类似于三元运算符。
nvl(value, default_value): 如果 value 为空，则返回默认值。
COALESCE(v1, v2, ...): 返回参数列表中第一个非 NULL 的值。

CASE WHEN ... THEN ... ELSE ... END:

sql

SELECT name,
       CASE WHEN score >= 60 THEN 'Pass'
            ELSE 'Fail' END as status
FROM student_scores;

6. 聚合函数 (Aggregate Functions - UDAF)

通常与 GROUP BY 连用。

count(*): 统计行数。
sum(col): 求和。
avg(col): 求平均值。
max(col) / min(col): 最大/最小值。
collect_list(col): 将某列去重前合并成一个数组（Array）。
collect_set(col): 将某列去重后合并成一个数组。

7. 窗口函数 (Window Functions)

这是 Hive 处理复杂分析（如排名、移动平均）的利器。

排名： row_number(), rank(), dense_rank()
取值： lag(col, n) (往前取第 n 行), lead(col, n) (往后取第 n 行)
分布： ntile(n) (切片/分组)

模块二：Hive 多表连接查询 JOIN（打通多表数据的核心）

一、大白话理解 JOIN

单表查询只能看单一维度的数据，比如订单表只能看订单消费，用户表只能看用户基本信息，账单表只能看月度账单。而JOIN 连接查询，就是通过共同的关联键（phone_no 用户编号），把多张表的数据「拼起来」，实现用户 - 订单 - 账单的全维度分析。

二、JOIN 核心基础规则

核心语法

hive

SELECT 
  左表别名.字段1, 
  右表别名.字段2
FROM 左表名 [AS] 左表别名
[JOIN类型] JOIN 右表名 [AS] 右表别名
ON 关联条件; -- 必须写，否则会产生笛卡尔积，数据量直接爆炸

执行规则：Hive 的 JOIN 执行顺序是从左到右，建议把小表放左边，大表放右边，节省内存，提升查询性能
关联键规则：两张表的关联键（比如 phone_no）数据类型必须完全一致，否则会导致关联失效、查询报错
别名规则：多表关联时，建议给每张表起简短的别名，简化 SQL，避免同名字段冲突

三、5 种 JOIN 类型全解（大白话 + 业务案例）

JOIN 类型	关键字	核心特性（大白话）	适用场景
内连接	`INNER JOIN`	只保留两张表中完全匹配关联条件的数据，两边都有对应数据才会出现在结果里	查询同时有用户信息、有订单、有账单的完整数据，比如有宽带订单的用户地址
左外连接	`LEFT [OUTER] JOIN`	左表的所有数据全部保留，右表匹配不上的字段填 NULL	以左表为主体，补充右表信息，比如查询所有用户的账单情况（包含没有账单的用户）
右外连接	`RIGHT [OUTER] JOIN`	右表的所有数据全部保留，左表匹配不上的字段填 NULL	以右表为主体，补充左表信息，实际开发中完全可以用 LEFT JOIN 替换，把表顺序调换即可
全外连接	`FULL [OUTER] JOIN`	左右两张表的所有数据全部保留，两边匹配不上的字段互相填 NULL	需要合并两张表的全量数据，无数据丢失，大数据量下性能极差，非必要不使用
左半开连接	`LEFT SEMI JOIN`	只返回左表中在右表能匹配上关联条件的数据，结果只包含左表的字段	替代`IN`子查询，做「存在性判断」，性能比 INNER JOIN 高很多，因为匹配到一条就停止扫描

核心业务案例 1：基础 JOIN 用法（任务 5.5 配套）

hive

-- 1. 内连接：查询产生账单的用户状态（只保留有账单的用户）
-- 代码5-10
SELECT 
  u.phone_no AS `用户编号`,
  u.run_name AS `用户状态`,
  b.fee_code AS `费用类型`,
  b.year_month AS `账单年月`
FROM mmconsume_billevents AS b
INNER JOIN mediamatch_usermsg AS u
ON b.phone_no = u.phone_no; -- 关联条件：用户编号

-- 2. 左外连接：查询所有用户的账单情况（包含无账单的用户）
-- 代码5-11
SELECT 
  u.phone_no AS `用户编号`,
  u.run_name AS `用户状态`,
  b.fee_code AS `费用类型`,
  b.year_month AS `账单年月`
FROM mediamatch_usermsg AS u
LEFT JOIN mmconsume_billevents AS b
ON u.phone_no = b.phone_no;

-- 3. 左半开连接：查询有账单的用户信息（性能比内连接高）
-- 代码5-13 正确示例
SELECT u.phone_no, u.run_name
FROM mediamatch_usermsg AS u
LEFT SEMI JOIN mmconsume_billevents AS b
ON u.phone_no = b.phone_no;

-- ❌ 错误示范：左半开连接的SELECT/WHERE里绝对不能引用右表的字段，必报错
-- SELECT u.phone_no, b.fee_code FROM u LEFT SEMI JOIN b ON u.phone_no=b.phone_no;

核心业务案例 2：任务 5.5 查询用户宽带订单的地址数据

需求：筛选订单表中产品名称包含「联合宽带」的订单，关联用户基本表，获取宽带订单对应用户的地址信息。

hive

-- 任务5.5 完整可执行代码
SELECT 
  o.phone_no AS `用户编号`,
  o.prodname AS `订购产品名称`,
  u.addressoj AS `用户详细地址`
FROM order_index o -- 订单表别名o
-- 内连接用户表，只保留两边都匹配上的数据
INNER JOIN mediamatch_usermsg u
ON o.phone_no = u.phone_no -- 关联键：用户编号
-- 模糊匹配宽带产品订单
WHERE o.prodname LIKE '%联合宽带%';

四、UNION ALL 结果集合并

1. 大白话理解

JOIN 是把多张表左右拼列，而 UNION ALL 是把多个 SELECT 查询的结果上下拼行，把多个结果集合并成一个。

2. 核心语法与强制规则

hive

SELECT 字段1, 字段2 FROM 表1
UNION ALL
SELECT 字段1, 字段2 FROM 表2
[UNION ALL SELECT 字段1, 字段2 FROM 表3 ...]

强制规则（不遵守必报错）：

每个 SELECT 子查询返回的列数量、列名、字段数据类型必须完全一致
UNION ALL 只合并结果，不会自动去重；去重需要用 UNION（性能低，大数据量优先用 UNION ALL + DISTINCT）

业务案例：合并账单表和订单表的用户信息

hive

-- 代码5-14 合并账单和订单表的用户等级信息
SELECT phone_no, owner_code, owner_name
FROM mmconsume_billevents
UNION ALL
SELECT phone_no, owner_code, owner_name
FROM order_index;

模块三：桶表抽样查询（大数据量高效统计）

一、大白话理解

当订单数据达到百万、千万级时，全表扫描统计会消耗大量集群资源，查询耗时极长。桶表抽样查询就是先把全量数据按指定字段哈希取模，均匀分到固定数量的「桶」里，查询时只抽取 1 个 / 几个桶的样本数据，快速得到统计结果，兼顾效率和准确性。

二、核心语法与参数说明

1. 桶表创建语法

hive

CREATE TABLE 桶表名(
  字段1 类型,
  字段2 类型,
  ...
)
CLUSTERED BY (分桶字段名) 
[SORTED BY (排序字段 排序规则)] 
INTO 桶数量 BUCKETS -- 分成几个桶
ROW FORMAT DELIMITED FIELDS TERMINATED BY '分隔符';

2. 抽样查询语法

hive

SELECT 字段
FROM 桶表名
TABLESAMPLE(BUCKET x OUT OF y ON 分桶字段);	·

参数大白话解释：

y：必须是桶表总桶数的倍数或因子，决定抽样比例（抽样比例 = 总桶数 ÷ y）
例：总桶数 4，y=4 → 抽 1/4 数据；y=2 → 抽 1/2 数据；y=8 → 抽 1/8 数据
x：从第几个桶开始抽取，x 必须小于等于 y，否则抽不到数据
分桶字段：必须和创建桶表时的分桶字段完全一致，否则抽样无效

三、完整业务实战（任务 5.6：抽样统计用户订购产品情况）

步骤 1：创建订单分桶表

hive

-- 代码5-16 创建订单分桶表，按用户编号phone_no分4个桶
CREATE TABLE IF NOT EXISTS order_index_bucket(
 phone_no STRING COMMENT '用户编号',
 prodname STRING COMMENT '产品名称',
 offerid STRING COMMENT '套餐编号',
 offername STRING COMMENT '套餐名称',
 business_name STRING COMMENT '业务状态',
 orderno STRING COMMENT '订单编号'
)
COMMENT '订单分桶表'
-- 按用户编号分4个桶
CLUSTERED BY(phone_no) INTO 4 BUCKETS
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ';';

步骤 2：开启分桶配置，向桶表导入数据

hive

-- 必须开启分桶强制配置，否则插入数据不会自动分桶
SET hive.enforce.bucketing = true;

-- 代码5-17 向分桶表插入全量订单数据
INSERT OVERWRITE TABLE order_index_bucket
SELECT 
  phone_no,
  prodname,
  offerid,
  offername,
  business_name,
  orderno
FROM order_index;

步骤 3：抽样查询与统计

hive

-- 代码5-18 抽取第3个桶的全部数据（总4个桶，抽1/4数据）
SELECT * 
FROM order_index_bucket
TABLESAMPLE(BUCKET 3 OUT OF 4 ON phone_no);

-- 代码5-19 统计第1个桶中的数据条数，验证抽样比例
SELECT COUNT(*) AS 桶内数据条数
FROM order_index_bucket
TABLESAMPLE(BUCKET 1 OUT OF 4 ON phone_no);

-- 拓展：抽样统计每个产品的订购次数
SELECT 
  prodname AS `产品名称`,
  COUNT(*) AS `订购次数`
FROM order_index_bucket
TABLESAMPLE(BUCKET 1 OUT OF 2 ON phone_no) -- 抽1/2数据
GROUP BY prodname
ORDER BY `订购次数` DESC;

✅ 自学避坑点：

插入数据前必须开启SET hive.enforce.bucketing = true;，否则数据不会按规则分桶，抽样会完全失效
抽样的y必须是总桶数的倍数 / 因子，x必须≤y，否则会抽不到数据
分桶字段必须是表内已有的字段，不能是分区字段，且必须和抽样时的字段完全一致
桶表不仅能用于抽样，还能大幅提升大表 JOIN 的性能，是大数据优化的重要手段

模块四：拓展实操题解析

文档中的两道进阶操作题，拆解用到的知识点和业务逻辑，帮你吃透函数的组合使用。

实操题 1：计算订单订购到生效的间隔天数、产品生效周期

hive

-- 原代码
SELECT 
  phone_no,
  DATEDIFF(effdate, orderdate) AS `订购到生效天数`,
  DATEDIFF(expdate, effdate) AS `产品生效周期天数` 
FROM order_index;

解析：

用到DATEDIFF(结束日期, 开始日期)函数，计算两个日期的间隔天数
第一个计算：产品生效日期 effdate - 订单订购日期 orderdate，得到订单处理时长
第二个计算：产品失效日期 expdate - 产品生效日期 effdate，得到产品的合约生效周期

实操题 2：正常开户用户账单打 9 折，其他用户原价计费

hive

-- 原代码
SELECT 
  b.phone_no,
  CASE u.run_name
    WHEN '正常' THEN (CAST(b.should_pay AS DOUBLE) - CAST(b.favour_fee AS DOUBLE)) * 0.9
    ELSE CAST(b.should_pay AS DOUBLE) - CAST(b.favour_fee AS DOUBLE)
  END AS `最终实付金额`
FROM mmconsume_billevents b 
LEFT JOIN 
  -- 子查询：筛选2022-07-01前开户的用户
  (SELECT phone_no, run_name 
   FROM mediamatch_usermsg 
   WHERE DATEDIFF('2022-07-01', open_time) >= 0) u
ON b.phone_no = u.phone_no;

解析：

子查询：用DATEDIFF计算开户时间到 2022-07-01 的天数，筛选出 2022-07-01 前开户的用户
左外连接：以账单表为主体，关联筛选后的用户表，保证所有账单数据都被保留
CASE WHEN 条件判断：用户状态为「正常」的，实付金额打 9 折，其他用户按原价计费
核心：先做类型转换计算实付金额，再做折扣计算，保证数值计算的准确性

新手高频踩坑避坑指南

字符串金额不转换直接计算
- 坑：直接对 STRING 类型的 cost/should_pay 做大小比较、加减计算，导致结果完全错误
- 解决：所有数值计算前，必须用CAST(字段 AS DOUBLE)转换成数值类型
CASE WHEN 分支顺序错误
- 坑：把范围大的条件放前面，导致小范围条件永远匹配不到，比如先写>=1000再写>=2000，2000 元的订单会被分到中等消费

解决：条件分支按范围从小到大的顺序写，先判断高阈值，再判断低阈值

LEFT JOIN 过滤条件位置错误
- 坑：把右表的过滤条件写在 WHERE 子句里，导致左外连接直接退化成内连接，左表的 NULL 数据被过滤
- 解决：外连接中，副表的过滤条件必须写在ON子句里，主表的过滤条件写在 WHERE 子句里
左半开连接引用右表字段
- 坑：在 SELECT/WHERE 里引用右表的字段，直接语法报错
- 解决：左半开连接只能返回左表的字段，仅用于「存在性判断」，需要右表字段请用 INNER JOIN
UNION ALL 列不匹配
- 坑：多个 SELECT 子查询的列数量、类型、名称不一致，执行报错
- 解决：严格统一所有子查询的列顺序、数量、类型、名称，必须完全一致
桶表抽样失效
- 坑：插入数据前没开分桶配置、分桶字段和抽样字段不一致、y 不是总桶数的倍数 / 因子，导致抽样结果为空 / 数据量不对
- 解决：严格遵守分桶配置、字段匹配、参数规则，插入数据后先验证桶内数据量
分组维度不全导致数据错误
- 坑：统计月度消费只按用户 + 月份分组，导致不同年份的同月份数据被合并
- 解决：时间维度的分组，必须同时包含年份 + 月份，保证时间维度的唯一性

入门自测题（学完检验成果）

题目 1

请写出 SQL，对 2022 年的账单数据，给用户做年度消费分层：年消费≥10000 元为高价值用户，5000-10000 元为中价值用户，<5000 元为低价值用户，统计每个层级的用户数量。

题目 2

请写出 SQL，查询所有订购了「联合宽带」产品的用户，对应的用户状态、开户时间、详细地址、近 1 年的总消费金额。

题目 3

请写出 SQL，从订单分桶表 order_index_bucket 中抽取 1/4 的数据，统计每个产品的订购总金额，按金额降序排列，取 TOP10 产品。

第 5 章 广电用户账单与订单数据查询进阶 ​

开篇自学说明 ​

模块一：Hive 内置函数（进阶查询的核心工具） ​

一、内置函数基础操作（自学必记） ​

二、6 大类核心内置函数（按业务使用频率排序） ​

1. 条件函数 CASE WHEN + 类型转换函数 CAST（任务 5.1 核心） ​

（1）CASE WHEN 条件函数 ​

（2）CAST 类型转换函数 ​

（3）核心业务实战（任务 5.1：统计订单的消费类型） ​

2. 字符函数（任务 5.2 核心） ​

核心业务实战（任务 5.2：统计用户每年消费应付总额） ​

3. 日期函数（任务 5.3 核心） ​

核心业务实战（任务 5.3：统计用户每月消费应付总额） ​

4. 数学函数（任务 5.4 核心） ​

核心业务实战（任务 5.4：统计用户每月实际账单金额） ​

额外补充 ​

5. 条件判断函数 (Conditional Functions) ​

6. 聚合函数 (Aggregate Functions - UDAF) ​

7. 窗口函数 (Window Functions) ​

模块二：Hive 多表连接查询 JOIN（打通多表数据的核心） ​

一、大白话理解 JOIN ​

二、JOIN 核心基础规则 ​

三、5 种 JOIN 类型全解（大白话 + 业务案例） ​

核心业务案例 1：基础 JOIN 用法（任务 5.5 配套） ​

核心业务案例 2：任务 5.5 查询用户宽带订单的地址数据 ​

四、UNION ALL 结果集合并 ​

1. 大白话理解 ​

2. 核心语法与强制规则 ​

业务案例：合并账单表和订单表的用户信息 ​

模块三：桶表抽样查询（大数据量高效统计） ​

一、大白话理解 ​

二、核心语法与参数说明 ​

1. 桶表创建语法 ​

2. 抽样查询语法 ​

三、完整业务实战（任务 5.6：抽样统计用户订购产品情况） ​

步骤 1：创建订单分桶表 ​

步骤 2：开启分桶配置，向桶表导入数据 ​

步骤 3：抽样查询与统计 ​

模块四：拓展实操题解析 ​

实操题 1：计算订单订购到生效的间隔天数、产品生效周期 ​

实操题 2：正常开户用户账单打 9 折，其他用户原价计费 ​

新手高频踩坑避坑指南 ​

入门自测题（学完检验成果） ​

题目 1 ​

题目 2 ​

题目 3 ​

第 5 章广电用户账单与订单数据查询进阶

开篇自学说明

模块一：Hive 内置函数（进阶查询的核心工具）

一、内置函数基础操作（自学必记）

二、6 大类核心内置函数（按业务使用频率排序）

1. 条件函数 CASE WHEN + 类型转换函数 CAST（任务 5.1 核心）

（1）CASE WHEN 条件函数

（2）CAST 类型转换函数

（3）核心业务实战（任务 5.1：统计订单的消费类型）

2. 字符函数（任务 5.2 核心）

核心业务实战（任务 5.2：统计用户每年消费应付总额）

3. 日期函数（任务 5.3 核心）

核心业务实战（任务 5.3：统计用户每月消费应付总额）

4. 数学函数（任务 5.4 核心）

核心业务实战（任务 5.4：统计用户每月实际账单金额）

额外补充

5. 条件判断函数 (Conditional Functions)

6. 聚合函数 (Aggregate Functions - UDAF)

7. 窗口函数 (Window Functions)

模块二：Hive 多表连接查询 JOIN（打通多表数据的核心）

一、大白话理解 JOIN

二、JOIN 核心基础规则

三、5 种 JOIN 类型全解（大白话 + 业务案例）

核心业务案例 1：基础 JOIN 用法（任务 5.5 配套）

核心业务案例 2：任务 5.5 查询用户宽带订单的地址数据

四、UNION ALL 结果集合并

1. 大白话理解

2. 核心语法与强制规则

业务案例：合并账单表和订单表的用户信息

模块三：桶表抽样查询（大数据量高效统计）

一、大白话理解

二、核心语法与参数说明

1. 桶表创建语法

2. 抽样查询语法

三、完整业务实战（任务 5.6：抽样统计用户订购产品情况）

步骤 1：创建订单分桶表

步骤 2：开启分桶配置，向桶表导入数据

步骤 3：抽样查询与统计

模块四：拓展实操题解析

实操题 1：计算订单订购到生效的间隔天数、产品生效周期

实操题 2：正常开户用户账单打 9 折，其他用户原价计费

新手高频踩坑避坑指南

入门自测题（学完检验成果）

题目 1

题目 2

题目 3