时间戳

首页 > 解决方案 > 如何优化我关于在同一个表中连接 3 个表的查询？

mysql - 如何优化我关于在同一个表中连接 3 个表的查询？

问题描述

我想获得从三个月前开始每月购买我产品的 id 客户。今天是 2020-02-15。所以我想得到在 2019 年 11 月、2019 年 12 月、2020 年 1 月购买的客户。

我只有这样的 1 个表顺序（MySQL）：

订单表（主键 = ID（自动增量））： 

-----------------------------------------------
|      ID      |    id_cust  |    buy_date    |
-----------------------------------------------
|       1      |       10    |   2019-11-01   | 
|       2      |       11    |   2019-11-10   |
|       3      |       10    |   2019-12-11   |
|       4      |       12    |   2019-12-12   |
|       5      |       10    |   2020-01-13   |
|       6      |       11    |   2020-01-14   |
|       7      |       12    |   2020-01-15   |
-----------------------------------------------

根据我想要的，答案是 id_cust 10

我已经尝试过了，得到这样的结果：

SELECT g1.`id_cust`
FROM `orders` g1 
    JOIN `orders` g2
    ON g2.`id_cust`   = g1.`id_cust`
      AND g2.`buy_date` >= STR_TO_DATE(CONCAT('01-', LPAD(MONTH(DATE_SUB(NOW(), INTERVAL 2 MONTH)), 2, '0'), '-', YEAR(DATE_SUB(NOW(), INTERVAL 2 MONTH))), '%d-%m-%Y')
      AND g2.`buy_date` < STR_TO_DATE(CONCAT('01-', LPAD(MONTH(DATE_SUB(NOW(), INTERVAL 1 MONTH)), 2, '0'), '-', YEAR(DATE_SUB(NOW(), INTERVAL 1 MONTH))), '%d-%m-%Y')
    JOIN `orders` g3
    ON g3.`id_cust`   = g1.`id_cust`
      AND g3.`id_cust`   = g2.`id_cust`
      AND g3.`buy_date` >= STR_TO_DATE(CONCAT('01-', LPAD(MONTH(DATE_SUB(NOW(), INTERVAL 1 MONTH)), 2, '0'), '-', YEAR(DATE_SUB(NOW(), INTERVAL 1 MONTH))), '%d-%m-%Y')
      AND g3.`buy_date` < STR_TO_DATE(CONCAT('01-', LPAD(MONTH(NOW()), 2, '0'), '-', YEAR(NOW())), '%d-%m-%Y')
WHERE g1.`buy_date` >= STR_TO_DATE(CONCAT('01-', LPAD(MONTH(DATE_SUB(NOW(), INTERVAL 3 MONTH)), 2, '0'), '-', YEAR(DATE_SUB(NOW(), INTERVAL 3 MONTH))), '%d-%m-%Y')
AND g1.`buy_date` < STR_TO_DATE(CONCAT('01-', LPAD(MONTH(DATE_SUB(NOW(), INTERVAL 2 MONTH)), 2, '0'), '-', YEAR(DATE_SUB(NOW(), INTERVAL 2 MONTH))), '%d-%m-%Y')
GROUP BY g1.`id_cust`

请帮助我简化我的语法，因为当它在大量数据上运行时非常慢，或者如果我的语法有误，请更正我的语法。

标签： mysqlsqldatabase

解决方案

这个怎么样？

select c.id_cust
from (select o.id_cust, year(buy_date) as yyyy, month(buy_date) as mm,
             row_number() over (partition by o.id_cust) as month_counter
      from orders o
      where buy_date >= date_format(current_date - interval 3 month, '%Y-%m-%d') and
            buy_date < date_format(current_date, '%Y-%m-%d')
      group by id_cust, yyyy, mm
     ) c
where month_counter = 3;

这样做只是过滤到您关心的三个月。然后它按年和月聚合，只返回第三行。

实际上，这更容易表达为：

select o.id_cust
from orders o
where buy_date >= date_format(current_date - interval 3 month, '%Y-%m-%d') and
      buy_date < date_format(current_date, '%Y-%m-%d')
group by o.id_cust
having count(distinct year(buy_date), month(buy_date)) = 3;

推荐阅读