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 小部件。基本上，在这种情况下，我需要计算出每个用户连续支付了多少个小部件。小部件应按生产日期排序，以确定它们是否“连续”，并且小部件不是每天都生产的，但 2 月 3 日生产的小部件和 2 月 11 日生产的小部件可以是连续的，只要没有小部件于 2 月 4 日至 10 日生产。

网站的工作原理是这样的:网站所有者在网站上放置了许多小部件，用户可以向这些小部件质押资金。他们支付的金额始终为 1 美元。一旦有足够多的用户为小部件付费，就会生成该小部件并将生产日期添加到该小部件中。

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

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

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`)
);

Side notes:

1. If your widgets can't be assigned to several users - there is no point to split the data into 2 tables (widgets and users_widgest). In this case, keep only 1 table with all the data.

2. You can trust the autoincrement for order of the purchase events, but if you will not add the date of purchase - it will be lost.

3. If all of your widgets are paid - u can skip the price or paid field and perform a simple count of all user widgets.

有了我上面指定的 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);

This query will tell you how many consecutive paid widgets were added per user. but please note, that if the user added 2 paid widgets, 1 free widgets and then again 2 paid widgets u will receive for that user result of 2 because only 2 purchases followed another purchase.

==========更新==========

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

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;

Please note that in this case still if the user bought 2 consecutive widgets then missed one and later on bought 2 additional - u will receive a count of 2 for this user (1 for every time he performed a consecutive purchase)

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