当前位置：首页 > article >正文

【MySQL】获取最近7天和最近14天的订单数量，使用MySQL详细写出，使用不同的方法

article 2024/11/15 6:53:27

1. 获取最近7天和最近14天的订单数量，使用MySQL详细写出，使用不同的方法

要获取最近7天和最近14天的订单数量，我们可以使用不同的方法来优化查询性能。以下是两种方法：

1.1 方法一：使用日期计算

SELECT
  SUM(CASE WHEN create_time BETWEEN CURDATE() - INTERVAL 6 DAY AND CURDATE() THEN 1 ELSE 0 END) AS orders_last_7_days,
  SUM(CASE WHEN create_time BETWEEN CURDATE() - INTERVAL 13 DAY AND CURDATE() THEN 1 ELSE 0 END) AS orders_last_14_days
FROM
  sale_order;

在这个查询中，我们使用 CASE 表达式来判断订单的创建时间是否在最近7天或14天内。如果是，那么对应的 SUM 表达式会增加1，否则增加0。最后，我们得到每个时间段内的订单总数。

1.2 方法二：使用条件聚合

SELECT
  COUNT(CASE WHEN create_time BETWEEN CURDATE() - INTERVAL 6 DAY AND CURDATE() THEN order_id ELSE NULL END) AS orders_last_7_days,
  COUNT(CASE WHEN create_time BETWEEN CURDATE() - INTERVAL 13 DAY AND CURDATE() THEN order_id ELSE NULL END) AS orders_last_14_days
FROM
  sale_order;

这个方法与方法一类似，但是它使用 COUNT 函数和 CASE 表达式来直接计算订单数量。如果 create_time 在指定的日期范围内，CASE 表达式返回 order_id，否则返回 NULL。COUNT 函数会忽略 NULL 值，只计算非 NULL 的值。

1.3 方法三：使用日期函数和聚合

SELECT
  -- 最近7天的订单数量
  IFNULL(SUM(CASE WHEN DATEDIFF(CURDATE(), create_time) <= 7 THEN 1 ELSE 0 END), 0) AS orders_last_7_days,
  -- 最近14天的订单数量
  IFNULL(SUM(CASE WHEN DATEDIFF(CURDATE(), create_time) <= 14 THEN 1 ELSE 0 END), 0) AS orders_last_14_days
FROM
  sale_order;

在这个查询中，我们使用 DATEDIFF 函数来计算当前日期和订单创建日期之间的天数差。如果天数差小于或等于7或14，那么 CASE 表达式返回1，否则返回0。然后，我们使用 SUM 函数来计算总和。

1.4 方法四：使用日历表（对于大数据量更有效）

如果数据量非常大，可以考虑使用日历表来优化查询。日历表是一个包含日期的表，可以与订单表进行关联查询。

首先，创建一个日历表：

CREATE TABLE calendar (
    date DATE PRIMARY KEY
);

INSERT INTO calendar (date) VALUES ('2023-01-01'), ('2023-01-02'), ..., ('2023-12-31');

然后，使用日历表来查询：

SELECT
  c.date,
  COUNT(*) AS orders_count
FROM
  sale_order so
JOIN
  calendar c ON so.create_time BETWEEN c.date AND c.date + INTERVAL 6 DAY
GROUP BY
  c.date
HAVING
  c.date >= CURDATE() - INTERVAL 7 DAY;

这个查询将返回最近7天的订单数量。通过关联日历表，我们可以快速计算出每个日期范围内的订单数量。

1.5 注意事项

确保 create_time 字段上有索引，以加速日期范围查询。
如果 create_time 包含时间信息，确保比较的是日期部分。
根据实际数据量和查询频率，选择合适的方法。对于非常大的数据集，使用日历表可能更有效。
考虑查询缓存和查询优化器的提示，以进一步提高性能。

2. 如何为MySQL中的日期字段创建合适的索引以提高查询效率？要为MySQL中的日期字段创建合适的索引以提高查询效率，可以采取以下步骤和策略：

