时间维度聚合

按不同时间维度（年/季/月/周/日）进行数据聚合分析。

📊 支持情况

1. 时间维度提取

按年聚合

-- MySQL
SELECT YEAR(order_date) AS year, SUM(amount) AS total
FROM orders
GROUP BY YEAR(order_date)
ORDER BY year;

-- Oracle
SELECT EXTRACT(YEAR FROM order_date) AS year, SUM(amount) AS total
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date)
ORDER BY year;

按季度聚合

-- MySQL
SELECT
    YEAR(order_date) AS year,
    QUARTER(order_date) AS quarter,
    SUM(amount) AS total
FROM orders
GROUP BY YEAR(order_date), QUARTER(order_date)
ORDER BY year, quarter;

-- Oracle
SELECT
    EXTRACT(YEAR FROM order_date) AS year,
    TO_CHAR(order_date, 'Q') AS quarter,
    SUM(amount) AS total
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date), TO_CHAR(order_date, 'Q')
ORDER BY year, quarter;

-- Hive / MaxCompute
SELECT
    year(order_date) AS year,
    quarter(order_date) AS quarter,
    sum(amount) AS total
FROM orders
GROUP BY year(order_date), quarter(order_date);

按月聚合

-- MySQL
SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    SUM(amount) AS total
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;

-- Oracle
SELECT
    TO_CHAR(order_date, 'YYYY-MM') AS month,
    SUM(amount) AS total
FROM orders
GROUP BY TO_CHAR(order_date, 'YYYY-MM')
ORDER BY month;

-- Hive
SELECT
    date_format(order_date, 'yyyy-MM') AS month,
    sum(amount) AS total
FROM orders
GROUP BY date_format(order_date, 'yyyy-MM');

按周聚合

-- MySQL - 周起始于周一
SELECT
    DATE_FORMAT(DATE_SUB(order_date, INTERVAL WEEKDAY(order_date) DAY), '%Y-%m-%d') AS week_start,
    SUM(amount) AS total
FROM orders
GROUP BY week_start
ORDER BY week_start;

-- MySQL - 按ISO周
SELECT
    YEARWEEK(order_date, 1) AS iso_week,
    MIN(order_date) AS week_start,
    SUM(amount) AS total
FROM orders
GROUP BY YEARWEEK(order_date, 1)
ORDER BY iso_week;

-- Oracle - 按周
SELECT
    TO_CHAR(order_date, 'IYYY') AS iso_year,
    TO_CHAR(order_date, 'IW') AS iso_week,
    SUM(amount) AS total
FROM orders
GROUP BY TO_CHAR(order_date, 'IYYY'), TO_CHAR(order_date, 'IW');

按日聚合

-- MySQL
SELECT DATE(order_date) AS day, SUM(amount) AS daily_sales
FROM orders
GROUP BY DATE(order_date)
ORDER BY day;

-- Oracle
SELECT TRUNC(order_date) AS day, SUM(amount) AS daily_sales
FROM orders
GROUP BY TRUNC(order_date)
ORDER BY day;

按小时/分钟聚合

-- MySQL - 按小时
SELECT
    DATE_FORMAT(order_date, '%Y-%m-%d %H:00') AS hour_slot,
    COUNT(*) AS orders_count
FROM orders
GROUP BY hour_slot;

-- Oracle - 按分钟
SELECT
    TO_CHAR(order_date, 'YYYY-MM-DD HH24:MI') AS minute_slot,
    COUNT(*) AS orders_count
FROM orders
GROUP BY TO_CHAR(order_date, 'YYYY-MM-DD HH24:MI');

2. 多时间维度同时分析

同时按年/月/日统计

-- MySQL
SELECT
    YEAR(order_date) AS year,
    MONTH(order_date) AS month,
    DAY(order_date) AS day,
    SUM(amount) AS daily_sales
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY YEAR(order_date), MONTH(order_date), DAY(order_date)
ORDER BY year, month, day;

嵌套GROUP BY（MySQL）

-- ROLLUP语法
SELECT
    COALESCE(YEAR(order_date), 'Total') AS year,
    COALESCE(QUARTER(order_date), 'Year') AS quarter,
    SUM(amount) AS total
FROM orders
GROUP BY YEAR(order_date), QUARTER(order_date) WITH ROLLUP;

GROUPING SETS（通用）

-- MySQL / Oracle / Hive
SELECT
    YEAR(order_date) AS year,
    QUARTER(order_date) AS quarter,
    SUM(amount) AS total
FROM orders
GROUP BY GROUPING SETS (
    (YEAR(order_date), QUARTER(order_date)),  -- 年+季度
    (YEAR(order_date)),                        -- 年度总计
    ()                                         -- 总体总计
);

3. 实战案例

场景1：销售趋势报表

-- MySQL - 多维度销售统计
SELECT
    DATE_FORMAT(order_date, '%Y') AS year,
    DATE_FORMAT(order_date, '%m') AS month,
    SUM(amount) AS monthly_sales,
    COUNT(DISTINCT user_id) AS buyers,
    COUNT(*) AS orders
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
GROUP BY DATE_FORMAT(order_date, '%Y'), DATE_FORMAT(order_date, '%m')
ORDER BY year, month;

场景2：按周统计用户行为

-- Hive
SELECT
    year(event_time) AS year,
    weekofyear(event_time) AS week,
    event_type,
    COUNT(*) AS event_count,
    COUNT(DISTINCT user_id) AS unique_users
