-- 员工表
CREATE OR REPLACE TEMPORARY VIEW employees AS
VALUES
  (1, '张三', '技术部', 15000, 28, '2021-03-15', '北京'),
  (2, '李四', '市场部', 12000, 35, '2019-07-01', '上海'),
  (3, '王五', '技术部', 18000, 32, '2020-01-10', '北京'),
  (4, '赵六', '财务部', 10000, 42, '2018-05-20', '广州'),
  (5, '孙七', '技术部', 22000, 27, '2022-11-01', '深圳'),
  (6, '周八', '市场部',  9000, 26, '2023-06-15', '上海'),
  (7, '吴九', '财务部', 13000, 38, '2017-09-01', '北京'),
  (8, '郑十', '技术部', 16000, 30, '2021-12-01', '广州')
AS t(emp_id, name, dept, salary, age, join_date, city);

-- 部门表
CREATE OR REPLACE TEMPORARY VIEW departments AS
VALUES
  ('技术部', '王五', 500000),
  ('市场部', '李四', 300000),
  ('财务部', '赵六', 200000),
  ('人事部', NULL,  150000)
AS t(dept_name, manager, budget);

-- 订单表
CREATE OR REPLACE TEMPORARY VIEW orders AS
VALUES
  (1001, 1, '笔记本电脑',  8000,  1, '2026-01-15'),
  (1002, 3, '显示器',      2500,  2, '2026-01-16'),
  (1003, 1, '键盘',         500,  3, '2026-02-01'),
  (1004, 5, '服务器',     50000,  1, '2026-02-15'),
  (1005, 3, '显示器',      2500,  1, '2026-03-01'),
  (1006, 2, '打印机',      1500,  2, '2026-03-10'),
  (1007, 5, '笔记本电脑',  8000,  2, '2026-03-20'),
  (1008, 8, '鼠标',         100, 10, '2026-04-01')
AS t(order_id, emp_id, product, amount, quantity, order_date);

-- 成绩表（含 NULL）
CREATE OR REPLACE TEMPORARY VIEW scores AS
VALUES
  ('张三', '数学', 90),
  ('张三', '语文', 85),
  ('李四', '数学', 72),
  ('李四', '语文', 88),
  ('王五', '数学', 95),
  ('王五', '语文', 92),
  ('赵六', '数学', 65),
  ('赵六', '语文', 78),
  ('孙七', '数学', NULL),
  ('孙七', '语文', 80)
AS t(student, subject, score);

-- 查看数据
SELECT * FROM employees;

-- 选指定列
SELECT name, dept, salary FROM employees;

-- 表达式 + 别名
SELECT name, salary * 12 AS annual_salary FROM employees;

-- 去重
SELECT DISTINCT dept FROM employees;

SELECT name, salary,
  CASE
    WHEN salary >= 18000 THEN '高'
    WHEN salary >= 13000 THEN '中'
    ELSE '低'
  END AS salary_level
FROM employees;

SELECT name,
  CAST(salary AS DOUBLE) / 10000 AS salary_wan,
  CAST(join_date AS DATE) AS join_dt,
  CAST(age AS STRING) AS age_str
FROM employees;

-- 比较运算
SELECT * FROM employees WHERE salary > 13000;

-- 多条件
SELECT * FROM employees WHERE dept = '技术部' AND salary >= 15000;

-- IN 集合
SELECT * FROM employees WHERE city IN ('北京', '上海');

-- LIKE 模糊
SELECT * FROM employees WHERE name LIKE '张%';

-- BETWEEN
SELECT * FROM employees WHERE salary BETWEEN 10000 AND 18000;

-- IS NULL（注意：NULL 不能用 = 判断）
SELECT * FROM scores WHERE score IS NULL;

-- 单列降序
SELECT name, salary FROM employees ORDER BY salary DESC;

-- 多列
SELECT dept, salary, name FROM employees ORDER BY dept, salary DESC;

-- NULL 排到最后
SELECT * FROM scores ORDER BY score DESC NULLS LAST;

-- 前 3 条
SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 3;

-- 指定偏移（通过子查询 + row_number，Spark 不直接支持 OFFSET）
SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 3;

SELECT dept,
  COUNT(*) AS 人数,
  ROUND(AVG(salary), 0) AS 平均薪资,
  SUM(salary) AS 总薪资,
  MAX(salary) AS 最高,
  MIN(salary) AS 最低
FROM employees
GROUP BY dept;

SELECT dept,
  COUNT(*) AS cnt,
  ROUND(AVG(salary)) AS avg_sal
FROM employees
GROUP BY dept
HAVING COUNT(*) >= 3       -- 注意：HAVING 中只能用聚合表达式，不能用别名
  AND AVG(salary) > 10000;

