1
2
3
4
5
6
7
8

-- 统计所有行（包括 NULL）
SELECT count(*) FROM orders;

-- 统计指定列非 NULL 的行数
SELECT count(user_id) FROM orders;

-- 去重统计（注意性能：会强制走一个 Reducer）
SELECT count(DISTINCT user_id) FROM orders;

1
2
3
4
5
6
7

-- 优化后的写法
SELECT count(1)
FROM (
    SELECT user_id
    FROM orders
    GROUP BY user_id
) t;

1
2
3
4

-- 使用近似计算，性能极高
SELECT count(DISTINCT user_id) as exact_cnt, -- 精确值（慢）
       approx_count_distinct(user_id) as approx_cnt -- 近似值（快）
FROM orders;

1
2
3
4
5
6
7
8

SELECT
dept_id,
sum(salary) AS total_salary,
avg(salary) AS avg_salary,
max(salary) AS max_salary,
min(salary) AS min_salary
FROM employee
GROUP BY dept_id;

1
2

总体方差: var_pop(col)
样本方差: var_samp(col)

1
2
3
4
5
6
7

-- 计算用户每日使用时长的波动情况
SELECT 
  AVG(duration_minutes) AS avg_duration,  -- 平均时长
  VAR_SAMP(duration_minutes) AS variance_duration,  -- 时长方差
  STDDEV_SAMP(duration_minutes) AS stddev_duration  -- 时长标准差
FROM user_behavior
WHERE dt BETWEEN '2023-10-01' AND '2023-10-31';

1
2

总体标准差: stddev_pop(col)
样本标准差: stddev_samp(col)

1
2
3
4
5
6
7
8
9

-- 计算用户订单金额的标准差，了解消费稳定性
SELECT 
  user_id,
  AVG(order_amount) AS avg_amount,           -- 平均订单金额
  STDDEV_SAMP(order_amount) AS std_amount    -- 订单金额标准差
FROM order_table
WHERE dt >= '2023-10-01'
GROUP BY user_id
LIMIT 10;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 监控API响应时间的稳定性
SELECT 
  api_name,
  AVG(response_time_ms) AS avg_time,
  STDDEV_SAMP(response_time_ms) AS std_time,
  -- 计算波动系数：标准差/平均值，衡量相对波动
  STDDEV_SAMP(response_time_ms) / AVG(response_time_ms) AS cv
FROM api_monitor
WHERE hour = '2023-10-22 10:00:00'
GROUP BY api_name;

1
2
3
4
5
6

-- 识别响应时间异常的服务
SELECT *
FROM api_monitor
WHERE response_time_ms > 
  (SELECT AVG(response_time_ms) + 3 * STDDEV_SAMP(response_time_ms) 
   FROM api_monitor WHERE dt = '2023-10-22')

1
2

总体协方差: covar_pop(col1, col2)
样本协方差: covar_samp(col1, col2)

1
2
3
4
5
6

-- 同一关系，不同单位导致协方差值完全不同
SELECT 
  COVAR_SAMP(price_yuan, sales) AS covar_yuan,    -- 价格(元)与销量
  COVAR_SAMP(price_cent, sales) AS covar_cent     -- 价格(分)与销量
FROM product_data;
-- covar_cent 会是 covar_yuan 的100倍，但实际关系相同！

1
2
3
4
5
6
7
8

-- 分析广告曝光量与点击量的关系
SELECT 
  COVAR_SAMP(impressions, clicks) AS covar_imp_clk,
  -- 业务解读：正值表示曝光增加时点击也倾向于增加
  AVG(impressions) AS avg_impressions,
  AVG(clicks) AS avg_clicks
FROM ad_performance
WHERE dt = '2023-10-22';

1
2
3
4
5
6
7
8
9

-- 分析用户浏览时长与购买金额的关系
SELECT 
  user_id,
  COVAR_SAMP(session_duration, purchase_amount) AS covar_duration_amount
FROM user_sessions
GROUP BY user_id
HAVING COVAR_SAMP(session_duration, purchase_amount) > 0
-- 只筛选正相关用户：浏览越久，买得越多
LIMIT 10;

