SELECT 
  COUNT(*),        -- 所有行數(shù)（包含NULL）
  COUNT(bonus),    -- 非NULL行數(shù)
  AVG(COALESCE(bonus, 0)) -- NULL轉(zhuǎn)為0計算
FROM employees;

-- 錯誤：非分組字段出現(xiàn)在SELECT
SELECT department, name, AVG(salary) 
FROM employees; -- 報錯或未定義行為
-- 正確：所有非聚合字段必須出現(xiàn)在GROUP BY
SELECT department, name, AVG(salary)
FROM employees
GROUP BY department, name;

-- WHERE：分組前過濾行
SELECT department, AVG(salary)
FROM employees
WHERE hire_date > '2020-01-01' -- 先過濾
GROUP BY department;
-- HAVING：分組后過濾組
SELECT department, AVG(salary) avg_sal
FROM employees
GROUP BY department
HAVING avg_sal > 5000; -- 后過濾

-- 低效：全表掃描
SELECT department, AVG(salary)
FROM employees
GROUP BY department;
-- 高效：添加索引
ALTER TABLE employees ADD INDEX idx_dept (department);

-- 結(jié)果順序不保證
SELECT department, COUNT(*)
FROM employees
GROUP BY department;
-- 顯式排序
SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department
ORDER BY emp_count DESC;

-- 允許：單層分組函數(shù)
SELECT AVG(MAX(salary)) -- ? 錯誤嵌套
-- 正確：使用子查詢
SELECT AVG(max_sal)
FROM (
  SELECT department, MAX(salary) AS max_sal
  FROM employees
  GROUP BY department
) dept_max;

-- 統(tǒng)計不重復值
SELECT 
  COUNT(DISTINCT department), -- 不同部門數(shù)量
  COUNT(DISTINCT CASE WHEN salary > 5000 THEN 1 END) -- 高薪人數(shù)
FROM employees;

-- 使用 COALESCE 處理空分組
SELECT 
  COALESCE(department, '未分配') AS dept,
  COUNT(*) 
FROM employees
GROUP BY department;

-- 分組順序影響結(jié)果
SELECT 
  YEAR(hire_date) AS hire_year,
  department,
  COUNT(*)
FROM employees
GROUP BY hire_year, department; -- 先按年再按部門

-- 默認截斷長度1024字符
SET SESSION group_concat_max_len = 10000;
SELECT 
  department,
  GROUP_CONCAT(name ORDER BY salary DESC SEPARATOR '|') 
FROM employees
GROUP BY department;

SELECT 
  YEAR(order_date) AS order_year,
  QUARTER(order_date) AS quarter,
  product_category,
  SUM(amount) AS total_sales,
  COUNT(DISTINCT customer_id) AS customers
FROM orders
GROUP BY order_year, quarter, product_category
WITH ROLLUP; -- 添加小計和總計行

SELECT 
  department,
  COUNT(*) AS emp_count,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS pct
FROM employees
GROUP BY department;

SELECT 
  department,
  name,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

SELECT 
  department,
  AVG(salary) AS avg_salary,
  AVG(salary) - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees
GROUP BY department;

SELECT 
  DATE_FORMAT(order_date, '%Y-%m') AS month,
  SUM(amount) AS monthly_sales,
  LAG(SUM(amount)) OVER (ORDER BY DATE_FORMAT(order_date, '%Y-%m')) AS prev_month
FROM orders
GROUP BY month;

-- 復合索引優(yōu)化分組
ALTER TABLE orders 
  ADD INDEX idx_category_date (product_category, order_date);
-- 覆蓋索引
EXPLAIN SELECT product_category, COUNT(*) 
FROM orders 
GROUP BY product_category; -- 使用索引

-- 增大臨時表內(nèi)存
SET tmp_table_size = 256*1024*1024; -- 256MB
SET max_heap_table_size = 256*1024*1024;
-- 監(jiān)控臨時表使用
SHOW STATUS LIKE 'Created_tmp%';

-- 按日期分區(qū)
CREATE TABLE sales (
    sale_id INT,
    sale_date DATE,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023)
);
-- 分區(qū)分組查詢
SELECT YEAR(sale_date), SUM(amount)
FROM sales
GROUP BY YEAR(sale_date); -- 僅掃描相關(guān)分區(qū)

-- 創(chuàng)建分組結(jié)果緩存
CREATE TABLE sales_summary AS
SELECT 
  product_id, 
  YEAR(order_date) AS year, 
  SUM(amount) AS total
FROM orders
GROUP BY product_id, year;
-- 定期刷新
REPLACE INTO sales_summary
SELECT product_id, YEAR(order_date), SUM(amount)
FROM orders
WHERE order_date > (SELECT MAX(order_date) FROM sales_summary)
GROUP BY product_id, YEAR(order_date);

-- 明確分組粒度
SELECT 
  DATE(order_date) AS day,  -- 按天
  HOUR(order_time) AS hour, -- 按小時
  COUNT(*) 
FROM orders
GROUP BY day, hour;

-- 分頁處理大結(jié)果集
SELECT department, AVG(salary)
FROM employees
GROUP BY department
LIMIT 10 OFFSET 20; -- 第三頁

-- 驗證分組總數(shù)
SELECT COUNT(DISTINCT department) 
FROM employees; -- 應與分組行數(shù)一致
-- 交叉驗證
SELECT 
  (SELECT COUNT(*) FROM employees) AS total,
  SUM(emp_count) AS group_total
FROM (
  SELECT department, COUNT(*) AS emp_count
  FROM employees
  GROUP BY department
) dept_groups;

-- 檢查分組性能
EXPLAIN 
SELECT department, AVG(salary)
FROM employees
GROUP BY department;
-- 關(guān)注以下指標：
-- 1. Using temporary (是否使用臨時表)
-- 2. Using filesort (是否文件排序)
-- 3. key (使用的索引)

-- 陷阱1：錯誤處理NULL
SELECT department, AVG(bonus) -- 忽略NULL
FROM employees;
-- 陷阱2：混淆WHERE和HAVING
SELECT department, AVG(salary)
FROM employees
WHERE AVG(salary) > 5000; -- 錯誤！WHERE不能使用聚合函數(shù)
-- 陷阱3：未排序的分頁
SELECT department, COUNT(*)
FROM employees
GROUP BY department
LIMIT 10; -- 結(jié)果隨機

SELECT 
  c.country,
  p.category,
  YEAR(o.order_date) AS order_year,
  COUNT(DISTINCT o.customer_id) AS customers,
  COUNT(*) AS orders,
  SUM(o.amount) AS revenue,
  AVG(o.amount) AS avg_order_value,
  GROUP_CONCAT(DISTINCT p.product_name ORDER BY p.product_name SEPARATOR ', ') AS products
FROM orders o
JOIN products p ON o.product_id = p.id
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY c.country, p.category, order_year WITH ROLLUP
HAVING revenue > 10000
ORDER BY country, category, order_year DESC;