-- CUBE：所有维度的所有组合
SELECT dept, city,
  ROUND(AVG(salary)) AS avg_salary,
  COUNT(*) AS cnt
FROM employees
GROUP BY CUBE(dept, city)
ORDER BY dept NULLS FIRST, city NULLS FIRST;

-- ROLLUP：层级汇总（dept → city）
SELECT dept, city,
  ROUND(AVG(salary)) AS avg_salary,
  COUNT(*) AS cnt
FROM employees
GROUP BY ROLLUP(dept, city)
ORDER BY dept NULLS FIRST, city NULLS FIRST;

SELECT *
FROM scores
PIVOT (
  FIRST(score)          -- 如果每个(student, subject)只有一行，用 ANY_VALUE 也行
  FOR subject IN ('数学', '语文')
);

SELECT name, dept, salary,
  RANK()        OVER (PARTITION BY dept ORDER BY salary DESC) AS rk,
  DENSE_RANK()  OVER (PARTITION BY dept ORDER BY salary DESC) AS drk,
  ROW_NUMBER()  OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
FROM employees;

SELECT name, dept, salary,
  LAG(salary, 1)  OVER (PARTITION BY dept ORDER BY salary) AS prev_salary,
  LEAD(salary, 1) OVER (PARTITION BY dept ORDER BY salary) AS next_salary,
  salary - LAG(salary, 1) OVER (PARTITION BY dept ORDER BY salary) AS diff
FROM employees;

SELECT name, dept, salary,
  SUM(salary)   OVER (PARTITION BY dept) AS dept_total,
  ROUND(AVG(salary) OVER (PARTITION BY dept), 0) AS dept_avg,
  ROUND(salary / SUM(salary) OVER (PARTITION BY dept) * 100, 1) AS pct
FROM employees;

