帮助编写一个 mysql 查询 - 这之前一定已经完成了 1000 次，但我很挣扎..请帮忙？

Question

更新： 我正在编辑我的问题，希望得到更好的答案。我发现这并不那么简单，但我不敢相信没有比迄今为止提到的更简单的解决方案。 我现在正在寻找是否有某种 php、mysql 解决方案以最有效的方式处理这个问题。我修改了下面的问题，试图让事情变得更清楚

我有一个包含以下字段的表：

• UserID
• GroupID
• Action
• ActionDate

该表仅存储系统上的用户添加到组（操作 = 1）或从组中删除（操作 = -1）。每当发生上述操作之一时，都会记录日期时间，如 ActionDate

只要用户在该帐单月份中至少 15 天属于该组（帐单月份是指不一定是月初，可能是从 1 月 15 日到 2 月 15 日）

我每个月都会在计费月开始时为当时属于其组的所有用户向我的组计费。现在，在一个月内，他们可能会向组中添加新用户或从组中删除现有用户。 如果他们删除了某个用户，我需要知道该用户在该帐单月份的至少 15 天内是否属于该组。如果他什么都不做，如果不是，那么该用户的团体需要退款（因为他们在月初为该用户付款，但他加入该团体的时间不到 15 天） 如果他们添加了用户并且该用户在该组中至少存在 15 天（即在帐单月份的 15 天内添加并且在 15 天结束之前未删除），则必须为该用户向该组收费。如果用户最终没有成为该组的一员 15 天，那么我们什么也不做（免费）。

一些额外的复杂性包括：

• 在该计费月份内，用户可能会被多次添加或删除，我们需要跟踪他属于该组的总天数
• 我们需要能够区分为了正确对组进行计费而（最终）删除或添加（最终）的用户。 （例如，作为群组成员有 10 天的用户 - 如果他最终被从群组中删除，那么我们会退款。如果他被添加到群组中，那么我们不会收费 - 因为少于 10 天）
• 在任何情况下在给定的计费月份中，用户可能不会出现在该表中，因为他们的状态未更改 - 即他们仍然是组的一部分或从未属于组。事实是，不需要对这些用户做任何事情，因为如果有必要，他们将被包含在“今天组中有多少用户”的基本每月计算中，

我开始意识到没有简单的 mysql 解决方案，我需要一个 php， mysql 组合。请帮忙！！！

这是我最近的 sql 尝试，但它没有包含我在下面讨论的所有问题：

SELECT * 
  FROM groupuserlog 
 where action = 1 
   and actiondate >= '2010-02-01' 
   and actiondate < date_add('2010-02-01',INTERVAL 15 DAY) 
   and userid not in (select userid 
                        from groupuserlog 
                       where action = -1 
                         and actiondate < '2010-03-01' 
                         and actiondate > date_add('2010-02-01', INTERVAL 15 DAY))

Answer 1

我假设用户可能在计费周期之前很久就加入了群组，并且在计费周期内可能不会更改状态。这需要扫描整个表以构建一个如下所示的成员资格表：

create table membership (
   UserId int not null,
   GroupId int not null,
   start datetime not null,
   end datetime not null,
   count int not null,
   primary key (UserId, GroupId, end )
);

一旦正确填充，您想要的答案就很容易获得：

set @sm = '2009-02-01';
set @em = date_sub( date_add( @sm, interval 1 month), interval 1 day);

# sum( datediff( e, s ) + 1 ) -- +1 needed to include last day in billing

select UserId, 
       GroupId,  
       sum(datediff( if(end > @em, @em, end), 
                     if(start<@sm, @sm, start) ) + 1 ) as n
from membership 
where start <= @em and end >= @sm
group by UserId, GroupId
having n >= 15;