FROM user_events
GROUP BY year(event_time), weekofyear(event_time), event_type
ORDER BY year, week, event_type;

场景3：多时段对比

-- MySQL - 早/午/晚/夜
SELECT
    CASE
        WHEN HOUR(order_time) BETWEEN 6 AND 11 THEN '早餐'
        WHEN HOUR(order_time) BETWEEN 12 AND 14 THEN '午餐'
        WHEN HOUR(order_time) BETWEEN 18 AND 21 THEN '晚餐'
        ELSE '夜宵'
    END AS time_period,
    COUNT(*) AS order_count
FROM orders
GROUP BY time_period;

场景4：工作日/周末分析

-- MySQL
SELECT
    CASE WHEN WEEKDAY(order_date) < 5 THEN '工作日' ELSE '周末' END AS day_type,
    AVG(daily_sales) AS avg_sales
FROM (
    SELECT
        DATE(order_date) AS order_date,
        SUM(amount) AS daily_sales
    FROM orders
    GROUP BY DATE(order_date)
) daily
GROUP BY day_type;

4. 特殊时间维度

按半月聚合

-- MySQL
SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    CASE WHEN DAY(order_date) <= 15 THEN '上半月' ELSE '下半月' END AS half_month,
    SUM(amount) AS total
FROM orders
GROUP BY month, half_month;

按旬聚合

-- MySQL
SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    CASE
        WHEN DAY(order_date) <= 10 THEN '上旬'
        WHEN DAY(order_date) <= 20 THEN '中旬'
        ELSE '下旬'
    END AS decade,
    SUM(amount) AS total
FROM orders
GROUP BY month, decade;

5. 时间范围筛选

当天/本周/本月/本季/本年

-- MySQL
-- 今天
SELECT * FROM orders WHERE DATE(order_date) = CURDATE();

-- 本周
SELECT * FROM orders WHERE YEARWEEK(order_date, 1) = YEARWEEK(CURDATE(), 1);

-- 本月
SELECT * FROM orders WHERE DATE_FORMAT(order_date, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m');

-- 本季度
SELECT * FROM orders
WHERE QUARTER(order_date) = QUARTER(CURDATE())
  AND YEAR(order_date) = YEAR(CURDATE());

-- 本年
SELECT * FROM orders WHERE YEAR(order_date) = YEAR(CURDATE());

相对时间范围

-- MySQL
-- 最近7天
SELECT * FROM orders WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);

-- 最近3个月
SELECT * FROM orders WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH);

-- 去年
SELECT * FROM orders
WHERE order_date >= DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-01-01'), INTERVAL 1 YEAR)
  AND order_date < DATE_FORMAT(CURDATE(), '%Y-01-01');

⚠️ 注意事项

1. 时区对日期的影响

-- 存储的是UTC时间，查询时需要转换
-- Oracle
SELECT *
FROM orders
WHERE order_date AT TIME ZONE 'Asia/Shanghai' >= TRUNC(SYSTIMESTAMP);

2. 时间字段类型

-- 确保字段是DATE/DATETIME类型
-- 如果是字符串，需要转换
SELECT * FROM orders WHERE CAST(order_date AS DATE) = CURDATE();

3. 性能优化

-- 在日期字段上建立索引
CREATE INDEX idx_orders_date ON orders(order_date);

-- 按日期分区（Hive/MaxCompute）
-- 分区裁剪可以大幅提升性能
SELECT * FROM orders WHERE dt >= '2024-01-01' AND dt < '2024-01-15';

📝 练习题

-- 建表
CREATE TABLE sales (
    id INT,
    sale_time DATETIME,
    amount DECIMAL(10,2),
    region STRING
);

INSERT INTO sales VALUES
(1, '2024-01-14 08:15:00', 500, '华东'),
(2, '2024-01-14 12:30:00', 800, '华东'),
(3, '2024-01-14 18:45:00', 1200, '华东'),
(4, '2024-01-15 09:00:00', 600, '华北'),
(5, '2024-01-15 14:20:00', 950, '华北'),
(6, '2024-01-20 10:00:00', 1500, '华东'),
(7, '2023-12-15 11:00:00', 700, '华东');

要求：

1. 按日统计销售额
2. 按区域和时段（早/午/晚）统计
3. 计算本周与上周对比
4. 按月份统计本年数据

-- 参考答案（MySQL）
-- 1. 按日统计
SELECT DATE(sale_time) AS day, SUM(amount) AS daily_sales
FROM sales
GROUP BY DATE(sale_time)
ORDER BY day;

-- 2. 区域+时段
SELECT
    region,
    CASE
        WHEN HOUR(sale_time) < 12 THEN '上午'
        WHEN HOUR(sale_time) < 18 THEN '下午'
        ELSE '晚上'
    END AS time_period,
    SUM(amount) AS total
FROM sales
GROUP BY region, time_period;

-- 3. 周同比（简化）
SELECT
    WEEK(sale_time) AS week,
    SUM(amount) AS weekly_sales,
    LAG(SUM(amount)) OVER (ORDER BY WEEK(sale_time)) AS prev_week_sales
FROM sales
WHERE YEAR(sale_time) = 2024
GROUP BY WEEK(sale_time);

-- 4. 按月统计
SELECT DATE_FORMAT(sale_time, '%Y-%m') AS month, SUM(amount) AS monthly_sales
FROM sales
WHERE YEAR(sale_time) = 2024
GROUP BY month
ORDER BY month;