SELECT name, join_date, salary,
  SUM(salary) OVER (
    ORDER BY join_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cum_salary
FROM employees;

WITH ranked AS (
  SELECT name, dept, salary,
    ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT name, dept, salary FROM ranked WHERE rn <= 2;

-- INNER JOIN（默认）
SELECT e.name, e.dept, d.budget
FROM employees e
JOIN departments d ON e.dept = d.dept_name;

-- LEFT JOIN
SELECT e.name, e.dept, d.budget
FROM employees e
LEFT JOIN departments d ON e.dept = d.dept_name;

-- RIGHT JOIN
SELECT e.name, d.dept_name, d.budget
FROM employees e
RIGHT JOIN departments d ON e.dept = d.dept_name;

-- FULL OUTER JOIN
SELECT e.name, e.dept, d.budget
FROM employees e
FULL OUTER JOIN departments d ON e.dept = d.dept_name;

-- CROSS JOIN (笛卡尔积，谨慎使用)
SELECT e.name, o.product
FROM employees e
CROSS JOIN (SELECT DISTINCT product FROM orders) o;

-- 找出比自己部门平均薪资高的员工
SELECT e.name, e.dept, e.salary, t.dept_avg
FROM employees e
JOIN (
  SELECT dept, AVG(salary) AS dept_avg
  FROM employees
  GROUP BY dept
) t ON e.dept = t.dept
WHERE e.salary > t.dept_avg;

-- 小表 JOIN 大表时加 hint，避免 Shuffle
SELECT /*+ BROADCAST(d) */ e.name, e.dept, d.budget
FROM employees e
JOIN departments d ON e.dept = d.dept_name;

-- 或
SELECT /*+ MAPJOIN(d) */ e.name, e.dept, d.budget
FROM employees e
JOIN departments d ON e.dept = d.dept_name;

-- 并集（去重）
SELECT dept FROM employees
UNION
SELECT dept_name FROM departments;

-- 交集（两边都有）
SELECT dept FROM employees
INTERSECT
SELECT dept_name FROM departments;

-- 差集（左边有、右边没有）
SELECT dept_name FROM departments
EXCEPT
SELECT dept FROM employees;

SELECT name, city,
  CONCAT(name, '-', city) AS name_city,
  CONCAT_WS(' | ', name, dept, city) AS info,
  SUBSTR(name, 1, 1) AS surname,
  CHAR_LENGTH(name) AS name_len,
  INSTR(product, '脑') AS pos_nao,    -- 返回子串位置，0 = 没找到
  SPLIT(product, '电') AS splitted
FROM employees
CROSS JOIN (SELECT DISTINCT product FROM orders WHERE product = '笔记本电脑') t;

SELECT name,
  CAST(join_date AS DATE) AS join_dt,
  YEAR(join_date) AS join_year,
  MONTH(join_date) AS join_month,
  DATE_FORMAT(join_date, 'yyyy年MM月') AS join_ym,
  DATEDIFF(CURRENT_DATE, join_date) AS days_since_join,
  ROUND(MONTHS_BETWEEN(CURRENT_DATE, join_date), 1) AS months_since_join,
  ADD_MONTHS(join_date, 3) AS probation_end,
  LAST_DAY(join_date) AS month_end,
  DATE_ADD(join_date, 7) AS week_after,
  TRUNC(join_date, 'MM') AS month_begin    -- 当月第一天
FROM employees;

SELECT DATE_FORMAT(order_date, 'yyyy-MM') AS month,
  SUM(amount) AS total_amount,
  COUNT(*) AS order_count
FROM orders
GROUP BY month
ORDER BY month;

SELECT student, subject, score,
  COALESCE(score, 0) AS score_filled,          -- 第一个非 NULL 值
  IF(score IS NULL, '缺考', CAST(score AS STRING)) AS status
FROM scores;

-- 或
SELECT student, subject,
  CASE WHEN score IS NULL THEN '缺考'
       ELSE CAST(score AS STRING)
  END AS status
FROM scores;

SELECT name, salary,
  ROUND(salary / 12 / 22, 2) AS daily_salary,
  CEIL(salary / 10000) AS ceil_wan,
  FLOOR(salary / 5000) AS floor_5k,
  ABS(-salary) AS abs_val,
  RAND(42) AS random_val       -- 固定种子，可重放
FROM employees;

-- 创建数组
SELECT name, ARRAY(salary, age * 100) AS nums
FROM employees;

-- explode：数组展开为多行
SELECT name, t.num
FROM employees
LATERAL VIEW EXPLODE(ARRAY(salary, age * 100)) t AS num;

-- 创建 map
SELECT name, MAP('salary', salary, 'age', age) AS info
FROM employees;

-- 访问 map 的 key
SELECT name, MAP('salary', salary, 'age', age)['salary'] AS sal
FROM employees;

-- transform：数组元素转换
SELECT name,
  TRANSFORM(ARRAY(salary, age * 100), x -> x * 2) AS doubled
FROM employees;

-- filter：过滤数组
SELECT name,
  FILTER(ARRAY(salary, age * 100), x -> x > 10000) AS gt_10000
FROM employees;

-- exists：是否有满足条件的
SELECT name,
  EXISTS(ARRAY(salary, age * 100), x -> x > 20000) AS has_big
FROM employees;

-- 注册临时 UDF
CREATE OR REPLACE TEMPORARY FUNCTION salary_level AS '
  SELECT CASE
    WHEN salary >= 18000 THEN ''高级''
    WHEN salary >= 13000 THEN ''中级''
    ELSE ''初级''
  END
';

-- 或用 Python UDF（需要先注册到 SparkSession）
-- PySpark 侧：
-- spark.udf.register("py_salary_level", lambda s: "高级" if s >= 18000 else ...)

-- 使用
SELECT name, salary, salary_level(salary) AS level
FROM employees;

-- CTE（WITH 子句）：推荐，可读性更高
WITH dept_stats AS (
  SELECT dept,
    AVG(salary) AS avg_sal,
    COUNT(*) AS cnt
  FROM employees
  GROUP BY dept
)
SELECT e.name, e.dept, e.salary,
  ROUND(e.salary - d.avg_sal) AS diff_avg
FROM employees e
JOIN dept_stats d ON e.dept = d.dept
WHERE e.salary > d.avg_sal;

-- 等价子查询
SELECT e.name, e.dept, e.salary,
  ROUND(e.salary - t.avg_sal) AS diff_avg
FROM employees e
JOIN (
  SELECT dept, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY dept
) t ON e.dept = t.dept
WHERE e.salary > t.avg_sal;

-- 逻辑计划
EXPLAIN SELECT * FROM employees WHERE salary > 13000;

-- 带物理细节
EXPLAIN EXTENDED SELECT * FROM employees WHERE salary > 13000;

-- formatted 格式（Spark 3.x）
EXPLAIN FORMATTED SELECT * FROM employees WHERE salary > 13000;

-- 广播提示（小表 JOIN 大表）
SELECT /*+ BROADCAST(d) */ e.name, d.budget
FROM employees e JOIN departments d ON e.dept = d.dept_name;

-- 指定 Shuffle 分区数
SELECT /*+ SHUFFLE_HASH(e) */ *
FROM employees e JOIN orders o ON e.emp_id = o.emp_id;

-- 合并小文件
SELECT /*+ COALESCE(4) */ * FROM employees;

-- 重新分区
SELECT /*+ REPARTITION(8) */ * FROM employees;

-- 将表缓存到内存（后续查询加速）
CACHE TABLE employees;
UNCACHE TABLE employees;

-- 检查是否缓存
SHOW CACHED TABLES;

-- LAZY 缓存：只定义，使用时才加载
CACHE LAZY TABLE employees;