All geek questions in one place

Help write a mysql query - it should have been done 1000 times earlier, but I'm afraid. Help?

Update : I am editing my question in the hope of getting a better answer. I see that this is not so simple, but I cannot believe that there is no simpler solution than has been mentioned so far. Now I want to see if there is some php, mysql solution to deal with this in the most efficient way. I modified my question below to try to make it clearer

I have a table with the following fields:

  • User ID
  • Groupid
  • Act
  • Actiondate

This table is simply stored whenever a user on my system is added to a group (action = 1) or removed from a group (action = -1). The datetime is recorded whenever one of the above actions occurs, since the ActionDate

The group charges a fee for each user they have every month, if the user has been part of the group for at least 15 days of this billing month (the billing month means that the month does not start, it can be from January 15 - February 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))
+3
4

, , , , , , , , 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

. ( ) , -, . - , shoudl , .

, , .

.

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
                    }
                }
0

, , , . , , , :

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: count = 0, ( ) = 1. 0 1 - - .

:

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

" 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), . , , - , .

EDIT: :

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)

, feb 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)

, :

  • .
  • ,
  • , ( , max (ActionDate) , . - "00:00: 00" , "00:00:00" ).
  • tbl ( ) .
  • .

, , - . reset .

+1

, - ?

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

, , add. , , ?

, 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 :

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;
0

Source: https://habr.com/ru/post/1734500/

More articles:


All Articles