仅适用于8.0之前的版本…
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(purchase_id SERIAL PRIMARY KEY
,user CHAR(1) NOT NULL
,date DATE NOT NULL
);
INSERT INTO my_table VALUES
(1,'a','2018-01-01'),
(2,'b','2018-01-02'),
(3,'a','2018-01-02'),
(4,'a','2018-01-03'),
(5,'b','2018-01-04'),
(6,'a','2018-01-04');
SELECT a.purchase_id
, a.user
, a.date
, a.i rank
FROM
( SELECT x.*
, CASE WHEN @prev = user THEN @i:=@i+1 ELSE @i:=1 END i
, @prev := user
FROM my_table x
, (SELECT @prev:=null,@i:=0) vars
ORDER
BY user
, date
) a
ORDER
BY purchase_id;
+-------------+------+------------+------+
| purchase_id | user | date | rank |
+-------------+------+------------+------+
| 1 | a | 2018-01-01 | 1 |
| 2 | b | 2018-01-02 | 1 |
| 3 | a | 2018-01-02 | 2 |
| 4 | a | 2018-01-03 | 3 |
| 5 | b | 2018-01-04 | 2 |
| 6 | a | 2018-01-04 | 4 |
+-------------+------+------------+------+