Создание ранга от наименьшей суммы до наибольшей группы сумм по нескольким столбцам

В настоящее время я использую MySQL 5.6.30, и мне нужна ваша помощь.

Вот данные в таблице, имя которой temp_work 

+----+-----------+----------+----------+--------+---------+-------+---------+
| id | client_id | account  | category | t_year | t_month | t_day | amount  |
+----+-----------+----------+----------+--------+---------+-------+---------+
| 1  | 100       | Saving   | deposit  | 2016   | 12      | 14    | 100.84  |
| 2  | 100       | Checking | withdraw | 2016   | 12      | 15    | 300.24  |
| 3  | 100       | Checking | deposit  | 2016   | 12      | 29    | 60.00   |
| 4  | 101       | Saving   | Withdraw | 2016   | 12      | 29    | 245.16  |
| 5  | 100       | Saving   | Withdraw | 2016   | 12      | 30    | 2200.00 |
| 6  | 100       | Checking | Withdraw | 2016   | 12      | 30    | 2372.16 |
| 7  | 100       | Saving   | deposit  | 2016   | 12      | 30    | 4327.00 |
| 8  | 101       | Checking | Withdraw | 2017   | 1       | 3     | 80.00   |
| 9  | 101       | Checking | Withdraw | 2017   | 1       | 3     | 1033.45 |
| 10 | 100       | Saving   | Withdraw | 2017   | 1       | 3     | 1339.16 |
| 11 | 100       | Checking | deposit  | 2017   | 1       | 4     | 140.00  |
| 12 | 100       | Checking | Withdraw | 2017   | 1       | 4     | 216.73  |
| 13 | 101       | Checking | Withdraw | 2017   | 1       | 4     | 1261.72 |
+----+-----------+----------+----------+--------+---------+-------+---------+

И мне нужен ранг от самой низкой суммы до самой большой группы сумм по client_id, счету, категории, t_year, t_month.

Итак, это то, что я ожидал или подобное 

+-----------+----------+----------+--------+---------+---------+------+
| client_id | account  | category | t_year | t_month | amount  | rank |
+-----------+----------+----------+--------+---------+---------+------+
| 100       | Checking | deposit  | 2016   | 12      | 60.00   | 1    |
| 100       | Checking | deposit  | 2017   | 1       | 140.00  | 1    |
| 100       | Checking | withdraw | 2016   | 12      | 300.24  | 1    |
| 100       | Checking | withdraw | 2016   | 12      | 2327.16 | 2    |
| 100       | Checking | Withdraw | 2017   | 1       | 216.73  | 1    |
| 100       | Saving   | deposit  | 2016   | 12      | 100.84  | 1    |
| 100       | Saving   | deposit  | 2016   | 12      | 4327.00 | 2    |
| 100       | Saving   | Withdraw | 2016   | 12      | 2200.00 | 1    |
| 100       | Saving   | Withdraw | 2017   | 1       | 1339.16 | 1    |
| 101       | Checking | Withdraw | 2017   | 1       | 80.00   | 1    |
| 101       | Checking | Withdraw | 2017   | 1       | 1033.45 | 2    |
| 101       | Checking | Withdraw | 2017   | 1       | 1261.72 | 3    |
| 101       | Saving   | Withdraw | 2016   | 12      | 245.16  | 1    |
+-----------+----------+----------+--------+---------+---------+------+

Вот моя первая попытка: 

Select  
tw1.client_id, tw1.account, tw1.category, tw1.t_year, tw1.t_month, tw1.amount
,@rownum = case when @tmonth <> tw1.t_month then 0 else @rownum + 1 end as ranking
,@tmonth := t_month as cmonth
From
( Select    @rownum := 0 from dual ) as r,
( Select    @tmonth := 0 from dual ) as m,
( Select    client_id, account, category, t_year, t_month, amount
  From      temp_work   as  tw
  Order by  client_id, account, category, t_year, t_month, amount) as tw1

Вторая попытка: 

Select  tw1.client_id, tw1.account, tw1.category, tw1.t_year, tw1.t_month, tw1.amount
    ,@rownum = case when concat(@tyear, '-', @tmonth) <> concat(t_year, '-', t_month) then 0 else @rownum + 1 end as rank
    ,@tyear := t_year as cyear
    ,@tmonth := t_month as cmonth        
From
( Select    @rownum := 0 ) as r,
( Select    @tyear := 0 ) as y,
( Select    @tmonth := 0 ) as m,
(
Select  client_id, account, category, t_year, t_month, amount
From    temp_work   
Order by    tw.client_id, tw.account, tw.category, t_year, t_month, tw.amount
) as tw1

Однако, вышеуказанные запросы дают мне тот же результат, что и ниже.

+-----------+----------+----------+--------+---------+---------+------+
| client_id | account  | category | t_year | t_month | amount  | rank |
+-----------+----------+----------+--------+---------+---------+------+
| 100       | Checking | deposit  | 2016   | 12      | 60.00   | 1    |
| 100       | Checking | deposit  | 2017   | 1       | 140.00  | 1    |
| 100       | Checking | withdraw | 2016   | 12      | 300.24  | 1    |
| 100       | Checking | withdraw | 2016   | 12      | 2327.16 | 0    |
| 100       | Checking | Withdraw | 2017   | 1       | 216.73  | 1    |
| 100       | Saving   | deposit  | 2016   | 12      | 100.84  | 1    |
| 100       | Saving   | deposit  | 2016   | 12      | 4327.00 | 0    |
| 100       | Saving   | Withdraw | 2016   | 12      | 2200.00 | 1    |
| 100       | Saving   | Withdraw | 2017   | 1       | 1339.16 | 1    |
| 101       | Checking | Withdraw | 2017   | 1       | 80.00   | 1    |
| 101       | Checking | Withdraw | 2017   | 1       | 1033.45 | 0    |
| 101       | Checking | Withdraw | 2017   | 1       | 1261.72 | 0    |
| 101       | Saving   | Withdraw | 2016   | 12      | 245.16  | 1    |
+-----------+----------+----------+--------+---------+---------+------+

Пожалуйста, дайте мне подсказка, чтобы решить эту проблему.

Большое вам спасибо.

2 2
sql mysql group-by rank

2 ответа:

Рассмотрим коррелированный подсчет агрегированного подзапроса для ранжирования: 

SELECT t.client_id, t.account, t.category, t.t_year, t.t_month, t.amount,
       (SELECT COUNT(*) FROM temp_work sub
        WHERE sub.amount <= t.amount 
        AND sub.client_id = t.client_id
        AND sub.account = t.account
        AND sub.t_year = t.t_year
        AND sub.t_month = t.t_Month) AS rank
FROM temp_work t
ORDER BY t.client_id, t.account, t.category, t.t_year, t.t_month, t.amount
0

Попробуйте перестроить свой ордер по инструкции, чтобы ваша "сумма"была вторым значением в ордере по. Это приблизит вас к тому, чего вы хотите.

Order by client_id, amount, account, category, t_year, t_month

Порядок, в котором вы перечисляете столбцы, определяет порядок их сортировки.

Для получения дополнительной информации: https://dev.mysql.com/doc/refman/5.7/en/sorting-rows.html

1