MySQL HABTM -查找每个用户最连续的小部件

Question

我目前有以下SQL数据库：

CREATE TABLE `users` (
  `id` INT(10) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(40),
  `email` VARCHAR(40),
  PRIMARY KEY  (`id`)
);

CREATE TABLE `widgets` (
  `id` INT(10) NOT NULL AUTO_INCREMENT,
  `production_date` DATETIME,
  `title` VARCHAR(100),
  PRIMARY KEY  (`id`)
);

CREATE TABLE `users_widgets` (
  `id` INT(10) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11),
  `widget_id` INT(11),
  PRIMARY KEY  (`id`)
);
ALTER TABLE `users_widgets` ADD CONSTRAINT `users_widgets_fk1` FOREIGN KEY (`user_id`) REFERENCES users(`id`);
ALTER TABLE `users_widgets` ADD CONSTRAINT `users_widgets_fk2` FOREIGN KEY (`widget_id`) REFERENCES widgets(`id`);

用户HABTM小部件。基本上，在这个场景中，我需要为每个用户计算出该用户连续购买了多少个小部件。widget应该由production_date订购，以确定它们是否是“连续的”，并且widget不是每天都生产的，但在2月3d生产的widget和在2月11日生产的widget可以是连续的，只要2月4-10日没有生产任何widget。

这就是网站的运作方式:网站所有者在网站上放置了许多小工具，用户可以承诺购买这些小工具。他们支付的金额将始终是1美元。一旦足够多的用户为一个小部件付费，该小部件就会被生产出来，并向该小部件添加一个production_date。

我试图实现的本质上是一个客户忠诚度计划。用户购买了多少个连续投入生产的小部件？然后，比如说，我可以为连续购买X个小部件的用户提供优惠券或折扣。每个用户只能为一个小部件付费一次，但可以为多个小部件付费，有些是生产中的，有些不是。不在生产中的小部件(production_date=null)不应影响连续小部件的计算。

自动增量在这里实际上不起作用，因为小部件投入生产的顺序可能与最初创建数据库记录的顺序不同。因此，我需要按production_date对小部件进行排序，以便确定哪些小部件是连续的。

Answer 1

首先，您需要指定“付费”的含义，以及如何在您的表中表示它。此外，您需要澄清您应该如何处理的情况下，用户添加了2个付费小部件，一个免费小部件，然后额外的2个付费小部件。

在我看来，您的users_widgets表中缺少信息，因为您的小部件可以分配给许多用户，但您不知道小部件被分配给特定用户的日期，而是小部件是否支付。我建议这个表应该是这样的：

CREATE TABLE `users_widgets` (
  `id` INT(10) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11),
  `widget_id` INT(11),
  `purchase_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `price` decimal(10,2),
  PRIMARY KEY  (`id`)
);

旁注：

如果您的小部件不能分配给多个用户，则使用

1. -将数据拆分为两个表(小部件和users_widgest)是没有意义的。在这种情况下，只保留一个包含所有数据的表。
2. 您可以信任购买事件订单的自动递增，但如果您不添加购买日期-它将丢失。
3. 如果您的所有小部件都已支付-您可以跳过价格或已支付字段，并对所有用户小部件执行简单计数。

有了我上面指定的users_widgets表，您应该执行以下查询：

SELECT a.user_id, sum(if(a.price is not null and b.price is not null,1,0)) 
FROM users_widgets a 
JOIN users_widgets b 
    ON a.user_id = b.user_id AND a.id<b.id 
LEFT OUTER JOIN users_widgets c 
    ON a.user_id = c.user_id AND a.id < c.id AND b.id > c.id 
WHERE c.id is null 
GROUP BY (a.user_id);

此查询将告诉您每个用户添加了多少连续的付费窗口小部件。但请注意，如果用户添加了2个付费窗口小部件，1个免费窗口小部件，然后再次2个付费窗口小部件u将收到该用户的结果为2，因为只有2购买后，另一个购买。

==========UPDATE==========

检查这是否可以帮助您解决问题

SELECT u1.user_id, count(1) 
FROM users_widgets u1 
JOIN widgets w1 
   ON u1.widget_id=w1.id 
JOIN widgets w2 
   ON w1.production_date<w2.production_date 
JOIN users_widgets u2 
   ON w2.id=u2.widget_id 
LEFT OUTER JOIN widgets w3 
   ON w1.production_date < w3.production_date 
   AND w2.production_date>w3.production_date 
WHERE w3.id is null 
  AND u1.user_id=u2.user_id 
GROUP BY user_id;

请注意，在这种情况下，如果用户购买了2个连续的widgets，然后错过了一个，随后又购买了2个，则-u将收到该用户的计数为2(每次执行连续购买时为1)。

我强烈建议您不要在生产数据库中运行此类查询。