扫描需要由游标执行（这不会很快）。我们需要按 ActionDate 和 Action 对输入表进行排序，以便“加入”事件出现在“离开”事件之前。计数字段
是否可以帮助应对病态情况 - 会员资格在某一天结束，然后在同一天重新开始，在同一天再次结束，并在同一天再次开始，等等。在这些情况下，我们递增每个开始事件的计数，以及每个结束事件的递减。仅当结束事件导致倒计时为零时，我们才会关闭会员资格。在填充成员资格表结束时，您可以查询 count 的值：封闭的成员资格应具有 count = 0，开放的成员资格（尚未关闭）应具有 count = 1。应仔细检查 count 超出 0 和 1 的任何条目- 这表明某处存在错误。

光标查询是：

select UserID as _UserID, GroupID as _GroupID, Date(ActionDate) adate, Action from tbl 
order by UserId, GroupId, Date(ActionDate), Action desc;

“Action desc”应该打破联系，以便如果有人在同一日期加入和离开组，则开始事件出现在结束事件之前。 ActionDate 需要从日期时间转换为日期，因为我们对天单位感兴趣。

游标内的操作如下：

if (Action = 1) then 
  insert into membership 
    set start=ActionDate, end='2037-12-31', UserId=_UserId, GroupId=_GroupId, count=1
    on duplicate key update set count = count + 1;
elsif (Action == -1) 
  update membership 
    set end= if( count=1, Actiondate, end),
        count = count - 1 
    where UserId=_UserId and GroupId=_GroupId and end = '2037-12-31';
end if

我没有给您所需的游标定义的确切语法（您可以在 MySQL 手册中找到），因为完整的代码会模糊这个想法。事实上，在应用程序中执行游标逻辑可能会更快 - 甚至可能在应用程序中构建成员资格详细信息。

编辑：这是实际的代码：

create table tbl (
   UserId int not null,
   GroupId int not null,
   Action int not null,
   ActionDate datetime not null
);

create table membership (
   UserId int not null,
   GroupId int not null,
   start datetime not null,
   end datetime not null,
   count int not null,
   primary key (UserId, GroupId, end )
);

drop procedure if exists popbill;
delimiter //

CREATE PROCEDURE popbill()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE _UserId, _GroupId, _Action int;
  DECLARE _adate date;
  DECLARE cur1 CURSOR FOR 
  select UserID, GroupID, Date(ActionDate) adate, Action 
  from tbl order by UserId, GroupId, Date(ActionDate), Action desc;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  truncate table membership;

  OPEN cur1;

  REPEAT
    FETCH cur1 INTO _UserId, _GroupId, _adate, _Action;
    IF NOT done THEN
       IF _Action = 1 THEN
          INSERT INTO membership
          set start=_adate, end='2037-12-31', 
              UserId=_UserId, GroupId=_GroupId, count=1
          on duplicate key update count = count + 1;
       ELSE
          update membership 
          set end= if( count=1, _adate, end),
              count = count - 1 
          where UserId=_UserId and GroupId=_GroupId and end = '2037-12-31';
       END IF;
    END IF;
  UNTIL done END REPEAT;

  CLOSE cur1;
END
//

delimiter ;

这是一些测试数据：

insert into tbl values (1, 10, 1, '2009-01-01' );
insert into tbl values (1, 10, -1, '2009-01-02' );
insert into tbl values (1, 10, 1, '2009-02-03' );
insert into tbl values (1, 10, -1, '2009-02-05' );
insert into tbl values (1, 10, 1, '2009-02-05' );
insert into tbl values (1, 10, -1, '2009-02-05' );
insert into tbl values (1, 10, 1, '2009-02-06' );
insert into tbl values (1, 10, -1, '2009-02-06' );
insert into tbl values (2, 10, 1, '2009-02-20' );
insert into tbl values (2, 10, -1, '2009-05-30');
insert into tbl values (3, 10, 1, '2009-01-01' );
insert into tbl values (4, 10, 1, '2009-01-31' );
insert into tbl values (4, 10, -1, '2009-05-31' );

这是正在运行的代码和结果：

call popbill;
select * from membership;

