I tried something similar just now
Here is MySQL for My PC
mysql> select * from information_schema.global_variables where variable_name='datadir' or variable_name like 'versio%';
+-------------------------+------------------------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-------------------------+------------------------------+
| VERSION_COMMENT | MySQL Community Server (GPL) |
| VERSION | 5.5.12-log |
| VERSION_COMPILE_MACHINE | x86 |
| DATADIR | C:\MySQL_5.5.12\data\ |
| VERSION_COMPILE_OS | Win64 |
+-------------------------+------------------------------+
5 rows in set (0.00 sec)
I will run this using MyISAM
- Step 01) create a table called 'rolando'
- Step 02) insert 'dominique' and 'diamond'
- Step 03) copy the table structure to 'pamela'
- Step 04) alter 'pamela' to not have auto_increment
- Step 05) In DOS, copy rolando.MYD to pamela.MYD
- Step 06) run
REPAIR TABLE pamela;
(Rebuild pamela.MYI)
- Step 07) run
SELECT COUNT(1) FROM pamela;
- Step 08) run
SHOW CREATE TABLE pamela\G
- Step 09) run
SELECT * FROM pamela;
- Step 10) insert 'carlik' into pamela
- Step 11) run
SELECT * FROM pamela;
Let's see if these steps are kosher.
Here are Steps 1-4
mysql> drop table if exists rolando;
Query OK, 0 rows affected (0.02 sec)
mysql> drop table if exists pamela;
Query OK, 0 rows affected (0.00 sec)
mysql> create table rolando
-> (
-> name varchar(20),
-> id int not null auto_increment,
-> primary key (id)
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)
mysql> insert into rolando (name) values ('dominique'),('diamond');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from rolando;
+-----------+----+
| name | id |
+-----------+----+
| dominique | 1 |
| diamond | 2 |
+-----------+----+
2 rows in set (0.00 sec)
mysql> create table pamela like rolando;
Query OK, 0 rows affected (0.05 sec)
mysql> show create table rolando\G
*************************** 1. row ***************************
Table: rolando
Create Table: CREATE TABLE `rolando` (
`name` varchar(20) DEFAULT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> show create table pamela\G
*************************** 1. row ***************************
Table: pamela
Create Table: CREATE TABLE `pamela` (
`name` varchar(20) DEFAULT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.02 sec)
mysql> alter table pamela modify id int(11) unsigned not null;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table pamela\G
*************************** 1. row ***************************
Table: pamela
Create Table: CREATE TABLE `pamela` (
`name` varchar(20) DEFAULT NULL,
`id` int(11) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> select count(1) from pamela;
+----------+
| count(1) |
+----------+
| 0 |
+----------+
1 row in set (0.01 sec)
mysql>
Here is Step 6
C:\>copy C:\MySQL_5.5.12\data\test\rolando.MYD C:\MySQL_5.5.12\data\test\pamela.MYD
1 file(s) copied.
C:\>
Here are the rest of the Steps starting at Step 7
mysql> repair table pamela;
+-------------+--------+----------+------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------+--------+----------+------------------------------------+
| test.pamela | repair | warning | Number of rows changed from 0 to 2 |
| test.pamela | repair | status | OK |
+-------------+--------+----------+------------------------------------+
2 rows in set (0.03 sec)
mysql> select count(1) from pamela;
+----------+
| count(1) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
mysql> insert into pamela (name,id) values ('carlik',3);
Query OK, 1 row affected (0.00 sec)
mysql> select * from pamela;
+-----------+----+
| name | id |
+-----------+----+
| dominique | 1 |
| diamond | 2 |
| carlik | 3 |
+-----------+----+
3 rows in set (0.00 sec)
mysql>
Dangerous game, isn't it ???
Guess what? Stuff like this is actually published in "High Performance MySQL : Optimization, Backups, Replication, and more", Pages 146-148 under the Subheading Speeding Up ALTER TABLE. Page 147 Paragraph 1 says:
The technique we are about to demonstrate is unsupported,
undocumented, and may not work. Use it at your risk. We advise you to
back up you data first!
I also had an earlier post when someone ask a similar question : Can I rename the values in a MySQL ENUM column in one query?
You got guts, @atxdba !!!
This is essentially a gaps-and-islands problem. And when I have my SQL Server hat on, I often solve this kind of problem with two ROW_NUMBER()
calls. Sadly, MySQL, unlike many other major SQL products, does not support ROW_NUMBER()
, nor any other ranking function. To make up for that, however, you can use variable assignment in SELECTs, which MySQL does support (unlike many other major SQL products).
Below is a solution followed by an explanation:
SELECT
member_id,
member_name,
event_id,
COUNT(*) AS consecutive_times_missed,
MIN(event_date) AS first_date_missed,
MAX(event_date) AS last_date_missed
FROM (
SELECT
member_id,
member_name,
event_id,
event_date,
is_missed,
@occ_ranking := (event_id = @last_event) * (member_id = @last_member) * @occ_ranking + 1,
@att_ranking := (event_id = @last_event) * (member_id = @last_member)
* (is_missed = @last_missed) * @att_ranking + 1,
@occ_ranking - @att_ranking AS grp,
@last_member := member_id,
@last_event := event_id,
@last_missed := is_missed
FROM (
SELECT
m.member_id,
m.member_name,
e.event_id,
e.event_date,
(a.attendance_date IS NULL) AS is_missed
FROM members m
INNER JOIN event_dates e ON m.member_join_date <= e.event_date
LEFT JOIN attendance a ON m.member_id = a.member_id
AND e.event_id = a.event_id
AND e.event_date = a.attendance_date,
(
SELECT
@occ_ranking := 0,
@att_ranking := 0,
@last_member := 0,
@last_event := 0,
@last_missed := 0
) v
ORDER BY
m.member_id,
e.event_date
) s
) s
WHERE
is_missed = 1
GROUP BY
member_id,
member_name,
event_id,
grp
HAVING
COUNT(*) >= 3
;
Basically, you start with joining members
and event_dates
to get all the event occurrences the members could have attended based on their membership dates. Then you throw in the attendance
table (via a left join) to flag the missed occurrences. Here's an example of what you get by this time:
member_id event_id event_date is_missed
--------- -------- ---------- ---------
1 1 2012-07-10 0
2 1 2012-07-10 1
1 1 2012-07-14 0
2 1 2012-07-14 1
… … … …
At this point, the resulting set needs to be sorted by member_id, event_id
, because that is crucial to the subsequent rankings calculation.
Two different rankings are produced for every row. One is a ranking within the row's partition of (member_id, event_id)
(it is reset as soon as a new event or a new member is encountered). The other is a ranking within the specific group of consecutive event occurrences, either attended or missed, that the row belongs to (this ranking, in addition to being reset upon coming across a new member or event, also gets reset whenever the other group is encountered). And so you get something like this:
member_id event_id event_date is_missed ranking1 ranking2
--------- -------- ---------- --------- -------- --------
… … … … … …
1 1 2012-07-27 0 4 4
1 1 2012-07-28 0 5 5
1 1 2012-07-29 1 6 1
2 1 2012-07-10 1 1 1
2 1 2012-07-14 1 2 2
2 1 2012-07-25 1 3 3
2 1 2012-07-27 0 4 1
… … … … … …
As you may have noticed, the difference between the two ranking numbers is constant throughout the particular group of consecutive event occurrences of the same kind ("attended" or "missed") and is also unique for that group within its partition. Therefore, every such group can be identified by member_id
, event_id
and the just mentioned difference.
And it now remains simply to filter the events leaving just the missed ones, group the rows and get the necessary aggregated data, like the number of rows and, possibly, as in the query above, the dates of the first and the last event occurrence in the group. The number of rows is also used in an additional filter condition to omit groups with fewer rows than required.
Best Answer
DB fiddle