避免在日期字段上使用函数：当查询条件中包含对日期字段的函数调用时（如 DATE_FORMAT, DAY(), MONTH() 等），索引将无法被有效利用。相反，应该将日期字符串转换为日期格式，然后与字段进行比较，以便索引能够被使用。
使用 BETWEEN 运算符：对于日期范围查询，使用 BETWEEN 运算符而不是 LIKE，因为 LIKE 可能会导致全表扫描。例如，使用 create_time BETWEEN '2021-01-01' AND '2021-01-31' 而不是 create_time LIKE '2021-01-%'。
创建日期前缀索引：如果字段是 DATETIME 或 TIMESTAMP 类型，并且你经常根据日期部分进行查询，可以考虑创建一个只包含日期部分的索引。例如，使用 ALTER TABLE your_table ADD INDEX idx_date (your_date_column); 来创建索引。
考虑索引的选择性：索引的选择性是指索引中不同值的数量与表中行的数量的比率。选择性高的索引更有助于提高查询效率。通常，具有高选择性的列（如性别、状态等）是创建索引的好选择。
使用复合索引：如果查询经常涉及多个列，并且这些列经常一起用于JOIN或WHERE条件，那么可以考虑创建一个复合索引。例如，如果经常根据 user_id 和 create_time 进行查询，可以创建一个索引 INDEX idx_user_time (user_id, create_time)。
维护索引：随着数据的不断变化，索引可能会变得碎片化，导致性能下降。定期重建索引可以提高其性能。
分析查询计划：使用 EXPLAIN 关键字分析查询计划，查看是否使用了索引以及如何优化查询。
避免过度索引：虽然索引可以提高查询速度，但过多的索引会增加写操作的负担，因为每次插入或更新数据时，索引也需要更新。因此，只对那些经常用于搜索条件的列创建索引。

通过上述方法，可以为MySQL中的日期字段创建合适的索引，以提高查询效率并优化数据库性能。

3.在MySQL中，除了使用日期函数，还有哪些方法可以进行日期范围查询？

在MySQL中，除了使用日期函数进行日期范围查询，还可以采用以下几种方法：

使用数值计算：
如果日期是以整数形式存储的（例如，‘2021-01-01’ 存储为 20200101），可以通过数值比较来查询日期范围，避免日期函数导致的索引失效。
```
SELECT *
FROM sale_order
WHERE
  CAST(create_time AS UNSIGNED) >= CAST('2021-01-01' AS UNSIGNED) AND
  CAST(create_time AS UNSIGNED) < CAST(CURDATE() AS UNSIGNED);
```

使用 STR_TO_DATE 和 CONCAT：
将日期转换为字符串，并使用 BETWEEN 运算符进行比较。

SELECT *
FROM sale_order
WHERE create_time BETWEEN STR_TO_DATE(CONCAT('2021-01-01', ' 00:00:00'), '%Y-%m-%d %H:%i:%s')
                    AND STR_TO_DATE(CONCAT(CURDATE(), ' 23:59:59'), '%Y-%m-%d %H:%i:%s');

使用 TIMESTAMPDIFF：
使用 TIMESTAMPDIFF 函数来比较日期差异。

SELECT *
FROM sale_order
WHERE TIMESTAMPDIFF(DAY, '2021-01-01', create_time) BETWEEN 0 AND 7;

使用 DATE_SUB 和 DATE_ADD：
使用 DATE_SUB 和 DATE_ADD 函数来计算日期范围的开始和结束点。

SELECT *
FROM sale_order
WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
  AND create_time < CURDATE();

使用 LEAD 或 LAG 窗口函数（MySQL 8.0+）：
如果需要比较当前日期与表中的日期之间的关系，可以使用窗口函数。

SELECT *
FROM (
  SELECT *,
         LEAD(create_time) OVER (PARTITION BY create_time ORDER BY create_time) AS next_day,
         LAG(create_time) OVER (PARTITION BY create_time ORDER BY create_time) AS prev_day
  FROM sale_order
) AS dates
WHERE create_time BETWEEN '2021-01-01' AND CURDATE()
  OR next_day BETWEEN '2021-01-01' AND CURDATE()
  OR prev_day BETWEEN '2021-01-01' AND CURDATE();