+--------+---------+---------------------+---------------------+-------+
| UserId | GroupId | start               | end                 | count |
+--------+---------+---------------------+---------------------+-------+
|      1 |      10 | 2009-01-01 00:00:00 | 2009-01-02 00:00:00 |     0 |
|      1 |      10 | 2009-02-03 00:00:00 | 2009-02-05 00:00:00 |     0 |
|      1 |      10 | 2009-02-06 00:00:00 | 2009-02-06 00:00:00 |     0 |
|      2 |      10 | 2009-02-20 00:00:00 | 2009-05-30 00:00:00 |     0 |
|      3 |      10 | 2009-01-01 00:00:00 | 2037-12-31 00:00:00 |     1 |
|      4 |      10 | 2009-01-31 00:00:00 | 2009-05-31 00:00:00 |     0 |
+--------+---------+---------------------+---------------------+-------+
6 rows in set (0.00 sec)

然后，检查 2 月 09 日出现了多少个计费天：

set @sm = '2009-02-01';
set @em = date_sub( date_add( @sm, interval 1 month), interval 1 day);

select UserId, 
       GroupId,  
       sum(datediff( if(end > @em, @em, end), 
                 if(start<@sm, @sm, start) ) + 1 ) as n
from membership 
where start <= @em and end >= @sm
group by UserId, GroupId;

+--------+---------+------+
| UserId | GroupId | n    |
+--------+---------+------+
|      1 |      10 |    4 |
|      2 |      10 |    9 |
|      3 |      10 |   28 |
|      4 |      10 |   28 |
+--------+---------+------+
4 rows in set (0.00 sec)

这可以只扫描表以查找自上次运行以来的更改：

1. 删除“截断成员资格”语句。
2. 创建一个包含处理的最后一个时间戳的控制表
3. 计算要包含在本次运行中的最后一个时间戳（我建议 max(ActionDate) 不好，因为可能会出现一些带有较早时间戳的无序到达。一个好的选择是今天早上的“00:00:00”，或该月第一天的“00:00:00”）。
4. 更改游标查询以仅包含上次运行日期（来自控制表）和计算出的上次日期之间的表条目。
5. 最后用计算出的最后日期更新控制表。

如果你这样做，传递一个允许你从头开始重建的标志也是一个好主意 - 即。将控制表重置为开始时间，并在运行常规过程之前截断成员资格表。

Answer 2

不确定你的桌子，但也许类似？

SELECT COUNT(UserID)
FROM MyTable
WHERE MONTH(ActionDate) = 3
AND GroupID = 1
AND Action = 1
GROUP BY UserID

Answer 3

我认为所有的复杂性都在于如何找出给定添加操作的相邻删除操作。那么，添加一个指向后续操作的主键的列怎么样？

假设该列名为 NextID，

有多少用户在给定月份加入某个群组并在该群组中保留了至少 15 天：

SELECT COUNT(DISTINCT UserID)
FROM MyTable AS AddedUsers
LEFT OUTER JOIN MyTable
  ON MyTable.ID = AddedUsers.NextID
  AND MyTable.ActionDate > DATE_ADD(AddedUsers.ActionDate, INTERVAL 15 DAY)
  AND MyTable.Action = -1
WHERE MONTH(AddedUsers.ActionDate) = 3 AND YEAR(AddedUsers.ActionDate) = 2012
  AND AddedUsers.GroupID = 1
  AND AddedUsers.Action = 1
  AND MONTH(DATE_ADD(AddedUsers.ActionDate, INTERVAL 15 DAY)) = 3;

在给定月份有多少人从群组中删除且未在群组中保留 至少 15 天至少 15 天：

SELECT COUNT(DISTINCT UserID)
FROM MyTable AS RemovedUsers
INNER JOIN MyTable
  ON MyTable.NextID = RemovedUsers.ID
  AND RemovedUsers.ActionDate <= DATE_ADD(MyTable.ActionDate, INTERVAL 15 DAY)
  AND MyTable.Action = 1
WHERE MONTH(RemovedUsers.ActionDate) = 3 AND YEAR(RemovedUsers.ActionDate) = 2012
  AND RemovedUsers.GroupID = 1
  AND RemovedUsers.Action = -1;

