我在使用mysql查询时遇到了问题，无法根据订单频率获取一些客户统计数据。
我得到了以下标准：
在过去12个月内订购。
订购三次或更多。
订单间隔不到2个月
我能做的前两件事没问题，这是最后一件。它还需要考虑以下因素：
客户可能是新客户，并且在6个月前才首次购买（例如），并且需要从该点开始计算。
到目前为止，我的情况是：

SELECT c.customers_id, c.customers_firstname, c.customers_email_address, 
    b.biggestOrder AS largest_single_order_in_range,
    b.orders_id AS largest_single_order_id,
    COUNT(o.orders_id) AS order_count_total,
    MAX(o.date_purchased) AS last_order_date,
    MIN(o.date_purchased) AS first_order_date
FROM orders o
LEFT JOIN orders_total ot ON ot.orders_id = o.orders_id
LEFT JOIN customers c ON c.customers_id = o.customers_id
LEFT JOIN (
    SELECT o2.customers_id, o2.orders_id,
        MAX(ot2.`value`) AS biggestOrder
    FROM orders_total ot2
    LEFT JOIN orders o2 ON o2.orders_id = ot2.orders_id
    WHERE
        ot2.class = 'ot_subtotal' AND o2.orders_status = 3
    GROUP BY
        o2.customers_id
) b ON b.customers_id = c.customers_id
WHERE
    ot.class = 'ot_subtotal' AND o.orders_status = 3 
GROUP BY
    b.customers_id
HAVING
    last_order_date >= DATE_SUB(NOW(), INTERVAL 12 MONTH) 
ORDER BY
    last_order_date DESC

--添加了表创建（下面是示例数据/插入）：

CREATE TABLE `orders` (
  `orders_id` int(11) NOT NULL AUTO_INCREMENT,
  `customers_id` int(11) NOT NULL,
  `last_modified` datetime DEFAULT NULL,
  `date_purchased` datetime DEFAULT NULL,
  `orders_status` int(5) NOT NULL,
  PRIMARY KEY (`orders_id`),
  KEY `idx_orders_customers_id` (`customers_id`),
  KEY `idx_orders_status` (`orders_status`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `orders_total` (
  `orders_total_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `orders_id` int(11) NOT NULL,
  `value` decimal(15,4) NOT NULL,
  `class` varchar(32) CHARACTER SET latin1 NOT NULL,
  PRIMARY KEY (`orders_total_id`),
  KEY `idx_orders_total_orders_id` (`orders_id`),
  KEY `idx_class` (`class`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `customers` (
  `customers_id` int(11) NOT NULL AUTO_INCREMENT,
  `customers_firstname` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `customers_email_address` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`customers_id`),
  KEY `idx_customers_email_address` (`customers_email_address`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

更新：这很接近，但并不准确（下面的代码片段不完整，但会添加到顶部的查询中）。

SELECT ...., COUNT(DISTINCT MONTH(o.date_purchased)) AS month_count
....
HAVING month_count >= 6

但这只会确保在1个月x 6个月内至少下了1个订单，这可能会给出前6个月的结果一个订单已经下了，然后剩下的6个月内什么都没有，还将排除第一个订单不到6个月前的客户。