Обновите столбец ранга на основе общей суммы группы

Я написал следующий код в SQLite: 

CREATE TABLE payments (
  customerNumber REAL NOT NULL,
  checkNumber TEXT NOT NULL,
  paymentDate TEXT NOT NULL,
  amount REAL NOT NULL,
  rank REAL default 0,
  PRIMARY KEY  (customerNumber,checkNumber)
);

insert  into payments(customerNumber,checkNumber,paymentDate,amount) values(103,'HQ336336','2004-10-19 00:00:00',6066.78);
insert  into payments(customerNumber,checkNumber,paymentDate,amount) values(103,'JM555205','2003-06-05 00:00:00',14571.44);
insert  into payments(customerNumber,checkNumber,paymentDate,amount) values(112,'BO864823','2004-12-17 00:00:00',14191.12);
insert  into payments(customerNumber,checkNumber,paymentDate,amount) values(112,'HQ55022','2003-06-06 00:00:00',32641.98);
insert  into payments(customerNumber,checkNumber,paymentDate,amount) values(121,'MA302151','2004-11-28 00:00:00',34638.14);
insert  into payments(customerNumber,checkNumber,paymentDate,amount) values(121,'KI831359','2004-11-04 00:00:00',17876.32);
insert  into payments(customerNumber,checkNumber,paymentDate,amount) values(161,'KG644125','2005-02-02 00:00:00',12692.19);
insert  into payments(customerNumber,checkNumber,paymentDate,amount) values(161,'NI908214','2003-08-05 00:00:00',38675.13);
insert  into payments(customerNumber,checkNumber,paymentDate,amount) values(181,'CM564612','2004-04-25 00:00:00',22602.36);
insert  into payments(customerNumber,checkNumber,paymentDate,amount) values(181,'GQ132144','2003-01-30 00:00:00',5494.78);

Теперь мой вопрос: Как я могу обновить столбец rank в таблице на основе ранжирования "total(сумма) from payments group by customerNumber"?

1 3
sqlite sqlite3 ranking

1 ответ:

Edit (удален код SQL Server, SQLite не поддерживает соединения в операторах обновления):

Я думаю, что это должно быть нормально: 

drop table if exists ranks;

create temp table ranks (
    rank integer primary key,
    customerNumber real,
    total real
);

insert into ranks
select null, customerNumber, total(amount) as total
from payments
group by customerNumber
order by total desc;


update payments set
    rank = (
        select rank from ranks 
        where customerNumber = payments.customerNumber
    );

select * from payments;

Добавлено:

Вот очень грязный способ сделать это в одном операторе, он использует очень специфические подзапросы для ранжирования (это какой-то номер строки SQLite) каждого клиента. Как я уже сказал, много много sybqueries, но это цена, чтобы заставить его работать в одном запросе без использования join in update.

update payments set
    rank = (
        select
            (select count(0) from (select total(amount) as t, customerNumber as c
                    from payments group by customerNumber order by t desc) t1
            where t1.t >= t2.t) as rank
        from (select total(amount) as t, customerNumber as c from payments group by customerNumber order by t desc) 
        t2 where t2.c = payments.customerNumber order by t2.t desc
    );
3