Answer 4

我开始研究 Martin 提出的解决方案，并意识到虽然这可能是正确的选择，但我决定使用我最了解的 php，而不是复杂的 sql。虽然效率肯定较低，但由于我的桌子尺寸永远不会太大，这对我来说最有意义。

最后，我编写了一个简单的查询，它按时间顺序为给定月份组中的所有用户活动创建用户历史记录。

SELECT Concat(firstname,' ',lastname) as name, username, UserID,ACTION , Date(ActionDate), Unix_Timestamp(ActionDate) as UN_Action, DateDiff('$enddate', actiondate ) AS DaysTo, DateDiff( actiondate, '$startdate' ) AS DaysFrom
        FROM `groupuserlog` inner join users on users.id = groupuserlog.userid WHERE groupuserlog.groupid = $row[groupid] AND ( actiondate < '$enddate' AND actiondate >= '$startdate') ORDER BY userid, actiondate

然后，我循环遍历结果集并收集每个用户的所有数据。该月的第一个操作（添加或删除）指示该用户是否是该组中以前存在的用户。然后，我查看历史记录并简单计算活跃天数 - 最后，我只是查看是否应该退款或收费，具体取决于用户之前是否存在于该组中。

它不是那么漂亮，但它干净地完成了工作，并允许我进行一些我需要做的额外处理。

感谢大家的帮助。

我的 php 代码，如果有人感兴趣的话，如下所示：

while($logrow = mysql_fetch_row($res2)) {

                list($fullname, $username, $guserid,$action,$actiondate,$uxaction,$daysto,$daysfrom) = $logrow;
                if($action == 1)
                    $actiondesc = "Added";
                else
                    $actiondesc = "Removed";


                //listing each user by individual action and building a history
                //the first action is very important as it defines the previous action

                if($curruserid != $guserid) {

                    if($curruserid > 0) {
                        //new user history so reset and store previous user value
                        if($wasMember) {
                            //this was an existing member so check if need refund (if was not on for 15 days)
                            $count = $basecount + $count;
                            echo "<br>User was member and had $count days usage";
                            if($count< 15) {
                                array_push($refundarrinfo, "$fullname (#$guserid $username)");
                                array_push($refundarr, $guserid);
                                echo " REFUND";
                            } else
                                echo " NONE";

                        } else {
                            //this user was not an existing member - see if need to charge (ie if was on for min 15 days)
                            $count = $basecount + $count;
                            echo "<br>User was not a member and was added for $count days usage";
                            if($count >= 15) {
                                array_push($billarrinfo, "$fullname (#$guserid $username)");
                                array_push($billarr, $guserid);
                                echo " CHARGE";
                            } else
                                echo " NONE";
                        }
                    }

                    $basecount = 0;
                    $count = 0;
                    $prev_uxaction = 0;

                    //setup new user - check first action
                     echo "<br><hr><br>$guserid<br>$actiondesc - $actiondate"; // - $daysto - $daysfrom";
                    if($action == 1)
                        $wasMember = FALSE;
                    else {
                        //for first action - if is a remove then store in basecount the number of days that are for sure in place
                        $basecount = $daysfrom;
                        $wasMember = TRUE; //if doing a remove myust have been a member
                    }

                } else
                    echo "<br>$actiondesc - $actiondate";// - $daysto - $daysfrom";

                $curruserid = $guserid;

               if($action == 1) { //action = add
                    $count = $daysto;
                    $prev_uxaction = $uxaction;  //store this actiondate in case needed for remove calculation
                } else { //action = remove
                    //only do something if this is a remove coming after an add - if not it has been taken care of already
                    if($prev_uxaction != 0) {
                        //calc no. of days between previous date and this date and overwrite count by clearing and storing in basecount
                        $count = ($uxaction - $prev_uxaction)/(60 * 60 * 24);
                        $basecount = $basecount + $count;
                        $count = 0; //clear the count as it is stored in basecount
                    }
                }