SQL 统计

在 SQL 中，统计每天的数据通常涉及到按日期分组并计算相关统计信息。以下是一些常见的 SQL 查询示例，展示如何按天统计数据。

示例数据

假设我们有一个 orders 表，包含以下字段：

• id：订单 ID
• order_date：订单日期
• amount：订单金额

1. 统计每天的订单数量

SELECT 
    DATE(order_date) AS order_day, 
    COUNT(*) AS order_count
FROM 
    orders
GROUP BY 
    DATE(order_date)
ORDER BY 
    order_day;

2. 统计每天的订单总金额

SELECT 
    DATE(order_date) AS order_day, 
    SUM(amount) AS total_amount
FROM 
    orders
GROUP BY 
    DATE(order_date)
ORDER BY 
    order_day;

3. 统计每天的平均订单金额

SELECT 
    DATE(order_date) AS order_day, 
    AVG(amount) AS average_amount
FROM 
    orders
GROUP BY 
    DATE(order_date)
ORDER BY 
    order_day;

4. 统计每天的最大和最小订单金额

SELECT 
    DATE(order_date) AS order_day, 
    MAX(amount) AS max_amount, 
    MIN(amount) AS min_amount
FROM 
    orders
GROUP BY 
    DATE(order_date)
ORDER BY 
    order_day;

5. 统计每天的订单数量和总金额

SELECT 
    DATE(order_date) AS order_day, 
    COUNT(*) AS order_count, 
    SUM(amount) AS total_amount
FROM 
    orders
GROUP BY 
    DATE(order_date)
ORDER BY 
    order_day;

6. 统计每天的订单数量和平均金额

SELECT 
    DATE(order_date) AS order_day, 
    COUNT(*) AS order_count, 
    AVG(amount) AS average_amount
FROM 
    orders
GROUP BY 
    DATE(order_date)
ORDER BY 
    order_day;

7. 统计每天的订单数量和总金额，并按月份分组

SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS order_month, 
    COUNT(*) AS order_count, 
    SUM(amount) AS total_amount
FROM 
    orders
GROUP BY 
    DATE_FORMAT(order_date, '%Y-%m')
ORDER BY 
    order_month;

8. 统计每天的订单数量和总金额，并按年份分组

SELECT 
    YEAR(order_date) AS order_year, 
    COUNT(*) AS order_count, 
    SUM(amount) AS total_amount
FROM 
    orders
GROUP BY 
    YEAR(order_date)
ORDER BY 
    order_year;

总结

通过以上示例，你可以看到如何使用 SQL 按天统计数据。你可以根据具体需求调整查询，例如按月份、年份或其他时间粒度进行分组。常用的函数包括 DATE()、DATE_FORMAT()、COUNT()、SUM()、AVG()、MAX() 和 MIN()。