1
2
3
4
5
6
7
8

-- 选择与目标变量相关性强的特征
SELECT 
  feature_name,
  COVAR_SAMP(feature_value, target_value) AS covar_with_target
FROM training_data
GROUP BY feature_name
ORDER BY ABS(COVAR_SAMP(feature_value, target_value)) DESC;
-- 协方差绝对值越大，特征与目标变量关系越强

1

皮尔逊相关系数: corr(c1, c2)

1
2
3
4
5
6
7
8

-- 筛选与目标变量相关性强的特征
SELECT 
  feature_name,
  ABS(CORR(feature, target)) AS abs_correlation
FROM model_features
GROUP BY feature_name
ORDER BY abs_correlation DESC;
-- 通常保留|r|>0.3的特征，剔除|r|<0.1的特征

1
2
3
4
5
6
7
8

-- 监控关键指标对的相关性是否稳定
SELECT 
  dt,
  CORR(active_users, revenue) AS dau_revenue_corr
FROM daily_business_metrics
GROUP BY dt
ORDER BY dt;
-- 如果某天相关性突然下降，需要排查原因

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 基于行为相关性进行用户分群
SELECT 
  user_id,
  CASE 
    WHEN CORR(browse_time, purchase_amount) > 0.6 THEN '高价值探索型'
    WHEN CORR(browse_time, purchase_amount) < -0.3 THEN '快速决策型'
    ELSE '普通型'
  END AS user_segment
FROM user_behavior
GROUP BY user_id;

1
2
3
4

-- 平均值受异常值影响大，分位数更稳健
数据: [1, 2, 3, 4, 1000]
平均值: 202 (被1000拉高)
中位数: 3 (不受极端值影响)

1
2
3
4
5
6
7

-- 语法：PERCENTILE(col, p) OVER()
-- 计算精确分位数，但性能较差，适合小数据集
SELECT 
  PERCENTILE(CAST(salary AS BIGINT), 0.5) AS median_salary,
  PERCENTILE(CAST(salary AS BIGINT), 0.25) AS q1_salary,
  PERCENTILE(CAST(salary AS BIGINT), 0.75) AS q3_salary
FROM employee_table;

1
2
3
4
5

-- 计算单个百分位数
PERCENTILE_APPROX(col, p [, B])

-- 计算多个百分位数（返回数组）
PERCENTILE_APPROX(col, array(p1, p2, ...) [, B])

1
2
3
4
5
6
7

-- 语法：PERCENTILE_APPROX(col, p [, B])
-- B: 精度参数，默认10000，越大越精确但越慢
SELECT 
  PERCENTILE_APPROX(salary, 0.5) AS median_salary,      -- 中位数
  PERCENTILE_APPROX(salary, 0.95) AS p95_salary,        -- 95分位
  PERCENTILE_APPROX(salary, 0.99, 100000) AS p99_salary -- 高精度99分位
FROM employee_table;

1
2
3
4
5
6

-- 错误示例：PERCENTILE 不能用于浮点数
SELECT PERCENTILE(response_time, 0.95) FROM logs; -- 可能报错

-- 正确做法：使用 PERCENTILE_APPROX 或先转换
SELECT PERCENTILE_APPROX(response_time, 0.95) FROM logs;
SELECT PERCENTILE(CAST(response_time AS INT), 0.95) FROM logs; -- 精度丢失

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- 使用数组一次计算多个分位数
SELECT 
  PERCENTILE_APPROX(salary, ARRAY(0.25, 0.5, 0.75, 0.9, 0.95, 0.99)) AS percentiles
FROM employee_table;

-- 结果：数组形式 [q1, median, q3, p90, p95, p99]
-- 提取特定分位值：
SELECT 
  percentiles[0] AS q1,      -- 25%分位（数组索引从0开始）
  percentiles[1] AS median,  -- 中位数
  percentiles[5] AS p99      -- 99%分位
