Saya punya meja dengan transaksi:
Transactions
------------
id | account | type | date_time | amount
----------------------------------------------------
1 | 001 | 'R' | '2012-01-01 10:01:00' | 1000
2 | 003 | 'R' | '2012-01-02 12:53:10' | 1500
3 | 003 | 'A' | '2012-01-03 13:10:01' | -1500
4 | 002 | 'R' | '2012-01-03 17:56:00' | 2000
5 | 001 | 'R' | '2012-01-04 12:30:01' | 1000
6 | 002 | 'A' | '2012-01-04 13:23:01' | -2000
7 | 003 | 'R' | '2012-01-04 15:13:10' | 3000
8 | 003 | 'R' | '2012-01-05 12:12:00' | 1250
9 | 003 | 'A' | '2012-01-06 17:24:01' | -1250
dan saya ingin memilih semua jenis tertentu ('R'), tetapi bukan yang segera (dalam urutan bidang tanggal_waktu) memiliki transaksi lain dari jenis lain ('A') untuk akun yang sama yang diajukan...
Jadi, kueri harus menampilkan baris berikut, dengan mempertimbangkan contoh sebelumnya:
id | account |type | date | amount
----------------------------------------------------
1 | 001 | 'R' | '2012-01-01 10:01:00' | 1000
5 | 001 | 'R' | '2012-01-04 12:30:01' | 1000
7 | 003 | 'R' | '2012-01-04 15:13:10' | 3000
(Seperti yang Anda lihat, baris 2 tidak ditampilkan karena baris 3 'membatalkan'... juga baris 4 'dibatalkan' oleh baris 6'; Baris 7 muncul (walaupun akun 003 milik baris #2 yang dibatalkan , kali ini di baris 7 tidak dibatalkan oleh baris 'A' mana pun); Dan baris 8 tidak akan muncul (juga untuk akun 003 karena sekarang yang ini dibatalkan oleh 9, yang tidak membatalkan 7 juga, hanya yang sebelumnya satu: 8...
Saya telah mencoba Gabung, subkueri di klausa Where tetapi saya benar-benar tidak yakin bagaimana cara membuat kueri saya...
Apa yang saya coba:
Mencoba bergabung:
SELECT trans.type as type,
trans.amount as amount,
trans.date_time as dt,
trans.account as acct,
FROM Transactions trans
INNER JOIN ( SELECT t.type AS type, t.acct AS acct, t.date_time AS date_time
FROM Transactions t
WHERE t.date_time > trans.date_time
ORDER BY t.date_time DESC
) AS nextTrans
ON nextTrans.acct = trans.acct
WHERE trans.type IN ('R')
AND nextTrans.type NOT IN ('A')
ORDER BY DATE(trans.date_time) ASC
Ini menimbulkan kesalahan, karena saya tidak bisa memasukkan nilai eksternal ke GABUNG di MySQL.
Mencoba subquery di mana:
SELECT trans.type as type,
trans.amount as amount,
trans.date_time as dt,
trans.account as acct,
FROM Transactions trans
WHERE trans.type IN ('R')
AND trans.datetime <
( SELECT t.date_time AS date_time
FROM Transactions t
WHERE t.account = trans.account
ORDER BY t.date_time DESC
) AS nextTrans
ON nextTrans.acct = trans.acct
ORDER BY DATE(trans.date_time) ASC
Ini salah, saya dapat memasukkan nilai eksternal ke WHERE di MySQL tetapi saya tidak dapat menemukan cara untuk memfilter dengan benar untuk apa yang saya butuhkan...
EDIT PENTING:
Saya berhasil mencapai solusi, tetapi sekarang memerlukan optimasi yang serius. Ini dia:
SELECT *
FROM (SELECT t1.*, tFlagged.id AS cancId, tFlagged.type AS cancFlag
FROM transactions t1
LEFT JOIN (SELECT t2.*
FROM transactions t2
ORDER BY t2.date_time ASC ) tFlagged
ON (t1.account=tFlagged.account
AND
t1.date_time < tFlagged.date_time)
WHERE t1.type = 'R'
GROUP BY t1.id) tCanc
WHERE tCanc.cancFlag IS NULL
OR tCanc.cancFlag <> 'A'
Saya bergabung dengan tabel itu sendiri, hanya mempertimbangkan akun yang sama dan tanggal_waktu yang bagus. Gabung diurutkan berdasarkan tanggal_waktu. Pengelompokan berdasarkan id Saya hanya berhasil mendapatkan hasil join pertama, yang kebetulan merupakan transaksi berikutnya untuk akun yang sama.
Kemudian pada pilihan luar, saya memfilter yang memiliki 'A', karena itu berarti transaksi berikutnya secara efektif merupakan pembatalan. Dengan kata lain, jika tidak ada transaksi berikutnya untuk akun yang sama atau jika transaksi berikutnya adalah 'R', maka tidak dibatalkan dan harus ditampilkan di hasilnya...
Saya mengerti:
+----+---------+------+---------------------+--------+--------+----------+
| id | account | type | date_time | amount | cancId | cancFlag |
+----+---------+------+---------------------+--------+--------+----------+
| 1 | 001 | R | 2012-01-01 10:01:00 | 1000 | 5 | R |
| 5 | 001 | R | 2012-01-04 12:30:01 | 1000 | NULL | NULL |
| 7 | 003 | R | 2012-01-04 15:13:10 | 3000 | 8 | R |
+----+---------+------+---------------------+--------+--------+----------+
Ini menghubungkan setiap transaksi dengan transaksi berikutnya tepat waktu untuk akun yang sama dan kemudian menyaring transaksi yang telah dibatalkan... Sukses!!
Seperti yang saya katakan, masalahnya sekarang adalah optimasi. Data asli saya memiliki banyak baris (seperti yang diharapkan dimiliki oleh tabel yang menyimpan transaksi sepanjang waktu), dan untuk tabel ~10.000 baris saat ini, saya mendapatkan hasil positif dengan kueri ini dalam 1 menit.44 detik. Saya kira itulah masalahnya dengan bergabung... (Bagi mereka yang mengetahui protokol di sini, apa yang harus saya lakukan? meluncurkan pertanyaan baru di sini dan memposting ini sebagai solusi untuk pertanyaan ini? Atau tunggu saja jawaban lainnya di sini?)