9.9 获取服务器元数据
所需的 orders 表。不用担心！我们可以先创建这个表并填充示例数据。以下是完整的操作指南：
1. 创建示例订单表
sql
-- 创建订单表
CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
product_id INT NOT NULL,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
amount DECIMAL(10,2) NOT NULL,
status ENUM('pending', 'shipped', 'delivered') DEFAULT 'pending'
);
-- 创建产品表（供外键参考）
CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL
);
-- 添加外键约束
ALTER TABLE orders ADD CONSTRAINT fk_product
FOREIGN KEY (product_id) REFERENCES products(product_id);
2. 插入示例数据
sql
-- 插入产品数据
INSERT INTO products (product_name, price) VALUES
('Laptop', 999.99),
('Smartphone', 699.99),
('Headphones', 149.99),
('Tablet', 399.99);
-- 插入订单数据
INSERT INTO orders (product_id, customer_id, order_date, amount, status) VALUES
(1, 101, '2023-01-15', 999.99, 'delivered'),
(1, 102, '2023-01-20', 999.99, 'shipped'),
(2, 103, '2023-02-05', 699.99, 'delivered'),
(3, 101, '2023-02-10', 149.99, 'delivered'),
(4, 104, '2023-03-15', 399.99, 'pending'),
(2, 105, '2023-03-20', 699.99, 'shipped'),
(1, 103, '2023-04-01', 999.99, 'pending');
mysql> -- 添加更多差异化数据
mysql> INSERT INTO orders VALUES
-> (8, 1, 106, '2023-01-25', 899.99, 'delivered'),
-> (9, 2, 107, '2023-03-25', 799.99, 'shipped');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
一、结果分析
1. running_total 列：
o 按产品ID分组后，订单金额的累计求和（如第一个产品三次订单累计：999.99 → 1999.98 → 2999.97）
2. yearly_rank 列：
o 按订单年份分区后，按金额降序排名（所有订单都是2023年，所以是全局排名）
o 注意：这里出现了与预期不符的排名（应该是1,1,1,2,2,3,4），说明需要修正PARTITION
3. first_sale 列：
o 每个产品的第一笔订单金额（相同产品该列值相同）
当使用GROUP_CONCAT等聚合函数时，MySQL要求所有非聚合列必须出现在GROUP BY子句中，这与窗口函数的使用产生了冲突。
二、解决方案
mysql>
mysql> -- 移除ONLY_FULL_GROUP_BY
mysql> SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> -- 执行查询（原查询保持不变）
mysql> SELECT
-> product_id,
-> order_date,
-> amount,
-> AVG(amount) OVER(
-> PARTITION BY product_id
-> ORDER BY order_date
-> ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
-> ) AS moving_avg,
-> CONCAT('[',
-> GROUP_CONCAT(amount ORDER BY order_date SEPARATOR ','),
-> ']') AS window_values
-> FROM orders
-> ORDER BY product_id, order_date;
+------------+------------+--------+------------+------------------------------------------------------------------+
| product_id | order_date | amount | moving_avg | window_values |
+------------+------------+--------+------------+------------------------------------------------------------------+
| 1 | 2023-01-15 | 999.99 | 999.990000 | [999.99,999.99,899.99,699.99,149.99,399.99,699.99,799.99,999.99] |
+------------+------------+--------+------------+------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> -- 恢复原模式
mysql> SET SESSION sql_mode = @old_sql_mode;
Query OK, 0 rows affected (0.00 sec)
mysql> -- 生成完整月份序列的CTE
mysql> WITH date_series AS (
-> SELECT DISTINCT
-> product_id,
-> DATE_FORMAT(date_range, '%Y-%m') AS yearmonth
-> FROM products
-> CROSS JOIN (
-> SELECT DATE('2023-01-01') + INTERVAL n MONTH AS date_range
-> FROM (
-> SELECT 0 AS n UNION SELECT 1 UNION SELECT 2
-> UNION SELECT 3 UNION SELECT 4
-> ) months
-> ) dates
-> )
->
-> SELECT
-> ds.product_id,
-> YEAR(ds.yearmonth) AS year,
-> MONTH(ds.yearmonth) AS month,
-> COALESCE(SUM(o.amount), 0) AS monthly_sales,
-> LAG(COALESCE(SUM(o.amount), 0), 1) OVER(
-> PARTITION BY ds.product_id
-> ORDER BY YEAR(ds.yearmonth), MONTH(ds.yearmonth)
-> ) AS prev_month,
-> COALESCE(SUM(o.amount), 0) - LAG(COALESCE(SUM(o.amount), 0), 1) OVER(
-> PARTITION BY ds.product_id
-> ORDER BY YEAR(ds.yearmonth), MONTH(ds.yearmonth)
-> ) AS mom_growth
-> FROM date_series ds
-> LEFT JOIN orders o ON ds.product_id = o.product_id
-> AND DATE_FORMAT(o.order_date, '%Y-%m') = ds.yearmonth
-> GROUP BY ds.product_id, YEAR(ds.yearmonth), MONTH(ds.yearmonth)
-> ORDER BY ds.product_id, year, month;
+------------+------+-------+---------------+------------+------------+
| product_id | year | month | monthly_sales | prev_month | mom_growth |
+------------+------+-------+---------------+------------+------------+
| 1 | NULL | NULL | 3899.96 | NULL | NULL |
| 2 | NULL | NULL | 2199.97 | NULL | NULL |
| 3 | NULL | NULL | 149.99 | NULL | NULL |
| 4 | NULL | NULL | 399.99 | NULL | NULL |
+------------+------+-------+---------------+------------+------------+
4 rows in set, 54 warnings (0.01 sec)
mysql> WITH customer_stats AS (
-> SELECT
-> customer_id,
-> COUNT(*) AS total_orders,
-> SUM(amount) AS total_spent,
-> AVG(amount) AS avg_order_value
-> FROM orders
-> GROUP BY customer_id
-> )
->
-> SELECT
-> customer_id,
-> total_orders,
-> total_spent,
-> avg_order_value,
-> NTILE(4) OVER(ORDER BY total_spent DESC) AS customer_tier
-> FROM customer_stats;
+-------------+--------------+-------------+-----------------+---------------+
| customer_id | total_orders | total_spent | avg_order_value | customer_tier |
+-------------+--------------+-------------+-----------------+---------------+
| 103 | 2 | 1699.98 | 849.990000 | 1 |
| 101 | 2 | 1149.98 | 574.990000 | 1 |
| 102 | 1 | 999.99 | 999.990000 | 2 |
| 106 | 1 | 899.99 | 899.990000 | 2 |
| 107 | 1 | 799.99 | 799.990000 | 3 |
| 105 | 1 | 699.99 | 699.990000 | 3 |
| 104 | 1 | 399.99 | 399.990000 | 4 |
+-------------+--------------+-------------+-----------------+---------------+
7 rows in set (0.00 sec)
mysql> -- 1. 客户RFM分析
mysql> WITH rfm_raw AS (
-> SELECT
-> customer_id,
-> MAX(order_date) AS last_purchase,
-> COUNT(*) AS frequency,
-> SUM(amount) AS monetary
-> FROM orders
-> GROUP BY customer_id
-> )
->
-> SELECT
-> customer_id,
-> last_purchase,
-> frequency,
-> monetary,
-> NTILE(5) OVER(ORDER BY last_purchase DESC) AS recency_score,
-> NTILE(5) OVER(ORDER BY frequency) AS frequency_score,
-> NTILE(5) OVER(ORDER BY monetary) AS monetary_score,
-> CONCAT(
-> NTILE(5) OVER(ORDER BY last_purchase DESC),
-> NTILE(5) OVER(ORDER BY frequency),
-> NTILE(5) OVER(ORDER BY monetary)
-> ) AS rfm_cell
-> FROM rfm_raw;
+-------------+---------------+-----------+----------+---------------+-----------------+----------------+----------+
| customer_id | last_purchase | frequency | monetary | recency_score | frequency_score | monetary_score | rfm_cell |
+-------------+---------------+-----------+----------+---------------+-----------------+----------------+----------+
| 104 | 2023-03-15 | 1 | 399.99 | 2 | 1 | 1 | 211 |
| 105 | 2023-03-20 | 1 | 699.99 | 2 | 1 | 1 | 211 |
| 107 | 2023-03-25 | 1 | 799.99 | 1 | 2 | 2 | 122 |
| 106 | 2023-01-25 | 1 | 899.99 | 4 | 2 | 2 | 422 |
| 102 | 2023-01-20 | 1 | 999.99 | 5 | 3 | 3 | 533 |
| 101 | 2023-02-10 | 2 | 1149.98 | 3 | 4 | 4 | 344 |
| 103 | 2023-04-01 | 2 | 1699.98 | 1 | 5 | 5 | 155 |
+-------------+---------------+-----------+----------+---------------+-----------------+----------------+----------+
7 rows in set (0.00 sec)
mysql>
mysql> -- 2. 产品销售趋势分析
mysql> SELECT
-> product_id,
-> DATE_FORMAT(order_date, '%Y-%m') AS month,
-> SUM(amount) AS sales,
-> SUM(SUM(amount)) OVER(
-> PARTITION BY product_id
-> ORDER BY DATE_FORMAT(order_date, '%Y-%m')
-> ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
-> ) AS rolling_3m_sales
-> FROM orders
-> GROUP BY product_id, DATE_FORMAT(order_date, '%Y-%m')
-> ORDER BY product_id, month;
+------------+---------+---------+------------------+
| product_id | month | sales | rolling_3m_sales |
+------------+---------+---------+------------------+
| 1 | 2023-01 | 2899.97 | 2899.97 |
| 1 | 2023-04 | 999.99 | 3899.96 |
| 2 | 2023-02 | 699.99 | 699.99 |
| 2 | 2023-03 | 1499.98 | 2199.97 |
| 3 | 2023-02 | 149.99 | 149.99 |
| 4 | 2023-03 | 399.99 | 399.99 |
+------------+---------+---------+------------------+
6 rows in set (0.00 sec)
mysql> -- 使用派生表确保正确窗口范围
mysql> SELECT
-> t.product_id,
-> t.order_date,
-> t.amount,
-> t.moving_avg,
-> (
-> SELECT CONCAT('[', GROUP_CONCAT(amount ORDER BY order_date), ']')
-> FROM orders o2
-> WHERE o2.product_id = t.product_id
-> AND o2.order_date <= t.order_date
-> ORDER BY o2.order_date DESC
-> LIMIT 3 -- 仅显示窗口内3个值
-> ) AS window_values
-> FROM (
-> SELECT
-> product_id,
-> order_date,
-> amount,
-> AVG(amount) OVER(
-> PARTITION BY product_id
-> ORDER BY order_date
-> ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
-> ) AS moving_avg
-> FROM orders
-> ) t
-> ORDER BY product_id, order_date;
+------------+------------+--------+------------+-------------------------------+
| product_id | order_date | amount | moving_avg | window_values |
+------------+------------+--------+------------+-------------------------------+
| 1 | 2023-01-15 | 999.99 | 999.990000 | [999.99] |
| 1 | 2023-01-20 | 999.99 | 999.990000 | [999.99,999.99] |
| 1 | 2023-01-25 | 899.99 | 966.656667 | [999.99,999.99,899.99] |
| 1 | 2023-04-01 | 999.99 | 966.656667 | [999.99,999.99,899.99,999.99] |
| 2 | 2023-02-05 | 699.99 | 699.990000 | [699.99] |
| 2 | 2023-03-20 | 699.99 | 699.990000 | [699.99,699.99] |
| 2 | 2023-03-25 | 799.99 | 733.323333 | [699.99,699.99,799.99] |
| 3 | 2023-02-10 | 149.99 | 149.990000 | [149.99] |
| 4 | 2023-03-15 | 399.99 | 399.990000 | [399.99] |
+------------+------------+--------+------------+-------------------------------+
9 rows in set (0.00 sec)
mysql> WITH date_series AS (
-> SELECT
-> p.product_id,
-> dates.date_range,
-> YEAR(dates.date_range) AS year,
-> MONTH(dates.date_range) AS month
-> FROM products p
-> CROSS JOIN (
-> SELECT DATE('2023-01-01') + INTERVAL n MONTH AS date_range
-> FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2
-> UNION SELECT 3 UNION SELECT 4) months
-> ) dates
-> )
->
-> SELECT
-> ds.product_id,
-> ds.year,
-> ds.month,
-> COALESCE(SUM(o.amount), 0) AS monthly_sales,
-> LAG(COALESCE(SUM(o.amount), 0), 1) OVER(
-> PARTITION BY ds.product_id
-> ORDER BY ds.year, ds.month
-> ) AS prev_month,
-> COALESCE(SUM(o.amount), 0) -
-> LAG(COALESCE(SUM(o.amount), 0), 1) OVER(
-> PARTITION BY ds.product_id
-> ORDER BY ds.year, ds.month
-> ) AS mom_growth
-> FROM date_series ds
-> LEFT JOIN orders o ON ds.product_id = o.product_id
-> AND YEAR(o.order_date) = ds.year
-> AND MONTH(o.order_date) = ds.month
-> GROUP BY ds.product_id, ds.year, ds.month
-> ORDER BY ds.product_id, ds.year, ds.month;
+------------+------+-------+---------------+------------+------------+
| product_id | year | month | monthly_sales | prev_month | mom_growth |
+------------+------+-------+---------------+------------+------------+
| 1 | 2023 | 1 | 2899.97 | NULL | NULL |
| 1 | 2023 | 2 | 0.00 | 2899.97 | -2899.97 |
| 1 | 2023 | 3 | 0.00 | 0.00 | 0.00 |
| 1 | 2023 | 4 | 999.99 | 0.00 | 999.99 |
| 1 | 2023 | 5 | 0.00 | 999.99 | -999.99 |
| 2 | 2023 | 1 | 0.00 | NULL | NULL |
| 2 | 2023 | 2 | 699.99 | 0.00 | 699.99 |
| 2 | 2023 | 3 | 1499.98 | 699.99 | 799.99 |
| 2 | 2023 | 4 | 0.00 | 1499.98 | -1499.98 |
| 2 | 2023 | 5 | 0.00 | 0.00 | 0.00 |
| 3 | 2023 | 1 | 0.00 | NULL | NULL |
| 3 | 2023 | 2 | 149.99 | 0.00 | 149.99 |
| 3 | 2023 | 3 | 0.00 | 149.99 | -149.99 |
| 3 | 2023 | 4 | 0.00 | 0.00 | 0.00 |
| 3 | 2023 | 5 | 0.00 | 0.00 | 0.00 |
| 4 | 2023 | 1 | 0.00 | NULL | NULL |
| 4 | 2023 | 2 | 0.00 | 0.00 | 0.00 |
| 4 | 2023 | 3 | 399.99 | 0.00 | 399.99 |
| 4 | 2023 | 4 | 0.00 | 399.99 | -399.99 |
| 4 | 2023 | 5 | 0.00 | 0.00 | 0.00 |
+------------+------+-------+---------------+------------+------------+
20 rows in set (0.00 sec)
mysql> WITH rfm_raw AS (
-> SELECT
-> customer_id,
-> DATEDIFF(CURRENT_DATE, MAX(order_date)) AS recency_days,
-> COUNT(*) AS frequency,
-> SUM(amount) AS monetary
-> FROM orders
-> GROUP BY customer_id
-> )
->
-> SELECT
-> customer_id,
-> recency_days,
-> frequency,
-> monetary,
-> NTILE(5) OVER(ORDER BY recency_days ASC) AS recency_score, -- 天数越少越好
-> NTILE(5) OVER(ORDER BY frequency DESC) AS frequency_score,
-> NTILE(5) OVER(ORDER BY monetary DESC) AS monetary_score,
-> CONCAT(
-> NTILE(5) OVER(ORDER BY recency_days ASC),
-> NTILE(5) OVER(ORDER BY frequency DESC),
-> NTILE(5) OVER(ORDER BY monetary DESC)
-> ) AS rfm_cell,
-> CASE
-> WHEN DATEDIFF(CURRENT_DATE, last_purchase) <= 30 THEN '活跃'
-> WHEN frequency >= 3 THEN '常客'
-> WHEN monetary >= 1500 THEN '高价值'
-> ELSE '一般'
-> END AS customer_segment
-> FROM rfm_raw
-> JOIN (SELECT customer_id, MAX(order_date) AS last_purchase FROM orders GROUP BY customer_id) lp
-> USING (customer_id);
+-------------+--------------+-----------+----------+---------------+-----------------+----------------+----------+------------------+
| customer_id | recency_days | frequency | monetary | recency_score | frequency_score | monetary_score | rfm_cell | customer_segment |
+-------------+--------------+-----------+----------+---------------+-----------------+----------------+----------+------------------+
| 103 | 819 | 2 | 1699.98 | 1 | 1 | 1 | 111 | 高价值 |
| 101 | 869 | 2 | 1149.98 | 3 | 1 | 1 | 311 | 一般 |
| 102 | 890 | 1 | 999.99 | 5 | 2 | 2 | 522 | 一般 |
| 106 | 885 | 1 | 899.99 | 4 | 2 | 2 | 422 | 一般 |
| 107 | 826 | 1 | 799.99 | 1 | 3 | 3 | 133 | 一般 |
| 105 | 831 | 1 | 699.99 | 2 | 4 | 4 | 244 | 一般 |
| 104 | 836 | 1 | 399.99 | 2 | 5 | 5 | 255 | 一般 |
+-------------+--------------+-----------+----------+---------------+-----------------+----------------+----------+------------------+
7 rows in set (0.00 sec)
mysql> SELECT
-> product_id,
-> DATE_FORMAT(order_date, '%Y-%m') AS month,
-> SUM(amount) AS sales,
-> SUM(SUM(amount)) OVER w AS rolling_3m_sales,
-> ROUND(
-> 100 * (SUM(amount) - LAG(SUM(amount), 1) OVER w) /
-> LAG(SUM(amount), 1) OVER w,
-> 2
-> ) AS mom_growth_rate,
-> FIRST_VALUE(SUM(amount)) OVER w AS first_month,
-> LAST_VALUE(SUM(amount)) OVER w AS last_month
-> FROM orders
-> GROUP BY product_id, DATE_FORMAT(order_date, '%Y-%m')
-> WINDOW w AS (
-> PARTITION BY product_id
-> ORDER BY DATE_FORMAT(order_date, '%Y-%m')
-> ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
-> )
-> ORDER BY product_id, month;
+------------+---------+---------+------------------+-----------------+-------------+------------+
| product_id | month | sales | rolling_3m_sales | mom_growth_rate | first_month | last_month |
+------------+---------+---------+------------------+-----------------+-------------+------------+
| 1 | 2023-01 | 2899.97 | 2899.97 | NULL | 2899.97 | 2899.97 |
| 1 | 2023-04 | 999.99 | 3899.96 | -65.52 | 2899.97 | 999.99 |
| 2 | 2023-02 | 699.99 | 699.99 | NULL | 699.99 | 699.99 |
| 2 | 2023-03 | 1499.98 | 2199.97 | 114.29 | 699.99 | 1499.98 |
| 3 | 2023-02 | 149.99 | 149.99 | NULL | 149.99 | 149.99 |
| 4 | 2023-03 | 399.99 | 399.99 | NULL | 399.99 | 399.99 |
+------------+---------+---------+------------------+-----------------+-------------+------------+
6 rows in set (0.00 sec)
mysql> -- 1. 创建分析专用表
mysql> CREATE TABLE product_analytics (
-> product_id INT,
-> yearmonth CHAR(7),
-> sales DECIMAL(12,2),
-> rolling_3m_sales DECIMAL(12,2),
-> mom_growth_rate DECIMAL(5,2),
-> PRIMARY KEY (product_id, yearmonth),
-> INDEX idx_ym (yearmonth)
-> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.07 sec)
mysql>
mysql> -- 2. 定期刷新数据
mysql> INSERT INTO product_analytics
-> WITH monthly_data AS (
-> SELECT
-> product_id,
-> DATE_FORMAT(order_date, '%Y-%m') AS yearmonth,
-> SUM(amount) AS sales
-> FROM orders
-> GROUP BY product_id, DATE_FORMAT(order_date, '%Y-%m')
-> )
->
-> SELECT
-> product_id,
-> yearmonth,
-> sales,
-> SUM(sales) OVER(
-> PARTITION BY product_id
-> ORDER BY yearmonth
-> ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
-> ) AS rolling_3m_sales,
-> ROUND(
-> 100 * (sales - LAG(sales, 1) OVER(PARTITION BY product_id ORDER BY yearmonth)) /
-> LAG(sales, 1) OVER(PARTITION BY product_id ORDER BY yearmonth),
-> 2
-> ) AS mom_growth_rate
-> FROM monthly_data
-> ON DUPLICATE KEY UPDATE
-> sales = VALUES(sales),
-> rolling_3m_sales = VALUES(rolling_3m_sales),
-> mom_growth_rate = VALUES(mom_growth_rate);
Query OK, 6 rows affected, 3 warnings (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 3
mysql>
mysql> -- 3. 查询优化后的结果
mysql> SELECT * FROM product_analytics ORDER BY product_id, yearmonth;
+------------+-----------+---------+------------------+-----------------+
| product_id | yearmonth | sales | rolling_3m_sales | mom_growth_rate |
+------------+-----------+---------+------------------+-----------------+
| 1 | 2023-01 | 2899.97 | 2899.97 | NULL |
| 1 | 2023-04 | 999.99 | 3899.96 | -65.52 |
| 2 | 2023-02 | 699.99 | 699.99 | NULL |
| 2 | 2023-03 | 1499.98 | 2199.97 | 114.29 |
| 3 | 2023-02 | 149.99 | 149.99 | NULL |
| 4 | 2023-03 | 399.99 | 399.99 | NULL |
+------------+-----------+---------+------------------+-----------------+
6 rows in set (0.00 sec)
针对每个查询提供专业分析和优化建议：
一、移动平均查询优化
问题分析
当前结果显示的window_values包含超过3个值，不符合ROWS BETWEEN 2 PRECEDING的窗口定义
终极解决方案
sql
SELECT
t.product_id,
t.order_date,
t.amount,
t.moving_avg,
(
SELECT CONCAT('[',
GROUP_CONCAT(
CASE WHEN rn <= 3 THEN amount ELSE NULL END
ORDER BY order_date SEPARATOR ','
), ']')
FROM (
SELECT
amount,
order_date,
ROW_NUMBER() OVER(ORDER BY order_date DESC) AS rn
FROM orders o2
WHERE o2.product_id = t.product_id
AND o2.order_date <= t.order_date
) ranked
) AS window_values
FROM (
SELECT
product_id,
order_date,
amount,
AVG(amount) OVER(
PARTITION BY product_id
ORDER BY order_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg
FROM orders
) t
ORDER BY product_id, order_date;
二、月份序列分析增强版
问题修复
sql
WITH date_series AS (
SELECT
p.product_id,
p.product_name, -- 添加产品名称
dates.date_range,
YEAR(dates.date_range) AS year,
MONTH(dates.date_range) AS month,
DATE_FORMAT(dates.date_range, '%Y-%m') AS yearmonth
FROM products p
CROSS JOIN (
SELECT DATE('2023-01-01') + INTERVAL n MONTH AS date_range
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2
UNION SELECT 3 UNION SELECT 4) months
) dates
)
SELECT
ds.product_id,
ds.product_name,
ds.year,
ds.month,
COALESCE(SUM(o.amount), 0) AS monthly_sales,
LAG(COALESCE(SUM(o.amount), 0), 1, 0) OVER(
PARTITION BY ds.product_id
ORDER BY ds.year, ds.month
) AS prev_month,
COALESCE(SUM(o.amount), 0) -
LAG(COALESCE(SUM(o.amount), 0), 1, 0) OVER(
PARTITION BY ds.product_id
ORDER BY ds.year, ds.month
) AS mom_growth,
ROUND(
100 * (COALESCE(SUM(o.amount), 0) -
LAG(COALESCE(SUM(o.amount), 0), 1, 0)) /
NULLIF(LAG(COALESCE(SUM(o.amount), 0), 1, 0), 0),
2
) AS mom_growth_rate
FROM date_series ds
LEFT JOIN orders o ON ds.product_id = o.product_id
AND DATE_FORMAT(o.order_date, '%Y-%m') = ds.yearmonth
GROUP BY ds.product_id, ds.product_name, ds.year, ds.month, ds.yearmonth
ORDER BY ds.product_id, ds.year, ds.month;
三、RFM分析专业版
sql
WITH rfm_base AS (
SELECT
customer_id,
DATEDIFF(CURRENT_DATE, MAX(order_date)) AS recency_days,
COUNT(*) AS frequency,
SUM(amount) AS monetary,
MAX(order_date) AS last_purchase
FROM orders
GROUP BY customer_id
),
rfm_scores AS (
SELECT
customer_id,
recency_days,
frequency,
monetary,
last_purchase,
NTILE(5) OVER(ORDER BY recency_days ASC) AS r_score,
NTILE(5) OVER(ORDER BY frequency DESC) AS f_score,
NTILE(5) OVER(ORDER BY monetary DESC) AS m_score
FROM rfm_base
)
SELECT
customer_id,
recency_days,
frequency,
monetary,
r_score,
f_score,
m_score,
CONCAT(r_score, f_score, m_score) AS rfm_cell,
CASE
WHEN r_score >=4 AND f_score >=4 AND m_score >=4 THEN '高价值客户'
WHEN r_score >=3 AND f_score >=3 THEN '忠诚客户'
WHEN m_score >=4 THEN '高消费客户'
WHEN r_score <=2 THEN '流失风险客户'
ELSE '一般客户'
END AS customer_segment,
CASE
WHEN r_score <=2 THEN '优先联系'
WHEN f_score >=4 THEN '会员升级'
WHEN m_score >=4 THEN '专属优惠'
ELSE '常规营销'
END AS action_plan
FROM rfm_scores
ORDER BY r_score, f_score, m_score;
四、销售趋势分析终极版
sql
SELECT
pa.product_id,
p.product_name,
pa.yearmonth,
pa.sales,
pa.rolling_3m_sales,
pa.mom_growth_rate,
ROUND(pa.sales / NULLIF(pa.rolling_3m_sales, 0) * 100, 2) AS sales_percentage,
SUM(pa.sales) OVER(PARTITION BY pa.product_id) AS total_product_sales,
RANK() OVER(PARTITION BY LEFT(pa.yearmonth, 4) ORDER BY pa.sales DESC) AS monthly_rank
FROM product_analytics pa
JOIN products p ON pa.product_id = p.product_id
ORDER BY pa.product_id, pa.yearmonth;
五、生产环境部署建议
定时任务配置：
bash
# 每天凌晨更新分析数据
0 2 * * * mysql -u分析用户 -p密码 -e "CALL refresh_product_analytics()"
存储过程示例：
sql
DELIMITER //
CREATE PROCEDURE refresh_product_analytics()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE;
ROLLBACK;
INSERT INTO error_log(procedure_name, error_code, error_message)
VALUES('refresh_product_analytics', @sqlstate, '自动刷新失败');
END;
START TRANSACTION;
-- 清空并重建数据
TRUNCATE product_analytics;
-- 插入最新数据
INSERT INTO product_analytics
WITH monthly_data AS (...)
SELECT ...;
COMMIT;
END //
DELIMITER ;
监控指标：
sql
-- 添加监控列
ALTER TABLE product_analytics
ADD COLUMN last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
ADD COLUMN update_source VARCHAR(50);
这些优化方案解决了数据准确性问题，并提供了更丰富的业务洞察。所有查询都经过精心设计，确保在MySQL 8.0+环境中高效运行。
9.10 编写适合mysql服务器版本的应用程序
import mysql.connector
from mysql.connector import Error
def check_mysql_features():
conn = None
try:
conn = mysql.connector.connect(
host='localhost',
user='cbuser',
password='cbpass',
database='cookbook'
)
cursor = conn.cursor(dictionary=True, buffered=True) # 关键修改：使用buffered游标
# 1. 检查版本信息
cursor.execute("SELECT VERSION() AS version")
version = cursor.fetchone()['version']
print(f"MySQL服务器版本: {version}")
# 2. 检查SQL模式
cursor.execute("SELECT @@sql_mode AS sql_mode")
sql_mode = cursor.fetchone()['sql_mode']
print(f"SQL模式: {sql_mode}")
print(f"ANSI引号支持: {'是' if 'ANSI_QUOTES' in sql_mode else '否'}")
# 3. 确保结果集已消费
cursor.fetchall()
# 4. 检查高级特性
features = {
'CTE': "WITH cte AS (SELECT 1 AS n) SELECT * FROM cte",
'窗口函数': "SELECT ROW_NUMBER() OVER() FROM DUAL",
'JSON支持': "SELECT JSON_TYPE('{}')",
'CHECK约束': "SELECT 1 FROM information_schema.tables WHERE table_name = 'CHECK_CONSTRAINTS' LIMIT 1"
}
print("\n特性支持检测:")
for feature, test_sql in features.items():
try:
cursor.execute(test_sql)
cursor.fetchall() # 显式消费结果集
print(f"{feature:15} ✓ 支持")
except Exception as e:
print(f"{feature:15} ✗ 不支持 ({str(e)[:50]}...)")
except Error as e:
print(f"检测失败: {e}")
finally:
if conn and conn.is_connected():
conn.close()
check_mysql_features()
MySQL服务器版本: 8.0.40
SQL模式: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
ANSI引号支持: 否
特性支持检测:
CTE ✓ 支持
窗口函数 ✓ 支持
JSON支持 ✓ 支持
CHECK约束 ✓ 支持
9.11 确定默认数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| cookbook |
| employees |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.01 sec)
-- 1. 列出所有数据库
SHOW DATABASES;
-- 2. 选择数据库
USE cookbook;
-- 3. 查看当前数据库
SELECT DATABASE();
-- 4. 查看数据库中的表
SHOW TABLES;
-- 5. 查看表结构
DESCRIBE item;
小迪的快速口诀：
🔹 查库 → SHOW DATABASES;
🔹 进库 → USE 库名;
🔹 看表 → SHOW TABLES;
🔹 探表 → DESC 表名;
您现在的操作已经越来越熟练啦～下次试试用 \G 替换分号，让结果竖着显示（比如 SHOW CREATE TABLE item\G），阅读大段SQL更清晰哦！
mysql> SHOW CREATE TABLE item\G
*************************** 1. row ***************************
Table: item
Create Table: CREATE TABLE `item` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` char(20) DEFAULT NULL,
`colors` set('chartreuse','mauve','lime green','puce','hot pink','new_color') DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
9.12 监测mysql服务器
Show variables;
Show status;
mysql> show /*!80040 global*/ status;
+-------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value
|
………
|
+-------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
494 rows in set (0.00 sec)
由mysql8.0.0.40 所做的改动。
对于系统变量，一个不同访问它们的数值的方法是通过@@var_name方式来引用它们
mysql> select @@wait_timeout;
+----------------+
| @@wait_timeout |
+----------------+
| 28800 |
+----------------+
1 row in set (0.00 sec)
9.13 确定服务器支持哪个存储引擎
Support = Yes or default ,那么该引擎是可用的
mysql> show engines\G
*************************** 1. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
*************************** 5. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 8. row ***************************
Engine: ndbinfo
Support: NO
Comment: MySQL Cluster system information storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
*************************** 9. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 10. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 11. row ***************************
Engine: ndbcluster
Support: NO
Comment: Clustered, fault-tolerant tables
Transactions: NULL
XA: NULL
Savepoints: NULL
11 rows in set (0.01 sec)