FROM (
  SELECT PERCENTILE_APPROX(salary, ARRAY(0.25, 0.5, 0.75, 0.9, 0.95, 0.99)) AS percentiles
  FROM employee_table
) t;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- 监控API响应时间的P99，用于SLA评估
SELECT 
  api_name,
  COUNT(*) AS request_count,
  AVG(response_time_ms) AS avg_time,
  MIN(response_time_ms) AS min_time,
  MAX(response_time_ms) AS max_time,
  PERCENTILE_APPROX(response_time_ms, 0.99) AS p99_time,  -- 关键指标
  PERCENTILE_APPROX(response_time_ms, 0.95) AS p95_time
FROM api_monitor
WHERE dt = '2023-10-22' AND hour = '10'
GROUP BY api_name
HAVING p99_time > 1000  -- 筛选P99超过1秒的API
ORDER BY p99_time DESC;

1
2
3
4
5
6
7

1. 将数据从小到大排序
2. 计算 Q1（第25百分位数）和 Q3（第75百分位数）
3. IQR = Q3 - Q1
4. 异常值边界：
   - 下界 = Q1 - 1.5 × IQR
   - 上界 = Q3 + 1.5 × IQR
5. 任何低于下界或高于上界的数据点视为异常值

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

-- 计算订单金额的IQR并识别异常订单
WITH order_stats AS (
  SELECT 
    PERCENTILE_APPROX(order_amount, 0.25) as q1,
    PERCENTILE_APPROX(order_amount, 0.75) as q3,
    PERCENTILE_APPROX(order_amount, 0.75) - PERCENTILE_APPROX(order_amount, 0.25) as iqr
  FROM orders
  WHERE dt = '2024-01-01'
)
SELECT 
  o.order_id,
  o.order_amount,
  CASE 
    WHEN o.order_amount < (s.q1 - 1.5 * s.iqr) THEN '过低异常'
    WHEN o.order_amount > (s.q3 + 1.5 * s.iqr) THEN '过高异常'
    ELSE '正常'
  END as outlier_type,
  s.q1 - 1.5 * s.iqr as lower_bound,
  s.q3 + 1.5 * s.iqr as upper_bound
FROM orders o
CROSS JOIN order_stats s
WHERE o.dt = '2024-01-01'
  AND (o.order_amount < (s.q1 - 1.5 * s.iqr) 
       OR o.order_amount > (s.q3 + 1.5 * s.iqr));

1
2
3
4
5
6

-- 语法：PERCENTILE_CONT(p) WITHIN GROUP (ORDER BY col)
-- 返回插值结果（如果分位点不在具体数据点上）
SELECT 
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY response_time) AS median_time,
  PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY response_time) AS p90_time
FROM api_logs;

1
2
3
4
5
6
7
8
9

-- 精确分位数（只支持 int 类型，数据量大时慢）
SELECT percentile(age, 0.5) AS median_age FROM employee;

-- 近似分位数（支持 double，性能好，推荐）
SELECT percentile_approx(salary, 0.95) AS p95_salary FROM employee;

-- 一次算多个分位数（传数组）
SELECT percentile_approx(salary, array(0.5, 0.9, 0.95, 0.99))
FROM employee;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- 语法：COLLECT_LIST(expr)
-- 收集所有值，保留重复项，按照输入顺序排列

-- 示例：收集每个部门的员工姓名
SELECT 
  department,
  COLLECT_LIST(employee_name) AS all_employees,
  COUNT(*) AS employee_count
FROM employees
GROUP BY department;

-- 结果示例：
-- IT → ['张三', '李四', '张三', '王五']  -- 张三出现两次
-- HR → ['赵六', '钱七']

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- 将多行数据转为宽表格式
SELECT 
  user_id,
  MAP_AGG(metric_name, metric_value) AS metrics_map
FROM user_metrics
WHERE dt = '2023-10-22'
GROUP BY user_id;

-- 展开为列
SELECT 
  user_id,
  metrics_map['pv'] AS page_views,
  metrics_map['uv'] AS unique_visitors,
  metrics_map['avg_duration'] AS avg_duration
FROM (
  SELECT 
    user_id,
    MAP_AGG(metric_name, metric_value) AS metrics_map
  FROM user_metrics
  WHERE dt = '2023-10-22'
  GROUP BY user_id
) t;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- 语法：COLLECT_SET(expr)
-- 收集唯一值，不保证顺序

