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

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中的日期字段创建合适的索引以提高查询效率，可以采取以下步骤和策略：

1. 避免在日期字段上使用函数：当查询条件中包含对日期字段的函数调用时（如 DATE_FORMAT, DAY(), MONTH() 等），索引将无法被有效利用。相反，应该将日期字符串转换为日期格式，然后与字段进行比较，以便索引能够被使用。

2. 使用 BETWEEN 运算符：对于日期范围查询，使用 BETWEEN 运算符而不是 LIKE，因为 LIKE 可能会导致全表扫描。例如，使用 create_time BETWEEN '2021-01-01' AND '2021-01-31' 而不是 create_time LIKE '2021-01-%'。

3. 创建日期前缀索引：如果字段是 DATETIME 或 TIMESTAMP 类型，并且你经常根据日期部分进行查询，可以考虑创建一个只包含日期部分的索引。例如，使用 ALTER TABLE your_table ADD INDEX idx_date (your_date_column); 来创建索引。

4. 考虑索引的选择性：索引的选择性是指索引中不同值的数量与表中行的数量的比率。选择性高的索引更有助于提高查询效率。通常，具有高选择性的列（如性别、状态等）是创建索引的好选择。

5. 使用复合索引：如果查询经常涉及多个列，并且这些列经常一起用于JOIN或WHERE条件，那么可以考虑创建一个复合索引。例如，如果经常根据 user_id 和 create_time 进行查询，可以创建一个索引 INDEX idx_user_time (user_id, create_time)。

6. 维护索引：随着数据的不断变化，索引可能会变得碎片化，导致性能下降。定期重建索引可以提高其性能。

7. 分析查询计划：使用 EXPLAIN 关键字分析查询计划，查看是否使用了索引以及如何优化查询。

8. 避免过度索引：虽然索引可以提高查询速度，但过多的索引会增加写操作的负担，因为每次插入或更新数据时，索引也需要更新。因此，只对那些经常用于搜索条件的列创建索引。

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

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

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

1. 使用数值计算：
   如果日期是以整数形式存储的（例如，‘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);
   

2. 使用 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');
   

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

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

4. 使用 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();
   

5. 使用 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();
   

6. 使用事件调度器：
   对于频繁的日期范围查询，可以设置一个事件调度器，定期计算并存储结果。

7. 使用应用程序逻辑：
   在某些情况下，可以在应用程序层面处理日期范围查询，而不是在数据库层面。

8. 使用缓存：
   如果数据不经常变化，可以将结果缓存起来，避免频繁的数据库查询。

9. 使用物化视图（在某些存储引擎中可用）：
   创建一个物化视图，存储日期范围查询的结果，定期刷新。

选择哪种方法取决于具体的数据模型、查询需求和性能考虑。在某些情况下，组合使用这些方法可能会更有效。

原文地址：https://blog.csdn.net/2301_81133727/article/details/142446699

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！