-- 示例：收集每个部门的唯一员工姓名
SELECT 
  department,
  COLLECT_SET(employee_name) AS unique_employees,
  SIZE(COLLECT_SET(employee_name)) AS unique_count
FROM employees
GROUP BY department;

-- 结果示例：
-- IT → ['张三', '李四', '王五']  -- 张三只出现一次
-- HR → ['赵六', '钱七']

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 有序去重：先排序，再去重
SELECT 
  department,
  COLLECT_LIST(employee_name) AS all_ordered,  -- 先收集有序列表
  ARRAY_DISTINCT(COLLECT_LIST(employee_name)) AS unique_ordered  -- 再去重
FROM (
  SELECT department, employee_name
  FROM employees
  DISTRIBUTE BY department
  SORT BY department, employee_name
) t
GROUP BY department;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

-- 语法：MAP_AGG(key_expr, value_expr)
-- 将多行键值对收集为Map，重复key会取最后一个value

-- 示例：收集员工ID到姓名的映射
SELECT 
  department,
  MAP_AGG(employee_id, employee_name) AS id_name_map
FROM employees
GROUP BY department;

-- 结果示例：
-- IT → {1001: '张三', 1002: '李四', 1003: '王五'}
-- HR → {2001: '赵六', 2002: '钱七'}

-- 提取Map中的值
SELECT 
  department,
  id_name_map[1001] AS employee_1001,  -- 获取key为1001的值
  MAP_KEYS(id_name_map) AS all_ids,   -- 获取所有key
  MAP_VALUES(id_name_map) AS all_names -- 获取所有value
FROM (
  SELECT department, MAP_AGG(employee_id, employee_name) AS id_name_map
  FROM employees
  GROUP BY department
) t;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- 语法：MAP_FROM_ARRAYS(keys_array, values_array)
-- 示例：从两列创建Map
SELECT 
  user_id,
  MAP_FROM_ARRAYS(
    COLLECT_LIST(behavior_type),  -- 行为类型数组
    COLLECT_LIST(behavior_count)   -- 行为次数数组
  ) AS behavior_map
FROM user_behavior
GROUP BY user_id;

-- 结果示例：
-- 用户1 → {'view': 10, 'click': 5, 'purchase': 2}
-- 用户2 → {'view': 15, 'click': 3}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- 语法：HISTOGRAM_NUMERIC(expr, nbuckets)
-- 生成数值分布的直方图，nbuckets指定分桶数量

-- 示例：分析工资分布（分10个桶）
SELECT 
  department,
  HISTOGRAM_NUMERIC(salary, 10) AS salary_histogram
FROM employees
GROUP BY department;

-- 结果示例：Map<桶的上界, 频数>
-- IT → {5000.0: 5, 10000.0: 12, 15000.0: 8, ...}
-- 表示：5000以下5人，5000-10000之间12人，以此类推

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 语法：NGRAMS(array<T>, n, k)
-- 从数组中提取最常见的n-gram序列，返回前k个

-- 示例：分析搜索词的最常见二元组
SELECT 
  NGRAMS(SPLIT(search_query, ' '), 2, 5) AS top_bigrams
FROM search_logs
WHERE dt = '2023-10-22';

-- 结果示例：
-- [{'ngram': ['大数据', '学习'], 'estfrequency': 100},
--  {'ngram': ['Hive', '教程'], 'estfrequency': 85}]

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

-- 收集用户按时间排序的行为序列
SELECT 
  user_id,
  COLLECT_LIST(
    STRUCT(
      event_time,
      event_type,
      page_id
    )
  ) AS behavior_sequence
FROM (
  SELECT 
    user_id,
    event_time,
    event_type,
    page_id
  FROM user_events
  WHERE dt = '2023-10-22'
  DISTRIBUTE BY user_id
  SORT BY user_id, event_time  -- 保证按用户和时间排序
) t
GROUP BY user_id
LIMIT 10;

-- 结果：每个用户的行为轨迹数组
-- 用于后续的序列分析、路径分析、用户画像

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- 将订单中的所有商品聚合
SELECT 
  order_id,
  COLLECT_LIST(
    NAMED_STRUCT(
      'product_id', product_id,
      'product_name', product_name,
      'quantity', quantity,
      'price', price
    )
  ) AS order_items,
  SUM(quantity * price) AS order_total,
  COUNT(DISTINCT product_id) AS distinct_products
FROM order_items
GROUP BY order_id
HAVING order_total > 1000
ORDER BY order_total DESC;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 将用户的多个标签聚合为Map
SELECT 
  user_id,
  MAP_AGG(tag_category, tag_value) AS user_tags
FROM user_tags
WHERE tag_weight > 0.5
GROUP BY user_id;

-- 结果示例：用户标签Map
-- 用户1 → {'兴趣': '科技', '年龄段': '25-30', '消费水平': '高'}
-- 用户2 → {'兴趣': '美食', '年龄段': '30-35', '城市': '成都'}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- 将多行数据转为宽表格式
SELECT 
  user_id,
  MAP_AGG(metric_name, metric_value) AS metrics_map
FROM user_metrics
WHERE dt = '2023-10-22'
GROUP BY user_id;

-- 展开为列
SELECT 
  user_id,
  metrics_map['pv'] AS page_views,
  metrics_map['uv'] AS unique_visitors,
  metrics_map['avg_duration'] AS avg_duration
FROM (
  SELECT 
    user_id,
    MAP_AGG(metric_name, metric_value) AS metrics_map
  FROM user_metrics
  WHERE dt = '2023-10-22'
  GROUP BY user_id
) t;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- 计算用户间的共同行为数量
WITH user_behavior_set AS (
  SELECT 
    user_id,
    COLLECT_SET(page_id) AS viewed_pages
  FROM page_views
  WHERE dt = '2023-10-22'
  GROUP BY user_id
)
SELECT 
  a.user_id AS user1,
  b.user_id AS user2,
  SIZE(ARRAY_INTERSECTION(a.viewed_pages, b.viewed_pages)) AS common_pages_count,
  -- 计算Jaccard相似度
  SIZE(ARRAY_INTERSECTION(a.viewed_pages, b.viewed_pages)) * 1.0 /
  SIZE(ARRAY_UNION(a.viewed_pages, b.viewed_pages)) AS jaccard_similarity
FROM user_behavior_set a
JOIN user_behavior_set b
WHERE a.user_id < b.user_id
  AND SIZE(ARRAY_INTERSECTION(a.viewed_pages, b.viewed_pages)) > 5
ORDER BY common_pages_count DESC
LIMIT 100;

1
2
3
4
5
6
7
8
9

-- collect_list：保留重复值，保留顺序（不严格保证）
SELECT user_id, collect_list(product_id) AS bought_items
FROM orders
GROUP BY user_id;

-- collect_set：自动去重，无序
SELECT user_id, collect_set(product_id) AS unique_items
FROM orders
GROUP BY user_id;

1
2
3
4
5

SELECT
user_id,
concat_ws(',', collect_list(cast(product_id AS string))) AS items_str
FROM orders
GROUP BY user_id;

1
2
3
4
5
6
7
8
9

SELECT year, month, region, sum(sales)
FROM orders
GROUP BY year, month, region
GROUPING SETS (
    (year, month, region), -- 最细粒度
    (year, month), -- 按年月
    (year), -- 按年
    () -- 总计
);

1
2
3

SELECT year, month, region, sum(sales)
FROM orders
GROUP BY year, month, region WITH ROLLUP;

1
2
3
4
5
6

GROUPING SETS (
  (year, month, region),
  (year, month),
  (year),
  ()
)

1
2
3

SELECT year, month, region, sum(sales)
FROM orders
GROUP BY year, month, region WITH CUBE;

1
2

(year,month,region), (year,month), (year,region), (month,region),
(year), (month), (region), ()

1
2
3
4
5
6

SELECT
year, month, region,
sum(sales) AS total,
grouping__id AS gid
FROM orders
GROUP BY year, month, region WITH CUBE;